PLSQL——集合（索引表、嵌套表、变长数组、bulk collect、批量绑定）

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.索引表

1. 使用整数（可为负）（pls_integer,binary_integer）或字符串来作为下标,
2. 下标可以不连续;
3. 元素个数无限制,
4. 只能用在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;    

declare
   --声名记录类型
   type recType is record(
       name varchar2(30),
       sex varchar2(3)
   );
   --声名一个索引表类型
   type indType is table of recType index by pls_integer;
   --声名一个变量
   ind  indType;
   --声名一个记录类型变量
   rec recType;
   --声名一个记录类型变量
   rec2 recType;
begin
   --给记录类型赋值
   rec.name:='张三';
   rec.sex:='男';
   --给集合中添加一个元素
   ind(1):=rec;
   --取集合中的值 
   rec2:=ind(1);---取出来的是一个记录类型数据
   dbms_output.put_line(rec2.name||','||rec2.sex);
end;

4.嵌套表

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

定义嵌套表类型：
type 类型名称 is table of 数据类型（存储的数据的数据类型）;
变量声名：
变量名 类型名称1.集合类型

嵌套表在赋值之前需要初始化
嵌套表的初始化：
变量名:=类型名(); --初始化
变量名:=类型名(值,值,值);

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--loop循环
      --打印集合元素
      dbms_output.put_line(tab2(ind));
      --退出条件
      exit when ind=tab2.last;
      ind:=tab2.next(ind);
   end loop;
   
  for i in tab2.first..tab2.last loop--for循环
    dbms_output.put_line(tab2(i));
  end loop;
end;

嵌套表在数据库中的使用：
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;

insert into tab(deptno,namelist) values(10,嵌套表类型('SMITH','JSON','EDISION'));

insert into tab(deptno,namelist) values(20,tabType('SMI','SON','SION'));

select * from tab;
select * from table(select namelist from tab where deptno=20);
先创建嵌套表类型再建表
删除：先删除表，再删除嵌套表类型
Drop type 类型名字;

5.变长数组

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

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

变量名 类型名;

使用和嵌套表一样，使用前需要初始化
变量：=类型();
变量：=类型(值,值,值,值,值);

declare
   --声名一个变长数组类型
   type arrType is varray(10) of varchar2(20);
   --声名一个数组变量
   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; 

fetch 游标 bulk collect into 集合类型变量;

-- 查询员工的信息
declare
   --声名一个游标变量
   cursor cur is select * from emp;
   --声名一个集合类型
   type tabType is table of emp%rowtype;
   --声名一个变量
   emplist tabType;
begin
   --打开游标
   open cur;
   fetch cur bulk collect into emplist;
   --关闭游标
   close cur;
   for i in emplist.first..emplist.last loop
     dbms_output.put_line(emplist(i).ename);
   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
   --声名一个集合类型
   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;