第五章 PL/SQL集合与记录

第五章 PL/SQL集合与记录

<!-- InstanceEndEditable --><!-- InstanceBeginEditable name="EditRegion2" -->

一、什么是集合

集合就是相同类型的元素的有序合集。它一个通用的概念，其中包含了列表、数组和其他相似的数据类型。每一个元素都有唯一的下标来标识当前元素在集合中的位置。PL/SQL提供了以下几种集合类型：

1. 索引表，也称为关联数组，可以让我们使用数字或字符串作下标来查找元素。(这有点和其他语言中的哈希表相类似。)
2. 嵌套表可以容纳任意个数的元素，使用有序数字作下标。我们可以定义等价的SQL类型，把嵌套表存到数据库中去，并通过SQL语句进行操作。
3. 变长数组能保存固定数量的元素(但可以在运行时改变它的大小)，使用有序数字作为下标。同嵌套表一样，也可以保存到数据库中去，但灵活性不如嵌套表好。

虽热集合是一维的，但我们可以把一个集合作为另外一个集合的元素来建立多维集合。

要在应用程序中使用集合，我们要先定义一个或多个PL/SQL类型，然后声明这些类型的变量。我们可以在过程、函数或包中定义集合类型。还可以把集合作为参数在客户端和存储子程序之间传递数据。

要查找复杂类型的数据，我们可以在集合中存放PL/SQL记录或SQL对象类型。嵌套表和变长数组也可以作为对象类型的属性。

1、理解嵌套表

在数据库中，嵌套表可以被当作单列的数据表来使用。Oracle在往嵌套表中存放数据时是没有特定顺序的。但是，当我们把检索出来的数据存放在PL/SQL变量时，所有行的下标就会从1开始顺序编号。这样，就能像访问数组那样访问每一行数据。

嵌套表有两个重要的地方不同于数组：

1. 数组有固定的上限，而嵌套表是没有上界的。所以，嵌套表的大小是可以动态增长的。如下图：
2. 数 组必须是密集的(dense)，有着连续的下标索引。所以我们不能从数组中删除元素。而对于嵌套表来说，初始化时，它是密集的，但它是允许有间隙的 (sparse)，也就是说它的下标索引可以是不连续的。所以我们可以使用内置过程DELETE从嵌套表中删除元素。这样做会在下标索引上留下空白，但内 置函数NEXT仍能让我们遍历连续地访问所有下标。

2、理解变长数组

VARRAY被称为变长数组。它允许我们使用一个独立的标识来确定整个集合。这种关联能让我们把集合作为一个整体来操作，并很容易地引用其中每一个元素。下面是一个变长数组的例子，如果我们要引用第三个元素的话，就可以使用Grade(3)。

变长数组有一个长度最大值，是在我们定义时指定的。它的索引有一个固定的下界1和一个可扩展的上界。例如变长数组Grades当前上界是7，但我们 可以把它扩展到8、9、10等等。因此，一个变长数组能容纳不定个数的元素，从零(空的时候)到类型定义时所指定的最大长度。

3、理解关联数组(索引表)

关联数组就是键值对的集合，其中键是唯一的，用于确定数组中对应的值。键可以是整数或字符串。第一次使用键来指派一个对应的值就是添加元素，而后续这样的操作就是更新元素。下面的例子演示了如何使用关联数组：

DECLARE TYPE population_typeIS TABLE OF NUMBER INDEX BY VARCHAR2 (64); country_populationpopulation_type; continent_populationpopulation_type; howmanyNUMBER ; whichVARCHAR2 (64); BEGIN country_population('Greenland' ):=100000; country_population('Iceland' ):=750000; howmany:=country_population('Greenland' ); continent_population('Australia' ):=30000000; continent_population('Antarctica' ):=1000;--Createsnewentry continent_population('Antarctica' ):=1001;--Replacespreviousvalue which:=continent_population.FIRST; --Returns'Antarctica' --asthatcomesfirstalphabetically. which:=continent_population.LAST; --Returns'Australia' howmany:= continent_population(continent_population.LAST); --Returnsthevaluecorrespondingtothelastkey,inthis --casethepopulationofAustralia. END ; /

关联数组能帮我们存放任意大小的数据集合，快速查找数组中的元素。它像一个简单的SQL表，可以按主键来检索数据。

因为关联数组的作用是存放临时数据，所以不能对它应用像INSERT和SELECT INTO这样的SQL语句。

4、全球化设置对使用VARCHAR2类型作主键的关联数组的影响

如果在使用VARCHAR2作为键的关联数组的会话中改变国家语言或全球化设置，就有可能引起一个运行时异常。例如，在一个会话中改变初始化参数 NLS_COMP或NLS_SORT的值后，再调用NEXT和PRIOR就可能会引起异常。如果我们必须在会话中更改这些设置的话，那么，在重新使用关联 数组的相关操作之前，就必须确保参数值被改回原值。

在用字符串作为关联数组的键的时候，声明时必须使用VARCHAR2、STRING或LONG类型，但使用的时候可以使用其他类型，如NVARCHAR2，VARCHAR2等，甚至是DATE，这些类型值都会被TO_CHAR函数转成VARCHAR2。

但是，在使用其他类型作为键的时候一定要慎重。这里举几个例子：当初始化参数NLS_DATE_FORMAT发生改变时，函数SYSDATE转成字 符串的值就可能发生改变，这样的话，array_element(SYSDATE)的结果就和先前的结果不一样了；两个不同的NVARCHAR2类型值转 成VARCHAR2值之后可能得出的结果是相同的，这样，数组array_element(national_string1)和 array_element(national_string2)可能引用同一个元素。

当我们使用数据库连接(database link)把关联数组作为参数传递给远程数据库时，如果两个数据库的全球化设置不一致，远程数据库会使用自己的字符顺序来调用FIRST和NEXT操作， 即使该顺序与原集合顺序不同。由于字符集的不同，就可能出现在一个数据库中两个不同的键在另一个数据库被当作同一个键处理的情况，这时程序就会收到一个 VALUE_ERROR异常。

二、集合类型的选择

如果我们有用其他语言编写的代码或业务逻辑，通常可以把其中的数组或集合直接转成PL/SQL的集合类型。

1. 其他语言中的数组可以转成PL/SQL中的VARRAY。
2. 其他语言中的集合和包(bags)可以转成PL/SQL中的嵌套表。
3. 哈希表和其他无序查找表(unordered lookup table)可以转成PL/SQL中的关联数组。

当编写原始代码或从头开始设计业务逻辑的时候，我们应该考虑每种类型的优势，然后决定使用哪个类型更加合适。

1、嵌套表与关联数组间的选择

嵌套表和关联数组(原来称为索引表)都使用相似的下标标志，但它们在持久化和参数传递上有些不同的特性。

嵌套表可以保存到数据表字段中，而关联数组不可以。嵌套表适于存放能够被持久化的重要数据。

关联数组适用于存放较小量的数据，每次调用过程或包初始化时在内存中构建出来。它能够保存容量不固定的信息，因为它的长度大小是可变的。关联数组的索引值很灵活，可以是负数，不连续的数字，适当的时候还可以使用字符串代替数字。

PL/SQL能自动地将使用数字作为键的关联数组和主数组(host array)进行转换。集合和数据库服务器间数据传输的最有效的方法就是使用匿名PL/SQL块进行批量绑定数据绑定。

2、嵌套表与变长数组间的选择

在数据个数能够预先确定的情况下，使用变长数组是一个很好的选择。在存入数据库的时候，变长数组会保持它们原有的顺序和下标。

无论在表内(变长数组大小不到4k)还是在表外(变长数组大小超过4k)，每个变长数组都被作为独立的一个对象对待。我们必须对变长数组中的所有元素进行一次性检索或更新。但对于较大量的数据来说，变长数组就不太适用了。

嵌套表是可以有间隙的：我们可以任意地删除元素，不必非得从末端开始。嵌套表数据是存放在系统生成的数据表中，这就使嵌套表适合查询和更新集合中的部分元素。我们不能依赖于元素在嵌套表中的顺序和下标，因为这些顺序和下标在嵌套表存到数据库时并不能被保持。

三、定义集合类型

要使用集合，我们首先要创建集合类型，然后声明该类型的变量。我们可以在任何PL/SQL块、子程序或包的声明部分使用TABLE和VARRAY类型。

集合的作用域和初始化规则同其他类型和变量一样。在一个块或子程序中，当程序进入块或子程序时集合被初始化，退出时销毁。在包中，集合在我们第一次引用包的时候初始化，直至会话终止时才销毁。

• 嵌套表

对于嵌套表，可以使用下面的语法来进行定义：

TYPE type_nameIS TABLE OF element_type[NOT NULL ];

其中type_name是在集合声明使用的类型标识符，而element_type可以是除了REF CURSOR类型之外的任何PL/SQL类型。对于使用SQL声明的全局嵌套表来说，它的元素类型受到一些额外的限制。以下几种类型是不可以使用的：

1. BINARY_INTEGER, PLS_INTEGER
2. BOOLEAN
3. LONG, LONG RAW
4. NATURAL, NATURALN
5. POSITIVE, POSITIVEN
6. REF CURSOR
7. SIGNTYPE
8. STRING

• 变长数组

对于变长数组类型，可以使用下面的语法进行定义：

TYPE type_nameIS {VARRAY|VARYINGARRAY }(size_limit)OF element_type[NOT NULL ];

type_name和element_type的含义与嵌套表相同。size_limit是正整数，代表数组中最多允许存放元素的个数。在定义VARRAY时，我们必须指定它的长度最大值。下例中，我们定义了一个存储366个DATE类型的VARRAY：

DECLARE TYPE CalendarIS VARRAY(366)OF DATE ;

• 关联数组

对于关联数组，可以使用下面的语法进行定义：

TYPE type_nameIS TABLE OF element_type[NOT NULL ] INDEX BY [BINARY_INTEGER |PLS_INTEGER |VARCHAR2 (size_limit)]; INDEX BY key_type;

key_type可以是BINARY_INTEGER或PLS_INTEGER，也可以是VARCHAR2或是它的子类型VARCHAR、 STRING或LONG。在用VARCHAR2做键的时候，我们必须指定VARCHAR2的长度，但这里不包括LONG类型，因为LONG等价于 VARCHAR2(32760)。而RAW、LONG RAW、ROWID、CHAR和CHARACTER都是不允许作为关联数组的键的。在引用一个使用VARCHAR2类型作为键的关联数组中的元素时，我们 还可以使用其他类型，如DATE或TIMESTAMP，因为它们自动地会被TO_CHAR函数转换成VARCHAR2。索引表可以使用不连续的键作下标索 引。如下例中，索引表的下标是7468而不是1：

DECLARE TYPE emptabtypIS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER ; emp_tabemptabtyp; BEGIN /*Retrieveemployeerecord.*/ SELECT * INTO emp_tab(7468) FROM emp WHERE empno=7468; END ;

1、定义与PL/SQL集合类型等价的SQL类型

要把嵌套表或变长数组存到数据表中，我们必须用CREATE TYPE来创建SQL类型。SQL类型可以当作数据表的字段或是SQL对象类型的属性来使用。

我们可以在PL/SQL中声明与之等价的类型，或在PL/SQL变量声明时直接使用SQL类型名。

• 嵌套表的例子

下面的SQL*Plus脚本演示了如何在SQL中创建嵌套表，并把它作为对象类型的属性来使用：

CREATE TYPE CourseListAS TABLE OF VARCHAR2 (10)--definetype / CREATE TYPE StudentAS OBJECT(--createobject id_numINTEGER (4), nameVARCHAR2 (25), addressVARCHAR2 (35), statusCHAR (2), coursesCourseList)--declarenestedtableasattribute /

标识符courses代表整张嵌套表，courses中的每个元素存放一个大学课程的代号，如"Math 1020"。

• 变长数组的例子

下面的脚本创建了能够存储变长数组的数据库字段，其中每个元素包含一个VARCHAR2类型值：

--Eachprojecthasa16-charactercodename. --Wewillstoreupto50projectsatatimeinadatabasecolumn. CREATE TYPE projectlistAS VARRAY(50)OF VARCHAR2 (16); / CREATE TABLE department(--createdatabasetable dept_idNUMBER (2), NAMEVARCHAR2 (15), budgetNUMBER (11,2), --Eachdepartmentcanhaveupto50projects. projectsprojectlist) /

四、声明PL/SQL集合变量

在定义了集合类型之后，我们就可以声明该类型的变量了。在声明中要使用新的类型名称，使用方法跟使用预定义类型(如NUMBER和INTEGER等)声明的方法一样。

• 例一：声明嵌套表、变长数组和关联数组

DECLARE TYPE nested_typeIS TABLE OF VARCHAR2 (20); TYPE varray_typeIS VARRAY(50)OF INTEGER ; TYPE associative_array_typeIS TABLE OF NUMBER INDEX BY BINARY_INTEGER ; v1nested_type; v2varray_type; v3associative_array_type;

• 例二：%TYPE

我们可以利用%TYPE来引用已声明过的集合类型，这样，在集合的定义发生改变时，所有依赖这个集合类型的变量也会相应地改变自己的元素个数和类型，与类型保持一致：

DECLARE TYPE platoonIS VARRAY(20)OF soldier; p1platoon; --Ifwechangethenumberofsoldiersinaplatoon,p2will --reflectthatchangewhenthisblockisrecompiled. p2p1%TYPE ;

• 例三：把嵌套表声明为过程参数

我们可以把集合声明为函数或过程的形式参数。这样，就能把集合从一个存储子程序传递到另一个。下面例子中把嵌套表声明为打包过程的参数：

CREATE PACKAGE personnelAS TYPE staffIS TABLE OF employee; ... PROCEDURE award_bonuses(membersIN staff); END personnel;

想要从包外调用PERSONNEL.AWARD_BONUSES，我们就得声明PERSONNEL.STAFF类型的变量，然后把它作为参数传递进去。我们还可以在函数说明部分指定RETURN的类型为集合：

DECLARE TYPE SalesForceIS VARRAY(25)OF Salesperson; FUNCTION top_performers(nINTEGER )RETURN SalesForceIS ...

• 例四：用%TYPE和%ROWTYPE指定集合的元素类型

在指定元素的集合类型时，我们可以使用%TYPE和%ROWTYPE。示例如下：

DECLARE TYPE EmpListIS TABLE OF emp.ename%TYPE ;--basedoncolumn CURSOR c1IS SELECT *FROM dept; TYPE DeptFileIS VARRAY(20)OF c1%ROWTYPE ;--basedoncursor

• 例五：记录类型的变长数组

下面的例子中，我们使用RECORD作为元素的数据类型：

DECLARE TYPE anentryIS RECORD ( termVARCHAR2 (20), meaningVARCHAR2 (200) ); TYPE glossaryIS VARRAY(250)OF anentry;

• 例六：为集合的元素添加NOT NULL约束

DECLARE TYPE EmpListIS TABLE OF emp.empno%TYPE NOT NULL ;

五、初始化与引用集合

在我们为嵌套表和变长数组初始化之前，它们都会自动地被设置成空值。所谓的空值指的是集合本身是空，不是针对它所拥有的元素。可以使用系统定义的与集合类型同名的函数来初始化集合。

我们必须显式地调用构造函数为每一个变长数组和嵌套表变量进行初始化操作(对于关联数组来说，是不需要使用构造函数进行初始化的)。

• 例一：嵌套表的构造函数

在下面的例子中，我们为构造函数CourseList()传递多个元素，然后构造函数就能为我们返回包含这些元素的嵌套表：

DECLARE TYPE courselistIS TABLE OF VARCHAR2 (16); my_coursescourselist; BEGIN my_courses:=courselist('Econ2010' , 'Acct3401' , 'Mgmt3100' ); END ;

由于嵌套表没有声明最大长度，所以我们可以在构造中可以放置任意个数的元素。

• 例二：变长数组的构造函数

DECLARE TYPE projectlistIS VARRAY(50)OF VARCHAR2 (16); accounting_projectsprojectlist; BEGIN accounting_projects:=projectlist('ExpenseReport' , 'Outsourcing' , 'Auditing' ); END ;

我们不需要初始化整个变长数组，对于一个长度为50的变长数组来说，我们只需传递一部分元素给它的构造函数即可。

• 例三：包含空元素的集合构造函数

如果我们没有对元素使用NOT NULL约束，那么我们就可以把空值传给构造函数：

BEGIN my_courses:=CourseList('Math3010' ,NULL ,'Stat3202' );

• 例四：把声明和构造结合起来

我们可以在声明的时候初始化集合，这是一个很好的编程习惯：

DECLARE TYPE courselistIS TABLE OF VARCHAR2 (16); my_coursescourselist:=courselist('Art1111' , 'Hist3100' , 'Engl2005' );

• 例五：空的(empty)变长数组构造函数

如果在调用构造函数时不传递任何参数，就会得到一个空的(empty)集合，这里指的是集合内容为空，而不是集合本身为空：

DECLARE TYPE clienteleIS VARRAY(100)OF customer; vipsclientele:=clientele();--initializeemptyvarray BEGIN IF vipsIS NOT NULL THEN --conditionyieldsTRUE ... END IF ; END ;

这种情况下，我们可以调用EXTEND方法来添加元素。

• 例六：SQL语句中使用嵌套表构造函数

下例中，我们把几个标量值和一个CourseList嵌套表插入到表SOPHOMORES中：

BEGIN INSERT INTO sophomores VALUES (5035,'JanetAlvarez' ,'122BroadSt' ,'FT' , courselist('Econ2010' , 'Acct3401' , 'Mgmt3100' ));

• 例七：SQL语句中使用变长数组构造函数

下例中，我们把一行数据插入到表DEPARTMENT。变长数组构造函数ProjectList()为字段PROJECTS提供数据：

BEGIN INSERT INTO department VALUES (60,'Security' ,750400, projectlist('NewBadges' , 'TrackComputers' , 'CheckExits' ));

1、引用集合中的元素

集合的引用包含了集合的名称和用圆括号夹起来的下标索引。下标索引决定了要选取哪个元素。语法如下：

collection_name(subscript)

多数情况下，下标是一个运算结果为整数的表达式，对于使用字符串作键的关联数组来说也可能是一个VARCHAR2类型值。下标索引允许的范围如下：

1. 对于嵌套表，索引值的范围在1至2**31之间。
2. 对于变长数组，索引值的范围在1至最大长度之间，最大长度是在声明时指定的。
3. 对于使用数字作键的关联数组来说，索引值的范围在-2**31至2**31之间。
4. 对于使用字符串作键的关联数组来说，键的长度和可用值的数量要依赖于类型声明时对VARCHAR2的长度限制和数据库字符集。

• 例一：使用下标索引来引用嵌套表中的元素

这里我们演示一下如何引用嵌套表NAMES中的元素：

DECLARE TYPE rosterIS TABLE OF VARCHAR2 (15); namesroster:=roster('JHamil' , 'DCaruso' , 'RSingh' ); BEGIN FOR iIN names.FIRST..names.LASTLOOP IF names(i)='JHamil' THEN NULL ; END IF ; END LOOP ; END ;

• 例二：把嵌套表元素作为参数传递

这个例子中我们在调用子程序时引用了集合中的元素：

DECLARE TYPE rosterIS TABLE OF VARCHAR2 (15); namesroster:=roster('JHamil' , 'DPiro' , 'RSingh' ); iBINARY_INTEGER :=2; BEGIN verify_name(names(i));--callprocedure END ;

六、集合的赋值

集合可以用INSERT、UPDATE、FETCH或SELECT语句来赋值，也可以用赋值语句或调用子程序来赋值。

我们可以使用下面的语法来为某个指定的集合元素进行赋值：

collection_name(subscript):=expression;

其中expression的值和被指定的元素类型必须一致。下面我们来看三个例子。

• 例一：数据的兼容性

例子中的集合与集合之间互相赋值，但必须是两个集合类型相同的才可以，光是元素的类型相同是不够的。

DECLARE TYPE clienteleIS VARRAY(100)OF customer; TYPE vipsIS VARRAY(100)OF customer; --Thesefirsttwovariableshavethesamedatatype. group1clientele:=clientele(...); group2clientele:=clientele(...); --Thisthirdvariablehasasimilardeclaration, --butisnotthesametype. group3vips:=vips(...); BEGIN --Allowedbecausetheyhavethesamedatatype group2:=group1; --Notallowedbecausetheyhavedifferentdatatypes group3:=group2; END ;

• 例二：为嵌套表赋空值

当我们把一个被自动初始化为空的嵌套表或变长数组赋给另外一个嵌套表或变长数组时，被赋值的集合就会被重新初始化，结果也为NULL。

DECLARE TYPE clienteleIS TABLE OF VARCHAR2 (64); --Thisnestedtablehassomevalues. group1clientele:=clientele('Customer1' ,'Customer2' ); --Thisnestedtableisnotinitialized("atomicallynull"). group2clientele; BEGIN --Atfirst,thetestIF group1IS NULL yieldsFALSE . --Thenweassignanullnestedtabletogroup1. group1:=group2; --NowthetestIF group1IS NULL yieldsTRUE . --Wemustuseanotherconstructortogiveitsomevalues. END ;

• 例三：集合赋值时可能引起的异常

在下面几种给集合元素赋值的情况下，可能会引起多种异常。

1. 如果下标索引不存在或无法转换成正确的数据类型，PL/SQL就会抛出预定义异常VALUE_ERROR。通常，下标是一个整数。但关联数组的下标也可以是VARCHAR2类型。
2. 如果所给下标索引指向了一个未初始化的元素时，PL/SQL就会抛出SUBSCRIPT_BEYOND_COUNT异常。
3. 如果集合被自动初始化为空值并且程序引用了其中的一个元素，PL/SQL会抛出COLLECTION_IS_NULL异常。

DECLARE TYPE wordlistIS TABLE OF VARCHAR2 (5); wordswordlist; BEGIN /*Assumeexecutioncontinuesdespitetheraisedexceptions.*/ --RaisesCOLLECTION_IS_NULL.Wehaven'tusedaconstructoryet. --Thisexceptionappliestovarraysandnestedtables,butnot --associativearrayswhichdon'tneedaconstructor. words(1):=10; --Afterusingaconstructor,wecanassignvaluestotheelements. words:=wordlist(10, 20, 30 ); --AnyexpressionthatreturnsaVARCHAR2(5)isOK. words(1):='yes' ; words(2):=words(1)||'no' ; --RaisesVALUE_ERRORbecausetheassignedvalueistoolong. words(3):='longerthan5characters' ; --RaisesVALUE_ERRORbecausethesubscriptofanestedtablemust --beaninteger. words('B' ):='dunno' ; --RaisesSUBSCRIPT_BEYOND_COUNTbecauseweonlymade3elements --intheconstructor.Toaddnewones,wemustcalltheEXTEND --methodfirst. words(4):='maybe' ; END ;

七、比较集合

我们可以检查一个集合是不是空，但不能判断两个集合是不是相同。像大于、小于这样的操作都是不允许使用的。

• 例一：检查集合是否为空

嵌套表和变长数组都能被自动初始化为空值，所以它们可以做判空操作：

DECLARE TYPE staffIS TABLE OF employee; membersstaff; BEGIN --ConditionyieldsTRUEbecausewehaven’tusedaconstructor. IF membersIS NULL THEN ... END ;

• 例二：比较两个集合

集合不能直接进行等或不等的比较。例如下面的IF条件表达式就是不允许的。

DECLARE TYPE clienteleIS TABLE OF VARCHAR2 (64); group1clientele:=clientele('Customer1' ,'Customer2' ); group2clientele:=clientele('Customer1' ,'Customer3' ); BEGIN --Equalitytestcausescompilationerror. IF group1=group2THEN ... END IF ; END ;

这个约束也适用于隐式的比较。所以，集合不能出现在DISTINCT、GROUP BY或ORDER BY中。

如果我们想对集合进行比较的话，就得自定义等于、小于、大于比较规则。同时还要编写一个或多个函数来检查集合和它们的元素并返回真假值。

八、在SQL语句中使用PL/SQL的集合类型

集合允许我们用PL/SQL来操作复杂的数据类型。我们的程序能计算下标索引值，并在内存中处理指定的元素，然后用SQL语句把结果保存到数据库中。

1、关于嵌套表的例子

• 例一：创建与PL/SQL嵌套表对应的SQL类型

在SQL*Plus中，我们可以创建与PL/SQL嵌套表和变长数组相对应的SQL类型：

SQL >CREATE TYPE CourseListAS TABLE OF VARCHAR2 (64);

我们可以把这些类型当作数据库字段来使用：

SQL >CREATE TABLE department( 2nameVARCHAR2 (20), 3directorVARCHAR2 (20), 4officeVARCHAR2 (20), 5coursesCourseList) 6NESTEDTABLE coursesSTOREAS courses_tab;

字段COURSES中的每一个值都是一个嵌套表类型，能够保存系(department)所提供的课程。

• 例二：向数据库中插入嵌套表

现在，我们可以数据表填充了。嵌套表的构造函数为字段COURSES提供了值：

BEGIN INSERT INTO department VALUES ('English' ,'LynnSaunders' ,'BreakstoneHall205' , courselist('ExpositoryWriting' , 'FilmandLiterature' , 'ModernScienceFiction' , 'DiscursiveWriting' , 'ModernEnglishGrammar' , 'IntroductiontoShakespeare' , 'ModernDrama' , 'TheShortStory' , 'TheAmericanNovel' )); END ;

• 例三：从数据库中检索嵌套表

我们可以把英语系所提供的所有课程放到PL/SQL嵌套表中：

DECLARE english_coursesCourseList; BEGIN SELECT courses INTO english_courses FROM department WHERE name='English' ; END ;

在PL/SQL中，我们可以循环遍历嵌套表的元素并使用TRIM或EXTEND方法来更新嵌套表中部分或全部的元素。然后，在把更新后的结果保存到数据库中去。

• 例四：用嵌套表更新数据库中

我们可以修改英语系所提供的课程列表：

DECLARE new_coursescourselist :=courselist('ExpositoryWriting' , 'FilmandLiterature' , 'DiscursiveWriting' , 'ModernEnglishGrammar' , 'RealismandNaturalism' , 'IntroductiontoShakespeare' , 'ModernDrama' , 'TheShortStory' , 'TheAmericanNovel' , '20th-CenturyPoetry' , 'AdvancedWorkshopinPoetry' ); BEGIN UPDATE department SET courses=new_courses WHERE NAME='English' ; END ;

2、变长数组的一些例子

假设我们在SQL*Plus中定义了对象类型Project：

SQL >CREATE TYPE ProjectAS OBJECT( 2project_noNUMBER (2), 3titleVARCHAR2 (35), 4costNUMBER (7,2));

下一步，定义VARRAY类型的ProjectList，用来存放Project对象：

SQL >CREATE TYPE ProjectListAS VARRAY(50)OF Project;

最后，创建关系表department，其中的一个字段类型为ProjectList：

SQL >CREATE TABLE department( 2dept_idNUMBER (2), 3nameVARCHAR2 (15), 4budgetNUMBER (11,2), 5projectsProjectList);

在字段projects中的每一项都是一个用于存放给定系的项目计划的变长数组。

现在让我们准备插入一些测试数据。注意一下，在下面的例子中，变长数组的构造函数ProjectList()是如何为字段projects提供数据的：

BEGIN INSERT INTO department VALUES (30,'Accounting' ,1205700, projectlist(project(1,'DesignNewExpenseReport' ,3250), project(2,'OutsourcePayroll' ,12350), project(3,'EvaluateMergerProposal' ,2750), project(4,'AuditAccountsPayable' ,1425) )); INSERT INTO department VALUES (50,'Maintenance' ,925300, projectlist(project(1,'RepairLeakinRoof' ,2850), project(2,'InstallNewDoorLocks' ,1700), project(3,'WashFrontWindows' ,975), project(4,'RepairFaultyWiring' ,1350), project(5,'WinterizeCoolingSystem' ,1125) )); INSERT INTO department VALUES (60,'Security' ,750400, projectlist(project(1,'IssueNewEmployeeBadges' ,13500), project(2,'FindMissingICChips' ,2750), project(3,'UpgradeAlarmSystem' ,3350), project(4,'InspectEmergencyExits' ,1900) )); END ;

现在，让我们对Security系做个更新操作：

DECLARE new_projectsprojectlist :=projectlist(project(1,'IssueNewEmployeeBadges' ,13500), project(2,'DevelopNewPatrolPlan' ,1250), project(3,'InspectEmergencyExits' ,1900), project(4,'UpgradeAlarmSystem' ,3350), project(5,'AnalyzeLocalCrimeStats' ,825) ); BEGIN UPDATE department SET projects=new_projects WHERE dept_id=60; END ;

接下来，对Accounting系做一次查询操作，并把结果放到本地变量中去：

DECLARE my_projectsprojectlist; BEGIN SELECT projects INTO my_projects FROM department WHERE dept_id=30; END ;

最后，删除记录Accounting：

BEGIN DELETE FROM department WHERE dept_id=30; END ;

3、使用SQL语句操作特定的集合元素

默认情况下，SQL操作会一次性的保存或检索整个集合而不是一个独立的元素。要用SQL语句操作集合中的独立的元素，可以使用TABLE操作符。 TABLE操作符用一个子查询把变长数组或嵌套表的内容提取出来，这样的话，INSERT、UPDATE或DELETE语句就可以作用于嵌套表，而不是整 张数据表了。

下面，让我们看看一些具体的操作实例。

• 例一：向嵌套表中插入元素

首先，我们向历史系的嵌套表COURSES插入一条记录：

BEGIN --TheTABLEoperatormakesthestatementapplytothenested --tablefromthe'History'rowoftheDEPARTMENTtable. INSERT INTO TABLE (SELECT courses FROM department WHERE NAME='History' ) VALUES ('ModernChina' ); END ;

• 例二：更新嵌套表中的元素

然后对嵌套表的学分进行调整：

BEGIN UPDATE TABLE (SELECT courses FROM department WHERE NAME='Psychology' ) SET credits=credits+adjustment WHERE course_noIN (2200,3540); END ;

• 例三：从嵌套表中检索一个元素

下面，我们从历史系检索出一个特定课程名称：

DECLARE my_titleVARCHAR2 (64); BEGIN --Weknowthatthereisonehistorycoursewith'Etruscan' --inthetitle.Thisqueryretrievesthecompletetitle --fromthenestedtableofcoursesfortheHistorydepartment. SELECT title INTO my_title FROM TABLE (SELECT courses FROM department WHERE NAME='History' ) WHERE NAMELIKE '%Etruscan%' ; END ;

• 例四：从嵌套表中删除元素

最后，我们从英语系中删除所有那些学分为5的课程：

BEGIN DELETE TABLE (SELECT courses FROM department WHERE NAME='English' ) WHERE credits=5; END ;

• 例五：从变长数组中检索元素

下面例子演示了从变长数组类型的字段projects中检索出公务处第四个项目的名称和费用：

DECLARE my_costNUMBER (7,2); my_titleVARCHAR2 (35); BEGIN SELECT COST,title INTO my_cost,my_title FROM TABLE (SELECT projects FROM department WHERE dept_id=50) WHERE project_no=4; ... END ;

• 例六：对变长数组应用INSERT、UPDATE和DELETE操作

目前，我们还不能在INSERT、UPDATE和DELETE语句中引用变长数组中的元素，必须先检索整个变长数组，使用PL/SQL来添加、删除或更新元素，然后把修改结果重新放回数据库中。

下面的存储过程ADD_PROCEDURE演示了如何按给定的位置向department中插入一个新的project。

CREATE PROCEDURE add_project( dept_noIN NUMBER , new_projectIN project, POSITIONIN NUMBER )AS my_projectsprojectlist; BEGIN SELECT projects INTO my_projects FROM department WHERE dept_no=dept_id FOR UPDATE OF projects; my_projects.EXTEND;--makeroomfornewproject /*Movevarrayelementsforward.*/ FOR iIN REVERSE POSITION..my_projects.LAST-1LOOP my_projects(i+1):=my_projects(i); END LOOP ; my_projects(POSITION):=new_project;--addnewproject UPDATE department SET projects=my_projects WHERE dept_no=dept_id; END add_project;

下例代码为一个指定的工程更新数据：

CREATE PROCEDURE update_project( dept_noIN NUMBER , proj_noIN NUMBER , new_titleIN VARCHAR2 DEFAULT NULL , new_costIN NUMBER DEFAULT NULL )AS my_projectsprojectlist; BEGIN SELECT projects INTO my_projects FROM department WHERE dept_no=dept_id FOR UPDATE OF projects; /*Findproject,updateit,thenexitloopimmediately.*/ FOR iIN my_projects.FIRST..my_projects.LASTLOOP IF my_projects(i).project_no=proj_noTHEN IF new_titleIS NOT NULL THEN my_projects(i).title:=new_title; END IF ; IF new_costIS NOT NULL THEN my_projects(i).COST:=new_cost; END IF ; EXIT ; END IF ; END LOOP ; UPDATE department SET projects=my_projects WHERE dept_no=dept_id; END update_project;

• 例七：对嵌套表应用INSERT、UPDATE和DELETE操作

为了能对一个PL/SQL嵌套表使用DML操作，我们需要使用TABLE和CAST操作符。这样，我们就可以直接使用SQL标志对嵌套表进行集合操作而不用把更改过的嵌套表保存在数据库中。

CAST的操作数可以是PL/SQL集合变量和SQL集合类型(使用CREATE TYPE语句创建)。CAST可以把PL/SQL集合转成SQL类型的。

下面的例子用来计算修改后的课程列表和原始课程列表的不同点的数量(注意，课程3720的学分从4变成3)：

DECLARE revisedcourselist :=courselist(course(1002,'ExpositoryWriting' ,3), course(2020,'FilmandLiterature' ,4), course(2810,'DiscursiveWriting' ,4), course(3010,'ModernEnglishGrammar' ,3), course(3550,'RealismandNaturalism' ,4), course(3720,'IntroductiontoShakespeare' ,3), course(3760,'ModernDrama' ,4), course(3822,'TheShortStory' ,4), course(3870,'TheAmericanNovel' ,5), course(4210,'20th-CenturyPoetry' ,4), course(4725,'AdvancedWorkshopinPoetry' ,5) ); num_changedINTEGER ; BEGIN SELECT COUNT(*) INTO num_changed FROM TABLE (CAST(revisedAS courselist))NEW , TABLE (SELECT courses FROM department WHERE NAME='English' )OLD WHERE NEW .course_no=OLD.course_no AND (NEW .title!=OLD.titleOR NEW .credits!=OLD.credits); DBMS_OUTPUT.put_line(num_changed); END ;

九、使用多级集合

除了标量类型或对象类型集合之外，我们也可以创建集合的集合。例如，我们可以创建元素是变长数组类型的变长数组，元素是嵌套表类型的变长数组等。

在用SQL创建字段类型为嵌套表类型的嵌套表时，Oracle会检查CREATE TABLE语句的语法，看如何定义存储表。

这里有几个例子演示了多级集合的语法。

• 多级VARRAY

DECLARE TYPE t1IS VARRAY(10)OF INTEGER ; TYPE nt1IS VARRAY(10)OF t1;--multilevelvarraytype vat1:=t1(2,3,5); --initializemultilevelvarray nvant1:=nt1(va,t1(55,6,73),t1(2,4),va); iINTEGER ; va1t1; BEGIN --multilevelaccess i:=nva(2)(3);--iwillgetvalue73 DBMS_OUTPUT.put_line(i); --addanewvarrayelementtonva nva.EXTEND; nva(5):=t1(56,32); --replaceaninnervarrayelement nva(4):=t1(45,43,67,43345); --replaceaninnerintegerelement nva(4)(4):=1;--replaces43345with1 --addanewelementtothe4thvarrayelement --andstoreinteger89intoit. nva(4).EXTEND; nva(4)(5):=89; END ;

• 多级嵌套表

DECLARE TYPE tb1IS TABLE OF VARCHAR2 (20); TYPE ntb1IS TABLE OF tb1;--tableoftableelements TYPE tv1IS VARRAY(10)OF INTEGER ; TYPE ntb2IS TABLE OF tv1;--tableofvarrayelements vtb1tb1:=tb1('one' ,'three' ); vntb1ntb1:=ntb1(vtb1); vntb2ntb2:=ntb2(tv1(3,5),tv1(5,7,3));--tableofvarrayelements BEGIN vntb1.EXTEND; vntb1(2):=vntb1(1); --deletethefirstelementinvntb1 vntb1.DELETE (1); --deletethefirststringfromthesecondtableinthenestedtable vntb1(2).DELETE (1); END ; /

• 多级关联数组

DECLARE TYPE tb1IS TABLE OF INTEGER INDEX BY BINARY_INTEGER ; --thefollowingisindex-bytableofindex-bytables TYPE ntb1IS TABLE OF tb1 INDEX BY BINARY_INTEGER ; TYPE va1IS VARRAY(10)OF VARCHAR2 (20); --thefollowingisindex-bytableofvarrayelements TYPE ntb2IS TABLE OF va1 INDEX BY BINARY_INTEGER ; v1va1:=va1('hello' ,'world' ); v2ntb1; v3ntb2; v4tb1; v5tb1;--emptytable BEGIN v4(1):=34; v4(2):=46456; v4(456):=343; v2(23):=v4; v3(34):=va1(33,456,656,343); --assignanemptytabletov2(35)andtryagain v2(35):=v5; v2(35)(2):=78;--itworksnow END ; /

• 多级集合和批量SQL

CREATE TYPE t1IS VARRAY(10)OF INTEGER ; / CREATE TABLE tab1(c1t1); INSERT INTO tab1 VALUES (t1(2,3,5)); INSERT INTO tab1 VALUES (t1(9345,5634,432453)); DECLARE TYPE t2IS TABLE OF t1; v2t2; BEGIN SELECT c1 BULK COLLECT INTO v2 FROM tab1; DBMS_OUTPUT.put_line(v2.COUNT);--prints2 END ; /

十、集合的方法

集合提供了以下几个方法，能帮助我们更加方便维护和使用它：

1. EXISTS
2. COUNT
3. LIMIT
4. FIRST和LAST
5. PRIOR和NEXT
6. EXTEND
7. TRIM
8. DELETE

一个集合方法就是一个内置于集合中并且能够操作集合的函数或过程，可以通过点标志来调用。使用方法如下：

collection_name.method_name[(parameters)]

集合的方法不能在SQL语句中使用。并且，EXTEND和TRIM方法不能用于关联数组。 EXISTS，COUNT，LIMIT，FIRST，LAST，PRIOR和NEXT是函数；EXTEND，TRIM和DELETE是过程。 EXISTS，PRIOR，NEXT，TRIM，EXTEND和DELETE对应的参数是集合的下标索引，通常是整数，但对于关联数组来说也可能是字符 串。

只有EXISTS能用于空集合，如果在空集合上调用其它方法，PL/SQL就会抛出异常COLLECTION_IS_NULL。

1、检测集合中的元素是否存在(EXISTS)

函数EXISTS(n)在第n个元素存在的情况下会返回TRUE，否则返回FALSE。我们主要使用EXISTS和DELETE来维护嵌套表。其中EXISTS还可以防止引用不存在的元素，避免发生异常。下面的例子中，PL/SQL只在元素i存在的情况下执行赋值语句：

IF courses.EXISTS (i)THEN courses(i):=new_course; END IF ;

当下标越界时，EXISTS会返回FALSE，而不是抛出SUBSCRIPT_OUTSIDE_LIMIT异常。

2、计算集合中的元素个数(COUNT)

COUNT能够返回集合所包含的元素个数。例如，当下面的变长数组projects中含有25个元素时，IF条件就为TRUE：

IF projects.COUNT=25THEN ...

COUNT函数很有用，因为集合的当前大小不总是能够被确定。例如，如果我们把一个字段中的值放入嵌套表中，那么嵌套表中会有多少个元素呢？COUNT会给我们答案。

我们可以在任何可以使用整数表达式的地方使用COUNT函数。下例中，我们用COUNT来指定循环的上界值：

FOR iIN 1..courses.COUNTLOOP ...

对于变长数组来说，COUNT值与LAST值恒等，但对于嵌套表来说，正常情况下COUNT值会和LAST值相等。但是，当我们从嵌套表中间删除一个元素，COUNT值就会比LAST值小。

计算元素个数时，COUNT会忽略已经被删除的元素。

3、检测集合的最大容量(LIMIT)

因为嵌套表和关联数组都没有上界限制，所以LIMIT总会返回NULL。但对于变长数组来说，LIMIT会返回它所能容纳元素的个数最大值，该值是 在变长数组声明时指定的，并可用TRIM和EXTEND方法调整。例如下面的变长数组projects在最大容量是25的时候，IF的条件表达式值为真：

IF projects.LIMIT=25THEN ...

我们可以在任何允许使用整数表达式的地方使用LIMIT函数。下面的例子中，我们使用LIMIT来决定是否可以为变长数组再添加15个元素：

IF (projects.COUNT+15)<projects.LIMITTHEN ...

4、查找集合中的首末元素(FIRST和LAST)

FIRST和LAST会返回集合中第一个和最后一个元素在集合中的下标索引值。而对于使用VARCHAR2类型作为键的关联数组来说，会分别返回最 低和最高的键值；键值的高低顺序是基于字符串中字符的二进制值，但是，如果初始化参数NLS_COMP被设置成ANSI的话，键值的高低顺序就受初始化参 数NLS_SORT所影响了。

空集合的FIRST和LAST方法总是返回NULL。只有一个元素的集合，FIRST和LAST会返回相同的索引值。

IF courses.FIRST=courses.LASTTHEN ...--onlyoneelement

下面的例子演示了使用FIRST和LAST函数指定循环范围的下界和上界值：

FOR iIN courses.FIRST..courses.LASTLOOP ...

实际上，我们可以在任何允许使用整数表达式的地方使用FIRST或LAST函数。下例中，我们用FIRST函数来初始化一个循环计数器：

i:=courses.FIRST; WHILE iIS NOT NULL LOOP ...

对于变长数组来说，FIRST恒等于1，LAST恒等于COUNT；但对嵌套表来说，FIRST正常情况返回1，如果我们把第一个元素删除，那么FIRST的值就要大于1，同样，如果我们从嵌套表的中间删除一个元素，LAST就会比COUNT大。