plsql基础二之集合

pl/sql 集合是存放同种类型数据的集合，集合类型有三种，索引表、嵌套表、变长数组。

 

集合的属性和方法：

• first:取集合第一个元素的下标
• last:取集合最后一个元素的下标
• count:取集合中存放的元素个数（实际存放数据的个数）
• limit:取集合能存放的数据个数（集合的长度，用于变长数组，索引表和嵌套表是无限长度）
• next(下标):取当前下标的下一个元素的下标
• prior(下标):取当前下标的上一个元素的下标
• extend(n,index):表示将集合扩展n个元素，index表示集合已有元素的下标，如果有第二个参数，表示扩展集合n个元素并且扩展后的对应值是index这个下标所对应的值
• delete(n):删除集合中的元素

1.索引表

索引表其实相当于python中的简单字典，只是索引表的索引是隐藏的。它的索引值可以是字符串也可以是数字类型。

索引表的使用：

1. 定义索引表

　　　　　　type  类型名 is  table of  数据类型  index by 索引数据类型；

　　　　　　变量名  类型名；

例：　

declare
    type ind is table of  varchar2(20)  index by pls_integer;
    v ind;
begin
    --赋值
    v(1):= 'a';
    v(2):= 'b';
    v(3):= 'c';
    --输出
    for i in v.first..v.last loop     --如果是loop或者是while循环，则可以用到 next(i) 和 prior（i）       
        dbms_output.put_line(v(i));     -- i:=v.next(i) 下面给出例子
    end loop;
    dbms_output.put_line(v.count);
end;

 

上述例子扩展
declare
    type ind is table of  VARCHAR2(20)  index by pls_integer;
    v ind;
    i INT(2):=1; --定义
begin
    --赋值
    v(1):= 'a';
    v(2):= 'b';
    v(3):= 'c';
    --输出
    LOOP
        dbms_output.put_line(v(i));
        EXIT WHEN i=v.last;
        i:=v.NEXT(i);
    end loop;
    dbms_output.put_line(v.count);
end;

2.嵌套表

嵌套表不需要声明索引的类型，它只能用整型，所以默认。

 定义：

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

　　　　变量名  类型名；

使用嵌套表时，需要初始化和扩展；

declare
     type ind is table of varchar2(10);
     v ind;
begin
     v:=ind('a','b','c'); ---初始化同时可以赋初始值
     v.extend(3);  --没对扩展的内存赋值时，这些值就是空的
     for i in v.first..v.last loop
         dbms_output.put_line(v(i));
     end loop;
end;

结果是
a
b
c
            --后面都是空值，当然扩展的时候可以随意赋值现有的，或者循环赋值
            --现在的总长度为初始的长+扩展的长度

3.变长数组

       定义：

　　　　　　type 类型名  is varray(默认长度) of  数据类型；

　　　　　　变量名 类型名；

declare
    type var is varray(30) of varchar2(10);
    v var;
    n number(2):=65;
BEGIN
    v:=var();  ---初始化
    v.extend(10); ---扩展的长度不能超过定义的默认长度
    for i in 1..10 loop  --循环赋值的最大值也不能超多初始化的长度
       v(i):=CHR(n);
       n:=n+1;
    END loop;
    for j in v.first ..v.last loop
        dbms_output.put_line(v(j));
    end loop;
end;

 

4.嵌套表变量的使用（索引表不能在数据库中使用）

create type  类型  is  table  of  数据类型；

create type tab1 is table of varchar2(10);

declare
    v  tab1;
begin
    --初始化
    v:=tab1('a','b','c');
    for i in v.first..v.last loop
        dbms_output.put_line(v(i));
    end loop;
end;

create table 表名(

　　变量名  类型  约束，

　　...

　　嵌套表变量  嵌套表类型

)nested table 嵌套表名  store as 数据库中没有的表名；

例：

create table tab(
   id  number(11) primary key,
   create_time date,
   namelist tabType
)nested table namelist store as names;

insert into tab(id,create_time,namelist) values(1,sysdate,tabType('smith','john','lucy'));

insert into tab(id,create_time,namelist) values(2,sysdate,tabType('smith','john','lucy'));

select * from tab;


select * from table(select namelist from tab where id=1);--子查询只能有一条结果

 

5.变长数组在数据库的使用

create type 类型名 is varray(默认长度) of 存储的数据的数据类型;

create type arrtype is varray(10) of varchar2(30);
变长数组类型在数据库中的使用和普通类型一样
create table arr(
    id number(11) primary key,
    create_time date,
    namelist arrtype
);

insert into arr(id,create_time,namelist) values(1,sysdate,arrtype('张三','李四','王五'));

select * from arr;

select * from table(select namelist from arr where id=1);

 

6.bulk collect

select ... bulk collect into 集合变量; --可以查出多条记录，集合变量如果是嵌套表或者变长数组时（是不需要初始化的）;

execute immediate select语句 bulk collect into 集合变量;

declare
   --声名一个嵌套表类型
   type ttype is table of varchar2(30);
   --声名一个变量
   tab ttype;
   job ttype;
begin
   --根据输入的部门编号，查出部门下所有员工的姓名和工作
   select ename,job bulk collect into tab,job from emp where deptno=&deptno;
   --遍历集合打印
   for i in tab.first..tab.last loop
     dbms_output.put_line(tab(i)||','||job(i));
     
   end loop;
end;

 

7.forall 批量绑定

就是for 循环   没有loop和end loop

declare
   type ttype is table of number(10);
   empno_list ttype;
begin
   select empno bulk collect into empno_list from emp where deptno=&deptno;
   forall i in empno_list.first..empno_list.last 
      delete from emp where empno=empno_list(i);
end;