oracle 记录,嵌套表,集合

jvdinujmrq

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)

---使用数组将查询的记录存放到嵌套表中
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;