142.Oracle数据库SQL开发之 集合——使用CAST转换类型
欢迎转载,转载请标明出处:http://blog.csdn.net/notbaron/article/details/50180843
使用CAST可以将一种集合类型转换为另一种集合类型。
1. 使用CAST将变长数组转换为嵌套表
创建并填充表customers_with_varray2
CREATE TYPE t_varray_address2 AS VARRAY(3) OFt_address;
/
CREATE TABLE customers_with_varray2 (
id INTEGER PRIMARY KEY,
first_name VARCHAR2(10),
last_name VARCHAR2(10),
addresses t_varray_address2
);
插入如下:
INSERT INTO customers_with_varray2 VALUES (
1, 'Jason','Bond',
t_varray_address2(
t_address('9 Newton Drive', 'Sometown', 'WY', '22123'),
t_address('6 Spring Street', 'New City', 'CA', '77712')
)
);
查询使用CAST将顾客#1的变长数组地址返回嵌套表,
如下:
collection_user@PDB1> select cast(cv.addresses ast_nested_table_address) from customers_with_varray2 cv where cv.id=1;
CAST(CV.ADDRESSESAST_NESTED_TABLE_ADDRESS)(STREET,CITY, STATE, ZIP)
----------------------------------------------------------------------------------------------------
T_NESTED_TABLE_ADDRESS(T_ADDRESS('9 NewtonDrive', 'Sometown', 'WY', '22123'), T_ADDRESS('6 Spring S
treet', 'New City', 'CA', '77712'))
2. 使用CAST将嵌套表转换为变长数组
CAST将customers_with_nested_table中顾客#1的地址转换回为变长数组。
执行如下:
collection_user@PDB1>select cast(cn.addresses as t_varray_address2) from customers_with_nested_tablecn where cn.id=1;
CAST(CN.ADDRESSESAST_VARRAY_ADDRESS2)(STREET,CITY, STATE, ZIP)
----------------------------------------------------------------------------------------------------
T_VARRAY_ADDRESS2(T_ADDRESS('2State Street', 'Beantown', 'MA', '12345'), T_ADDRESS('4 Hill Street',
'Lost Town', 'CA', '54321'))