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 (stoc本文来源gaodai$ma#com搞$$代**码网kcode,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;

喜欢 (0)赏

[搞代码]

分享 (0)