常用数组定义方式
oracle本身没有java中数组的概念,不过,平常很多人将下面存储数据的方式称作数组,所以个人暂时也就这样记忆吧。
1,个人常用二维数组的定义如下:(模仿下标都为数字的二维数组)
Type ARRAY1 is TABLE OF number index by binary_integer;
Type ARRAY2 is TABLE OF ARRAY1 index by binary_integer;
canshu ARRAY2;
2,常规语法如下:
declare
type type_array is table of t_user%rowtype index by binary_integer; --类似二维数组
var_array type_array;
begin
select * bulk collect into var_array from t_user;
for i in 1..var_array.count loop
dbms_output.put_line(var_array(i).user_id); --二维数组的访问
dbms_output.put_line(var_array(i).username);
end loop;
end;
3,数组的常用方式如下(摘自网络)
- --固定数组
- 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;