TYPE type_name ISTABLEOF element_type [NOTNULL]; -->element_type为索引表元数据指定数据类型(先使用TYPE声明表结构)
table_name TYPE_NAME; -->再使用声明的TYPE类型声明实际嵌套表
--声明嵌套表并输出嵌套表的实际内容
scott@CNMMBO> DECLARE
2 CURSOR name_cur IS
3 SELECT dname
4 FROM dept
5 WHERE deptno < 40;
6
7 TYPE name_type ISTABLEOF dept.dname%TYPE;
8
9 dname_tab name_type := name_type( ); -->需要初始化,否则将收到 ORA-06531:Reference to uninitialized collection.
10 v_counter INTEGER := 0;
11 BEGIN
12 FOR name_rec IN name_cur
13 LOOP
14 v_counter :=
15 v_counter
16 + 1;
17 dname_tab.EXTEND; -->需要扩展,否则将收到 ORA-06533: Subscript beyond count
18 dname_tab( v_counter ) := name_rec.dname;
19 DBMS_OUTPUT.put_line( 'Dname ('
20 v_counter
21 ') is :'
22 dname_tab( v_counter ) );
23 END LOOP;
24 END;
25 /
Dname (1) is :ACCOUNTING
Dname (2) is :RESEARCH
Dname (3) is :SALES
PL/SQL procedure successfully completed.
--将嵌套表作为表列的数据类型来使用
scott@CNMMBO> CREATEORREPLACE TYPE mail_type ISTABLEOF VARCHAR2( 100 );
2 /
Type created.
scott@CNMMBO>
CREATETABLE
tb_tmp -->创建表tb_tmp
2 (
3 empno NUMBER( 4 )
4 , ename VARCHAR2( 10 )
5 , mail mail_type -->列mail的类型为mail_type
6 )
7 NESTED TABLE mail -->注意此处需要指定嵌套表的存储方式
8 STORE AS mail_tab;
Table created.
--为嵌套表插入数据
scott@CNMMBO>
insertinto
tb_tmp select
8888,'Jack'
,'Jack@yahoo.com,Jack@163.com'from
dual; -->传统方式插入失败
insertinto tb_tmp select 8888,'Jack','Jack@yahoo.com,Jack@163.com'from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CHAR
scott@CNMMBO>
insertinto
tb_tmp select
8888,'Jack'
,q'['
Jack@yahoo.com','
Jack@163.com']'from
dual; -->分割字符串方式插入失败
insertinto tb_tmp select 8888,'Jack',q'['Jack@yahoo.com','Jack@163.com']'from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CHAR
scott@CNMMBO>
insertinto
tb_tmp select
8888,'Jack'
,mail_type('Jack@yahoo.com'
,'Jack@163.com'
) from
dual; -->插入时指定嵌套表类型
1 row created.
scott@CNMMBO> commit;
Commit complete.
--查看存在嵌套表数据类型的表中的记录
scott@CNMMBO> col mail format a30
scott@CNMMBO>
select * from
tb_tmp; -->查询数据时带有嵌套表的类型
EMPNO ENAME MAIL
---------- ---------- ------------------------------
8888 Jack MAIL_TYPE('Jack@yahoo.com', 'J
ack@163.com')
scott@CNMMBO> DECLARE
2 mail_tab mail_type; -->声明一个mail_type数据类型
3 BEGIN
4 SELECT mail
5 INTO mail_tab -->将数据保存到 mail_tab 变量中
6 FROM tb_tmp
7 WHERE empno = 8888;
8
9 FOR i IN 1 .. mail_tab.COUNT-->轮循输出嵌套表类型中的值
10 LOOP
11 DBMS_OUTPUT.put_line( 'Jack mail address is '
12 mail_tab( i ) );
13 END LOOP;
14 END;
15 /
Jack mail address is Jack@yahoo.com
Jack mail address is Jack@163.com
PL/SQL procedure successfully completed.
--更新表中含有嵌套表类型中的值
scott@CNMMBO> DECLARE
2 mail_tab mail_type := mail_type( 'Jackson@yahoo.com', 'Jackson@163.com' );
3 BEGIN
4 UPDATE tb_tmp
5 SET mail = mail_tab;
6
7 COMMIT;
8 DBMS_OUTPUT.put_line( 'Jack mail address was updated' );
9 END;
10 /
Jack mail address was updated
PL/SQL procedure successfully completed.
scott@CNMMBO> select * from tb_tmp;
EMPNO ENAME MAIL
---------- ---------- ------------------------------
8888 Jack MAIL_TYPE('Jackson@yahoo.com',
'Jackson@163.com')
--删除表中存在嵌套表类型的记录
scott@CNMMBO> deletefrom tb_tmp where empno=8888;
1 row deleted.
scott@CNMMBO> commit;
Commit complete.
scott@CNMMBO> select * from tb_tmp;
norows selected