oracle 嵌套表查询语句,Oracle PL/SQL之嵌套表（Nested Table）

Test Code:

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0

Connected as xxpo

SQL> CREATE OR REPLACE TYPE color_tab_t AS TABLE OF VARCHAR2(30);

2 /

Type created

SQL>

SQL> CREATE TABLE color_models (

2 model_type VARCHAR2(12)

3 , colors color_tab_t

4 )

5 NESTED TABLE colors STORE AS color_model_colors_tab

6 /

Table created

SQL>

SQL> BEGIN

2 INSERT INTO color_models

3 VALUES ('RGB', color_tab_t ('RED','GREEN','BLUE'));

4 END;

5 /

PL/SQL procedure successfully completed

SQL> select * from color_models;

MODEL_TYPE COLORS

------------ ------

RGB

SQL> select * from color_model_colors_tab;

select * from color_model_colors_tab

ORA-22812: cannot reference nested table column's storage table

SQL>

SQL> SELECT object_name

2 ,object_type

3 ,status

4 FROM dba_objects

5 WHERE object_name = upper('COLOR_MODEL_COLORS_TAB');

OBJECT_NAME OBJECT_TYPE STATUS

-------------------------------------------------------------------------------- ------------------- -------

COLOR_MODEL_COLORS_TAB TABLE VALID

SQL> SELECT /*+NESTED_TABLE_GET_REFS+*/ * FROM COLOR_MODEL_COLORS_TAB;

COLUMN_VALUE

------------------------------

RED

GREEN

BLUE

SQL>

Ref:

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

原文链接：

http://blog.csdn.net/t0nsha/article/details/6445503