Oracle PL/SQL之巢狀表格（Nested Table）

Test Code:

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0<br />Connected as xxpo</p><p>SQL> CREATE OR REPLACE TYPE color_tab_t AS TABLE OF VARCHAR2(30);<br /> 2 /</p><p>Type created</p><p>SQL><br />SQL> CREATE TABLE color_models (<br /> 2 model_type VARCHAR2(12)<br /> 3 , colors color_tab_t<br /> 4 )<br /> 5 NESTED TABLE colors STORE AS color_model_colors_tab<br /> 6 /</p><p>Table created</p><p>SQL><br />SQL> BEGIN<br /> 2 INSERT INTO color_models<br /> 3 VALUES ('RGB', color_tab_t ('RED','GREEN','BLUE'));<br /> 4 END;<br /> 5 /</p><p>PL/SQL procedure successfully completed</p><p>SQL> select * from color_models;</p><p>MODEL_TYPE COLORS<br />------------ ------<br />RGB <Objec</p><p>SQL> select * from color_model_colors_tab;</p><p>select * from color_model_colors_tab</p><p>ORA-22812: cannot reference nested table column's storage table</p><p>SQL><br />SQL> SELECT object_name<br /> 2 ,object_type<br /> 3 ,status<br /> 4 FROM dba_objects<br /> 5 WHERE object_name = upper('COLOR_MODEL_COLORS_TAB');</p><p>OBJECT_NAME OBJECT_TYPE STATUS<br />-------------------------------------------------------------------------------- ------------------- -------<br />COLOR_MODEL_COLORS_TAB TABLE VALID</p><p>SQL> SELECT /*+NESTED_TABLE_GET_REFS+*/ * FROM COLOR_MODEL_COLORS_TAB;</p><p>COLUMN_VALUE<br />------------------------------<br />RED<br />GREEN<br />BLUE</p><p>SQL>

 

Ref:

1. Page 357, Oracle PL/SQL Programming FIFTH EDITION, Steven Feuerstein with Bill Pribyl, O'REILLY

2. http://www.itpub.net/viewthread.php?tid=640129