--关联数组
declare
type list_of_ctrys is table of t_mt_ctry.cd%type
index by pls_integer;
ctrys list_of_ctrys;
l_row pls_integer;
begin
ctrys(10) := 'CN';
ctrys(1000) := 'US';
ctrys(-10) := 'JP';
ctrys(50) := 'MY';
l_row := ctrys.first;
dbms_output.put_line('l_row ' || l_row);
while l_row is not null
loop
dbms_output.put_line(ctrys(l_row));
l_row := ctrys.next(l_row);
end loop;
end;
--嵌套表
create or replace type list_of_names is table of varchar2(100);
declare
family_names list_of_names := list_of_names();
parent_names list_of_names := list_of_names();
children_names list_of_names := list_of_names();
begin
family_names.extend(4);
family_names(1) := 'zhangsan';
family_names(2) := 'zhanglisi';
family_names(3) := 'zhangwangwu';
family_names(4) := 'wangwu';
parent_names.extend;
parent_names(1) := 'zhangsan';
parent_names.extend;
parent_names(2) := 'wangwu';
children_names := family_names multiset except parent_names;
for i in children_names.first .. children_names.last
loop
dbms_output.put_line(children_names(i));
end loop;
end;
--zhanglisi
--zhangwangwu
--Varray
create or replace type parent_names is varray(2) of varchar2(20);
create or replace type children_names is varray(1) of varchar2(20);
create table family(
family_no integer,
parents parent_names,
children children_names
);
declare
parents parent_names := parent_names();
children children_names := children_names();
begin
parents.extend(2);
parents(1) := 'zhangsan';
parents(2) := 'lisi';
children.extend;
children(1) := 'zhangyi';
insert into family(family_no,parents,children) values(1,parents,children);
end;
select * from family;
Varray的查询结果: