oracle 集合的类型

集合的类型有三种：关联数组，varry，嵌套表




关联数组
只在PL/SQL中有效，无需初始化


----------下标可以使用pls_integer
declare
type a is table of varchar2(10) index by pls_integer;
l_row pls_integer;
name a;
begin
name(1):='jim';
name(2):='Wolf';
name(3):='Candy';


l_row:=name.first;


loop
exit when l_row is null;
dbms_output.put_line(name(l_row));
l_row:=name.next(l_row);
end loop;
end;
/
----------下标可以使用varchar2


declare
type a is table of varchar2(10) index by varchar2(10);
l_row pls_integer;
age a;
begin
age('Jim'):=22;
age('Wolf'):=32;
dbms_output.put_line(age('Jim'));
end;
/


嵌套表
可作为模式级别数据类型，运用于SQL


--------------------用法一
declare
type a is table of varchar2(10);
name a:=a();
begin
name.extend(3);
name(1):='Jim';
name(2):='Bonie';
name(3):='Elena';
dbms_output.put_line(name(1));
end;
/


--------------------用法二  将嵌套表的字段类型以SQL表的形式输出
create type a is table of varchar2(10);


drop table test;
create table test(
family varchar2(10)
,name a
)
nested table name store as name_1
;




declare
name a:=a();
begin
name.extend(3);
name(1):='Jim';
name(2):='Bonie';
name(3):='Elena';
insert into test(family,name) values('f_wang',name);
end;
/


select * from table(select name from test);




----------------用法三  将嵌套表类型转换SQL表形式的数据


declare
name a:=a();
name_1 varchar2(20);
cur_1 sys_refcursor;
begin
name.extend(3);
name(1):='Jim';
name(2):='Bonie';
name(3):='Elena';
open cur_1 for select * from table(name);
loop
fetch cur_1 into name_1;
exit when cur_1%notfound;
dbms_output.put_line(name_1);
end loop;
end;
/