136.Oracle数据库SQL开发之 集合——获取集合信息
欢迎转载，转载请标明出处：blog.csdn.net/notbaron/ar…\
获取变长数组信息，如下描述变长数组类型t_varray_address：
collection_user@PDB1> describe t_varray_address;
t_varray_address VARRAY(3) OF VARCHAR2(50)
collection_user@PDB1> desc customers_with_varray;
Name Null? Type
------------------------------------------------------------- ------------------------------------
ID NOT NULL NUMBER(38)
FIRST_NAME VARCHAR2(10)
LAST_NAME VARCHAR2(10)
ADDRESSES T_VARRAY_ADDRESS
可以从user_varrays视图中获得变长数组的信息。
查询如下：
collection_user@PDB1> selectparent_table_name,parent_table_column,type_name from user_varrays wheretype_name='T_VARRAY_ADDRESS';
PARENT_TABLE_NAME
----------------------------------------------------------------------------------------------------
PARENT_TABLE_COLUMN
----------------------------------------------------------------------------------------------------
TYPE_NAME
----------------------------------------------------------------------------------------------------
CUSTOMERS_WITH_VARRAY
ADDRESSES
T_VARRAY_ADDRESS
1. 获得嵌套表信息
可以使用DESCRIBE命令后的嵌套表的信息
collection_user@PDB1> describet_nested_table_address;
t_nested_table_address TABLE OF T_ADDRESS
Name Null? Type
------------------------------------------------------------- ------------------------------------
STREET VARCHAR2(15)
CITY VARCHAR2(15)
STATE CHAR(2)
ZIP VARCHAR2(5)
collection_user@PDB1> describecustomers_with_nested_table;
Name Null? Type
------------------------------------------------------------- ------------------------------------
ID NOT NULL NUMBER(38)
FIRST_NAME VARCHAR2(10)
LAST_NAME VARCHAR2(10)
ADDRESSES T_NESTED_TABLE_ADDRESS
将depth设置为2并描述customers_with_nested_table，如下：
collection_user@PDB1> set describe depth 2
collection_user@PDB1> describecustomers_with_nested_table;
Name Null? Type
------------------------------------------------------------- ------------------------------------
ID NOT NULL NUMBER(38)
FIRST_NAME VARCHAR2(10)
LAST_NAME VARCHAR2(10)
ADDRESSES T_NESTED_TABLE_ADDRESS
STREET VARCHAR2(15)
CITY VARCHAR2(15)
STATE CHAR(2)
ZIP VARCHAR2(5)
可以从user_nested_tables视图中获得嵌套表的信息。
查看如下：
collection_user@PDB1> selecttable_name,table_type_name,parent_table_name,parent_table_column fromuser_nested_tables where table_name='NESTED_ADDRESSES';
TABLE_NAME
----------------------------------------------------------------------------------------------------
TABLE_TYPE_NAME
----------------------------------------------------------------------------------------------------
PARENT_TABLE_NAME
----------------------------------------------------------------------------------------------------
PARENT_TABLE_COLUMN
----------------------------------------------------------------------------------------------------
NESTED_ADDRESSES
T_NESTED_TABLE_ADDRESS
CUSTOMERS_WITH_NESTED_TABLE
ADDRESSES
