oracle里数组,Oracle中的数组

--固定数组 declare type type_array is varray(10) of varchar2(20); var_array type_array:=type_array('ggs','jjh','wsb','csl','dd','bb'); begin for i in 1..var_array.count loop dbms_output.put_line(var_array(i)); end loop; end; --可变数组 dec

--固定数组

declare

type type_array is varray(10) of varchar2(20);

var_array type_array:=type_array('ggs','jjh','wsb','csl','dd','bb');

begin

for i in 1..var_array.count loop

dbms_output.put_line(var_array(i));

end loop;

end;

--可变数组

declare

type type_array is table of varchar2(20) index by binary_integer;

var_array type_array;

begin

var_array(1):='aa';

var_array(2):='bb';

for i in 1..var_array.count loop

dbms_output.put_line( var_array(i));

end loop;

end;

--可变数组取表

declare

begin

end;

create or replace procedure proc_stock(n number)

as

var_stock_code varchar2(10);

var_stock_price number;

begin

for i in 1..n loop

var_stock_code:= lpad(STR1 =>i ,LEN =>6 ,PAD =>'0' ) ;

var_stock_price:=trunc(dbms_random.value*100)+1;

--dbms_output.put_line(var_stock_code);

--dbms_output.put_line(var_stock_price);

insert into t_stock (stockcode,stockprice)

values(var_stock_code,var_stock_price);

commit;

end loop;

end;

declare

begin

proc_stock(1000000);

end;

--用游标访问 14.578秒 13.5 13.8

declare

cursor cur is select * from t_stock;

row_stock t_stock%rowtype;

begin

open cur;

loop

fetch cur into row_stock;

exit when cur%notfound;

null;

end loop;

close cur;

end;

--用数组实现 4.813 1.953 2

declare

type type_array is table of t_stock%rowtype index by binary_integer;

var_array type_array;

begin

select * bulk collect into var_array from t_stock;

for i in 1..var_array.count loop

null;

end loop;

end;

--访问自定义表

declare

type type_record is record(

username varchar2(20),

sex varchar2(2)

);

type_record_user type_record;

type type_array is table of type_record_user%type index by binary_integer;

var_array type_array;

begin

select username,sex bulk collect into var_array from tuser;

for i in 1..var_array.count loop

dbms_output.put_line(var_array(i).username);

dbms_output.put_line(var_array(i).sex);

end loop;

end; 本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源：php中文网