ORACLE PL/SQL 集合学习笔记(二)

三、嵌套表的使用方法

 

1、将嵌套表定义为PL/SQL的程序构造块

 

TYPE type_name IS TABLE OF element_type[NOT NULL];

 

如下例所示：

DECLARE -- Define a nested table of variable length strings. TYPE card_table IS TABLE OF VARCHAR2(5 CHAR); -- Declare and initialize a nested table with three rows. cards CARD_TABLE := card_table(NULL,NULL,NULL); BEGIN -- Print title. dbms_output.put_line( 'Nested table initialized as nulls.'); dbms_output.put_line( '----------------------------------'); -- Loop through the three records. FOR i IN 1..3 LOOP -- Print the contents. dbms_output.put ('Cards Varray ['||i||'] '); dbms_output.put_line('['||cards(i)||']'); END LOOP; -- Assign values to subscripted members of the varray. cards(1) := 'Ace'; cards(2) := 'Two'; cards(3) := 'Three'; -- Print title. dbms_output.put (CHR(10)); -- Visual line break. dbms_output.put_line( 'Nested table initialized as Ace, Two and Three.'); dbms_output.put_line( '-----------------------------------------------'); -- Loop through the three records to print the varray contents. FOR i IN 1..3 LOOP dbms_output.put_line('Cards ['||i||'] ' || '['||cards(i)||']'); END LOOP; END; /  

 

2、将嵌套表类型定义和用作PL/SQL的对象类型

CREATE OR REPLACE TYPE type_name

AS TABLE OF element_type [NOT NULL];

 

如下例所示：

-- Define a varray of four rows of variable length strings. CREATE OR REPLACE TYPE card_unit_varray AS VARRAY(13) OF VARCHAR2(5 CHAR); / -- Define a varray of four rows of variable length strings. CREATE OR REPLACE TYPE card_suit_varray AS VARRAY(4) OF VARCHAR2(8 CHAR); / -- Define a table of variable length strings. CREATE OR REPLACE TYPE card_deck_table AS TABLE OF VARCHAR2(17 CHAR); / DECLARE -- Define a counter to manage 1 to 52 cards in a deck. counter INTEGER := 0; -- Declare and initialize a varray of card suits. suits CARD_SUIT_VARRAY := card_suit_varray('Clubs' ,'Diamonds' ,'Hearts' ,'Spades'); -- Declare and initialize a varray of card units. units CARD_UNIT_VARRAY := card_unit_varray('Ace','Two','Three','Four' ,'Five','Six','Seven','Eight' ,'Nine','Ten','Jack','Queen' ,'King'); -- Declare and initialize a null nested table. deck CARD_DECK_TABLE := card_deck_table(); BEGIN -- Loop through the four suits of cards. FOR i IN 1..suits.COUNT LOOP -- Loop through the thirteen units of cards. FOR j IN 1..units.COUNT LOOP -- Increment counter. counter := counter + 1; -- Initialize row. deck.EXTEND; -- Assign a value to the element. deck(counter) := units(j)||' of '||suits(i); END LOOP; END LOOP; -- Print title. dbms_output.put_line('Deck of cards by suit.'); dbms_output.put_line('----------------------'); -- Loop through the deck of cards. FOR i IN 1..counter LOOP -- Print the contents. dbms_output.put_line('['||deck(i)||']'); END LOOP; END; /  

 

3、将嵌套表定义和用作表的列数据类型

在ORACLE 10G中，VARRAYS是以内联结构的形式存储的，并允许NOT NULL约束。相对而言，如果将嵌套表作为数据表的列，就不允许使用NOT NULL约束。

在嵌套表列上定义NOT NULL约束，相当于在定义嵌套表以前，管理插入到其中的记录行。而这是不可能的。NOT NULL约束在这种情形下就像一个数据库的引用完整性约束一样，因此也是不允许的。

 

1）INSERT

像前面介绍的VARRAYS一样，插入语句只有一种访问方式。它是以要么全有要么全无的方式访问数据类型的。

如下例所示：

INSERT INTO addresses VALUES (21 ,21 ,address_table ('Office of Senator McCain' ,'450 West Paseo Redondo' ,'Suite 200') ,'Tucson' ,'AZ' ,'85701' ,'USA');  

 

同样，如果我们通过如下所示的普通的SELECT语句来对插入的嵌套表类型列进行查询，会返回带有实际参数的构造函数的返回值集合，得到的结果并不是我们想要的。

 

-- Use an ordinary select statement. SELECT street_address FROM addresses;   

 

我们可以使用下面的语句来对嵌套表类型列进行查询。

 

 

-- Use SQL*Plus to format the output. COL column_value FORMAT A30 -- Print a list of the varray elements. SELECT column_value FROM THE (SELECT street_address FROM addresses WHERE address_id = 21);   

 

 

2）UPDATE

前面我们已经看到，如果不改变整个VARRAYS内容，那么PL/SQL是唯一的一种更新VARRAYS的方法，但是PL/SQL不是唯一的一种更新嵌套表的方法。嵌套表的优点就是我们可以更新各个行元素，这些更新可以直接通过DML的更新语句实现。

使用下面的示例程序可以替换嵌套表数据类型列STREET_ADDRESS的全部内容：

 

UPDATE addresses SET street_address = address_table('Office of Senator McCain' ,'2400 E. Arizona Biltmore Cir.' ,'Suite 1150') WHERE address_id = 21;   

 

也可以直接在SQL中更新嵌套表列的部分值，下面这个示例程序更新了嵌套表STREET_ADDRESS的第一行记录。

 

UPDATE THE (SELECT street_address FROM addresses WHERE address_id = 21) SET column_value = 'Office of Senator John McCain' WHERE column_value = 'Office of Senator McCain';  

 

另外，还可以使用PL/SQL实现这种更新操作。在PL/SQL中，可以使用下面这两种方法：

• 直接更新嵌套表的某一行记录
• 为某个嵌套表列而更新这一行的所有内容

更新某一行记录全部内容的方法类似前面对VARRAYS使用的那种方法，这里就不再重复。下面介绍怎样直接在嵌套表列中直接更新嵌套表的某一行记录。下面这个示例使用了动态SQL技术和绑定变量技术。

DECLARE -- Define old and new values. new_value VARCHAR2(30 CHAR) := 'Office of Senator John McCain'; old_value VARCHAR2(30 CHAR) := 'Office of Senator McCain'; -- Build SQL statement to support bind variables. sql_statement VARCHAR2(4000 CHAR) := 'UPDATE THE (SELECT street_address ' || ' FROM addresses ' || ' WHERE address_id = 21) ' || 'SET column_value = :1 ' || 'WHERE column_value = :2'; BEGIN -- Use dynamic SQL to run the update statement. EXECUTE IMMEDIATE sql_statement USING new_value, old_value; END; /