create type t1 is varray(10) of number; create type t2 is varray(10) of number; create type t3 is varray(10) of number;declare cursor t_sor is select col1,col2,col3 from tabname; v1 t1:=t1(); v2 t2:=t2(); v3 t3:=t3(); num number:=1; begin for v_sor in t_sor loop v1.extend(); v2.extend(); v3.extend(); v1(num):=v_sor.col1; v2(num):=v_sor.col2; v3(num):=v_sor.col3; num:=num+1; end loop; end; /
用Varray, 首先创建Varray: create type ADDRESS_TYPE ad varray(3) of varchar2(50); / 创建表: create table ADDRESS_VARRAY ( ID integer primary key, ADDRESSES ADDRESS_TYPE ); 插入Varray: insert into ADDRESS_VARRAY values (1,ADDRESS_TYPE('china','America','UK')); 选择Varry: declare ID ADDRESS_VARRAY.ID%type; ADD ADDRESS_VARRAY.ADDRESSES%type;cursor ADDRESS_CURSOR is select ID,ADDRESSES from ADDRESS_VARRAYbegin open ADDRESS_CURSOR; loop fetch ADDRESS_CURSOR into ID,ADD; exit when ADDRESS_CURSOR%notfound; dbms_out.put_line('id='||ID||',add='||ADD); end loop; close ADDRESS_CURSOR; end; /
create type t2 is varray(10) of number;
create type t3 is varray(10) of number;declare
cursor t_sor is
select col1,col2,col3 from tabname;
v1 t1:=t1();
v2 t2:=t2();
v3 t3:=t3();
num number:=1;
begin
for v_sor in t_sor loop
v1.extend();
v2.extend();
v3.extend();
v1(num):=v_sor.col1;
v2(num):=v_sor.col2;
v3(num):=v_sor.col3;
num:=num+1;
end loop;
end;
/
首先创建Varray:
create type ADDRESS_TYPE ad varray(3) of varchar2(50);
/
创建表:
create table ADDRESS_VARRAY (
ID integer primary key,
ADDRESSES ADDRESS_TYPE
);
插入Varray:
insert into ADDRESS_VARRAY values (1,ADDRESS_TYPE('china','America','UK'));
选择Varry:
declare
ID ADDRESS_VARRAY.ID%type;
ADD ADDRESS_VARRAY.ADDRESSES%type;cursor ADDRESS_CURSOR is
select ID,ADDRESSES
from ADDRESS_VARRAYbegin
open ADDRESS_CURSOR; loop
fetch
ADDRESS_CURSOR
into
ID,ADD;
exit when ADDRESS_CURSOR%notfound; dbms_out.put_line('id='||ID||',add='||ADD);
end loop; close ADDRESS_CURSOR;
end;
/