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

原创

mb61c186c579369 2022-01-06 10:21:11 ©著作权

©著作权归作者所有：来自51CTO博客作者mb61c186c579369的原创作品，请联系作者获取转载授权，否则将追究法律责任

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>