PLSQL集合笔记

    针对多行单列的数据处理，用之前的标量变量肯定不行，这里引入一个新的数据类型复合数据类型。

这个复合类型类似于语言的数组struct[i]，包括索引表，嵌套表，边长数组三种类型，

记住记录是管理单行数据的结构体，而集合时管理多行数据的结果体

集合就是列表，可能有序也可能无序，有序列表的索引是唯一性的数字下标，而无序列表的索引是唯一性的标识符，标识符可以是数字，哈希值，或者字符串名。

目前三种集合类型区别：

一、索引表（index-by,或者叫做联合数组都行）称为PLSQL的表，下标与数组比较可以为负数，下标个数没有限制。下标可以是唯一的数字序号或者字符。

TYPE type_name IS TABLE OF element_type

[not null] index by key_type;

identifier type_name;

以上为语法

element_type 为元素的数据类型

key_type 为索引表元素下标的数据类型(binary_integer，pls_integer，varchar2);

set serveroutput on;

declare

type ename_table_type is table of emp.ename%TYPE

index by binary_integer;

ename_table ename_table_type;

begin

select ename into ename_table(-1) from emp where empno =&no;--此处为什么会用-1 ，随便用的一个下标，下标定义个是数字，所以任何一个唯一的数字都可以用在此处，

dbms_output.put_line('ename_table(-1));

end;

用varchar2类型作为下标类型，此处应该有疑问，下标是字符类型

set serveroutput on;

declare

type area_table_type is table of number

index by varchar2(10);定义的下标是字符，那用下标的时候就需要用具有唯一性的字符去当下标，不能用数字去当下标，同样遍历的时候也不存在，字符无法用for loop遍历。

area_table area_table_type;

begin

area_table('北京'):=1;

area_table('天津'):=2;

area_table('天上人间'):=3;

dbms_output.put_line(area_table.first);

dbms_output.put_line(area_table('天津'))

dbms_output.put_line(area_table.last);

二、嵌套表与数组比较下标从1开始，元素没有限制，这个比较浪费数组，他的元素值不是顺序的

语法

type type_name is table of element_type：相比于index-of只是少了一个index by

identifier type_name;

declare

type ename_table_type is table of emp.ename%TYPE;

ename_table ename_table_type:=ename_table_type('a','a');

下面展示嵌套表的使用方法

啊）

declare

    type ENAME_TABLE_TYPE is　table of emp.ename%TYPE;

ename_table ename_table_type:=ename_table_type('a','a','a','a','a');

begin

select ename into ename_table(2) from emp  #使用的时需要用构造方法ename_table_type()初始化，必要时可以用集合的extend()方法初始化

where empno=&no;

dbms_output.put_line(ename_table(2));

end;

吧）作为类型的时候需要用create type先创建类型，然后必须指定存储的表 

create type phone_type is table of varchar2(20);

create table employee(

id number(4),name varchar2(20),sal number(6,2),

phone phone_type)nested table phone store as phone_table; 指定存储的表

insert into employee values(1,'scott',800,phone_type('0473-343434','128381298'));

的）取数据这个案例提示了一个集合类型所拥有的方法

set serveroutput on;

declare

phone_table phone_type;

begin

select phone into phone_table from employee where id=1;

for i in 1..phone_table.count loop

dbms_output.put_line(phone_table(i));

end loop;

end;

取数据还有一个column_value的方法

select column_value from the 的使用

如果定义100个元素，那么可以使用extend（）方法进行初始化

集合的方法，count first last等输出与输入不一致顺序，是由于数据库存储的时候是按下表（如果是字符，那么下标就是用的镜像的哈希值）

三、变长数组 VARRAY 可以作为表类型使用，下标1开始最大为7，关于下标有的書上根本没有提及这个数值，使用构造房初始化，如果使用变长数组时需要注意一般处理大型的事物，IO的代价比较高，

TYPE TYPE_NAME IS VARRAY(SIZE_LIMIT) OF TABLE ELEMENT_TYPE [NOT NULL];

IDENTIFIER TYPE_NAME;

SIZE_LIMIT用来指定元素的最大个数，个人理解是元素的个数，不是指下标。

declare

TYPE ENAME_TABLE_TYPE IS VARRAY(30) OF EMP.ENAME%TYPE;

ENAME_TABLE ENAME_TABLE_TYPE:=ENAME_TABLE_TYPE('A','A','A');

关于varray表需要说明一点，就是如果直接select ename into emp_table(1) from emp where empno=&no 这个时候就需要初始化emp_table 

如果使用bulk collect  into 则不需要初始化，

select ename bulk collect into emp_table from emp where empno=&no;

输出可以用dbms_output.put_line(emp_table(1));

输出就需要用for i in 1..emp_table.count loop

以上输出方式

四、记录表是记录跟表的结合 PLSQL变量处理单行单列 PLSQL记录用于处理单行多列，PLSQL集合用户处理多行单列，记录表是结合了PLSQL记录跟PLSQL表的有点组成的

，这个地方有待学习？？如何进行多行多列处理，有一个拌饭就是用bulk collect 目前需要掌握的

select * bulk collect into emp_table from emp;

for i in 1..emp_table.count loop

....

end loop;

declare

type name_array_type is varray(20) of varchar2(30);

type city_array_type is varray(20) of varchar2(30);

name_array name_array_type;

city_array city_array_type;

begin

select name,city bulk collect into name_array,city_array from customer;

for i in 1..city_array.count loop

dbms_output.put_line(city_array(i)||name_array(i));

end loop;

end;

declare 输入编号，输出所有的信息

type item_table_type is table of item%ROWTYPE

index by binary_integer;

item_table item_table_type;

begin

select * bulk collect into item_table from item where ord_id=&id;

for i in 1..item_table.count loop

dbms_output.put_line('....');

end loop;

end;

五、多级集合类型，类似于多维数组，嵌套了集合类型的集合类型，此时的元素类型不能是集合类型，可以是标量，plsql记录以及对象类型这个对象类型好像没见过，

六、集合的方法，

exists判断集合元素是否存在，在就是true，不在就是false 

if ename_table.exists(1) then

ename_table(1):='scott';

else

dbms_output.put_line('a');

end if;

count返回当前集合变量中的元素个数，

for i in 1..ename_table.count loop

dbms_output.put_line(ename_table(i).ename);

end loop;

limit 用于返回集合元素的最大个数，这个一直没有明白，就像varray表可以定义的时候用varray（20）来定义元素的个数，但是下标却只能到7，有可能是这么理解，一个下标对应的元素个数之和最大不能超过20，这个还是有疑问。

declare
type ename_table_type is varray(20) of emp.ename%type;
ename_table1 ename_table_type:=ename_table_type('a');
ename_table2 ename_table_type:=ename_table_type('aaaa');
ename_table3 ename_table_type:=ename_table_type('aaaaaaaaaa');
ename_table4 ename_table_type:=ename_table_type('aaaa','3333333333');
begin
dbms_output.put_line(ename_table1.limit);
dbms_output.put_line(ename_table2.limit);
dbms_output.put_line(ename_table3.limit);
dbms_output.put_line(ename_table4.limit);
end;

四个值输出均为20，也就是他计算的时候是你定义的值，

first跟last，返回集合变量第一个元素的下标，跟最后一个元素的下标

dbms_output.put_line(ename_table.first);

dbms_output.put_line(ename_table.last);这个在遍历循环的时候用的上，for i in 1..ename_table.last loop

prior返回当前元素下标的前一个下标值，next返回当前元素下标的后面一个下标值，

extend扩展集合变量的大小，只是用与嵌套表跟varray表，这里还是有疑问，

trim从集合尾部删除元素，trim(n)如果不写n就是从尾部删除一个，如果写就是从尾部删除n个

delete用于删除指定的第n个元素，三种调用格式，delete(m,n)你懂得

集合赋值 两个集合进行赋值，不知道有什么意义（：=）或者sql语句将源集合中的数据赋值给目标集合时会自动清除原有的数据，给集合赋空值，集合不初始化即可

另外一个赋值为集合操作符，set multiset union，multiset intersect，multiset except

ename_table1 :=set(ename_table2);

multiset union取两个集合的并集

result=nt1 multiset union nt2;

multiset union distinct 取两个集合的并集并去除重复的值

multiset intersect 用于取得嵌套表的交集

multiset except 用户取得两个嵌套表的差集

针对以上的这些个操作，还有一些嵌套表所固有的东西，比如集合是否为null if ename_table is null

是否为空，等等，目前生产当中没有用到过这些东西。

本文转自 aklaus 51CTO博客，原文链接:http://blog.51cto.com/aklaus/1953493