plsql基础二之集合
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;