这篇文档大概写于2010-10月份，那时在工作中大量的使用pl/sql，所以对这门语言也比较系统的学习了下。在学习的过程中，惊喜的发现这门语言和我一直使用的java有很相似的地方，比如异常的处理等(注：pl/sql是我的第三个工作语言)。 其实，后来通过对javascript的深入学习，才意识到这几种语言都有很多相似的地方，推而广之，我现在认为几乎所有的语言应该都有相似的地方，毕竟，语言的发展和人类的科技的发展一样，都是循序渐进，而不是基因突变的结果。比如这几种语言处理的数据概括起来都是线性(如数组)的和离散(如对象或者map)的。
换了公司后，已经有一年多的时间没有再写pl/sql了，现在之所以把它翻出来，纯粹是为了凑数，想赶快把博客的积分增到100，好有发布文章到首页的权利，呵呵。
如果以后有机会再搞pl/sql，那时会把这篇文档再好好理理的，相信那时会有更加深刻的认识了。
什么是集合
集合是一种数据结构，可以在单个变量中保存若干行数据。举例来说，我需要将一个条形码的列表组装到一个集合中，并将集合从一个程序传递到另一个。集合不同于记录，记录中只能保存一行数据，其中可以包含不同类型的数据；而在集合中，所有行的数据都必须是同一类型的。有时候可以这样说，记录保存的是异构数据，而集合保存的是同构数据。如果你碰巧知道另一种程序设计语言，而最接近pl/sql集合的数据类型是数组。
请耐心听，我还要介绍更多的术语。
集合中单个的项被称为集合的元素。在使用集合时，引用一个特定元素的途径就是在括号中使用一个整数，称作索引。举例来说，bookcopies集合的第四个元素是：bookcopies(4),在这种情况下，索引“4”亦被称为该特定元素所对应的下标。
在pl/sql中，有3大类集合：
1、索引表
2、嵌套表
3、可变数组(VARRAY)
在我开始讨论各种集合类型的优缺点之前，会给出一些代码来阐明给特性。本例说明了
例子：书籍的集合如何从服务器取出一个数据集合，并将集合作为参数传递。
首先，我会自行定义一个数据类型，它是一个由varchar2组成的集合：
CREATE TYPE book_barcodes_t AS TABLE OF VARCHAR2(100);
该语句建立了一个新的book_barchodes_t数据类型，并将其定义存储在数据库内部。在创建数据类型之后，我可以在需要建立书籍条形码集合的时候，使用该数据类型。按照惯例，通常在程序员定义的数据类型之后附加_t。
既然Oracle允许在各种地方使用新的数据类型，那么我们可以将它用作一个pl/sql函数的返回类型。该函数的目的在于返回一个VARCHAR2字符列表，每个字符串都包含所请求书籍的某个特定的副本的条形码ID：
create or replace function available_copies(isbn_in in books.isbn%type)
/*除了名字外，在这里将用户定义类型用作函数的返回类型，与使用内置类型是相同的*/
return book_barchodes_t
is
/*这是实际容纳集合的变量。该变量的名字是复数，以便提醒我自己，其中保存了多个项*/
copies book_barcodes_t;
begin
/*填充集合的一种方法就是通过使用Oracle的bulk collect特性将数据取道集合中。在这里，Oracle会自动地设置集合的大小，然后将所有的匹配行放置到其中。只要结果集合不太大，那么这种填充集合的方式是非常高效的。顺便说一下，只有对嵌入到pl/sql应用程序中的查询，才能使用这个特定的字句*/
select barcode_id
bulk collect into copies
from book_copies
where isbn=isbn_in;
end;
这样，available_copies就创建并返回了一个集合，但代码是如何使用集合的呢？这里是一个非常简单的匿名块，它会调用上述函数，并对返回的集合中的元素进行迭代，打印出条形码：
declare
bar_ids book_barchdes_t
begin
bar_ids:=available_copies('11111');
for i in bar_ids.first..bar_ids.last
loop
dbms_output.put_line(bar_ids(i));
end loop;
end;
集合提供了一些特别的内置函数，可以提供集合的有关信息，诸如第一个和最后一个索引以及元素数目等。第六行利用了这些内置函数，来设置循环索引的的起始和结束范围。
有关集合的重要知识
对于一个pl/sql开发者，关于集合要了解的重要知识包括：
1、3类集合之间的差别，即何时适于使用何种集合。
2、用于声明和填充集合的语法和规则。
3、如何使用被称作方法的内置函数对集合进行操作。
不同类集合之间的差别
| 特征 | 索引表 | 嵌套表 | 可变数组(VARRY) |
| 可以在pl/sql中声明并使用 | 是 | 是 | 是 |
| 可以在数据库中声明并使用，还能用作数据库表中列的数据类型 | 否 | 是 | 是 |
| 在数据库中进行物理存储所用的方法 | 不可用 | 嵌套表的元素放置在物理上独立的“存储表”中 | VARRY元素作为不透明的数据结构存储在主表内部，或者存储为二进制大对象(BLOB) |
| 创建元素需要单独的步骤 | 否 | 在很多情况下是这样(必须分配空间) | 在很多情况下是这样(必须分配空间) |
| 索引值如何确定 | 由程序员自行指定 | 由oracle指定从1开始递增 | 由oracle指定从1开始递增 |
| 可用的索引值 | 任何整数值或字符串 | 在1和分配的元素数目之间的整数 | 在1和分配的元素数目之间的整数，上限由程序员定义 |
| 在哪些oracle版本中可用 | 7.3和更高版本 | 8.0和更高版本 | 8.0和更高版本 |
你可以看到，嵌套表和可变数组可以用作表的列，这对于某些应用程序来说是一个关键的优点。但在一个pl/sql程序中，他们可能要求你为每个元素都分配空间。另一方面，索引表只在程序中可用，但它们的元素不需要预先分配空间，而且下标的范围是任意的，即使负的也可以。我们需要知道的基本知识如下：
1、 在pl/sql程序内部，集合中最灵活、最好用的就是索引表。
2、 当与存储在数据库中的嵌套表数据打交道时，应使用嵌套表。嵌套表适于大的集合，应用程序通常每次只存取其中的一部分数据。
3、 在于存储在数据库中的VARRAY表数据打交道时，应使用VARRAY。这种数据类型适于小的集合，应用程序通常会对集合的内容进行完全的存取。
集合的语法及讨论
任何集合的声明都是一个两步的过程：
1、 确定元素的数据类型，并声明保存此类元素的集合类型。
2、 声明该集合类型的变量。
索引表的声明
DECLARE
TYPE typename IS TABLE OF DATATYPE INDEX BY DATATYPE;
varname typename;
其中：
typename:这是程序员定义的集合类型的名字。
DATATYPE:每个元素的数据类型。
varname:集合变量自身的名字。
你可以通过INDEX BY字句来判断这是一个索引表的声明，从Oracle9开始，索引的下标既可以是binary integer和varchar2，这就使得索引表可以模拟List和Map，当下标是binary integer时，相当于List，当下标是varchar2时，相当于Map。
Map用法举例：
declare
type emp_t2
is table of emp%rowtype index by varchar2(20);
tt1 emp_t2;
vEName emp.ename%type;
begin
for v in(select * from emp)
loop
tt1(v.ename).empno:=v.empno;
tt1(v.ename).ename:=v.ename;
tt1(v.ename).job:=v.job;
tt1(v.ename).mgr:=v.mgr;
tt1(v.ename).hiredate:=v.hiredate;
tt1(v.ename).sal:=v.sal;
tt1(v.ename).comm:=v.comm;
tt1(v.ename).deptno:=v.deptno;
end loop;
vEName:='SMITH';
dbms_output.put_line('ename:' || tt1(vEName).job);
end;
List用法举例
方法1：一次性的将所有数据加载到list中
declare
cursor c is select empno,ename,job,mgr from emp;
--emp_rec c%rowtype;
type emp_table is table of c%rowtype index by binary_integer;
emp_list emp_table;
begin
open c; /*将数据全部加载，如果数据很大，会导致占用内存过大*/
fetch c bulk collect into emp_list ;
close c;
for i in 1..emp_list.count
loop
dbms_output.put_line(emp_list(i).ename);
end loop;
end;
方法2：用循环的方法加载
--批量从数据库中提取数据的标准代码
declare
cursor c is select empno,ename,job,mgr from emp;
--emp_rec c%rowtype;
type emp_table is table of c%rowtype index by binary_integer;
emp_list emp_table;
begin
open c;
loop
fetch c bulk collect into emp_list limit 100;
--当执行fetch语句时，会将原来表中的数据清空
for i in 1..emp_list.count
loop
dbms_output.put_line(emp_list(i).ename);
end loop;
exit when c%notfound;
end loop;
close c;
end;
对索引表的手动赋值：
declare
type emp_t is table of emp.ename%type index by binary_integer;
empList emp_t;
begin
empList(1):='abc';
empList(2):='123';
dbms_output.put_line(empList(2));
end;
嵌套表的声明：
为建立一个独立的嵌套表类型（即，它驻留在服务器中，独立于任何单一的pl/sql程序），你可以单独进行如下操作：
CREATE TYPE typename AS TABLE OF DATATYPE;
这里是一个例子，它创建了一个独立的数据类型，对你的任何程序或表都是可用的：
CREATE TYPE patron_name_list_t AS TABLE OF VARCHAR2(60);
此外，你也可以在pl/sql程序内部声明一个局部的嵌套表数据类型，如：
DECLARE
TYPE my_list_t IS TABLE OF NUMBER;
my_list my_list_t;
但实际上，在pl/sql程序内部声明嵌套表数据类型是不常见的。这是因为嵌套表(和可变数组，就这一点而言)主要是用来扩展数据库的能力的，因此在通常情况下，数据类型是已经定义的，你只需引用而无需重新声明。
如果我想共享一个特定的索引表类型(例如，在两个pl/sql程序之间传递一个集合)，则可以将类型定义放置到一个包的定义中。我可以创建一个专用的包，只包含用来共享的数据类型：
CREATE OR REPLACE PACKAGE loptyes
AS
TYPE book_tab_t IS TABLE OF books%rowtype INDEX BY BINARY_INTEGER;
END;
我可以使用下列语句，将这样的一个值传递到已经声明的一个过程中：
PROCEDURE eatbooks (books_in IN loptypes.book_tab_t ) IS …….
或通过一个函数返回它：
FUNCTION greatbooks RETURN loptypes.book_tab_t IS…..
嵌套表的赋值
方法1：
declare
type emp_t is table of emp.ename%type ;
/*在声明一个嵌套表或可变数组集合时，empList变量开始将其生命周期初始化为NULL值，这与大多数其他变量是相同的*/
empList emp_t;
begin
/*通过调用构造函数初始化了empList变量，而没有使用参数。这样做使得empList变量变为非空值，但其中仍然没有任何元素*/
empList:=emp_t();
/*调用内置的extend方法来为2个元素分配空间。Oracle分别为前两个元素指定了下标1、2，并将新分配的元素的值设置为NULL*/
empList.extend(2)
/*通过赋值操作为元素*/
empList(1):='abc';
empList(2):='123';
dbms_output.put_line(empList(2));
end;
方法2：
declare
type emp_t is table of emp.ename%type ;
/*在声明一个嵌套表或可变数组集合时，empList变量开始将其生命周期初始化为NULL值，这与大多数其他变量是相同的*/
empList emp_t;
begin
/*通过调用构造函数初始化了empList变量,同时设定元素值*/
empList:=emp_t('abc','123');
dbms_output.put_line(empList(2));
end;
可变数组的声明：
声明VARRAY的语法类似于嵌套表，但需要对元素数目加一个上限：
CREATE TYPE typename AS VARRAY(max) OF DATATYPE
其中的max是程序员定义的，表示该集合类型的元素数目上限。类似于嵌套表，你还可以在pl/sql程序内部声明可变数组。
集合的内置方法
集合方法是一个内置过程或函数，它可以帮助你获得或改变集合的某些特征。
常用的方法如下表，在该表中，c是占位符，可以表示任何集合：
| 方法的语法 | 目的 | 前提 |
| c.exists(i) | 判断是否存在第i个元素 | 无 |
| c.count | 返回存在于c中的元素数目 | 对于索引表，没有前提。对于嵌套表和VARRAY来说，c必须已经初始化 |
| c.first ,c.last | 返回c中第一个(最后一个)元素的索引 | |
| c.prior(i) ,c.next(i) | 返回c中下一个索引值较小的(较大的)元素的索引，它出现在索引值为i的元素之前(之后)。通常用于稀疏索引表的遍历 | |
| c.extend(n) | 额外分配n个元素。如果省略了n,默认值为1 | C必须是一个已经初始化的嵌套表或VARRAY(索引表不需要使用extend方法)。扩展VARRAY时不能超出其上限 |
| c.limit | 返回一个VARRAY的上限 | C必须是VARRAY |
| c.delete | 删除一个集合的所有元素， |
|
| c.delete(i[,n]) | 删除索引表或嵌套表的单个元素,其中 i:从该下标开始删除操作。 n:删除的元素数目。默认值为1 |
|
遍历集合的通用代码
方法1(这是一个通用的方法，不管下标是稀疏的还是连贯的，都可以)
declare
idx pls_integer;
...在此处，c声明为某种类型的集合..
begin
...c可能在这里进行初始化并填充内容，也可能不这样做...
if c is not null then
idx:=c.first;
while idx is not null loop
...对c(idx)做一些有用的工作...
idx:=c.next(idx);
end loop;
end if;
end;
方法2（对于下标是连贯的情况）
declare
type v_t is table of varchar2(10);
eNameList v_t:=v_t('abc','123','uij');
begin
for i in 1..eNameList.count loop
dbms_output.put_line(eNameList(i));
end loop;
end;
479
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
