plsql中的集合

集合有3种类型：1索引表2嵌套表3变长数组

索引表语法：type type_name as table of element_type [not null] index by [pls_integer|binary_integer|varchar2(size)]

type deptno is table of dept.deptno%type not null index by pls_integer;

定义并操作索引表

declare
type idx_table is table of varchar(12) index by pls_integer;
v_emp idx_table;
begin
  v_emp(1):='smith';
  v_emp(2):='clark';
  v_emp(10):='jack';
  dbms_output.put_line(v_emp(1));
  dbms_output.put_line(v_emp(10));
  if v_emp.exists(3) then
  dbms_output.put_line(v_emp(3));
  end if;
  end;
  

declare
  type idx_object_table is table of t_objects%rowtype index by t_objects.object_name%type;
  v_obj idx_object_table;
  cursor obj_cur is
    select * from t_objects;
begin
  for objrow in obj_cur loop
    v_obj(objrow.object_name) := objrow;
    dbms_output.put_line(v_obj(objrow.object_name).object_id||v_obj(objrow.object_name).object_type);
  end loop;
end;

嵌套表是对索引表的扩展，可以存储到数据库中，索引表只是内存中的表，必须初始化。很像java中的类

type type_name as table of element_type [not null]

declare
  type emp_name_table is table of varchar2(20);
  type deptno_table is table of number(2);
  deptno_info   deptno_table;
  emp_name_info emp_name_table := mep_name_table('zhang', 'li');
begin
  dbms_output.put_line(emp_name_info(1));
  if deptno_info is null then
    deptno_info := deptno_table();
  end if;
  deptno_info.extend(5);
  for i in 1 .. 5 loop
    deptno_info(i) := i * 10;
  end loop;
end;

嵌套表放到数据库中的表中

create type empname_type is table of varchar2(20);
create table dept_nested(deptno number(2),dname varchar2(20),emplist empname_type)nested table emplist store as empname_table;
declare
  emp_list empname_type := empname_type('smith', 'jack', 'bush');
begin
  insert into dept_nested values (10, 'haha', emp_list);
  insert into dept_nested values (20, 'aa', empname_type('li', 'zhang'));
  select emplist into emp_list from dept_nested where deptno = 10;
  emp_list(1) := 'a';
  emp_list(2) := 'b';
  update dept_nested set emplist = emp_list where deptno = 10;
end;

查看结果的时候emplist列是collection

1 10 haha <Collection>
2 20 aa<Collection>

查看第一条记录的结果

1 a
2 b
3 bush

在sqlplus中查看的结果

SQL> select * from dept_nested;


    DEPTNO DNAME                EMPLIST
---------- -------------------- ------------------------------
        10 haha                 EMPNAME_TYPE('a', 'b', 'bush')
        20 aa                   EMPNAME_TYPE('li', 'zhang')

嵌套表列的内容是单独存储的，数据库表中的列只是一个引用，必须通过sql语句来操作存储表中的数据。

3变长数组，是指数组长度可变的数组。

type type_name is {varray|varing array} (size_limit) of element_type[not null];也可以被存储在数据库中

declare
  type projectlist is varray(50) of varchar2(16);
  type empno_type is varray(10) of number(4);
  project_list projectlist := projectlist('site', 'erp', 'crm', 'cms');
  empno_list   empno_type;
begin
  dbms_output.put_line(project_list(3));
  project_list.extend;
  project_list(5) := 'workflow';
  empno_list := empno_type(123, 232, 3454);
  dbms_output.put_line(empno_list(2));
end;
   

集合的方法

declare
  type projectlist is varray(50) of varchar2(16);
  project_list projectlist := projectlist('erp', 'crm');
begin
  if project_list.exists(5) then
    dbms_output.put_line('the value is ' + project_list(5));
  else
    dbms_output.put_line('the elements does not exists');
  end if;
  dbms_output.put_line(project_list.count);
  project_list.extend(10);
  dbms_output.put_line(project_list.count);
  dbms_output.put_line(project_list.limit);
  dbms_output.put_line(project_list.first);
  dbms_output.put_line(project_list.last);
  project_list.trim;--删除最后的元素
  project_list.delete(10);--删除第n个元素
  dbms_output.put_line(project_list.count);

end;

在plsql中最好使用forall使plsql能够将sql语句批量的交给sql引擎，能够提高效率,forall是用来批量绑定多个集合的变量到sql引擎，bulk collect关键字是可以批量地从sql引擎中批量接受数据到一个集合。

declare
  type dept_type is varray(20) of number;
  depts dept_type := dept_type(10, 20, 30);
begin
  forall i in depts.first .. depts.last
    delete from emp where deptno = depts(i);
  for i in 1 .. depts.count loop
    dbms_output.put_line(depts(i)) || ',' || sql%bulk_rowcount(i));
  end loop;
end;

declare
  type numtab is table of emp.empno%type;
  type nametab is table of emp.ename%type;
  nums  number;
  names nametab;
begin
  seect empno, ename bulk
    into nums, names from emp;
  for i in 1 .. nums.count loop
    dbms_out.put_line(nums(i));
  end loop;
end;