1、--创建嵌套表类型
Create Or Replace Type nested_table_type_name Is Table Of element_type;
--创建嵌套表对象
nested_table_variabe_name nested_table_type_name;
Create Table tmp
(
Id Number,
phone test_type,
email test_type
)
Nested Table phone Store As phone_table,
Nested Table email Store As email_table
Insert Into tmp Values(1,test_type('13400000001','13400000002','13400000003'),test_type('a@126.com','b@126.com'));
Insert Into tmp Values(2,test_type('13500000001','13500000002','13500000003'),test_type('a@163.com','b@163.com'));
Insert Into tmp(Id) Values(3);
Update tmp Set phone=test_type('13600000001','13600000002') Where Id=3;
Declare
test_rd test_type;
i Number;
Begin
Select phone Into test_rd From tmp Where Id=1;
For i In 1 .. test_rd.count
Loop
dbms_output.put_line(test_rd(i));
End Loop;
End;
输出:
13400000001
13400000002
test_rd test_type;
Begin
test_rd:=test_type('','','');
test_rd(1):='1';
test_rd(3):='2';
dbms_output.put_line('元素个数:'||test_rd.count);
dbms_output.put_line('元素1:'||test_rd(1));
dbms_output.put_line('元素2:'||test_rd(2));
dbms_output.put_line('元素3:'||test_rd(3));
End;
输出:
元素个数:3
元素1:1
元素2:
元素3:2
Create Or Replace Type nested_table_type_name Is Table Of element_type;
--创建嵌套表对象
nested_table_variabe_name nested_table_type_name;
注:使用嵌套表对象元素之前必须先初始化。下标从1开始,元素个数没限制
实例:
Create Or Replace Type test_type Is Table Of Varchar2(100)Create Table tmp
(
Id Number,
phone test_type,
email test_type
)
Nested Table phone Store As phone_table,
Nested Table email Store As email_table
Insert Into tmp Values(1,test_type('13400000001','13400000002','13400000003'),test_type('a@126.com','b@126.com'));
Insert Into tmp Values(2,test_type('13500000001','13500000002','13500000003'),test_type('a@163.com','b@163.com'));
Insert Into tmp(Id) Values(3);
Update tmp Set phone=test_type('13600000001','13600000002') Where Id=3;
Declare
test_rd test_type;
i Number;
Begin
Select phone Into test_rd From tmp Where Id=1;
For i In 1 .. test_rd.count
Loop
dbms_output.put_line(test_rd(i));
End Loop;
End;
输出:
13400000001
13400000002
13400000003
test_rd test_type;
Begin
test_rd:=test_type('','','');
test_rd(1):='1';
test_rd(3):='2';
dbms_output.put_line('元素个数:'||test_rd.count);
dbms_output.put_line('元素1:'||test_rd(1));
dbms_output.put_line('元素2:'||test_rd(2));
dbms_output.put_line('元素3:'||test_rd(3));
End;
输出:
元素个数:3
元素1:1
元素2:
元素3:2