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