PL/SQL集合详解

集合类型

/*
集合的定义：
  集合是相同类型元素的组合。在集合中，使用唯一的下标来标识其中的每个元素。
  
而集合又分为以下几个类型：
1、索引表也叫关联数组
2、嵌套表
3、可变长度数组
*/

索引表

/*
索引表：
	可以使用整数（pls_integer,binary_integer）或字符串当下标，下标可以是不连续的。
	索引表的元素的个数是无限制的
	索引表只能用在plsql中，不能保存再数据库
	
定义索引表类型的语法:
	type 类型名称 is table of 数据类型（是集合中值的数据类型） index by 下标的数据类型
声明一个集合变量：
	集合变量的名字  集合的类型；
集合中数据的存取:
	集合变量(下标)
*/
declare
	-- 定义索引表类型
	type indextable is table of varchar2(20) index by pls_integer;
	-- 声明集合变量
	v_indextable indextable;
begin
	-- 使用：集合变量（下标） 存储值或者赋值
	v_indextable(1):='a';
	v_indextable(3):='b';
	v_indextable(5):='c';
	v_indextable(-8):='d';
	-- 取值:集合变量（下标）
	dbms_output.put_line(v_indextable(1));
	dbms_output.put_line(v_indextable(3));
	dbms_output.put_line(v_indextable(5));
	dbms_output.put_line(v_indextable(-8));
end;

集合的属性和方法

/*
集合名字.属性名   集合名字.方法名
1.first取集合的第一元素的下标                     
2.last取集合的最后一个元素的下标
3.count 取集合的总长度 
4.limit 取集合元素索引的最大值  (索引表和嵌套表是不限个数的，所以返回null，变长数组返回定义时的最大索引 )
5.delete([n]) 删除集合中的元素，加n表示下标，删除对应下标的值
6.extend(n[,ind]):扩展集合元素 n是一个数字，表示扩展的元素个数，ind是集合中的一个元素的下标，加上它表示扩展集合时，给扩展的元素加上值，值是ind这个下标对应的元素
7.next(下标)取当前元素下一个元素的下标
8.prior(下标)取当前元素上一个元素的下标 
*/
declare
	-- 定义索引表类型
	type indextable is table of varchar2(20) index by pls_integer;
	-- 声明集合变量
	v_indextable indextable;
begin
	-- 使用：集合变量（下标） 存储值或者赋值
	v_indextable(1):='a';
	v_indextable(3):='b';
	v_indextable(5):='c';
	v_indextable(-8):='d';
	
	-- 取值:集合变量（下标）
	dbms_output.put_line(v_indextable.first);
	dbms_output.put_line(v_indextable.last);
	dbms_output.put_line(v_indextable.count);
	--dbms_output.put_line(v_indextable.limit);
	if v_indextable.limit is null then
		dbms_output.put_line('v_indextable.limit返回的是个null');
	end if;
	v_indextable.dalete(); -- delete()不写下标则删除全部，写下表则表示删除那一个，被删除的下标则不能再次访问
	dbms_output.put_line(v_indextable.count);
	v_indextable.extend(2);-- 表示扩展俩个集合元素，但是索引表是无限值的，所以不能扩展
	dbms_output.put_line(v_indextable.next(-8));-- 返回下一个元素的下标
	dbms_output.put_line(v_indextable.prior(-8)); -- 返回上一个小标
end;

遍历索引表集合

/*
loop
*/
declare
  -- 定义索引表类型
  type indextable is table of varchar2(20) index by pls_integer;
  -- 声明集合变量
  v_indextable indextable;
  -- 声明变量存储下标
  v_index pls_integer;
begin
  -- 使用：集合变量（下标） 存储值或者赋值
  -- 下标不存在就是添加，如果存在就是修改
  v_indextable(1):='a';
  v_indextable(3):='b';
  v_indextable(5):='c';
  v_indextable(-8):='d';
  -- 从前往后
  v_index:=v_indextable.first;-- 获取第一个下标
  loop
    dbms_output.put_line(v_index);
    dbms_output.put_line(v_indextable(v_index));-- 遍历值
    exit when v_index=v_indextable.last;
    v_index:=v_indextable.next(v_index);
  end loop;
  /*
  从后往前
  v_index:=v_indextable.last;-- 获取最后一个下标
  loop
    dbms_output.put_line(v_index);
    dbms_output.put_line(v_indextable(v_index));-- 遍历值
    exit when v_index=v_indextable.first;
    v_index:=v_indextable.prior(v_index);
  end loop;
  */
end;
/*
for  使用for循环遍历数组元素下标一定要是连续
*/
declare
  type index_table is table of number(21) index by pls_integer;
  t index_table;
begin
  t(1):=11;
  t(2):=23;
  t(3):=43;
  t(4):=22;
  t(5):=21;
  t(6):=56;
  t(7):=55;
  t(8):=32;
  dbms_output.put_line('for循环开始了');
  for j in t.first..t.last loop
    dbms_output.put_line(t(j));
  end loop;
end;

/*
while
*/
declare
  type index_table is table of number(21) index by pls_integer;
  t index_table;
  n number;
begin
  t(-1):=11;
  t(-2):=23;
  t(3):=43;
  t(1):=22;
  t(9):=21;
  t(20):=56;
  t(8):=55;
  t(10):=32;
  n:=t.first;
  while n<=t.last loop
    dbms_output.put_line(t(n));
    n:=t.next(n);
  end loop;
end;

/*
集合里面的存储元素也可以是记录类型
*/
declare
	-- 声明记录类型
	type type_emp is record(
    	ename emp.ename%type,
    v_emp type_emp;
    v_emp2 type_emp;
    -- 声明集合类型
    type mytable is table of type_emp index by pls_integer;
    -- 声明集合变量
	v mytable;    
begin
	v_emp.ename :='张三';
	v_emp.sal :='4500';
	-- 存放记录类型数据
	v(1):=v_emp;
	-- 取记录类型数据
	v_emp2 :=v(1);
	dbms_output.put_line(v_emp2.ename||','||v_emp2.sal);
end;

嵌套表

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

定义嵌套标类型：
	type 类型名称 is table of 数据类型（存储数据的数据类型）；
声明嵌套标类型变量：
	变量名 类型名称
	
数据的存取和索引表相同

嵌套表在赋值之前需要初始化
嵌套表的初始化：
	变量名:=类型名（）； -- 初始化
	变量名:=类型名(值，值，值);
*/
declare
	-- 定义嵌套表类型
 	type mytable is table of varchar2(20);
 	-- 声明变量
 	v mytable;
begin
	--为嵌套表初始化 
	v:=mytable('a');
	v(1):='aa';
	dbms_output.put_line(v(1));
end;

declare
	-- 定义嵌套表类型
 	type mytable is table of varchar2(20);
 	-- 声明变量
 	v1 mytable;
 	v2 mytable;
begin
	--为嵌套表初始化 
	v:=mytable('a');
	v(1):='aa';
	dbms_output.put_line(v(1));
	-- 扩展集合元素，默认值是null
	v.extend(2);  -- 扩展两个元素
	v.extend(2,1) -- 扩展两个元素，值是下标1的值
end;

遍历嵌套表

/*
loop
*/
declare
	-- 定义嵌套表类型
	type mytable is table of varchar2(20);
	-- 声明变量
	v1 mytable;
	-- 声明变量 下标
	n number;
begin
	v1:=mytable('a','b','c');
	n:=v1.first;
	loop
	dbms_output.put_line(n);
	exit when n=v1.last;
	n:=v1.next(n);
	end loop;
end;
/*
while
*/
declare
  -- 定义嵌套表类型
  type mytable is table of varchar2(20);
  -- 声明变量
  v mytable;
  n number;
begin
  v:=mytable('a','b','c');
  n:=v.first;
  while n<=v.last loop 
    dbms_output.put_line(v(n));	
	   n:=v.next(n);
  end loop;
end;

/*
for  因为他的下边是连续的
*/
declare
	-- 定义嵌套表类型
	type mytable is table of varchar2(20);
	-- 声明变量
	v1 mytable;
begin
	v1:=mytable('a','b','c');
	for n in v1.first..v1.last loop
		dbms_output.put_line(n);
	end loop;
end;

嵌套表在数据库中的使用

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

create type mytable is table of varchar2(20);
declare
	-- 声明变量 mytable是一个嵌套表类型（已经在我们数据库中）
	v mytable;
begin
	v:=mytable('a','abc','b');
	for n in v.first..v.last loop
		dbms_output.put_line(n);
	end loop;
end;

/*
创建表的时候也可以使用嵌套表类型
create table 表名(
	列  数据类型，
	嵌套表列  嵌套表类型
)nested table 嵌套表类型列名 store as 表名（是数据库中没有的表）；
*/
-- 创建
create table a(
	id number,
    namelist mytable
)nested table namelist store as myname;
-- 存值
insert into a(id,namelist) values(1,mytable('tom','jack','jerrt'));
-- 查询
select * from a;
-- 查询嵌套表中的数据
select * from table(select namelist from a where id =1);
-- 删除时要注意，先删除表在删除类型
drop table a;
drop type mytable;

变长数组

/*
使用整数(只能为正)(pls_integer,binary_integer)作为下标，下标也是连续的；
元素个数有限制的，可以使用在plsql中也可以存储在数据库中。
和嵌套表一样，使用前呢需要初始化
*/

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

declare
	-- 声明变长数组类型
	type myvar is varray(5) of varchar2(20);
	-- 声明变量
	v myvar;
begin
	v:=myvar('a','b','c');
	dbms_output.put_line(v(1));
	dbms_output.put_line(v(2));
	dbms_output.put_line(v(3));
	-- v.delete()  变长数组不能指定删除，
	-- v.extend() 扩展时不能超过声明时的长度
end;

遍历变长数组

/*
loop
*/
declare
	-- 声明变长数组类型
	type myvar is varray(5) of varchar2(20);
	-- 声明变量
	v myvar;
	-- 声明变量
	n pls_integer;
begin
	v:=myvar('a','b','c','d');
	n:=v.first;
	loop
		dbms_output.put_line(n);
		exit when n=v.last;
		n:=v.next(n);
	end loop;
end;
/*
for
*/
declare
	-- 声明变长数组类型
	type myvar is varray(5) of varchar2(20);
	-- 声明变量
	v myvar;
begin
	v:=myvar('a','b','c','d');
	for n in v.first..v.last loop
		dbms_output.put_line(n);
	end loop;
end;
/*
while
*/
declare
	-- 声明变长数组类型
	type myvar is varray(5) of varchar2(20);
	-- 声明变量
	v myvar;
	-- 声明变量
	n pls_integer;
begin
	v:=myvar('a','b','c','d');
	n:=v.first;
	while n<=v.last loop
		dbms_output.put_line(n);
		n:=v.next(n);
	end loop;
end;

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

/*
变长数组在数据库中的使用：
create type 类型名称 is varray(长度)|varying array(长度) of 数据类型;
*/
create type myvarray is varray(5) of varchar2(20);
declare
	-- 声明变量
	v myvarray;
begin
	v:=myvarray('a','b','c','d');
	for n in v.first..v.last loop
		dbms_output.put_line(n);
	end loop;
end;

/*
创建表
create table 表名(
	列名  数据类型，
	数组列 数组类型
);
*/
create table b(
	id number,
    abc myvarray
);
-- 查询
select * from b;
-- 增加
insert into b(id,abc) values(1,myvarray('a','b','c','d'));
select * from table(select abc from b where id=1);

bulk collect

/*
可以把一组数据取出来存入一个集合类型中
我们之前那个select...into 变量：只能查出一条数据保存到变量中
*/
declare
	v_ename emp.ename%type;
begin
	select ename into v_ename  from emp where empno=7369;
	dbms_output.put_line(v_ename);
end;
/*
而使用 select。。bulk collect into 集合类型的变量：
可以取多条数据存储在集合中
*/
declare
	v_ename emp.ename%type;
	-- 定义集合类型嵌套表
	type mytable is table of emp.ename%type;
	-- 定义集合变量
	v mytable;
begin
	select ename into v_ename from emp where empno=7369;
	dbms_output.put_line(v_ename);
	dbms_output.put_line('--------------');
	select ename bulk collect into v from emp;
	-- 遍历集合中所以的元素
	for n in v.first..v.last loop
		dbms_output.put_line(v(n));
	end loop;
end;

/*
bulk collect 还可以玩游标
fetch 游标 bulk collect into 集合类型变量
*/
declare
  -- 声明游标变量
  cursor cur_emp is select * from emp;
  -- 声明变量
  v_emp emp%rowtype;
begin
  open cur_emp;
  loop
    fetch cur_emp into  v_emp;
    exit when cur_emp%notfound;
    dbms_output.put_line(v_emp.empno);
  end loop;
  close cur_emp;
end;

declare
	-- 声明游标变量
	cursor cur_emp is select * from emp;
	-- 声明集合类型嵌套表
	type mytable is table of emp%rowtype;
	-- 声明集合变量
	v mytable;
begin
	open cur_emp;
		fetch cur_emp bulk collect into v;
		for n in v.first..v.last loop
			dbms_output.put_line(v(n).empno)
		end loop;
	close cur_emp;
end;

批量绑定

/*
格式：
forall 变量 in 集合
	sql语句：insert、delete、update
*/
-- 删除所有有部门编号的员工信息
declare
	-- 定义集合
	type mytable is table of dept.deptno%type;
	-- 定义集合变量
	v mytable;
begin
	select deptno bulk collect into v from dept;
	/*
	这是一种
	for n in v.first..v.last loop
		delete from emp where deptno = v(n);
	end loop;
	*/
	-- 这是forall
	forall n in v.first..v.last
		delete from emp where deptno = v(n);
		--update emp set sal=8000 where deptno=v(n);
		--insert into emp(empno) values(v(n));
end;