Oracle PL/SQL之嵌套表（Nested Table）

h9d234coe9

　　Test Code:
　　Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 Connected as xxpoSQL> CREATE OR REPLACE TYPE color_tab_t AS TABLE OF VARCHAR2(30);2  /Type createdSQL> SQL> CREATE TABLE color_models (2  model_type VARCHAR2(12)3  , colors color_tab_t4  )5  NESTED TABLE colors STORE AS color_model_colors_tab6  /Table createdSQL> SQL> BEGIN2  INSERT INTO color_models3  VALUES ('RGB', color_tab_t ('RED','GREEN','BLUE'));4  END;5  /PL/SQL procedure successfully completedSQL> select * from color_models;MODEL_TYPE   COLORS------------ ------RGB          <ObjecSQL> select * from color_model_colors_tab;select * from color_model_colors_tabORA-22812: cannot reference nested table column's storage tableSQL> SQL> SELECT object_name2        ,object_type3        ,status4  FROM   dba_objects5  WHERE  object_name = upper('COLOR_MODEL_COLORS_TAB');OBJECT_NAME                                                                      OBJECT_TYPE         STATUS-------------------------------------------------------------------------------- ------------------- -------COLOR_MODEL_COLORS_TAB                                                           TABLE               VALIDSQL> SELECT /*+NESTED_TABLE_GET_REFS+*/ * FROM COLOR_MODEL_COLORS_TAB;COLUMN_VALUE------------------------------REDGREENBLUESQL>
　　
　　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