pl/sql 集合是存放同种类型数据的集合,集合类型有三种,索引表、嵌套表、变长数组。
集合的属性和方法:
- first:取集合第一个元素的下标
- last:取集合最后一个元素的下标
- count:取集合中存放的元素个数(实际存放数据的个数)
- limit:取集合能存放的数据个数(集合的长度,用于变长数组,索引表和嵌套表是无限长度)
- next(下标):取当前下标的下一个元素的下标
- prior(下标):取当前下标的上一个元素的下标
- extend(n,index):表示将集合扩展n个元素,index表示集合已有元素的下标,如果有第二个参数,表示扩展集合n个元素并且扩展后的对应值是index这个下标所对应的值
- delete(n):删除集合中的元素
1.索引表
索引表其实相当于python中的简单字典,只是索引表的索引是隐藏的。它的索引值可以是字符串也可以是数字类型。
索引表的使用:
- 定义索引表
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;