1、Oracle 中的集合(精解) 集合精解Oracle 中一共有三种集合类型：indexby 表、 嵌套表和可变数组。其中indexby 表只能用于 PL/SQL 中，而不能用于数据库表定义。嵌套表可以存储在数据库表中。indexby 表和嵌套表统称为 PL/SQL 表。可变数组被声明为具有固定数目的元素的集合，而 PL /SQL 表没有声明上限。一、index by 表indexby 表在句法上类似于 C 或 JAVA 中的数组。首先需要定义表的属性，然后在声明使用。 句法：TYPE tabletype IS TABLE OF type INDEX BY BINARY_INTEGER;其中 t
2、abletype 是所定义的新类型的类型名，type 是一个预定义的类型，或通过%TYPE 或%ROWTYPE 对一个类型的引用。INDEX BY BINARY_INTEGER 子句是该表定义的一部分。这个子句对于嵌套表是不存在的。一旦声明了类型和变量，就可以引用其中的单个元素：tablename(index)其中 tablename 是表的名称，index 是一个 BINARY_INTEGER 类型的变量，或者是一个可以转换成 BINARY_INTEGER 类型的变量或表达式。注意：a、index-by 表是无大小约束的。其行数的唯一限制(除可用内存外)就是它的关键字是 BINARY_INT
3、EGER 类型的，因此要受到 BINARY_INTEGER 类型所能表示数值的约束(-21474836472147483647)。b、index-by 表中的元素不一定要按任何特定顺序排列。因为它们不像数组那样在内存中是连续存储的，所以其元素可以借助于任意关键字插入(如果你从PL/SQL 中把一个 index-by 表传递到 C 或 JAVA 的主机数组，其元素应该从 1开始依次编号) 。c、用于 index-by 表的关键字没有必要是有序的。任何 BINARY_INTEGER值或表达式都可以用作表的索引。d、关键字的类型是 BINARY_INTEGER、PLS_INTEGERVARCHAR2
4、 。e、index-by 表类似于数据库表，它有 key 和 value 两列。key 的类型是BINARY_INTEGER、PLA_INTEGER 或 VARCHAR2，而 value 的类型是在定义中指定的任何数据类型，可以是简单数值，也可以是记录，或者是对象，也可以是集合。如果 value 的值是一条记录，那么可以使用 table(index)field 来引用该记录的字段。f、 对于 index-by 表中的元素 i 赋值时，如果该元素 i 不存在，实际上会创建一个元素 i，这类似于对数据库表进行的 INSERT 操作。如果对元素 i 引用而其不存在，那么会抛出一个异常。可以使用 DE
5、LETE 方法来删除表元素。例如：DECLARETYPE NameTab IS TABLE OF students.first_name%TYPEINDEX BY BINARY_INTEGER;TYPE DateTab IS TABLE OF DATE INDEX BY BINARY_INTEGER;v_Names NameTab;v_Dates DateTab;BEGINv_Names(1) := Scott;v_Dates(-4) := SYSDATE - 1;END;非标量类型的 index-by 表DECLARETYPE StudentTab IS TABLE OF students%
6、ROWTYPE INDEX BY BINARY_INTEGER;v_Students StudentTab;BEGINSELECT * INTO v_Students(10001)FROM studentsWHERE id=10001;v_Students(1).first_name := Larry;v_Students(1).last_name := Lemon;END;对象类型的 index-by 表CREATE OR REPLACE TYPE MyObject AS OBJECT(field1 NUMBER,field2 VARCHAR2(20),field3 DATE);DECLAR
7、ETYPE ObjectTab IS TABLE OF MyObject INDEX BY BINARY_INTEGER;v_Objects ObjectTab;BEGINv_Objects(1) := MyObject(1,null,null); -用构造函数一次赋值v_Objects(1).field2 := Hello World!; -对单个元素中的域进行赋值v_Objects(1).field3 := SYSDATE;END;二、嵌套表嵌套表的基本功能与 index-by 表相同，嵌套表可以被看做是具有两个列的数据库表。我们可以从嵌套表中删除元素，这样得到一个具有非有序关键字的稀疏表
8、，这个表就像 index-by 表。然而，嵌套表必须用有序的关键字创建，而且关键字不能是负数。此外，嵌套表可以存储到数据库中，而 index-by 表则不能。嵌套表中的最大行数是 2G 字节，这也是最大的关键字值。 创建一个嵌套表类型的句法：TYPE table_name IS TABLE OF table_type NOT NULL;其中 table_name 是新类型的类型名字，table_type 是嵌套表中每一个元素的类型，它可以是用户定义的对象类型，也可以是使用%TYPE 的表达式，但是它不可以是 BOOLEAN、NCHAR、NCLOB、NVARCHAR2 或 REF CURSOR
9、类型。 如果存在 NOT NULL，那么嵌套表的元素不能是 NULL。 当声明了一个嵌套表时，它还没有任何元素，它会被自动初始化为 NULL。此时如果直接使用它的话就会抛出一个 COLLECTION_IS_NULL 的异常。可以使用构造器来进行初始化，嵌套表的构造器与表的类型名本身具有相同的名称。然而，构造器有不定数目的参数，每一个参数都应该与表元素类型相兼容，每一个参数就是其中的表元素。参数成为从索引 1 开始有序的表元素。如果使用的是不带参数的构造器进行初始化，这会创建一个没有元素的空表（这和数学上的空表概念一致），但不会被初始化为 NULL。虽然表是无约束的，但是你不能对不存在的元素赋值
10、，这样将会导致表的大小增加，会抛出一个异常。你可以使用EXTEND 方法来增加嵌套表的大小。例如：DECLARETYPE NumbersTab IS TABLE OF NUMBER;v_Tab1 NumbersTab := NumbersTab(-1);v_Primes NumbersTab := NumbersTab(1,2,3,5,7);v_Tab2 NumbersTab := NumbersTab();BEGINv_Tab1(1) := 12345;FOR v_Count IN 15 LOOPDBMS_OUTPUT.PUT(v_Primes(v_Count) | );END LOOP;D
11、BMS_OUTPUT.NEW_LINE;END;三、可变数组可变数组是一种非常类似于 C 或 JAVA 数组的数据类型。对可变数组的访问与对嵌套表或 index-by 表的访问类似。但是，可变数组在大小方面有一个固定的上界，这个上界作为类型声明的一部分被指定。可变数组是有上界的稀疏数据结构，元素插入可变数组中时从索引 1 开始，一直到在可变数组类型中声明的最大长度。可变数组的极限大小也是 2G 字节。 可变数组的元素在内存中连续存储。这不同于嵌套表的存储，嵌套表更像一个数据库表。 可变数组类型声明的句法：TYPE type_name IS VARRAY | VARYING ARRAY (max
12、imum_size) OF element_type NOT NULL;其中 type_name 是新的可变数组类型的类型名，maximum_size 是一个指定可变数组中元素最大数目的一个整数，element_type 是一个 PL/SQL 标量、记录或对象类型。element_type 可以使用%TYPE 来指定，但是它不可以是BOOLEAN、NCHAR、NCLOB、NVARCHAR2 或 REF CURSOR 类型。可变数组也使用构造器来进行初始化。传递到构造器的参数数目成为可变数组的初始长度，它必须小于或等于在可变数组类型中指定的最大长度。传递的参数就是可变数组的元素。如果引用可变数组
13、的元素超出其界限，那么就会抛出 SUBSCRIPT_OUTSIDE_LIMIT 异常。可变数组的大小也可以使用 EXTEND 方法来增加。不同于嵌套表的是，可变数组不能够被扩展超过为可变数组类型声明的极限大小。例如：DECLARETYPE Numbers IS VARRAY(20) OF NUMBER(3);v_NullList Numbers;v_List1 Numbers := Numbers(1,2);v_List2 Numbers := Numbers(null);BEGINIF v_NullList IS NULL THENDBMS_OUTPUT.PUT_LINE( v_NullLi
14、st is NULL);END IF;IF v_List2(1) IS NULL THENDBMS_OUTPUT.PUT_LINE( v_List2(1) is NULL);END IF;END;四、多重集合多重集合就是集合的集合，多层集合的类型声明与一维集合的声明相同，只是集合类型本身就是一个集合。因此我们使用两个括号来访问多层集合包含的元素。例如：DECLARETYPE t_Numbers IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;TYPE t_MultiNumbers IS TABLE OF t_Numbers INDEX BY BINARY_
15、INTEGER;TYPE t_MultiVarray IS VARRAY(10) OF t_Numbers;TYPE t_MultiNested IS TABLE OF t_Numbers;v_MultiNumbers t_MultiNumbers;BEGINv_MultiNumbers(1)(1) := 12345;END;五、三种集合之间的比较1、可变数组与嵌套表两者的相似之处：两种类型(加上 index-by 表)都使用 PL/SQL 中的下标符号来允许对单个元素的访问。两种类型都可以存储在数据库表中(当在 PL/SQL 语句块外声明时) 。集合方法可以应用于这两种类型。两者的区别：可变
16、数组有大小上限，而嵌套表没有一个明确的大小上限。当存储到数据库中时，可变数组保持了元素的排序和下标的值，而嵌套表却不同。2、嵌套表和 index-by 表两者的相似之处：两种表的数据类型具有相同的结构。两种表中的单个元素都是使用下标符号进行访问的。嵌套表可用的方法包括 index-by 表的所有表属性。两者的区别：嵌套表可以使用 SQL 进行操作，而且可以存储到数据库中，而 index-by 表则不能。嵌套表合法的下标范围为 12147483647，而 index-by 的范围为-21474836472147483647。index-by 表可以有负数下标，而嵌套表则不能。嵌套表可以自动为 N
17、ULL(用 IS NULL 操作符检验)。要添加元素，必须初始化和扩展嵌套表。嵌套表有可用的其它方法，如 EXTEND 和 TRIM。PL/SQL 会自动在主机数组和 index-by 表之间进行转换，但不能在主机数组和嵌套表之间转换。六、数据库中的集合1、存储集合的隐含式a、模式层类型为了从数据库表中存储和检索一个集合，该集合类型必须为 PL/SQL 和SQL 所知。这意味着它不能是 PL/SQL 的局部定义的集合，而应该与对象类型类似，必须使用 CREATE TYPE 语句来声明。在模式层创建的类型对于 PL/SQL 来说是全局的，它有类似于任何其他数据库对象的范围和可见性规则。模式层的类
18、型也可以用作数据库的列。声明为PL /SQL 语句块的局部类型，只在声明它的那个语句块中可见，而且不可用作数据库的列。在包头声明的类型在整个 PL/SQL 中都是可见的，但也仍然不能用作数据库的列。只有模式层的类型才可以用作数据库的列。例如：例子 1：模式级别的SQLCREATE OR REPLACE TYPE NameList AS VARRAY(20) OF VARCHAR2(30);/例子 2：局部的DECLARE TYPE DateList IS VARRAY(10) OF DATE;v_Dates DateList;v_Names NameList;BEGINNULL;END;b、已
19、存储可变数组的结构可变数组可以用作数据库列的类型。在这种情况下，整个可变数组都与其他列并排着被存储到一个数据库行中。不同的行包含不同的可变数组。注意：大于 4K 的可变数组数据实际上将与其余的表列分开存储，它将存储到 LOB 中。任何可变数组列的类型必须为数据库所知，并被存储在数据字典中，因此需要有 CREATE TYPE 语句。例如：CREATE OR REPLACE TYPE BookList AS VARRAY(10) OF NUMBER(4);CREATE TABLE class_material(department CHAR(3),course NUMBER(3),required
20、_reading BooList);c、已存储嵌套表的结构嵌套表也可以被存储为数据库的一个列。数据库表的每一行都可以包含一个不同的嵌套表。注意：表的类型在表定义中使用，就像列对象或内置类型那样。它必须是以CREATE TYPE 语句创建的模式层类型。对于每一个给定数据库表中的嵌套表，都需要 NESTED TABLE 子句。这个子句说明了数据实际存储的存储表的名称。存储表是系统产生的表，用来存储嵌套表中的实际数据。与已存储可变数组不同，嵌套表的数据并不是存储在表列中而是单独存储的(表外存储)。存储表可以以另一种模式存在，而且可以有与主表不同的存储参数。存储表可以在 user_tables 中描述
21、并存在于其中，但是不能被直接访问。例如：CREATE OR REPLACE TYPE StudentList AS TABLE OF NUMBER(5);CREATE TABLE library_catalog(catalog_number NUMBER(4),FOREIGN KEY (catalog_number) REFERENCES books(catalog_number),num_copies NUMBER,num_out NUMBER,checked_out StudentList)NESTED TABLE checked_out STORE AS co_tab;2、操作整个集合你
22、可以使用 SQL 的 DML 语句来操作一个存储在表中的集合。这些类型的操作将影响到整个集合，而非单个元素。集合中的元素可以通过使用PL/SQL 操作，也可以通过 SQL 运算符操作。a、INSERTINSERT 语句被用来将集合插入到数据库行中，必须首先创建并初始化一个集合。b、UPDATEUPDATE 也被用来修改一个存储集合。c、DELETEDELETE 可以删除一个包含集合的行。例如：DECLAREv_StudentList StudentList := StudentList(10000,10001,10002); BEGININSERT INTO library_catalog(c
23、atalog_number,num_copies,num_out)VALUES(1000,20,3);INSERT INTO library_catalog(catalog_number,num_copies,num_out)VALUES(1001,20,3);INSERT INTO library_catalog(catalog_number,num_copies,num_out)VALUES(1000,20,3);INSERT INTO library_catalog(catalog_number,num_copies,num_out)VALUES(1002,20,3);UPDATE li
24、brary_catalogSET checked_out = v_StudentListWHERE catalog_number = 1000;DELETE FROM library_catalogWHERE catalog_number = 1000;END;d、SELECT使用 SELECT 语句可把集合从数据库检索到 PL/SQL 变量中。一旦集合保存到 PL/SQL 中，就可以使用过程化语句来操作它。查询可变数组：例如：CREATE OR REPLACE PROCEDURE PrintRequired(p_Department IN class_material.department%
25、TYPE,p_Course IN class_material.course%TYPE) ISv_Books class_material.required_reading%TYPE;v_Title books.title%TYPE;BEGINSELECT required_readingINTO v_BooksFROM class_materialWHERE department = p_DepartmentAND course = p_Course;FOR v_Index IN 1v_Books.COUNT LOOPSELECT titleINTO v_TitleFROM booksWHE
26、RE catalog_number = v_Books(v_Index);END LOOP;END;查询嵌套表：当一个嵌套表被检索进某 PL/SQL 变量时，它就被赋与从 1 开始一直排到表中元素数的关键字值。后者可以使用 COUNT 方法来确定。例如：CREATE OR REPLACE PACKAGE BODY Library ASPROCEDURE PrintCheckedOut(p_CatalogNumber IN library_catalog.catalog_number%TYPE) IS v_StudentList StudentList;v_Student students%RO
27、WTYPE;v_Book books%ROWTYPE;v_FoundOne BOOLEAN := FALSE;BEGINSELECT checked_outINTO v_StudentListFROM library_catalogWHERE catalog_number = p_CatalogNumber;IF v_StudentList IS NOT NULL THENFOR v_Index IN 1v_StudentList.COUNT LOOP v_FoundOne := TRUE;SELECT * INTO v_StudentFROM StudentsWHERE ID = v_Stu
28、dentList(v_Index); END LOOP;END IF;END;带有无序关键字的已存储表：存储在数据库中的嵌套表不能用 PL/SQL 直接操作，而只能用 SQL 操作。因此，关键字值并不被记录下来。当使用 SELECT 命令从数据库中选择一个嵌套表时，关键字从 1 开始依次重新编号。因而，如果你把一个带有无序关键字的嵌套表插入到数据库时，关键字值将会改变，关键字也会从 1 开始依次重新编号。例如：CREATE OR REPLACE TYPE DateTab AS TABLE OF DATE;CREATE TABLE famous_dates(key VARCHAR2(100) P
29、RIMARY KEY;date_list DateTab)NESTED TABLE date_list STORE AS dates_tab;/DECLAREv_Dates DateTab := DateTab(TO_DATE(04-JUL-1776,DD-MON-YYYY),TO_DATE(12-APR-1861,DD-MON-YYYY),TO_DATE(05-JUN-1968,DD-MON-YYYY),TO_DATE(26-JAN-1986,DD-MON-YYYY),TO_DATE(01-JAN-2001,DD-MON-YYYY);PROCEDURE Print(p_Dates IN Da
30、teTab) IS v_Index BINARY_INTEGER := p_Dates.FIRST;BEGINWHILE v_Index = v_Characters.FIRST LOOPDBMS_OUTPUT.PUT(v_Characters(v_Index);v_Index := v_Characters.PRIOR(v_Index);END LOOP;DBMS_OUTPUT.NEW_LINE;END;6、EXTENDEXTEND 被用来把元素添加到嵌套表或可变数组的末端。它对于 index-by 表是无效的。EXTEND 有三种形式：EXTENDEXTEND(n)EXTEND(n, i)
31、没有参数的 EXTEND 仅仅用索引 LAST+1 把一个 NULL 元素添加到集合的末端。EXTEND(n) 把 n 个 NULL 元素添加到表的末端，而 EXTEND(n, i)把元素 i 的 n 个副本添加到表的末端。如果该集合是用 NOT NULL 约束创建的，那么只有最后的这种可以使用，因为它不添加 NULL 元素。因为嵌套表没有一个明确的大小上限，所以你可以调用带 n 的EXTEND，n 根据需要可取任意大的值( 其大小上限是 2G，同时受内存限制的影响)。然而，可变数组只能被扩展到其大小上限，因此，n 最大可以是(LIMIT - COUNT)。EXTEND 对集合的内部大小进行操
32、作，这包括嵌套表的任何已删除的元素。当一个元素已删除时(DELETE 方法)，该元素的数据也被消除，但是关键字却保留了下来。例如：DECLAREv_Numbers NumTab := NumTab(-2, -1, 0, 1, 2);PROCEDURE Print(p_Table IN NumTab) IS v_Index INTEGER;BEGINv_Index := p_Table.FIRST;WHILE v_Index The syntax is also available for update and delete statements.SET SERVEROUTPUT ONDECLA
33、REl_id t1.id%TYPE;BEGINUPDATE t1SET description = descriptionWHERE description = FOURRETURNING id INTO l_id;DBMS_OUTPUT.put_line(UPDATE ID= | l_id);DELETE FROM t1WHERE description = FOURRETURNING id INTO l_id;DBMS_OUTPUT.put_line(DELETE ID= | l_id);COMMIT;END;/UPDATE ID=4DELETE ID=4PL/SQL procedure
34、successfully completed.SQLWhen DML affects multiple rows we can still use the RETURNING INTO, but now we must return the values into a collection using the BULK COLLECT clause.SET SERVEROUTPUT ONDECLARETYPE t_tab IS TABLE OF t1.id%TYPE;l_tab t_tab;BEGINUPDATE t1SET description = descriptionRETURNING
35、 id BULK COLLECT INTO l_tab;FOR i IN l_tab.first l_tab.last LOOPDBMS_OUTPUT.put_line(UPDATE ID= | l_tab(i);END LOOP;COMMIT;END;/UPDATE ID=1UPDATE ID=2UPDATE ID=3PL/SQL procedure successfully completed.SQLWe can also use the RETURNING INTO clause in combination with bulk binds.SET SERVEROUTPUT ONDECL
36、ARETYPE t_desc_tab IS TABLE OF t1.description%TYPE;TYPE t_tab IS TABLE OF t1%ROWTYPE;l_desc_tab t_desc_tab := t_desc_tab(FIVE, SIX, SEVEN);l_tab t_tab;BEGINFORALL i IN l_desc_tab.first l_desc_tab.lastINSERT INTO t1 VALUES (t1_seq.nextval, l_desc_tab(i)RETURNING id, description BULK COLLECT INTO l_ta
37、b;FOR i IN l_tab.first l_tab.last LOOPDBMS_OUTPUT.put_line(INSERT ID= | l_tab(i).id | DESC= | l_tab(i).description);END LOOP;COMMIT;END;/INSERT ID=5 DESC=FIVEINSERT ID=6 DESC=SIXINSERT ID=7 DESC=SEVENPL/SQL procedure successfully completed.SQLThis functionality is also available from dymanic SQL.SET
38、 SERVEROUTPUT ONDECLARETYPE t_tab IS TABLE OF t1.id%TYPE;l_tab t_tab;BEGINEXECUTE IMMEDIATE UPDATE t1SET description = descriptionRETURNING id INTO :l_tabRETURNING BULK COLLECT INTO l_tab;FOR i IN l_tab.first l_tab.last LOOPDBMS_OUTPUT.put_line(UPDATE ID= | l_tab(i);END LOOP;COMMIT;END;/UPDATE ID=1UPDATE ID=2UPDATE ID=3PL/SQL procedure successfully completed.SQLFor more information see: RETURNING INTO ClauseHope this helps. Regards Tim.-End-
