集合的类型有三种:关联数组,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;
/
关联数组
只在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;
/