plsql集合

1.集合类型

集合(collection)：它是存放一组数据类型相同的数据，是相同类型元素的组合

集合数据类型分类:

1.索引表(index by tables):或者叫做关联数组(associative arrays)

2.嵌套表(nested table)

3.可变长度数组(varray:variable-size arrays)

2.集合的属性和方法

1.first取集合的第一个元素的下标                     

2.last取集合的最后一个元素的下标

3.count 取集合的总长度

4.limit 取集合元素下标的最大值  (索引表和嵌套表是不限个数的，所以返回null，变长数组返回定义时的最大索引 )

5.delete([n]) 删除集合中的元素，加n表示下标，删除对应下标的值

6.extend(n[,ind]):扩展集合元素 n是一个数字，表示扩展的元素个数，ind是集合中的一个元素的下标，加上它表示扩展集合时，给扩展的元素加上值，值是ind这个下标对应的元素

7.next(下标)取当前元素下一个元素的下标

8.prior(下标)取当前元素上一个元素的下标

3.索引表

使用整数（可为负）（pls_integer,binary_integer）或字符串来作为下标,下标可以不连续;元素个数无限制,只能用在PLSQL中,不可以存储在数据库中.

定义索引表类型的语法：

1.声明索引表类型

type 类型名称 is table of 数据类型(是集合中值的数据类型)index by 下标的数据类型（varchar2,pls_integer,binary_integer）;

type mytype is table of varchar2(200) index by pls_integer;

2声名一个集合变量

变量名 类型名

  v  mytype;

集合中数据的存取：

集合变量(下标)  

v(下标);

v(下标):=值;

declare

   --声名一个索引表类型

   type mytype is table of varchar2(200) index by binary_integer;

   --声名一个索引表类型变量

   tab mytype;

   --声名一个变量保存索引表的下标

   ind varchar2(20);

begin

   tab(1):='张三';

   tab(-2):='李四';

   tab(0):='王五';

   ind:=tab.first;

   ---遍历集合

   loop

      --打印集合元素

      dbms_output.put_line(tab(ind));

      --退出条件

      exit when ind=tab.last;

      ind:=tab.next(ind);

   end loop;

end;    

4.嵌套表

使用整数（只能为正）作为下标，下标是连续的；元素个数无限制，可以用在plsql中，也可以存储在数据库中

定义嵌套表类型：

type 类型名称 is table of 数据类型（存储的数据的数据类型）；

变量声明；

变量名 类型名称

数据的存取和索引表相同，嵌套表在赋值之前需要初始化

嵌套表的初始化：

       变量名：=类型名（）；--初始化

       变量名：=类型名（值，值，值）；

declare

--声明一个嵌套表类型，存放字符串数据

type tabType is table of varchar2(200);

--声明变量

tab1 tabType;

tab2 tabType;

--声明一个变量保存嵌套表的下标

ind pls_integer

begin

--初始化嵌套表

tab1:=tabType();

--打印嵌套表tab1的长度

dbms_output.put_line('tab1的长度'||tab1.count);

--初始化嵌套表tab2

tab2:=tabType('a','b','c','d');

--打印嵌套表tab2的长度

dbms_output.put_line('tab2的长度‘||tab2.count);

 ---遍历集合

   ind := 1;

   loop

      --打印集合元素

      dbms_output.put_line(tab2(ind));

      --退出条件

      exit when ind=tab2.last;

      ind:=tab2.next(ind);

   end loop;

嵌套表在数据库中的使用：

create type 类型名称 is table of 数据类型;

create type tabType is table of varchar2(30);

declare

   --声名一个tabType类型的嵌套表变量

   v_tab tabType;

begin

   --初始化

   v_tab:=tabType('1','2','3');

   --遍历

   for i in v_tab.first..v_tab.last loop

     dbms_output.put_line(v_tab(i));

   end loop;

end;

建表时使用嵌套表类型

create table 表名(

   列 数据类型,

   自定义列名 嵌套表类型

)nested table 嵌套表类型列名 store as 表名（是数据库中没有的表）;

create table tab(

   deptno number(10),

   namelist tabType

)nested table namelist store as emp_names;

5.变长数组

使用整数（只能为正）（pls_integer,binary_integer）作为下标，下标是连续的;元素个数有限制的，可以用在PLSQL中,也可以存储在数据库中

变长数组类型的声明

type 类型名称 is varray|varying array(长度) of 数据类型（保存的数据的数据类型）;

变量名 类型名;

使用和嵌套表一样，使用前需要初始化

变量：=类型();  

变量：=类型(值,值,值,值,值);

 --声名一个数组变量

   arr arrType;

begin

   --初始化

   arr:=arrType('a','b','c');

   --打印数组的长度

   dbms_output.put_line('数组的长度'||arr.count);

   arr.extend(7);

   --打印数组的长度

   dbms_output.put_line('数组的长度'||arr.count);

end;

变长数组在数据库中的使用

create type 类型名称 is varray|varying array(长度) of 数据类型;

create table 表名(

   列名 数据类型,

   数组列 数组类型

);

insert into 表名(列名,数组列名) values(值,数组类型(值,值,值));

select * from table(select 数组列 from 表名 where 条件);

6.bulk collect

bulk collect:可以把一组数据取出来存入一个集合类型变量中

select  ... into 变量：只能查出一条数据保存到变量中

select ... bulk collect into 集合类型变量：可以查出多条数据存入一个变量中

--查询所有员工的名字

declare

   --声名一个集合类型

   type tabType is table of emp.ename%type;

   --声名一个嵌套表变量

   namelist tabType;

begin

   --注意：这里是不需要初始化集合变量 namelist:=tabType();

   select ename bulk collect into namelist from emp;

   --打印员工姓名

   for i in namelist.first..namelist.last loop

       dbms_output.put_line(namelist(i));

   end loop;

   

end;

7.批量绑定

forall 变量 in 集合

   sql语句;--增insert、删delete、改update

--根据部门编号删除部门下的员工

declare

begin

   for v_dept in (select * from dept) loop

      delete from emp where deptno=v_dept.deptno;

   end loop;

end;

declare

declare

   --声名一个集合类型

   type tabType is table of dept.deptno%type;

   --声名一个变量

   dno tabType;

begin

  --将所有的部门编号放入dno中

  select deptno bulk collect into dno from dept;

  

  forall i in dno.first..dno.last

     delete from emp where deptno=dno(i);

end;