Oracle导出嵌套表,oracle 记要,嵌套表,集合

oracle 记录,嵌套表,集合

1.exists 检查集合中是否存在某个元素,collection.exists(index)

2.count 返回集合中元素的个数 collection.count

3.limit 返回varray中最大的条目数,关联数组和嵌套表返回空

4.first 返回集合的第一个元素

5.last 返回集合的最后一个元素

6.next 返回集合中的下一个元素,如果不存在返回空 collection.next

7.prior 返回集合中的上一个元素,如果不存在返回空

8.extend 扩展集合中元素的个数,不用于关联数组,和未被初始化的集合

collection.extend,collection.extend(n),collection.extend(n,i)

9.trim 从集合的结尾处删除元素

collection.trim(),collection.trim(n)

10.delete 从集合中删除元素 collection.delete,collection.delete(n),collection.delete(m,n)[i][/i]

---使用数组将查询的记录存放到嵌套表中

declare

type deptrecord is record

(

deptno dept.deptno%type,

dname dept.dname%type,

loc dept.loc%type

);

type number_array is table of deptrecord;

number_collection number_array:=number_array();

begin

number_collection.extend(5);

dbms_output.put_line('number_collection的数量：'||number_collection.count);

select * bulk collect into number_collection from dept;

dbms_output.put_line('number_collection的数量：'||number_collection.count);

for currow in number_collection.first .. number_collection.last loop

dbms_output.put_line(number_collection(currow).deptno);

end loop;

end;

---使用数组将查询的记录存放到嵌套表中

declare

type deptrecord is record(

deptno dept.deptno%type,

dname dept.dname%type,

loc dept.loc%type);

type number_array is table of deptrecord;

number_collection number_array := number_array();

i number := 0;

curroww number;

begin

for currow in (select * from dept) loop

i := i + 1;

number_collection.extend;

number_collection(i).deptno := currow.deptno;

number_collection(i).dname := currow.dname;

number_collection(i).loc := currow.loc;

end loop;

for i in 1 .. number_collection.count loop

dbms_output.put_line('');

dbms_output.put_line('循环方式一');

dbms_output.put_line('deptno:=' || number_collection(i).deptno);

dbms_output.put_line('dname:=' || number_collection(i).dname);

dbms_output.put_line('loc:=' || number_collection(i).loc);

end loop;

for i in number_collection.first .. number_collection.last loop

dbms_output.put_line('');

dbms_output.put_line('循环方式二');

dbms_output.put_line('deptno:=' || number_collection(i).deptno);

dbms_output.put_line('dname:=' || number_collection(i).dname);

dbms_output.put_line('loc:=' || number_collection(i).loc);

end loop;

curroww := number_collection.first;

loop

exit when curroww is null;

dbms_output.put_line('');

dbms_output.put_line('循环方式三');

dbms_output.put_line('deptno:=' || number_collection(i).deptno);

dbms_output.put_line('dname:=' || number_collection(i).dname);

dbms_output.put_line('loc:=' || number_collection(i).loc);

curroww := number_collection.next(curroww);

end loop;

end;