test result:SQL> CREATE OR REPLACE TYPE StudentList AS TABLE OF NUMBER(5);
2 /
Type created.SQL> CREATE TABLE library_catalog (
2 catalog_number NUMBER(4),
3 FOREIGN KEY (catalog_number) REFERENCES books(catalog_number),
4 num_copies NUMBER,
5 num_out NUMBER,
6 checked_out StudentList)
7 NESTED TABLE checked_out STORE AS co_tab;
Table created.SQL> insert into library_catalog values (1,1,1,studentlist(3,4));
1 row created.SQL> select * from table(select checked_out from library_catalog);
COLUMN_VALUE
------------
3
4SQL> update table(select checked_out from library_catalog)
2 set column_value = 100
3 where column_value = 3;
1 row updated.SQL> select * from table(select checked_out from library_catalog);COLUMN_VALUE
------------
100
4SQL> delete table(select checked_out from library_catalog)
2 where column_value = 4;
1 row deleted.SQL> select * from table(select checked_out from library_catalog);
COLUMN_VALUE
------------
100
2 /
Type created.SQL> CREATE TABLE library_catalog (
2 catalog_number NUMBER(4),
3 FOREIGN KEY (catalog_number) REFERENCES books(catalog_number),
4 num_copies NUMBER,
5 num_out NUMBER,
6 checked_out StudentList)
7 NESTED TABLE checked_out STORE AS co_tab;
Table created.SQL> insert into library_catalog values (1,1,1,studentlist(3,4));
1 row created.SQL> select * from table(select checked_out from library_catalog);
COLUMN_VALUE
------------
3
4SQL> update table(select checked_out from library_catalog)
2 set column_value = 100
3 where column_value = 3;
1 row updated.SQL> select * from table(select checked_out from library_catalog);COLUMN_VALUE
------------
100
4SQL> delete table(select checked_out from library_catalog)
2 where column_value = 4;
1 row deleted.SQL> select * from table(select checked_out from library_catalog);
COLUMN_VALUE
------------
100
-------------- ---------- ---------- -----------
1000 20 3 <Object>
1001 20 3 <Object>
1002 10 3 <Object>
2001 50 0 <Object>
3002 5 1 <Object>
ORA-01427: 单行子查询返回多于一个行
SQL> select * from table(select checked_out from library_catalog);select * from table(select checked_out from library_catalog)ORA-01427: 单行子查询返回多于一个行其它的更新,删除命令也类似....
SQL> select catalog_number,column_value
2 from library_catalog, table(checked_out);
CATALOG_NUMBER COLUMN_VALUE
-------------- ------------
1 30
2 50
2 100SQL> update table(select checked_out
2 from library_catalog
3 where catalog_number = 1)
4 set column_value = 90;
1 row updated.SQL> select catalog_number,column_value
2 from library_catalog, table(checked_out);
CATALOG_NUMBER COLUMN_VALUE
-------------- ------------
1 90
2 50
2 100我对嵌套表没有经验,这些测试也只是看了一下SQL REFERENCE的参考.
不过通常来说能用传统模式明确表达的数据结构,还是不要用嵌套表.ORACLE对嵌套表有多种的限制.