SQL>
SQL> CREATE OR REPLACE TYPE StudentList AS TABLE OF NUMBER(5);
2 /
Type created.
SQL>
SQL>
SQL> CREATE TABLE library_catalog (
2 catalog_number NUMBER(4),
3 num_copies NUMBER,
4 num_out NUMBER,
5 checked_out StudentList)
6 NESTED TABLE checked_out STORE AS co_tab;
Table created.
SQL>
SQL>
SQL> DECLARE
2 v_StudentList1 StudentList := StudentList(10000, 10002, 10003);
3 v_StudentList2 StudentList := StudentList(10000, 10002, 10003);
4 v_StudentList3 StudentList := StudentList(10000, 10002, 10003);
5 BEGIN
6 -- First insert rows with NULL nested tables.
7 INSERT INTO library_catalog (catalog_number, num_copies, num_out)
8 VALUES (1000, 20, 3);
9 INSERT INTO library_catalog (catalog_number, num_copies, num_out)
10 VALUES (1001, 20, 3);
11 INSERT INTO library_catalog (catalog_number, num_copies, num_out)
12 VALUES (1002, 10, 3);
13 INSERT INTO library_catalog (catalog_number, num_copies, num_out)
14 VALUES (2001, 50, 0);
15 INSERT INTO library_catalog (catalog_number, num_copies, num_out)
16 VALUES (3001, 5, 0);
17 INSERT INTO library_catalog (catalog_number, num_copies, num_out)
18 VALUES (3002, 5, 1);
19
20 UPDATE library_catalog
21 SET checked_out = v_StudentList1
22 WHERE catalog_number = 1000;
23 UPDATE library_catalog
24 SET checked_out = v_StudentList2
25 WHERE catalog_number = 1001;
26 UPDATE library_catalog
27 SET checked_out = v_StudentList3
28 WHERE catalog_number = 1002;
29
30 -- And update the last row using a new variable.
31 UPDATE library_catalog
32 SET checked_out = StudentList(10009)
33 WHERE catalog_number = 3002;
34 END;
35 /
PL/SQL procedure successfully completed.
SQL>
SQL> DELETE FROM library_catalog
2 WHERE catalog_number = 3001;
1 row deleted.
SQL>
SQL>
SQL>
SQL> select * from library_catalog;
CATALOG_NUMBER NUM_COPIES NUM_OUT
-------------- ---------- ----------
CHECKED_OUT
--------------------------------------------------------------------------------
1000 20 3
STUDENTLIST(10000, 10002, 10003)
1001 20 3
STUDENTLIST(10000, 10002, 10003)
1002 10 3
STUDENTLIST(10000, 10002, 10003)
CATALOG_NUMBER NUM_COPIES NUM_OUT
-------------- ---------- ----------
CHECKED_OUT
--------------------------------------------------------------------------------
2001 50 0
3002 5 1
STUDENTLIST(10009)
SQL>
SQL> drop table library_catalog;
Table dropped.
SQL>
SQL>
|