oracle 11g PL/SQL Programming学习五

宗9706

----------------------------------------------------------------------------

-----------------PL/SQL学习笔记系列 By Cryking-----------------

------------------------转载请注明出处,谢谢!------------------------
　　
　　第七章 集合(Collections)
　　在Oracle 11g里,主要有3种集合类型,他们是变长数组、嵌套表、关联数组.

集合是非常强大的结构,因为它们能在内存管理大型数据集.

集合又可以分为2个类别:数组和列表.

数组通常在定义时分配一个物理大小,而列表没有强制物理限制.

列表可以由非顺序数字或唯一字串来索引,此时列表被称为关联数组。

集合类型

VARRAY和NESTED表可以被定义为SQL类型和PL/SQL类型,它们是以连续的整数为索引的结构(从1开始索引).

连续索引结构不允许空白值出现,并且是密集型结构.VARRAY是以定义时固定数量的元素存储,而NESTED不是.

关联数组(如PL/SQL表)只能是PL/SQL类型,关联数组不是顺序的索引结构.关联数组的大小是动态变化的,像嵌套表,没有固定的大小.

如何选择集合类型:

1.使用变长数组(VARRAY):当集合的物理大小是静态不变,并且可能在表里使用的时候建议使用VARRAY.像其他语言(C/C++)里的数组类型

2.使用嵌套表(NESTED TABLES):当物理大小由运行时变化而变化,并且类型可能在表里使用,建议使用VARRAY.

嵌套表类似其他语言里的列表和包.

3.使用关联数组:当物理大小由运行时变化而变化,并且类型不能使用在表里的时候建议使用关联数组.类似C++的MAP和SET.

可变数组

VARRAY是一维结构.你可以在表、记录、自定义对象类型里使用VARRAY.

语法结构:

TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit)

OF element_type [ NOT NULL ];

索引下标从1开始(注意不是0),其他语言如C/C++的数组都是从0开始.

例:


00:52:39 hr@orcl> DECLARE
00:52:45   2    TYPE INTEGER_VARRAY IS VARRAY(3) OF INTEGER;--定义可变数组类型INTEGER_VARRAY,固定长度为3
00:52:45   3    VARRAY_INTEGER INTEGER_VARRAY := INTEGER_VARRAY(NULL, NULL, NULL);--定义可变数组类型变量,并初始化为NULL
00:52:45   4  BEGIN
00:52:45   5    -- Print initialized null values.
00:52:45   6    DBMS_OUTPUT.PUT_LINE('Varray initialized as nulls.');
00:52:45   7    DBMS_OUTPUT.PUT_LINE('––––––––––––––--------------');
00:52:45   8    FOR I IN 1 .. 3 LOOP --顺序输出数组各个元素
00:52:45   9      DBMS_OUTPUT.PUT('Integer Varray [' || I || '] ');
00:52:45  10      DBMS_OUTPUT.PUT_LINE('[' || VARRAY_INTEGER(I) || ']');
00:52:45  11    END LOOP;
00:52:45  12    VARRAY_INTEGER(1) := 11;--给每个元素赋值
00:52:45  13    VARRAY_INTEGER(2) := 12;
00:52:45  14    VARRAY_INTEGER(3) := 13;
00:52:45  15    -- Print initialized null values.
00:52:45  16    DBMS_OUTPUT.PUT(CHR(10));
00:52:45  17    DBMS_OUTPUT.PUT_LINE('Varray initialized as values.');
00:52:45  18    DBMS_OUTPUT.PUT_LINE('––-––––––––––––--------------');
00:52:45  19    FOR I IN 1 .. 3 LOOP--顺序输出数组各个元素
00:52:45  20      DBMS_OUTPUT.PUT_LINE('Integer Varray [' || I || '] ' || '[' ||
00:52:45  21                           VARRAY_INTEGER(I) || ']');
00:52:45  22    END LOOP;
00:52:45  23  END;
00:52:45  24  /
Varray initialized as nulls.
––––––––––––––--------------
Integer Varray [1] []
Integer Varray [2] []
Integer Varray [3] []
Varray initialized as values.
––-––––––––––––--------------
Integer Varray [1] [11]
Integer Varray [2] [12]
Integer Varray [3] [13]
PL/SQL 过程已成功完成。

　　

一般使用 可变数组类型名() 的方式来初始化可变数组变量为空.

如:varray_integer INTEGER_VARRAY := integer_varray();
　　当遇到集合元素的空间不够时,可通过集合的EXTEND方法来分配.

如：


01:03:20 hr@orcl> DECLARE
01:03:22   2    TYPE INTEGER_VARRAY IS VARRAY(3) OF INTEGER;
01:03:22   3    VARRAY_INTEGER INTEGER_VARRAY := INTEGER_VARRAY();--创建一个没有元素的集合变量
01:03:22   4  BEGIN
01:03:22   5    -- Allocate space as you increment the index.
01:03:22   6    FOR I IN 1 .. 3 LOOP
01:03:22   7      VARRAY_INTEGER.EXTEND; --分配空间,如不要这句,会得到错误:ORA-06533,下标超出数量
01:03:22   8      VARRAY_INTEGER(I) := 10 + I;
01:03:22   9    END LOOP;
01:03:22  10    DBMS_OUTPUT.PUT_LINE('Varray initialized as values.');
01:03:22  11    DBMS_OUTPUT.PUT_LINE('––––--------------––––––––––-');
01:03:22  12    FOR I IN 1 .. 3 LOOP
01:03:22  13      DBMS_OUTPUT.PUT('Integer Varray [' || I || '] ');
01:03:22  14      DBMS_OUTPUT.PUT_LINE('[' || VARRAY_INTEGER(I) || ']');
01:03:22  15    END LOOP;
01:03:22  16  END;
01:03:22  17  /
Varray initialized as values.
––––--------------––––––––––-
Integer Varray [1] [11]
Integer Varray [2] [12]
Integer Varray [3] [13]
PL/SQL 过程已成功完成。

　　

在对象类型中使用可变数组

定义可变数组的对象类型语法为:

CREATE OR REPLACE TYPE type_name AS {VARRAY | VARYING ARRAY} (size_limit)

OF element_type [ NOT NULL ];

例:


01:11:14 hr@orcl> CREATE OR REPLACE TYPE integer_varray AS VARRAY(3) OF INTEGER;
01:11:17   2  /
类型已创建。
已用时间:  00: 00: 00.42
01:11:19 hr@orcl> DECLARE
01:11:24   2    VARRAY_INTEGER INTEGER_VARRAY:=INTEGER_VARRAY(NULL, NULL, NULL);--直接使用对象类型INTEGER_VARRAY
01:11:24   3  BEGIN
01:11:24   4    -- Assign values to replace the null values.
01:11:24   5    FOR I IN 1 .. 3 LOOP
01:11:24   6      VARRAY_INTEGER(I) := 10 + I;
01:11:24   7    END LOOP;
01:11:24   8    -- Print the initialized values.
01:11:24   9    DBMS_OUTPUT.PUT_LINE('Varray initialized as values.');
01:11:24  10    DBMS_OUTPUT.PUT_LINE('––––––––––––––---------------');
01:11:24  11    FOR I IN 1 .. 3 LOOP
01:11:24  12      DBMS_OUTPUT.PUT('Integer Varray [' || I || '] ');
01:11:24  13      DBMS_OUTPUT.PUT_LINE('[' || VARRAY_INTEGER(I) || ']');
01:11:24  14    END LOOP;
01:11:24  15  END;
01:11:26  16  /
Varray initialized as values.
––––––––––––––---------------
Integer Varray [1] [11]
Integer Varray [2] [12]
Integer Varray [3] [13]
PL/SQL 过程已成功完成。

　　

你如何允许或不允许空行?

按照以往的规则,数组是密集型的.密集意味着在连续的存放中不允许空缺.也就是没有空白数据.

但在可变数组里允许NULL值.oracle 11G里不允许你在下标值上创建缺口.(这样下标不连续)

不允许NULL值可以简化数据访问和错误处理.(使用NOT NULL约束来实现)

如:


01:22:26 hr@orcl> CREATE OR REPLACE TYPE integer_varray AS VARRAY(100) OF INTEGER NOT NULL;
01:22:30   2  /
类型已创建。
--不用显示分配NULL的方式来初始化可变数组变量.使用EXTEND来分配,并使用LIMIT属性得到数组的大小,
01:22:31 hr@orcl> DECLARE
01:23:25   2    VARRAY_INTEGER INTEGER_VARRAY := INTEGER_VARRAY();
01:23:25   3  BEGIN
01:23:25   4    FOR I IN 1 .. VARRAY_INTEGER.LIMIT LOOP --可变数组在定义时固定大小了,可用LIMIT属性访问到
01:23:25   5      VARRAY_INTEGER.EXTEND;
01:23:25   6    END LOOP;
01:23:25   7    DBMS_OUTPUT.PUT('Integer Varray Initialized ');
01:23:25   8    DBMS_OUTPUT.PUT_LINE('[' || VARRAY_INTEGER.COUNT || ']');
01:23:25   9  END;
01:23:25  10  /
Integer Varray Initialized [100]
PL/SQL 过程已成功完成。

　　

在表列里使用可变数组

关系型数据库的第三范式：

1.表应该包含主键,用来唯一标志一行

2.表不应该包含复合列，像集合包含逗号字符串.

3.表应不包含任何传递依赖.也就是你设计单表时,应该至少还有另外一个表来解决每个传递依赖.

传递依赖是指列数据在依赖主键之前依赖一个或多个列.

不建议在表列里使用可变数组类型.
　　在表中定义可变数组


01:37:55 hr@orcl> CREATE OR REPLACE TYPE strings AS VARRAY(3) OF VARCHAR2(30 CHAR);
01:37:59   2  /
类型已创建。
01:38:02 hr@orcl> CREATE TABLE individuals
01:39:13   2  ( individual_id INTEGER NOT NULL
01:39:13   3  , first_name VARCHAR2(30 CHAR) NOT NULL
01:39:13   4  , middle_name VARCHAR2(30 CHAR)
01:39:13   5  , last_name VARCHAR2(30 CHAR) NOT NULL
01:39:13   6  , title VARCHAR2(10 CHAR)
01:39:13   7  , CONSTRAINT indiv_pk PRIMARY KEY(individual_id));
表已创建。
01:40:10 hr@orcl> CREATE TABLE addresses
01:40:52   2  ( address_id INTEGER NOT NULL
01:40:52   3  , individual_id INTEGER NOT NULL
01:40:52   4  , street_address STRINGS NOT NULL--使用可变数组类型strings
01:40:52   5  , city VARCHAR2(20 CHAR) NOT NULL
01:40:52   6  , state VARCHAR2(20 CHAR) NOT NULL
01:40:52   7  , postal_code VARCHAR2(20 CHAR) NOT NULL
01:40:52   8  , country_code VARCHAR2(10 CHAR) NOT NULL
01:40:52   9  , CONSTRAINT addr_pk PRIMARY KEY(address_id)
01:40:52  10  , CONSTRAINT addr_indiv_fk FOREIGN KEY(individual_id)
01:40:52  11  REFERENCES individuals (individual_id));
表已创建。
--插入数据(为方便测试已经干掉外键约束)
01:45:20 hr@orcl> INSERT INTO ADDRESSES
01:45:44   2  VALUES
01:45:44   3    (1,
01:45:44   4     2,
01:45:44   5     STRINGS('Office of Senator McCain',--用构造函数STRINGS初始化列值
01:45:44   6             '450 West Paseo Redondo',
01:45:44   7             'Suite 200'),
01:45:44   8     'Tucson',
01:45:44   9     'AZ',
01:45:44  10     '85701',
01:45:44  11     'USA');
已创建 1 行。
--查询可变数组类型列数据
01:45:44 hr@orcl> SELECT STREET_ADDRESS FROM ADDRESSES;
STREET_ADDRESS
--------------------------------------------------------------------------
STRINGS('Office of Senator McCain', '450 West Paseo Redondo', 'Suite 200')
已选择 1 行。

　　

查询以前也是DML操作的一种,因为它能锁行.现在称为DQL(数据查询语言)

--创建嵌套表类型
01:49:44 hr@orcl> CREATE OR REPLACE TYPE varray_nested_table IS TABLE OF VARCHAR2(30 CHAR);
01:49:47   2  /
类型已创建。
--将可变数组类型数据强制转换为嵌套表类型,注意结果显示格式与之前的不一样(内容一样)
01:49:49 hr@orcl> SELECT NESTED.COLUMN_VALUE
01:50:49   2    FROM ADDRESSES A,
01:50:49   3         TABLE(CAST(A.STREET_ADDRESS AS VARRAY_NESTED_TABLE)) NESTED
01:50:49   4   WHERE ADDRESS_ID = 1;
COLUMN_VALUE
------------------------------------------------------------
Office of Senator McCain
450 West Paseo Redondo
Suite 200
已选择3行。
--可变数组类型数据更新
01:52:24 hr@orcl> UPDATE ADDRESSES
01:54:37   2     SET STREET_ADDRESS = STRINGS('Office of Senator McCain',
01:54:37   3                                  '2400 E. Arizona Biltmore Cir.',
01:54:37   4                                  'Suite 1150')
01:54:37   5   WHERE ADDRESS_ID = 1;
已更新 1 行。

　　

你不能使用SQL直接或间接地更新可变数组类型数据的部分值(如STRINGS里的Suite 200),必须使用PL/SQL来更新。

如:

DECLARE

TYPE ADDRESS_TYPE IS RECORD(

ADDRESS_ID INTEGER,

INDIVIDUAL_ID INTEGER,

STREET_ADDRESS STRINGS,

CITY VARCHAR2(20 CHAR),

STATE VARCHAR2(20 CHAR),

POSTAL_CODE VARCHAR2(20 CHAR),

COUNTRY_CODE VARCHAR2(10 CHAR));

ADDRESS ADDRESS_TYPE;

CURSOR GET_STREET_ADDRESS(ADDRESS_ID_IN INTEGER) IS

SELECT * FROM ADDRESSES WHERE ADDRESS_ID = ADDRESS_ID_IN;

BEGIN

-- Access the cursor.

OPEN GET_STREET_ADDRESS(1);

FETCH GET_STREET_ADDRESS

INTO ADDRESS;

CLOSE GET_STREET_ADDRESS;

ADDRESS.STREET_ADDRESS(1) := 'Office of Senator John McCain';--更改可变数组列数据中的第一部分数据

UPDATE ADDRESSES SET STREET_ADDRESS = ADDRESS.STREET_ADDRESS WHERE ADDRESS_ID = 1;--整体更新

END;

---

个人感觉上面的匿名块也可以通过SQL来实现,如:

UPDATE ADDRESSES

SET STREET_ADDRESS = STRINGS('Office of Senator John McCain',--仅改变第一部分数据

'2400 E. Arizona Biltmore Cir.',

'Suite 1150')

WHERE ADDRESS_ID = 1;
　　

嵌套表

像可变数组,嵌套表也是一维结构.

你可以在表、记录、自定义类型中使用嵌套表.

嵌套表没有初始的最大大小.
　　嵌套表在PL/SQL中使用

语法

TYPE type_name IS TABLE OF element_type [ NOT NULL ];

示例:


14:44:00 hr@orcl> DECLARE
14:44:02   2    TYPE CARD_TABLE IS TABLE OF VARCHAR2(5 CHAR);--定义嵌套表类型
14:44:02   3    -- Declare a nested table with null values.
14:44:02   4    CARDS CARD_TABLE := CARD_TABLE(NULL, NULL, NULL);
14:44:02   5  BEGIN
14:44:02   6    --打印初始值
14:44:02   7    DBMS_OUTPUT.PUT_LINE('Nested table initialized as null values.');
14:44:02   8    DBMS_OUTPUT.PUT_LINE('--------------------––––––––––––––––––––');
14:44:02   9    FOR I IN 1 .. 3 LOOP
14:44:02  10      DBMS_OUTPUT.PUT('Cards Varray [' || I || '] ');
14:44:02  11      DBMS_OUTPUT.PUT_LINE('[' || CARDS(I) || ']');
14:44:02  12    END LOOP;
14:44:02  13    --元素赋值
14:44:02  14    CARDS(1) := 'Ace';
14:44:02  15    CARDS(2) := 'Two';
14:44:02  16    CARDS(3) := 'Three';
14:44:02  17    DBMS_OUTPUT.PUT(CHR(10)); -- Visual line break.
14:44:02  18    DBMS_OUTPUT.PUT_LINE('Nested table initialized as 11, 12 and 13.');
14:44:02  19    DBMS_OUTPUT.PUT_LINE('---------------------–––––––––––––––––––––');
14:44:02  20    FOR I IN 1 .. 3 LOOP
14:44:02  21      DBMS_OUTPUT.PUT_LINE('Cards [' || I || '] ' || '[' ||CARDS(I)|| ']');
14:44:02  22    END LOOP;
14:44:02  23  END;
14:44:02  24  /
Nested table initialized as null values.
--------------------––––––––––––––––––––
Cards Varray [1] []
Cards Varray [2] []
Cards Varray [3] []
Nested table initialized as 11, 12 and 13.
---------------------–––––––––––––––––––––
Cards [1] [Ace]
Cards [2] [Two]
Cards [3] [Three]
PL/SQL 过程已成功完成。

　　

如果你初始化集合变量失败,你将得到错误信息:ORA-06531.

--使用EXTEND分配空间来动态初始化嵌套表变量
14:50:05 hr@orcl> DECLARE
14:50:06   2    TYPE CARD_SUIT IS TABLE OF VARCHAR2(5 CHAR);
14:50:06   3    CARDS CARD_SUIT := CARD_SUIT(); --声明没有任何元素的集合变量
14:50:06   4  BEGIN
14:50:06   5    FOR I IN 1 .. 3 LOOP
14:50:06   6      CARDS.EXTEND; --分配1个元素空间
14:50:06   7      IF I = 1 THEN
14:50:06   8        CARDS(I) := 'Ace';
14:50:06   9      ELSIF I = 2 THEN
14:50:06  10        CARDS(I) := 'Two';
14:50:06  11      ELSIF I = 3 THEN
14:50:06  12        CARDS(I) := 'Three';
14:50:06  13      END IF;
14:50:06  14    END LOOP;
14:50:06  15    DBMS_OUTPUT.PUT_LINE('Nested table initialized as Ace, Two and Three.');
14:50:06  16    DBMS_OUTPUT.PUT_LINE('--------------------------––––––––––––––––––––-');
14:50:06  17    FOR I IN 1 .. 3 LOOP
14:50:06  18      DBMS_OUTPUT.PUT('Cards [' || I || '] ');
14:50:06  19      DBMS_OUTPUT.PUT_LINE('[' || CARDS(I) || ']');
14:50:06  20    END LOOP;
14:50:06  21  END;
14:50:07  22  /
Nested table initialized as Ace, Two and Three.
--------------------------––––––––––––––––––––-
Cards [1] [Ace]
Cards [2] [Two]
Cards [3] [Three]
PL/SQL 过程已成功完成。

　　

嵌套表做为类型使用

语法:

CREATE OR REPLACE TYPE type_name AS TABLE OF element_type [ NOT NULL ];

嵌套表中元素缺省是允许NULL值的.

示例:


14:55:36 hr@orcl> CREATE OR REPLACE TYPE card_table AS TABLE OF VARCHAR2(5 CHAR);
14:55:38   2  /
类型已创建。
14:55:40 hr@orcl> DECLARE
14:57:31   2    -- 用NULL值声明嵌套表变量CARDS
14:57:31   3    CARDS CARD_TABLE := CARD_TABLE(NULL, NULL, NULL);
14:57:31   4  BEGIN
14:57:31   5    DBMS_OUTPUT.PUT_LINE('Nested table initialized as nulls.');
14:57:31   6    DBMS_OUTPUT.PUT_LINE('--------------––––––––––––––––––––');
14:57:31   7    FOR I IN 1 .. 3 LOOP
14:57:31   8      DBMS_OUTPUT.PUT('Cards Varray [' || I || '] ');
14:57:31   9      DBMS_OUTPUT.PUT_LINE('[' || CARDS(I) || ']');
14:57:31  10    END LOOP;
14:57:31  11    --给嵌套表变量的各元素赋值
14:57:31  12    CARDS(1) := 'Ace';
14:57:31  13    CARDS(2) := 'Two';
14:57:31  14    CARDS(3) := 'Three';
14:57:31  15    DBMS_OUTPUT.PUT(CHR(10)); --换行
14:57:31  16    DBMS_OUTPUT.PUT_LINE('Nested table initialized as Ace, Two and Three.');
14:57:31  17    DBMS_OUTPUT.PUT_LINE('-----------------------–––––––––––––––––––––––-');
14:57:31  18    FOR I IN 1 .. 3 LOOP
14:57:31  19      DBMS_OUTPUT.PUT_LINE('Cards [' || I || '] ' || '[' || CARDS(I)|| ']');
14:57:31  20    END LOOP;
14:57:31  21  END;
14:57:32  22  /
Nested table initialized as nulls.
--------------––––––––––––––––––––
Cards Varray [1] []
Cards Varray [2] []
Cards Varray [3] []
Nested table initialized as Ace, Two and Three.
-----------------------–––––––––––––––––––––––-
Cards [1] [Ace]
Cards [2] [Two]
Cards [3] [Three]
PL/SQL 过程已成功完成。

　　

可变数组与嵌套表对比使用(可变数组大小固定,嵌套表不固定,都是从下标1开始)


--创建可变数组类型card_unit_varray
15:08:03 hr@orcl> CREATE OR REPLACE TYPE card_unit_varray AS VARRAY(13) OF VARCHAR2(5 CHAR);
15:08:05   2  /
类型已创建。
--创建可变数组类型card_suit_varray
15:08:05 hr@orcl> CREATE OR REPLACE TYPE card_suit_varray AS VARRAY(4) OF VARCHAR2(8 CHAR);
15:08:05   2  /
类型已创建。
--创建嵌套表类型card_deck_table
15:08:06 hr@orcl> CREATE OR REPLACE TYPE card_deck_table AS TABLE OF VARCHAR2(17 CHAR);
15:08:06   2  /
类型已创建。
15:12:31 hr@orcl> DECLARE
15:12:32   2    COUNTER INTEGER := 0;
15:12:32   3    --声明可变数组变量SUITS并初始化
15:12:32   4    SUITS CARD_SUIT_VARRAY := CARD_SUIT_VARRAY('Clubs',
15:12:32   5                                               'Diamonds',
15:12:32   6                                               'Hearts',
15:12:32   7                                               'Spades');
15:12:32   8    --声明可变数组变量UNITS并初始化
15:12:32   9    UNITS CARD_UNIT_VARRAY := CARD_UNIT_VARRAY('Ace',
15:12:32  10                                               'Two',
15:12:32  11                                               'Three',
15:12:32  12                                               'Four',
15:12:32  13                                               'Five',
15:12:32  14                                               'Six',
15:12:32  15                                               'Seven',
15:12:32  16                                               'Eight',
15:12:32  17                                               'Nine',
15:12:32  18                                               'Ten',
15:12:32  19                                               'Jack',
15:12:32  20                                               'Queen',
15:12:32  21                                               'King');
15:12:32  22    --声明空元素嵌套表变量DECK
15:12:32  23    DECK CARD_DECK_TABLE := CARD_DECK_TABLE();
15:12:32  24  BEGIN
15:12:32  25    FOR I IN 1 .. SUITS.COUNT LOOP
15:12:32  26      FOR J IN 1 .. UNITS.COUNT LOOP
15:12:32  27        COUNTER := COUNTER + 1;
15:12:32  28        DECK.EXTEND;--分配空间
15:12:32  29        DECK(COUNTER) := UNITS(J) || ' of ' || SUITS(I);
15:12:32  30      END LOOP;
15:12:32  31    END LOOP;
15:12:32  32    DBMS_OUTPUT.PUT_LINE('Deck of cards by suit.');
15:12:32  33    DBMS_OUTPUT.PUT_LINE('–––––-----------––––––');
15:12:32  34    FOR I IN 1 .. COUNTER LOOP
15:12:32  35      DBMS_OUTPUT.PUT_LINE('[' || DECK(I) || ']');
15:12:32  36    END LOOP;
15:12:32  37  END;
15:12:32  38  /
Deck of cards by suit.
–––––-----------––––––
[Ace of Clubs]
[Two of Clubs]
[Three of Clubs]
[Four of Clubs]
[Five of Clubs]
[Six of Clubs]
[Seven of Clubs]
[Eight of Clubs]
[Nine of Clubs]
[Ten of Clubs]
[Jack of Clubs]
[Queen of Clubs]
[King of Clubs]
[Ace of Diamonds]
[Two of Diamonds]
[Three of Diamonds]
[Four of Diamonds]
[Five of Diamonds]
[Six of Diamonds]
[Seven of Diamonds]
[Eight of Diamonds]
[Nine of Diamonds]
[Ten of Diamonds]
[Jack of Diamonds]
[Queen of Diamonds]
[King of Diamonds]
[Ace of Hearts]
[Two of Hearts]
[Three of Hearts]
[Four of Hearts]
[Five of Hearts]
[Six of Hearts]
[Seven of Hearts]
[Eight of Hearts]
[Nine of Hearts]
[Ten of Hearts]
[Jack of Hearts]
[Queen of Hearts]
[King of Hearts]
[Ace of Spades]
[Two of Spades]
[Three of Spades]
[Four of Spades]
[Five of Spades]
[Six of Spades]
[Seven of Spades]
[Eight of Spades]
[Nine of Spades]
[Ten of Spades]
[Jack of Spades]
[Queen of Spades]
[King of Spades]
PL/SQL 过程已成功完成。

　　


嵌套表做列数据类型

嵌套表列类型不允许使用NOT NULL约束.如果你对这样的列使用NOT NULL约束,你将得到错误ORA-02331.

如:


15:21:12 hr@orcl> CREATE OR REPLACE TYPE address_table AS TABLE OF VARCHAR2(30 CHAR) NOT NULL;
15:21:15   2  /
类型已创建。
已用时间:  00: 00: 00.18
15:21:16 hr@orcl> create table ADDRESSES1
15:23:03   2  (
15:23:03   3    ADDRESS_ID     INTEGER not null,
15:23:03   4    INDIVIDUAL_ID  INTEGER not null,
15:23:03   5    STREET_ADDRESS address_table not NULL
15:23:03   6  );
STREET_ADDRESS address_table not NULL
*
第 5 行出现错误:
ORA-02331: 无法创建数据类型为 Named Table Type 的列的约束条件
或
15:26:30 hr@orcl> CREATE OR REPLACE TYPE address_table AS TABLE OF VARCHAR2(30 CHAR);
15:26:36   2  /
类型已创建。
已用时间:  00: 00: 00.04
15:26:38 hr@orcl> create table ADDRESSES1
15:26:48   2  (
15:26:48   3    ADDRESS_ID     INTEGER not null,
15:26:48   4    INDIVIDUAL_ID  INTEGER not null,
15:26:48   5    STREET_ADDRESS address_table NOT NULL
15:26:48   6  );
STREET_ADDRESS address_table NOT NULL
*
第 5 行出现错误:
ORA-02331: 无法创建数据类型为 Named Table Type 的列的约束条件
--嵌套表做为表列的值可以是NULL的(不管创建的嵌套表类型有没有加NOT NULL)
如:
15:33:46 hr@orcl> create table ADDRESSES1
15:33:48   2  (
15:33:48   3    ADDRESS_ID     INTEGER not null,
15:33:48   4    INDIVIDUAL_ID  INTEGER not null,
15:33:48   5    STREET_ADDRESS address_table
15:33:48   6  )NESTED TABLE STREET_ADDRESS STORE AS test_nest;
表已创建。
15:35:20 hr@orcl> INSERT INTO ADDRESSES1 VALUES(3,4,NULL);
已创建 1 行。
已用时间:  00: 00: 00.03
15:35:44 hr@orcl> SELECT STREET_ADDRESS FROM ADDRESSES1;
STREET_ADDRESS
--------------------------------------------------------
<null>
已选择 1 行。

　　

嵌套表表列元素插入、更新、查询与可变数组的一样.


--更新嵌套表元素的另一种方式
15:59:45 hr@orcl>         UPDATE TABLE (SELECT STREET_ADDRESS FROM ADDRESSES1 WHERE ADDRESS_ID = 3)
16:00:01   2     SET COLUMN_VALUE = 'Office of Senator John McCain'
16:00:01   3   WHERE  COLUMN_VALUE IS NULL;
已更新2行。
--利用UPDATE来进行元素的INSERT操作(使用PL/SQL)
16:07:11 hr@orcl> DECLARE
16:07:37   2    TYPE ADDRESS_TYPE IS RECORD(
16:07:37   3      ADDRESS_ID     INTEGER,
16:07:37   4      INDIVIDUAL_ID  INTEGER,
16:07:37   5      STREET_ADDRESS ADDRESS_TABLE);
16:07:37   6    ADDRESS ADDRESS_TYPE;
16:07:37   7    CURSOR GET_STREET_ADDRESS(ADDRESS_ID_IN INTEGER) IS
16:07:37   8      SELECT * FROM ADDRESSES1 WHERE ADDRESS_ID = ADDRESS_ID_IN;
16:07:37   9
16:07:37  10  BEGIN
16:07:37  11    -- Access the cursor.
16:07:37  12    OPEN GET_STREET_ADDRESS(3);
16:07:37  13    FETCH GET_STREET_ADDRESS
16:07:37  14      INTO ADDRESS;
16:07:37  15    CLOSE GET_STREET_ADDRESS;
16:07:37  16    --增加元素.
16:07:37  17    ADDRESS.STREET_ADDRESS.EXTEND(2); --注意要先分配空间
16:07:37  18    ADDRESS.STREET_ADDRESS(2) := 'JFK Building';
16:07:37  19    ADDRESS.STREET_ADDRESS(3) := 'Suite 2400';
16:07:37  20    --更新到表列
16:07:37  21    UPDATE ADDRESSES1
16:07:37  22       SET STREET_ADDRESS = ADDRESS.STREET_ADDRESS
16:07:37  23     WHERE ADDRESS_ID = 3;
16:07:37  24  END;
16:07:37  25  /
PL/SQL 过程已成功完成。

　　

关联数组
关联数组可视为记录或用户自定义类型的一维数组,被称为PL/SQL表.

关联数组不能在表中使用.只能在PL/SQL中访问.

关于关联数组使用中应注意的问题:

1.关联数组不需要初始化,而且没有像可变数组或嵌套表一样的构造函数语法.

它们在赋值之前不需要分配空间.(不需要EXTEND)

2.使用整数作为索引值(下标值),在11g可使用唯一的变长字符串作为索引值.

3.可使用%ROWTYPE,记录类型,对象类型等.

4.可使用FORALL或BULK COLLECT等子句批量把记录从表转换到程序单元.

5.当使用字符串作为索引值时,要进行特殊处理(就是要注意字符集属性),如NLS_COMP或NLS_SORT初始化参数.

关联数组的使用

语法

CREATE OR REPLACE TYPE type_name AS TABLE OF element_type [ NOT NULL ]

INDEX BY [ PLS_INTEGER | BINARY_INTEGER | VARCHAR2(size) ];

或

CREATE OR REPLACE TYPE type_name AS TABLE OF element_type [ NOT NULL ]

INDEX BY key_type;

key_type可以是VARCHAR2, STRING,LONG等数据类型.

--注意关联数组不用初始化,没有构造函数似的语法
23:59:45 hr@orcl> DECLARE
00:00:30   2    TYPE CARD_TABLE IS TABLE OF VARCHAR2(5 CHAR) INDEX BY BINARY_INTEGER;
00:00:30   3    CARDS CARD_TABLE := CARD_TABLE('A', 'B', 'C');
00:00:30   4  BEGIN
00:00:30   5    NULL;
00:00:30   6  END;
00:00:31   7  /
CARDS CARD_TABLE := CARD_TABLE('A', 'B', 'C');
*
第 3 行出现错误:
ORA-06550: 第 3 行, 第 23 列:
PLS-00222: 在此范围中不存在名为 'CARD_TABLE' 的函数
ORA-06550: 第 3 行, 第 9 列:
PL/SQL: Item ignored
--同样的没有元素的时候,关联数组是不可使用的
00:00:32 hr@orcl> DECLARE
00:02:42   2    TYPE CARD_TABLE IS TABLE OF VARCHAR2(5 CHAR) INDEX BY BINARY_INTEGER;
00:02:42   3    CARDS CARD_TABLE;
00:02:42   4  BEGIN
00:02:42   5    DBMS_OUTPUT.PUT_LINE(CARDS(1));
00:02:42   6  END;
00:02:43   7  /
DECLARE
*
第 1 行出现错误:
ORA-01403: 未找到任何数据
ORA-06512: 在 line 5
注意错误信息与使用可变数组或嵌套表的时候不一样
00:02:44 hr@orcl> DECLARE
00:05:14   2    TYPE CARD_TABLE IS TABLE OF VARCHAR2(5 CHAR);--使用嵌套表类型
00:05:14   3    CARDS CARD_TABLE;
00:05:14   4  BEGIN
00:05:14   5    DBMS_OUTPUT.PUT_LINE(CARDS(1));
00:05:14   6  END;
00:05:15   7  /
DECLARE
*
第 1 行出现错误:
ORA-06531: 引用未初始化的收集
ORA-06512: 在 line 5
--尝试在关联数组中使用EXTEND来分配空间
00:05:15 hr@orcl> DECLARE
00:07:31   2    TYPE CARD_TABLE IS TABLE OF VARCHAR2(5 CHAR) INDEX BY BINARY_INTEGER;
00:07:31   3    CARDS CARD_TABLE;
00:07:31   4  BEGIN
00:07:31   5    IF CARDS.COUNT <> 0 THEN
00:07:31   6      DBMS_OUTPUT.PUT_LINE(CARDS(1));
00:07:31   7    ELSE
00:07:31   8      CARDS.EXTEND;
00:07:31   9    END IF;
00:07:31  10  END;
00:07:31  11  /
CARDS.EXTEND;
*
第 8 行出现错误:
ORA-06550: 第 8 行, 第 5 列:
PLS-00306: 调用 'EXTEND' 时参数个数或类型错误
ORA-06550: 第 8 行, 第 5 列:
PL/SQL: Statement ignored

　　
EXTEND只能使用在可变数组和嵌套表中.

--关联数组正常使用
00:14:05 hr@orcl> DECLARE
00:14:09   2    --定义可变数组(9个字符串长度,最大可放12个元素).
00:14:09   3    TYPE MONTHS_VARRAY IS VARRAY(12) OF STRING(9 CHAR);
00:14:09   4    --定义关联数组(元素类型为CHAR(9))
00:14:09   5    TYPE CALENDAR_TABLE IS TABLE OF VARCHAR2(9 CHAR) INDEX BY BINARY_INTEGER;
00:14:09   6    --声明并构造一个可变数组变量
00:14:09   7    MONTH MONTHS_VARRAY := MONTHS_VARRAY('January',
00:14:09   8                                         'February',
00:14:09   9                                         'March',
00:14:09  10                                         'April',
00:14:09  11                                         'May',
00:14:09  12                                         'June',
00:14:09  13                                         'July',
00:14:09  14                                         'August',
00:14:09  15                                         'September',
00:14:09  16                                         'October',
00:14:09  17                                         'November',
00:14:09  18                                         'December');
00:14:09  19    --声明一个关联数组变量
00:14:09  20    CALENDAR CALENDAR_TABLE;
00:14:09  21  BEGIN
00:14:09  22    --检查关联数组变量是否有元素,没有就赋值为可变数组对应的值
00:14:09  23    IF CALENDAR.COUNT = 0 THEN
00:14:09  24      DBMS_OUTPUT.PUT_LINE('Assignment loop:');
00:14:09  25      DBMS_OUTPUT.PUT_LINE('–--------–––––––');
00:14:09  26      FOR I IN MONTH.FIRST .. MONTH.LAST LOOP
00:14:09  27        CALENDAR(I) := '';
00:14:09  28        DBMS_OUTPUT.PUT_LINE('Index [' || I || '] is [' || CALENDAR(I) || ']');
00:14:09  29        CALENDAR(I) := MONTH(I);
00:14:09  30      END LOOP;
00:14:09  31      --打印关联数组中的元素
00:14:09  32      DBMS_OUTPUT.PUT(CHR(10));
00:14:09  33      DBMS_OUTPUT.PUT_LINE('Post-assignment loop:');
00:14:09  34      DBMS_OUTPUT.PUT_LINE('––––––––––-----------');
00:14:09  35      FOR I IN CALENDAR.FIRST .. CALENDAR.LAST LOOP
00:14:09  36        DBMS_OUTPUT.PUT_LINE('Index [' || I || '] is [' || CALENDAR(I) || ']');
00:14:09  37      END LOOP;
00:14:09  38    END IF;
00:14:09  39  END;
00:14:09  40  /
Assignment loop:
–--------–––––––
Index [1] is []
Index [2] is []
Index [3] is []
Index [4] is []
Index [5] is []
Index [6] is []
Index [7] is []
Index [8] is []
Index [9] is []
Index [10] is []
Index [11] is []
Index [12] is []
Post-assignment loop:
––––––––––-----------
Index [1] is [January]
Index [2] is [February]
Index [3] is [March]
Index [4] is [April]
Index [5] is [May]
Index [6] is [June]
Index [7] is [July]
Index [8] is [August]
Index [9] is [September]
Index [10] is [October]
Index [11] is [November]
Index [12] is [December]
PL/SQL 过程已成功完成。

　　


--下标为字符串类型的关联数组的使用
00:24:04 hr@orcl> DECLARE
00:24:09   2    CURRENT VARCHAR2(9 CHAR);
00:24:09   3    ELEMENT INTEGER;
00:24:09   4    TYPE MONTHS_VARRAY IS VARRAY(12) OF STRING(9 CHAR);
00:24:09   5    --定义下标为字符串的关联数组
00:24:09   6    TYPE CALENDAR_TABLE IS TABLE OF VARCHAR2(9 CHAR) INDEX BY VARCHAR2(9 CHAR);
00:24:09   7    MONTH    MONTHS_VARRAY := MONTHS_VARRAY('January',
00:24:09   8                                            'February',
00:24:09   9                                            'March',
00:24:09  10                                            'April',
00:24:09  11                                            'May',
00:24:09  12                                            'June',
00:24:09  13                                            'July',
00:24:09  14                                            'August',
00:24:09  15                                            'September',
00:24:09  16                                            'October',
00:24:09  17                                            'November',
00:24:09  18                                            'December');
00:24:09  19    CALENDAR CALENDAR_TABLE;
00:24:09  20  BEGIN
00:24:09  21    IF CALENDAR.COUNT = 0 THEN
00:24:09  22      DBMS_OUTPUT.PUT_LINE('Assignment loop:');
00:24:09  23      DBMS_OUTPUT.PUT_LINE('––--------––––––');
00:24:09  24      FOR I IN MONTH.FIRST .. MONTH.LAST LOOP
00:24:09  25        CALENDAR(MONTH(I)) := TO_CHAR(I);--关联数组的元素值为可变数组的下标值
00:24:09  26        DBMS_OUTPUT.PUT_LINE('Index [' || MONTH(I) || '] is [' || I || ']');
00:24:09  27      END LOOP;
00:24:09  28      DBMS_OUTPUT.PUT(CHR(10));
00:24:09  29      DBMS_OUTPUT.PUT_LINE('Post-assignment loop:');
00:24:09  30      DBMS_OUTPUT.PUT_LINE('–––––––––----------–-');
00:24:09  31      FOR I IN 1 .. CALENDAR.COUNT LOOP
00:24:09  32        IF I = 1 THEN
00:24:09  33          --使用FIRST方法得到第一个下标的值并赋值给变量
00:24:09  34          CURRENT := CALENDAR.FIRST;
00:24:09  35          --使用得到的下标值来访问第一个元素
00:24:09  36          ELEMENT := CALENDAR(CURRENT);
00:24:09  37        ELSE
00:24:09  38          --使用NEXT方法来检测下一个元素是否存在
00:24:09  39          IF CALENDAR.NEXT(CURRENT) IS NOT NULL THEN
00:24:09  40            --得到下一个元素的下标值并赋值给变量
00:24:09  41            CURRENT := CALENDAR.NEXT(CURRENT);
00:24:09  42            ELEMENT := CALENDAR(CURRENT);
00:24:09  43          ELSE
00:24:09  44            --如果没有元素存在就退出
00:24:09  45            EXIT;
00:24:09  46          END IF;
00:24:09  47        END IF;
00:24:09  48        --打印索引值和对应的元素值
00:24:09  49        DBMS_OUTPUT.PUT_LINE('Index [' || CURRENT || '] is [' || ELEMENT || ']');
00:24:09  50      END LOOP;
00:24:09  51    END IF;
00:24:09  52  END;
00:24:09  53  /
Assignment loop:
––--------––––––
Index [January] is [1]
Index [February] is [2]
Index [March] is [3]
Index [April] is [4]
Index [May] is [5]
Index [June] is [6]
Index [July] is [7]
Index [August] is [8]
Index [September] is [9]
Index [October] is [10]
Index [November] is [11]
Index [December] is [12]
Post-assignment loop:
–––––––––----------–-
Index [April] is [4]
Index [August] is [8]
Index [December] is [12]
Index [February] is [2]
Index [January] is [1]
Index [July] is [7]
Index [June] is [6]
Index [March] is [3]
Index [May] is [5]
Index [November] is [11]
Index [October] is [10]
Index [September] is [9]
PL/SQL 过程已成功完成。

　　


集合操作符

CARDINALITY:计算集合内的元素数量,可与SET一起使用来统计不重复的元素数量.语法:CARDINALITY(collection)

EMPTY: 检查变量是否为空.语法:variable_name IS [NOT] EMPTY

MEMBER OF:检查左边的操作数是否是右边的集合变量的成员.语法:variable_name MEMBER OF collection_name

MULTISET EXCEPT:从另一个集合里移除现在集合里有的元素.(求差集,类似MINUS).原型:collection MULTISET EXCEPT collection

MULTISET INTERSECT:求两个集合的交集.(类似SQL中的INTERSECT).原型:collection MULTISET INTERSECT collection

MULTISET UNION:合并两个集合,不去重,可使用DISTINCT去重.(类似SQL的UNION ALL).原型:collection MULTISET UNION collection

SET:去掉集合中重复的值(类似SQL的DISTINCT).原型:SET(collection)

SUBMULTISET:识别一个集合是否是另一个集合的子集.原型:collection SUBMULTISET OF collection

--集合操作符使用
00:43:15 hr@orcl> CREATE OR REPLACE TYPE T_list IS TABLE OF NUMBER;--创建嵌套表类型
00:43:17   2  /
类型已创建。
已用时间:  00: 00: 01.53
00:43:18 hr@orcl> CREATE OR REPLACE FUNCTION format_list(set_in T_list) RETURN VARCHAR2 IS
00:43:18   2  retval VARCHAR2(2000);
00:43:18   3  BEGIN
00:43:18   4    IF SET_IN IS NULL THEN
00:43:18   5      DBMS_OUTPUT.PUT_LINE('Result: <Null>');
00:43:18   6    ELSIF SET_IN IS EMPTY THEN --使用EMPTY
00:43:18   7      DBMS_OUTPUT.PUT_LINE('Result: <Empty>');
00:43:18   8    ELSE
00:43:18   9      FOR I IN SET_IN.FIRST .. SET_IN.LAST LOOP
00:43:18  10        IF I = SET_IN.FIRST THEN
00:43:18  11          IF SET_IN.COUNT = 1 THEN
00:43:18  12            RETVAL := '(' || SET_IN(I) || ')';
00:43:18  13          ELSE
00:43:18  14            RETVAL := '(' || SET_IN(I);
00:43:18  15          END IF;
00:43:18  16        ELSIF I <> SET_IN.LAST THEN
00:43:18  17          RETVAL := RETVAL || ', ' || SET_IN(I);
00:43:18  18        ELSE
00:43:18  19          RETVAL := RETVAL || ', ' || SET_IN(I) || ')';
00:43:18  20        END IF;
00:43:18  21      END LOOP;
00:43:18  22    END IF;
00:43:18  23    RETURN RETVAL;
00:43:18  24  END FORMAT_LIST;
00:43:20  25  /
函数已创建。
--CARDINALITY的使用,统计元素个数
00:44:51 hr@orcl> DECLARE
00:45:07   2    A T_LIST := T_LIST(1, 2, 3, 3, 4, 4);
00:45:07   3  BEGIN
00:45:07   4    DBMS_OUTPUT.PUT_LINE(CARDINALITY(A));
00:45:07   5  END;
00:45:08   6  /
6
PL/SQL 过程已成功完成。
--CARDINALITY和SET联合使用(SET去重),计算去重后的元素个数
00:45:08 hr@orcl> DECLARE
00:46:10   2    A T_LIST := T_LIST(1, 2, 3, 3, 4, 4);
00:46:10   3  BEGIN
00:46:12   4    DBMS_OUTPUT.PUT_LINE(CARDINALITY(set(A)));
00:46:22   5  end;
00:46:24   6  /
4
PL/SQL 过程已成功完成。
--MEMBER OF的使用
00:47:59 hr@orcl> DECLARE
00:48:15   2    TYPE t_LIST IS TABLE OF VARCHAR2(10);
00:48:15   3    N VARCHAR2(10) := 'One';
00:48:15   4    A t_LIST := t_LIST('One', 'Two', 'Three');
00:48:15   5  BEGIN
00:48:15   6    IF N MEMBER OF A THEN
00:48:15   7      DBMS_OUTPUT.PUT_LINE('”n” is member.');
00:48:15   8    END IF;
00:48:15   9  END;
00:48:15  10  /
”n” is member.
PL/SQL 过程已成功完成。
--MULTISET EXCEPT的使用,A集合减去在b集合中存在的元素(相当于MINUS)
00:48:17 hr@orcl> DECLARE
00:49:11   2    A T_LIST := T_LIST(1, 2, 3, 4);
00:49:11   3    B T_LIST := T_LIST(4, 5, 6, 7);
00:49:11   4  BEGIN
00:49:11   5    DBMS_OUTPUT.PUT_LINE(FORMAT_LIST(A MULTISET EXCEPT B));
00:49:11   6  END;
00:49:11   7  /
(1, 2, 3)
PL/SQL 过程已成功完成。
--MULTISET INTERSECT的使用,求交集
00:49:12 hr@orcl> DECLARE
00:50:55   2    A T_LIST := T_LIST(1, 2, 3, 4);
00:50:55   3    B T_LIST := T_LIST(4, 5, 6, 7);
00:50:55   4  BEGIN
00:50:55   5    DBMS_OUTPUT.PUT_LINE(FORMAT_LIST(A MULTISET INTERSECT B));
00:50:55   6  END;
00:50:55   7  /
(4)
PL/SQL 过程已成功完成。
--MULTISET UNION的使用,求并集,不去重
00:50:56 hr@orcl> DECLARE
00:51:47   2    A T_LIST := T_LIST(1, 2, 3, 4);
00:51:47   3    B T_LIST := T_LIST(4, 5, 6, 7);
00:51:47   4  BEGIN
00:51:47   5    DBMS_OUTPUT.PUT_LINE(FORMAT_LIST(A MULTISET UNION B));
00:51:47   6  END;
00:51:47   7  /
(1, 2, 3, 4, 4, 5, 6, 7)
PL/SQL 过程已成功完成。
--MULTISET UNION和DISTINC联合使用,合并集合后去重.
00:51:48 hr@orcl> DECLARE
00:52:42   2    A T_LIST := T_LIST(1, 2, 3, 4);
00:52:42   3    B T_LIST := T_LIST(4, 5, 6, 7);
00:52:42   4  BEGIN
00:52:42   5    DBMS_OUTPUT.PUT_LINE(FORMAT_LIST(A MULTISET UNION DISTINCT B));
00:52:42   6  END;
00:52:42   7  /
(1, 2, 3, 4, 5, 6, 7)
PL/SQL 过程已成功完成。
--MULTISET UNION和set联合使用,合并后去重
00:52:43 hr@orcl> DECLARE
00:54:32   2    A T_LIST := T_LIST(1, 2, 3, 4);
00:54:32   3    B T_LIST := T_LIST(4, 5, 6, 7);
00:54:32   4  BEGIN
00:54:32   5    DBMS_OUTPUT.PUT_LINE(FORMAT_LIST(SET(A MULTISET UNION B)));
00:54:32   6  END;
00:54:32   7  /
(1, 2, 3, 4, 5, 6, 7)
PL/SQL 过程已成功完成。
--SET的使用,去掉重复元素,注意SET只能用在标量数据类型的集合中,用户自定义类型的元素集合中不能使用
00:54:33 hr@orcl> DECLARE
00:55:27   2    A T_LIST := T_LIST(1, 2, 3, 3, 4, 4, 5, 6, 6, 7);
00:55:27   3  BEGIN
00:55:27   4    DBMS_OUTPUT.PUT_LINE(FORMAT_LIST(SET(A)));
00:55:27   5  END;
00:55:27   6  /
(1, 2, 3, 4, 5, 6, 7)
PL/SQL 过程已成功完成。
--SET和EMPTY的使用,用作判断.
00:55:28 SCOTT@orcl> DECLARE
00:56:55   2    A T_LIST := T_LIST(1, 2, 3, 4);
00:56:55   3    B T_LIST := T_LIST(1, 2, 3, 3, 4, 4);
00:56:55   4    C T_LIST := T_LIST();--创建空元素的集合C
00:56:55   5    FUNCTION ISSET(SET_IN T_LIST) RETURN VARCHAR2 IS--匿名块中使用函数
00:56:55   6    BEGIN
00:56:55   7      IF SET_IN IS A SET THEN --判断集合是否存在重复元素
00:56:55   8        IF SET_IN IS NOT EMPTY THEN --判断集合是否为空
00:56:55   9          RETURN 'Yes - a unique collection.';
00:56:55  10        ELSE
00:56:55  11          RETURN 'Yes - an empty collection.';
00:56:55  12        END IF;
00:56:55  13      ELSE
00:56:55  14        RETURN 'No - a non-unique collection.';
00:56:55  15      END IF;
00:56:55  16    END ISSET;
00:56:55  17  BEGIN
00:56:55  18    DBMS_OUTPUT.PUT_LINE(ISSET(A));
00:56:55  19    DBMS_OUTPUT.PUT_LINE(ISSET(B));
00:56:55  20    DBMS_OUTPUT.PUT_LINE(ISSET(C));
00:56:55  21  END;
00:56:56  22  /
Yes - a unique collection.
No - a non-unique collection.
Yes - an empty collection.
PL/SQL 过程已成功完成。
--SUBMULTISET的使用,判断一个集合是否为另一个集合的子集
01:00:31 SCOTT@orcl> DECLARE
01:00:44   2    A T_LIST := T_LIST(1, 2, 3, 4);
01:00:44   3    B T_LIST := T_LIST(1, 2, 3, 3, 4, 5);
01:00:44   4    C T_LIST := T_LIST(1, 2, 3, 3, 4, 4);
01:00:44   5  BEGIN
01:00:44   6    IF A SUBMULTISET C THEN
01:00:44   7      DBMS_OUTPUT.PUT_LINE('[a] is a subset of [c]');
01:00:44   8    END IF;
01:00:44   9    IF NOT B SUBMULTISET C THEN
01:00:44  10      DBMS_OUTPUT.PUT_LINE(' is not a subset of [c]');
01:00:44  11    END IF;
01:00:44  12  END;
01:00:44  13  /
[a] is a subset of [c]
is not a subset of [c]
PL/SQL 过程已成功完成。

　　


集合API

COUNT:该方法返回可变数组类型或嵌套表类型变量中已分配空间的元素个数.返回关联数组的所有元素个数.

DELETE:该方法删除集合的一个元素.原型:void DELETE(n,m)或void DELETE(n)

EXISTS:判断指定索引的元素值是否存在.返回TRUE或FALSE.原型:boolean EXISTS(n)

EXTEND:该方法给一个或多个元素分配空间,只能使用在可变数组类型或嵌套表类型中.原型:void EXTEND或void EXTEND(n)或void EXTEND(n,i)

FIRST:该方法返回最低下标的元素值.原型:mixed FIRST

LAST:该方法返回最高下标的元素值.原型:mixed LAST

LIMIT:该方法返回最高可能的下标值.返回类型只能是PLS_INTEGER,且只能用在可变数组类型中.原型:mixed LIMIT

NEXT(n):返回下一个更高下标的元素值(n为指定下标值).原型:mixed NEXT(n)

PRIOR(n):返回前一个更低下标的元素值(n为指定下标值).原型:mixed PRIOR(n)

TRIM:该方法移除集合的下标值.原型:void TRIM或void TRIM(n)

例:


--COUNT的使用(count其实是一个函数)
10:40:49 HR@orcl> DECLARE
10:40:54   2    TYPE number_table IS TABLE OF INTEGER;
10:40:54   3    number_list NUMBER_TABLE := number_table(1, 2, 3, 4, 5);
10:40:54   4  BEGIN
10:40:54   5    DBMS_OUTPUT.PUT_LINE('How many elements? [' || number_list.COUNT || ']');
10:40:54   6  END;
10:40:55   7  /
How many elements? [5]
PL/SQL 过程已成功完成。
--DELETE的使用(DELETE其实是一个可重载的存储过程)
--EXISTS的使用(EXISTS其实是一个函数)
10:40:55 HR@orcl> DECLARE
10:42:24   2    TYPE number_table IS TABLE OF INTEGER;--嵌套表类型
10:42:24   3    number_list NUMBER_TABLE;
10:42:24   4    PROCEDURE print_list(list_in NUMBER_TABLE) IS--声明本地存储
10:42:24   5    BEGIN
10:42:24   6      -- Check whether subscripted elements are there.
10:42:24   7      DBMS_OUTPUT.PUT_LINE('––––––---------------–––––––––');
10:42:24   8      FOR i IN list_in.FIRST .. list_in.LAST
10:42:24   9      LOOP
10:42:24  10        IF list_in.EXISTS(i) THEN
10:42:24  11          DBMS_OUTPUT.PUT_LINE('List [' || list_in(i) || ']');
10:42:24  12        END IF;
10:42:24  13      END LOOP;
10:42:24  14    END print_list;
10:42:24  15  BEGIN
10:42:24  16    -- Construct collection when one doesn't exist.
10:42:24  17    IF NOT number_list.EXISTS(1) THEN
10:42:24  18      number_list := number_table(1, 2, 3, 4, 5);
10:42:24  19    END IF;
10:42:24  20    DBMS_OUTPUT.PUT_LINE('Nested table before a deletion');
10:42:24  21    print_list(number_list);
10:42:24  22    number_list.DELETE(2, 4);--删除下标从2-4的元素
10:42:24  23    DBMS_OUTPUT.PUT_LINE(CHR(10) || 'Nested table after a deletion');
10:42:24  24    print_list(number_list);
10:42:24  25  END;
10:42:24  26  /
Nested table before a deletion
––––––---------------–––––––––
List [1]
List [2]
List [3]
List [4]
List [5]
Nested table after a deletion
––––––---------------–––––––––
List [1]
List [5]
PL/SQL 过程已成功完成。

--EXTEND的使用(EXTEND其实是一个可重载的存储过程)
10:46:07 HR@orcl> DECLARE
10:47:56   2    TYPE number_table IS TABLE OF INTEGER;
10:47:56   3    number_list NUMBER_TABLE;
10:47:56   4    PROCEDURE print_list(list_in NUMBER_TABLE) IS
10:47:56   5    BEGIN
10:47:56   6      -- Check whether subscripted elements are there.
10:47:56   7      DBMS_OUTPUT.PUT_LINE('––––––---------------–––––––––');
10:47:56   8      FOR i IN list_in.FIRST .. list_in.LAST
10:47:56   9      LOOP
10:47:56  10        IF list_in.EXISTS(i) THEN
10:47:56  11          DBMS_OUTPUT.PUT_LINE('List [' || list_in(i) || ']');
10:47:56  12        END IF;
10:47:56  13      END LOOP;
10:47:56  14    END print_list;
10:47:56  15  BEGIN
10:47:56  16    -- Construct collection when one doesn't exist.
10:47:56  17    IF NOT number_list.EXISTS(1) THEN
10:47:56  18      number_list := number_table(1, 2, 3, 4, 5);
10:47:56  19    END IF;
10:47:56  20    -- Print initialized contents.
10:47:56  21    DBMS_OUTPUT.PUT_LINE('Nested table before a deletion');
10:47:56  22    print_list(number_list);
10:47:56  23    number_list.EXTEND(2);--分配2个空间,元素默认为NULL
10:47:56  24    number_list.EXTEND(3, 4);--分配3个空间,并将这3个元素初始化为4
10:47:56  25    DBMS_OUTPUT.PUT_LINE(CHR(10) || 'Nested table after a deletion');
10:47:56  26    print_list(number_list);
10:47:56  27  END;
10:47:56  28  /
Nested table before a deletion
––––––---------------–––––––––
List [1]
List [2]
List [3]
List [4]
List [5]
Nested table after a deletion
––––––---------------–––––––––
List [1]
List [2]
List [3]
List [4]
List [5]
List []
List []
List [4]
List [4]
List [4]
PL/SQL 过程已成功完成。
--FIRST的使用(FIRST其实是一个函数),注意当下标为非数字类型时,不能在FOR循环中使用FIRST.
10:47:57 HR@orcl> DECLARE
11:28:53   2    TYPE number_table IS TABLE OF INTEGER INDEX BY VARCHAR2(9 CHAR);
11:28:53   3    number_list NUMBER_TABLE;
11:28:53   4  BEGIN
11:28:53   5    number_list('One') := 1;
11:28:53   6    number_list('Two') := 2;
11:28:53   7    number_list('Nine') := 9;
11:28:53   8    DBMS_OUTPUT.PUT_LINE('FIRST Index [' || number_list.FIRST || ']');
11:28:53   9    DBMS_OUTPUT.PUT_LINE('NEXT Index [' ||
11:28:53  10                         number_list.NEXT(number_list. FIRST) || ']');
11:28:53  11    DBMS_OUTPUT.PUT_LINE(CHR(10) || 'LAST Index [' || number_list.LAST || ']');
11:28:53  12    DBMS_OUTPUT.PUT_LINE('PRIOR Index [' ||
11:28:53  13                         number_list.PRIOR(number_list. LAST) || ']');
11:28:53  14  END;
11:28:53  15  /
FIRST Index [Nine]
NEXT Index [One]
LAST Index [Two]
PRIOR Index [One]
PL/SQL 过程已成功完成。
--LAST的使用(LAST其实是一个函数),注意当下标为非数字类型时,不能在FOR循环中使用LAST.
11:28:54 HR@orcl> DECLARE
11:37:19   2    TYPE number_varray IS VARRAY(5) OF INTEGER;
11:37:19   3    number_list NUMBER_VARRAY := number_varray(1, 2, 3);
11:37:19   4    PROCEDURE print_list(list_in NUMBER_VARRAY) IS
11:37:19   5    BEGIN
11:37:19   6      -- Print all subscripted elements.
11:37:19   7      DBMS_OUTPUT.PUT_LINE('–––––-------------––––––––-');
11:37:19   8      FOR i IN list_in.FIRST .. list_in.COUNT
11:37:19   9      LOOP
11:37:19  10        DBMS_OUTPUT.PUT_LINE('List Index [' || i || '] ' || 'List Value [' ||
11:37:19  11                             list_in(i) || ']');
11:37:19  12      END LOOP;
11:37:19  13    END print_list;
11:37:19  14  BEGIN
11:37:19  15    -- Print initial contents.
11:37:19  16    DBMS_OUTPUT.PUT_LINE('Varray after initialization');
11:37:19  17    print_list(number_list);
11:37:19  18    --Extend with null element to the maximum limit size.
11:37:19  19    number_list.EXTEND(number_list.LIMIT - number_list.LAST);
11:37:19  20    DBMS_OUTPUT.PUT_LINE(CHR(10));
11:37:19  21    DBMS_OUTPUT.PUT_LINE('Varray after extension');
11:37:19  22    print_list(number_list);
11:37:19  23  END;
11:37:19  24  /
Varray after initialization
–––––-------------––––––––-
List Index [1] List Value [1]
List Index [2] List Value [2]
List Index [3] List Value [3]

Varray after extension
–––––-------------––––––––-
List Index [1] List Value [1]
List Index [2] List Value [2]
List Index [3] List Value [3]
List Index [4] List Value []
List Index [5] List Value []
PL/SQL 过程已成功完成。
--TRIM的使用(TRIM其实是一个可重载的存储过程)
11:37:20 HR@orcl> DECLARE
11:40:47   2    TYPE number_varray IS VARRAY(5) OF INTEGER;--可变数组类型
11:40:47   3    number_list NUMBER_VARRAY := number_varray(1, 2, 3, 4, 5);
11:40:47   4    PROCEDURE print_list(list_in NUMBER_VARRAY) IS
11:40:47   5    BEGIN
11:40:47   6      -- Print all subscripted elements.
11:40:47   7      DBMS_OUTPUT.PUT_LINE('–––––-------------––––––––-');
11:40:47   8      FOR i IN list_in.FIRST .. list_in.COUNT
11:40:47   9      LOOP
11:40:47  10        DBMS_OUTPUT.PUT_LINE('List Index [' || i || '] ' || 'List Value [' ||
11:40:47  11                             list_in(i) || ']');
11:40:47  12      END LOOP;
11:40:47  13    END print_list;
11:40:47  14  BEGIN
11:40:47  15    DBMS_OUTPUT.PUT_LINE('Varray after initialization');
11:40:47  16    print_list(number_list);
11:40:47  17    --Trim one element from the end of the collection.
11:40:47  18    number_list.TRIM;
11:40:47  19    --Print collection minus last element.
11:40:47  20    DBMS_OUTPUT.PUT(CHR(10));
11:40:47  21    DBMS_OUTPUT.PUT_LINE('Varray after a trimming one element');
11:40:47  22    print_list(number_list);
11:40:47  23    --Trim three elements from the end of the collection.
11:40:47  24    number_list.TRIM(3);
11:40:47  25    DBMS_OUTPUT.PUT(CHR(10));
11:40:47  26    DBMS_OUTPUT.PUT_LINE('Varray after a trimming three elements');
11:40:47  27    print_list(number_list);
11:40:47  28  END;
11:40:47  29  /
Varray after initialization
–––––-------------––––––––-
List Index [1] List Value [1]
List Index [2] List Value [2]
List Index [3] List Value [3]
List Index [4] List Value [4]
List Index [5] List Value [5]
Varray after a trimming one element
–––––-------------––––––––-
List Index [1] List Value [1]
List Index [2] List Value [2]
List Index [3] List Value [3]
List Index [4] List Value [4]
Varray after a trimming three elements
–––––-------------––––––––-
List Index [1] List Value [1]
PL/SQL 过程已成功完成。