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;