这篇文档大概写于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;