常用数组定义方式

　　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;