数组集合oracle 11g PL/SQL Programming学习五
题记：写这篇博客要主是加深自己对数组集合的认识和总结实现算法时的一些验经和训教，如果有错误请指出，万分感谢。
第七章 集合(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('[b] 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]
[b] 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 进程已胜利实现。
文章结束给大家分享下程序员的一些笑话语录：
关于编程语言
如果 C++是一把锤子的话，那么编程就会变成大手指头。
如果你找了一百万只猴子来敲打一百万个键盘，那么会有一只猴子会敲出一 段 Java 程序，而其余的只会敲出 Perl 程序。
一阵急促的敲门声，“谁啊！”，过了 5 分钟，门外传来“Java”。
如果说 Java 很不错是因为它可以运行在所有的操作系统上，那么就可以说 肛交很不错，因为其可以使用于所有的性别上。
浙公网安备 33010602011771号