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;

--可變數組
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;