题记：写这篇博客要主是加深自己对数组集合的认识和总结实现算法时的一些验经和训教，如果有错误请指出，万分感谢。
第七章 集合(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> DECLARE00:52:45 2 TYPE INTEGER_VARRAY IS VARRAY(3) OF INTEGER;--定义可变数组型类INTEGER_VARRAY,定固长度为300:52:45 3 VARRAY_INTEGER INTEGER_VARRAY := INTEGER_VARRAY(NULL, NULL, NULL);--定义可变数组型类变量,并初始化为NULL00:52:45 4 BEGIN00: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> DECLARE01: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 BEGIN01:03:22 5 -- Allocate space as you increment the index.01:03:22 6 FOR I IN 1 .. 3 LOOP01: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 LOOP01: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.4201:11:19 hr@orcl> DECLARE01:11:24 2 VARRAY_INTEGER INTEGER_VARRAY:=INTEGER_VARRAY(NULL, NULL, NULL);--直接应用对象型类INTEGER_VARRAY01:11:24 3 BEGIN01:11:24 4 -- Assign values to replace the null values.01:11:24 5 FOR I IN 1 .. 3 LOOP01: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 LOOP01: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> DECLARE01:23:25 2 VARRAY_INTEGER INTEGER_VARRAY := INTEGER_VARRAY();01:23:25 3 BEGIN01: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 individuals01:39:13 2 ( individual_id INTEGER NOT NULL01:39:13 3 , first_name VARCHAR2(30 CHAR) NOT NULL01:39:13 4 , middle_name VARCHAR2(30 CHAR)01:39:13 5 , last_name VARCHAR2(30 CHAR) NOT NULL01: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 addresses01:40:52 2 ( address_id INTEGER NOT NULL01:40:52 3 , individual_id INTEGER NOT NULL01:40:52 4 , street_address STRINGS NOT NULL--应用可变数组型类strings01:40:52 5 , city VARCHAR2(20 CHAR) NOT NULL01:40:52 6 , state VARCHAR2(20 CHAR) NOT NULL01:40:52 7 , postal_code VARCHAR2(20 CHAR) NOT NULL01:40:52 8 , country_code VARCHAR2(10 CHAR) NOT NULL01: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 ADDRESSES01:45:44 2 VALUES01: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_VALUE01:50:49 2 FROM ADDRESSES A,01:50:49 3 TABLE(CAST(A.STREET_ADDRESS AS VARRAY_NESTED_TABLE)) NESTED01:50:49 4 WHERE ADDRESS_ID = 1;COLUMN_VALUE------------------------------------------------------------Office of Senator McCain450 West Paseo RedondoSuite 200已选择3行。--可变数组型类数据更新01:52:24 hr@orcl> UPDATE ADDRESSES01: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> DECLARE14: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 BEGIN14: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 LOOP14: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 LOOP14: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> DECLARE14: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 BEGIN14:50:06 5 FOR I IN 1 .. 3 LOOP14:50:06 6 CARDS.EXTEND; --配分1个素元空间14:50:06 7 IF I = 1 THEN14:50:06 8 CARDS(I) := 'Ace';14:50:06 9 ELSIF I = 2 THEN14:50:06 10 CARDS(I) := 'Two';14:50:06 11 ELSIF I = 3 THEN14: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 LOOP14: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> DECLARE14:57:31 2 -- 用NULL值声明嵌套表变量CARDS14:57:31 3 CARDS CARD_TABLE := CARD_TABLE(NULL, NULL, NULL);14:57:31 4 BEGIN14: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 LOOP14: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 LOOP14: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_varray15:08:03 hr@orcl> CREATE OR REPLACE TYPE card_unit_varray AS VARRAY(13) OF VARCHAR2(5 CHAR);15:08:05 2 /型类已建创。--建创可变数组型类card_suit_varray15:08:05 hr@orcl> CREATE OR REPLACE TYPE card_suit_varray AS VARRAY(4) OF VARCHAR2(8 CHAR);15:08:05 2 /型类已建创。--建创嵌套表型类card_deck_table15: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> DECLARE15: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 --声明空素元嵌套表变量DECK15:12:32 23 DECK CARD_DECK_TABLE := CARD_DECK_TABLE();15:12:32 24 BEGIN15:12:32 25 FOR I IN 1 .. SUITS.COUNT LOOP15:12:32 26 FOR J IN 1 .. UNITS.COUNT LOOP15: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 LOOP15: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.1815:21:16 hr@orcl> create table ADDRESSES115: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 NULL15: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.0415:26:38 hr@orcl> create table ADDRESSES115: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 NULL15: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 ADDRESSES115: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_table15: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.0315:35:44 hr@orcl> SELECT STREET_ADDRESS FROM ADDRESSES1;STREET_ADDRESS--------------------------------------------------------已选择 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> DECLARE16: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) IS16:07:37 8 SELECT * FROM ADDRESSES1 WHERE ADDRESS_ID = ADDRESS_ID_IN;16:07:37 916:07:37 10 BEGIN16:07:37 11 -- Access the cursor.16:07:37 12 OPEN GET_STREET_ADDRESS(3);16:07:37 13 FETCH GET_STREET_ADDRESS16: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 ADDRESSES116:07:37 22 SET STREET_ADDRESS = ADDRESS.STREET_ADDRESS16: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> DECLARE00: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 BEGIN00: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> DECLARE00: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 BEGIN00: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> DECLARE00:05:14 2 TYPE CARD_TABLE IS TABLE OF VARCHAR2(5 CHAR);--应用嵌套表型类00:05:14 3 CARDS CARD_TABLE;00:05:14 4 BEGIN00: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> DECLARE00: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 BEGIN00:07:31 5 IF CARDS.COUNT <> 0 THEN00:07:31 6 DBMS_OUTPUT.PUT_LINE(CARDS(1));00:07:31 7 ELSE00: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> DECLARE00: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 BEGIN00:14:09 22 --检查关联数组变量是否有素元,没有就值赋为可变数组对应的值00:14:09 23 IF CALENDAR.COUNT = 0 THEN00: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 LOOP00: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 LOOP00: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> DECLARE00: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 BEGIN00:24:09 21 IF CALENDAR.COUNT = 0 THEN00: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 LOOP00: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 LOOP00:24:09 32 IF I = 1 THEN00: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 ELSE00:24:09 38 --应用NEXT方法来检测下一个素元是否存在00:24:09 39 IF CALENDAR.NEXT(CURRENT) IS NOT NULL THEN00:24:09 40 --得到下一个素元的下标值并值赋给变量00:24:09 41 CURRENT := CALENDAR.NEXT(CURRENT);00:24:09 42 ELEMENT := CALENDAR(CURRENT);00:24:09 43 ELSE00: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.5300:43:18 hr@orcl> CREATE OR REPLACE FUNCTION format_list(set_in T_list) RETURN VARCHAR2 IS00:43:18 2 retval VARCHAR2(2000);00:43:18 3 BEGIN00:43:18 4 IF SET_IN IS NULL THEN00:43:18 5 DBMS_OUTPUT.PUT_LINE('Result: ');00:43:18 6 ELSIF SET_IN IS EMPTY THEN --应用EMPTY00:43:18 7 DBMS_OUTPUT.PUT_LINE('Result: ');00:43:18 8 ELSE00:43:18 9 FOR I IN SET_IN.FIRST .. SET_IN.LAST LOOP00:43:18 10 IF I = SET_IN.FIRST THEN00:43:18 11 IF SET_IN.COUNT = 1 THEN00:43:18 12 RETVAL := '(' || SET_IN(I) || ')';00:43:18 13 ELSE00:43:18 14 RETVAL := '(' || SET_IN(I);00:43:18 15 END IF;00:43:18 16 ELSIF I <> SET_IN.LAST THEN00:43:18 17 RETVAL := RETVAL || ', ' || SET_IN(I);00:43:18 18 ELSE00: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> DECLARE00:45:07 2 A T_LIST := T_LIST(1, 2, 3, 3, 4, 4);00:45:07 3 BEGIN00:45:07 4 DBMS_OUTPUT.PUT_LINE(CARDINALITY(A));00:45:07 5 END;00:45:08 6 /6PL/SQL 进程已胜利实现。--CARDINALITY和SET联合应用(SET去重),计算去重后的素元个数00:45:08 hr@orcl> DECLARE00:46:10 2 A T_LIST := T_LIST(1, 2, 3, 3, 4, 4);00:46:10 3 BEGIN00:46:12 4 DBMS_OUTPUT.PUT_LINE(CARDINALITY(set(A)));00:46:22 5 end;00:46:24 6 /4PL/SQL 进程已胜利实现。--MEMBER OF的应用00:47:59 hr@orcl> DECLARE00: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 BEGIN00:48:15 6 IF N MEMBER OF A THEN00: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> DECLARE00: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 BEGIN00: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> DECLARE00: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 BEGIN00: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> DECLARE00: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 BEGIN00: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> DECLARE00: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 BEGIN00: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> DECLARE00: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 BEGIN00: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> DECLARE00:55:27 2 A T_LIST := T_LIST(1, 2, 3, 3, 4, 4, 5, 6, 6, 7);00:55:27 3 BEGIN00: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> DECLARE00: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();--建创空素元的集合C00:56:55 5 FUNCTION ISSET(SET_IN T_LIST) RETURN VARCHAR2 IS--匿名块中应用函数00:56:55 6 BEGIN00: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 ELSE00:56:55 11 RETURN 'Yes - an empty collection.';00:56:55 12 END IF;00:56:55 13 ELSE00: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 BEGIN00: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> DECLARE01: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 BEGIN01:00:44 6 IF A SUBMULTISET C THEN01: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 THEN01: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> DECLARE10: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 BEGIN10: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> DECLARE10: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 BEGIN10: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.LAST10:42:24 9 LOOP10:42:24 10 IF list_in.EXISTS(i) THEN10: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 BEGIN10:42:24 16 -- Construct collection when one doesn't exist.10:42:24 17 IF NOT number_list.EXISTS(1) THEN10: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> DECLARE10: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) IS10:47:56 5 BEGIN10: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.LAST10:47:56 9 LOOP10:47:56 10 IF list_in.EXISTS(i) THEN10: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 BEGIN10:47:56 16 -- Construct collection when one doesn't exist.10:47:56 17 IF NOT number_list.EXISTS(1) THEN10: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个空间,素元默认为NULL10:47:56 24 number_list.EXTEND(3, 4);--配分3个空间,并将这3个素元初始化为410: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> DECLARE11: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 BEGIN11: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> DECLARE11: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) IS11:37:19 5 BEGIN11: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.COUNT11:37:19 9 LOOP11: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 BEGIN11: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> DECLARE11: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) IS11:40:47 5 BEGIN11: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.COUNT11:40:47 9 LOOP11: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 BEGIN11: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 很不错是因为它可以运行在所有的操作系统上，那么就可以说 肛交很不错，因为其可以使用于所有的性别上。