pl/sql之集合

集合

用户暂时可将表简单理解为此处我们所说的集合，拿以前的记录与集合对比，记录可以理解为一行数据(一行多列)，集合可以理解为一列的多个值（一列多行）。

举个狸子吧，假如有一张教师表信息如下：

在该表中t001------刘阳即可称为一条记录，如下图：

刘阳----谌燕----胡明星即可称为一个集合，如下图：

集合的属性与方法

集合的属性与方法用户可参考java中对象的属性和方法进行简单理解，简单区分属性和方法可看其是否传递了参数，若产生参数传递即为方法，反之，则为属性

属性：

• first——取集合第一个元素的下标
• last——取集合最后一个元素的下标
• count——集合中元素个数
• limit——集合的最大容量（索引表、嵌套表最大值未知返回的是null,可变数组返回的是在变长数组声明时规定的长度）

方法：

• delete——删除集合中元素
  （1）delete——清空集合中所有元素
  （2）delete(n)——删除集合中下标为n的元素，可变数组中不可使用
  （3）delete(m,n)——删除集合中下标在n-m之间的所有元素（m<=n），可变数组中不可使用
• extend——扩展集合元素
  （1）extend——在集合末端添加一个空元素
  （2）extend(n)——在集合末端添加n个空元素
  （3）extend(n,m)——把第m个元素拷贝n份添加到集合末端
• tirm/trim(i)——从集合末尾删除一个或n个元素（删除后不保留占位符）,不能用于索引表
• next(i)——取集合中下标为i的元素的下一个元素的下标
• prior(i)——取集合中下标为i的元素的上一个元素的下标

调用：

集合变量名.[属性名|方法名];

索引表（关联数组）——“稀疏数组”

索引表只能在pl/sql中使用不能存储在数据库中

一、语法格式

declare
--声明索引表类型
type 索引表名 is table of 集合中元素数据类型 index by 下标数据类型;
--声明索引表类型变量
索引表变量名 索引表名;
begin
null;
/*相关操作*/
end;

• 可通过索引表变量名（下标）：=…的格式给集合中元素赋值,亦可通过索引表变量名（下标）的样式来访问其中元素
• 在声明索引表类型时候，集合中元素数据类型无限制，但必须进一步确定精度，例如不能写varchar2但可以写varchar2(211) ;by后下标数据类型用户只能使用pls_integer,binary_integer，varchar2(…)。
• 下标可以不连续可以为负必须为整数,下标会进行一个由小到大的自动排序，例如用户赋值分别给赋值给下标为2，1，-2的元素赋值，在使用循环输出打印时还是会以-2，1，2的顺序打印，first与last同理

二、使用下标赋值与访问索引表中元素示例

declare
	type a is table of varchar2(21) index by pls_integer;
	b a;
begin
	b(-1):='赵';
	b(2):='钱';
	b(3):='孙';
	--删除索引表中下标为3的元素
	b.delete(3);
	dbms_output.put_line(b(-1)||','||b(2)||','||b(3));
end;

输出效果如下图：

三、索引表中集合属性与方法的使用示例

--属性
declare
	type a is table of varchar2(21) index by pls_integer;
	b a;
begin
	b(-1):='赵';
	b(2):='钱';
	b(3):='孙';
	dbms_output.put_line('b索引表中第一个元素下标为'||b.first);
	dbms_output.put_line('b索引表中最后一个元素下标为'||b.last);
	dbms_output.put_line('b索引表最多能容纳'||b.limit);
	dbms_output.put_line('b索引表中元素个数为'||b.count);
end;
/
--方法
declare
	type a is table of varchar2(21) index by pls_integer;
	b a;
begin
	b(-1):='赵';
	b(2):='钱';
	b(3):='孙';
	--打印下标为2的元素上一个元素的下标
	dbms_output.put_line('下标为2的元素上一个元素的下标:'||b.prior(2));
	--打印下标为2的元素下一个元素的下标
	dbms_output.put_line('下标为2的元素下一个元素的下标:'||b.next(2));
	--删除下标为-1的元素
	b.delete(-1);
	--打印检测下标为-1的元素是否存在结果
	/*exists(i)检测下标为i的元素是否存在，存在返回true,否则返回false */
	if b.exists(-1) then
		dbms_output.put_line('存在');
	else
		dbms_output.put_line('不存在');
	end if;
end;

运行效果如下：

四、循环与索引表示例

• while

declare
	type a is table of varchar2(21) index by pls_integer;
	b a;
    i NUMBER;
begin
	b(-1):='赵';
	b(2):='钱';
	b(3):='孙';
    i:=b.first;

	WHILE b.exists(i) LOOP
	    dbms_output.put_line(b(i));
        i:=b.next(i);
	END LOOP;
end;

• for

for循环只适用于数字连续的情况下例如1，2，3等不适用于索引表

• loop

declare
	type a is table of varchar2(21) index by pls_integer;
	b a;
    i NUMBER;
begin
	b(-1):='赵';
	b(2):='钱';
	b(3):='孙';
    i:=b.first;

	LOOP
	    dbms_output.put_line(b(i));
        EXIT WHEN i=b.last;
        i:=b.next(i);
	END LOOP;
end;

五、索引表与记录（多行多列）

DECLARE
--声明记录型
TYPE t_record is RECORD(
    ENAME VARCHAR2(100),
    AGE NUMBER
);
--声明索引表数据类型
TYPE xx is TABLE OF t_record index BY PLS_INTEGER;
--声明承载索引表数据类型的变量
b xx;

BEGIN

b(1).ENAME:='张三';
b(1).AGE:=18;
b(2).ENAME:='李四';
b(2).AGE:=20;
dbms_output.put_line(b(1).ENAME||', '||b(1).AGE||', '||b(2).ENAME||', '||b(2).AGE);
END;

嵌套表——“表中表”

嵌套表既可存储在数据库中，也可在pl/sql中使用

一、语法格式

declare
--声明嵌套表数据类型
type 嵌套表名 is table of 嵌套表中元素数据类型;
--声明承载变量
嵌套表变量名 嵌套表名;
begin
--初始化
嵌套表变量名 :=嵌套表名();
/*用户自定义操作*/
...;
end;

• 使用整数（只能为正）作为下标，下标是连续的,元素个数无限制的
• 必须进行初始化才能进行下一步相关操作

二、嵌套表中集合的属性与方法应用

declare
	type a is table of varchar2(21);
	b a;
begin
	b:=a('赵','钱','孙');
	dbms_output.put_line('b中元素个数:'||b.count);
	b.extend(3);
	dbms_output.put_line('扩充后b中元素个数:'||b.count);
end;

三、循环与嵌套表

• while

declare
	type a is table of varchar2(21);
	b a;
    i NUMBER;
begin
	b:=a('赵','钱','孙');
    i:=b.first;
	WHILE b.exists(i) LOOP
        dbms_output.put_line(b(i));
        i:=b.next(i);
    END LOOP;
end;

• loop

declare
	type a is table of varchar2(21);
	b a;
    i NUMBER;
begin
	b:=a('赵','钱','孙');
    i:=b.first;
    LOOP
        dbms_output.put_line(b(i));
        EXIT WHEN i=b.last;
        i:=b.next(i);
    END LOOP;
end;

• for

declare
	type a is table of varchar2(21);
	b a;
begin
	b:=a('赵','钱','孙');
    
    FOR i in b.first..b.last LOOP
        dbms_output.put_line(b(i));
    END LOOP;
end;

三条代码运行效果均如下所示：

四、在数据库中使用
（1）创建嵌套表类型

create type 类型名 is table of 集合中元素数据类型;

示例如下：

--创建一个名为xxx其中元素类型为varchar2(20)的嵌套表类型
create type xxx is table of varchar2(20);

（2）在建表语句中使用

/* 语法格式如下 create table 主表名（ 列名1 数据类型1, ..., 列名n 嵌套表类型名 ）nested table 嵌套表类型列名 store as 表名（数据库不存在的表）; */

• 如果创建的表中有多个嵌套表类型列则（）后的nested的语句个数应与其中嵌套表类型列个数相同，且store as后表名不能重复，示例如下:

create table demo（
	DEPTNO NUMBER,
	x xxx,
    m xxx
）nested table x store as b,nested table m store as c;

• select语句不能直接查询嵌套表数据，若要查询可使用如下语句：

select * from table(select 嵌套列名 from 主表名);

• insert语句也会产生细微差别，示例如下：

--insert into 主表名(嵌套表类型列名) values(嵌套表类型名（值1,值2,值3,...）);
insert into demo(x) values(xxx('赵','钱','孙'));

• 删除嵌套表类型语句 如下：

drop type 嵌套表类型名;

• 删除主表时，嵌套表亦会随之消失(“覆巢之下，焉有完卵”)，但嵌套表类型不会被删除

drop table 主表名;

（2-1）在pl/sql中使用（可以直接使用xxx类型）

--简单示例如下
declare
	a xxx;
begin
	a:=xxx('赵','钱','孙');
	dbms_output.put_line(a(1)||', '||a(2)||', '||a(3));
end;

可变数组——“茂密数组”

可变数组既可存储在数据库中，也可在pl/sql中使用

一、语法格式

declare
	--声明可变数组类型
	type 可变数组类型名 is varray(元素个数上限) of 数组中元素数据类型;
	--声明承载可变数组类型的变量
	变量名 可变数组类型名;
begin
	--初始化
	变量名：=可变数组类型名(值1,值2,值n);
	/*相关操作*/
	...;
end;

• 使用整数（只能为正）（pls_integer,binary_integer）作为下标，下标是连续的;元素个数有限制
• 可变数组有上限，limit属性返回声明时给定的元素个数
• 必须进行初始化才能进行下一步相关操作

二、可变数组中集合的属性与方法应用

declare
	type a is varray(7) of varchar2(100);
	b a;
begin
	b:=a('赵','钱','孙','李');
	dbms_output.put_line('扩充前元素个数：'||b.count);
	dbms_output.put_line('b集合最大容纳元素个数:'||b.limit);
	b.extend(3);
	dbms_output.put_line('扩充后元素个数：'||b.count);
    dbms_output.put_line('b集合最大容纳元素个数:'||b.limit);
	b.trim;
	dbms_output.put_line('删除最后一个元素后元素个数：'||b.count);
end;

三、循环与可变数组

• while

declare
	type a is varray(4) of varchar2(100);
	b a;
    i NUMBER;
begin
	b:=a('赵','钱','孙','李');
    i:=b.first;
	WHILE b.exists(i) LOOP
        dbms_output.put_line(b(i));
        i:=b.next(i);
    END LOOP;
end;

• loop

declare
	type a is varray(4) of varchar2(100);
	b a;
    i NUMBER;
begin
	b:=a('赵','钱','孙','李');
    i:=b.first;
	LOOP
        dbms_output.put_line(b(i));
        EXIT WHEN i=b.last;
        i:=b.next(i);
    END LOOP;
end;

• for

declare
	type a is varray(4) of varchar2(100);
	b a;
begin
	b:=a('赵','钱','孙','李');
    
	FOR i IN b.first..b.last LOOP
        dbms_output.put_line(b(i));
    END LOOP;
end;

运行效果如下：

四、在数据库中使用

可变数组类型的使用就如同使用一种数据类型，但插入该列数据时还应特别注意其集合身份

（1）创建可变数组类型

create type 可变数组类型名 is varray(可容量元素个数) of 数组中元素数据类型;

示例如下：

create TYPE n is VARRAY(4) OF VARCHAR2(100);

（2）在建表语句中使用

/* 语法格式如下： create table 表名( 列名1 数据类型1， ..., 列名n 可变数组类型名 ) */

示例如下：

--建表
create TABLE demo(
    no NUMBER,
    x n
);
--插入数据
INSERT INTO demo VALUES(10,n('赵','钱','孙','李'));
--查询数据
SELECT * FROM table(SELECT x FROM demo);

• 删除与嵌套表类型相同，故不在此赘述

（2-1）在pl/sql中使用（可以直接使用n类型）

DECLARE
    b n;
BEGIN
    b:=n('赵','钱','孙','李');
    FOR i in b.first..b.last LOOP
        dbms_output.put_line(b(i));
    END LOOP;
END;