oracle. 数组清空,Oracle数组 - 如花如果如落叶的个人空间 - OSCHINA - 中文开源技术交流社区...

/*创建变长数组类型*/

CREATE TYPE t_varray_address AS VARRAY(3) OF VARCHAR2(50)

ALTER TYPE t_varray_address MODIFY LIMIT 10 CASCADE;

/*创建嵌套表类型*/

CREATE TYPE t_nested_table_address AS TABLE OF t_address

CREATE TABLE customers_with_varray(

id  INTEGER PRIMARY KEY,

first_name VARCHAR2(10),

last_name  VARCHAR2(10),

addresses  t_varray_address

)

CREATE TABLE customers_with_nested_table(

id  INTEGER PRIMARY KEY,

first_name VARCHAR2(10),

last_name  VARCHAR2(10),

addresses  t_nested_table_address

)

NESTED TABLE

addresses

STORE AS

nested_addresses

/*获取变长数组类型*/

DESCRIBE t_varray_address

/*从user_varrays视图中检索出t_varray_address的详细信息*/

SELECT parent_table_name,parent_table_column,type_name FROM user_varrays

WHERE type_name='T_VARRAY_ADDRESS';

SELECT table_name,table_type_name,parent_table_name,parent_table_column FROM user_nested_tables

WHERE table_name='NESTED_ADDRESS'

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

/*填充变长数组元素*/

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

INSERT INTO customers_with_varray VALUES(

1,'Steve','Brown',

t_varray_address(

'2 State Street,Beantown,MA,12345',

'4 Hill Street,Lost Town,CA,54321'

)

)

INSERT INTO customers_with_varray VALUES(

2,'John','Smith',

t_varray_address(

'1 High Street,Newtown,CA,12347',

'3 New Street,AnyTown,MI,54323',

'7 Market Street,Main Town,MA,54323'

)

)

SELECT * FROM customers_with_varray

/*填充嵌套表元素*/

INSERT INTO customers_with_nested_table VALUES(

1,'Steve','Brown',

t_nested_table_address(

t_address('2 State Street','Beantown','MA','12345'),

t_address('4 Hill Street','Lost Town','CA','54321')

)

)

INSERT INTO customers_with_nested_table VALUES(

2,'John','Smith',

t_nested_table_address(

t_address('1 High Street','Newtown','CA','12347'),

t_address('3 New Street','AnyTown','MI','54321'),

t_address('7 Market Street','Main Town','MA','54323')

)

)

SELECT * FROM customers_with_nested_table WHERE id=1

SELECT a.*  FROM customers_with_nested_table c, TABLE(c.addresses) a  WHERE id = 1

SELECT *    FROM TABLE (SELECT addresses  FROM customers_with_nested_table

WHERE id = 1)

SELECT c.id,c.first_name,c.last_name,a.* FROM customers_with_varray c,

TABLE(c.addresses) a   WHERE id=1

SELECT a.city,a.state   FROM customers_with_nested_table c, TABLE(c.addresses) a

WHERE id = 1

/*更改变长数组元素*/

UPDATE customers_with_varray

SET addresses=t_varray_address(

'6 Any Street,Lost Town,GA,33347',

'3 New Street,Anytown,MT,54323')

WHERE id=2

SELECT a.* FROM customers_with_varray c,TABLE(addresses) a WHERE id=2

/*更改嵌套表元素*/

UPDATE TABLE(

SELECT addresses FROM customers_with_nested_table

WHERE id=2

) addr

SET VALUE(addr)=t_address('9 Any Street','Lost Town','VA','74321')

WHERE VALUE(addr)=t_address('1 High Street','Newtown','CA','12347')

EXPLAIN PLAN SET STATEMENT_ID='EXISTS_QUERY' FOR

SELECT deptno,dname FROM dept WHERE EXISTS(

SELECT deptno FROM dept WHERE loc='nanjing')

SELECT XMLELEMENT("location",loc) AS XML_CITY

FROM dept WHERE deptno=50