create or replace type wordtype as object (z varchar2(50));
/
create or replace type word as table of wordtype
/
/
create or replace type word as table of wordtype
/
解决方案 »
- 问几个问题,关于重命名as和like
- 新手问 11g安装很多遍,出现三个home,怎么删除前两个?
- 在Oracle中不用组函数(max().....,order by)求一列的最大值(要讲究效率)
- 使用vb + oralce 开发,遇到一个update的问题,在线等
- 大型网站的数据库配置是怎样的?
- 游标问题,请高手指教!
- 请问高人,如何看STATSPACK报告中的一些参数?
- 如何将FRXPRO的数据倒入到ORALCE中
- 求一个sql语句的写法
- 安装问题,“凭证检索失败”,请朋友指教
- 十萬火急!!!Oracle控制文件和日志文件丟失﹐只剩數據文件﹐請問如何恢復數據庫
- 请问oracle把审计保存到操作系统审计中,结果怎么查?
word&wordtype怎么使用,能否详细一些,谢谢!to beckhambobo(beckham) and luckysxn(风花雪) :
嵌套表怎么使用,能否给一个例子?谢谢!
DECLARE
TYPE Strings IS VARRAY(5) OF VARCHAR2(10);
-- Declare a varray with three elements
v_List Strings := Strings('One', 'Two', 'Three');
BEGIN
-- Subscript between 1 and 3, so this is legal.
v_List(2) := 'TWO';
-- Subscript beyond count, raises ORA-6533.
v_List(4) := 'bbb';
END;
create or replace function testrerecordnotabname (tableid in number)
return word
as
l_data mytabletype :=word();
begin
for i in (select * from a where id>=tableid) loop
l_data.extend;
l_data(l_data.count) := wordtype (i.z);
exit when i.id = 62;
end loop;
return l_data;
end;
/
commit;
其实索引表,嵌套表,数组都是oracle collection的例子
其中索引表是最灵活的,但是不能用于数据表.
----------------------
CREATE OR REPLACE PACKAGE packperson
AS
TYPE tssn is TABLE of NUMBER(10)
INDEX BY BINARY_INTEGER;
TYPE tfname is TABLE of VARCHAR2(15)
INDEX BY BINARY_INTEGER;
TYPE tlname is TABLE of VARCHAR2(20)
INDEX BY BINARY_INTEGER;
PROCEDURE allperson
(ssn OUT tssn,
fname OUT tfname,
lname OUT tlname);
PROCEDURE oneperson
(onessn IN NUMBER,
ssn OUT tssn,
fname OUT tfname,
lname OUT tlname);
END packperson;
/
Create the following package body on your Oracle server:
CREATE OR REPLACE PACKAGE BODY packperson
AS
PROCEDURE allperson
(ssn OUT tssn,
fname OUT tfname,
lname OUT tlname)
IS
CURSOR person_cur IS
SELECT ssn, fname, lname
FROM person;
percount NUMBER DEFAULT 1;
BEGIN
FOR singleperson IN person_cur
LOOP
ssn(percount) := singleperson.ssn;
fname(percount) := singleperson.fname;
lname(percount) := singleperson.lname;
percount := percount + 1;
END LOOP;
END;
PROCEDURE oneperson
(onessn IN NUMBER,
ssn OUT tssn,
fname OUT tfname,
lname OUT tlname)
IS
CURSOR person_cur IS
SELECT ssn, fname, lname
FROM person
WHERE ssn = onessn;
percount NUMBER DEFAULT 1;
BEGIN
FOR singleperson IN person_cur
LOOP
ssn(percount) := singleperson.ssn;
fname(percount) := singleperson.fname;
lname(percount) := singleperson.lname;
percount := percount + 1;
END LOOP;
END;
END;
TYPE Strings IS VARRAY(5) OF VARCHAR2(10);
Strings能否被用作表的字段类型?
/
create table library_catalog(
catalog_number number(4),
foreign dey(catalog_number) references books(catalog_number),
num_copies number,
checked_out studentlist)
nested table checked_out store as co_tab;
/
注意:最后一行的作用.