Oracle PL/SQL之嵌套表(Nested Table)
Oracle PL/SQL之嵌套表(Nested Table)
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 <Objec
- 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>
评论暂时关闭