oracel数组操用示例[2011-09-22收录]

oracle数组操作示例，出处http://fxz-2008.iteye.com/blog/469766

 

--固定数组
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;

 

 

 oracle数组操作示例，出处http://www.cnblogs.com/pswsblog/archive/2010/03/23/1692572.html

1、在countries表中插入数据，在插入之前进行检查，如果表中已经存在，则不插入重复数据。

declare
       type arr_type is varray(29) of varchar(100); --固定维数的数组
       cn_names arr_type := arr_type('澳大利亚','新西兰','巴布亚新几内亚','文莱','新喀里多尼亚','比利时','英国','丹麦','芬兰','希腊','爱尔兰',
       '意大利','卢森堡','马耳他','挪威','瑞士','葡萄牙','德国','瑞典','法国','荷兰','西班牙','奥地利','斐济','瓦努阿图',
       '美国','加拿大','日本','新加坡');
       en_names arr_type := arr_type('Australia','New Zealand','Papua New Guinea','Brunei Darussalam','New Caledonia','Belgium','United Kingdom',
       'Denmark','Finland','Greece','Ireland','Italy','Luxembourg','Malta','Norway','Switzerland','Portugal','Germany',
       'Sweden','France','Netherlands','Spain','Austria','Fiji','Vanuatu','United States','Canada','Japan','Singapore');
       v_row number;
begin
     for i in 1..cn_names.count loop
         select id into v_row from countries where name_cn = cn_names(i);
         if sql%notfound then
            insert into countries (id,name,name_cn,time_stamp) values (countries_seq.nextval,en_names(i),cn_names(i),sysdate);
         end if;
     end loop;

 2、使用数组操作和使用游标操作的区别

declare --使用游标
       cursor cur is select * from employees;
       rr employees%rowtype;
begin
     open cur;
     loop
         exit when cur%notfound;
         fetch cur into rr;
         dbms_output.put_line(rr.username);
     end loop;
     close cur;
end;

 

declare --使用数组
       type arr is table of employees%rowtype index by binary_integer; --可变数组
       v_arr arr;
begin
     select * bulk collect into v_arr from employees; --bulk collect将数据一起赋给v_arr,加快速度，这里不用会提示错误
     for i in 1..v_arr.count loop
         dbms_output.put_line(v_arr(i).username);
     end loop;
end;

 用数组的运行速度比用游标的速度快些！
 

oracle数组操作，出处http://tech.163.com/05/0701/10/1NIODMQS00091589.html

---------------------- 单维数组------------------------

DECLARE
TYPE emp_ssn_array IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;

best_employees emp_ssn_array;
worst_employees emp_ssn_array;

BEGIN
best_employees(1) := '123456';
best_employees(2) := '888888';

worst_employees(1) := '222222';
worst_employees(2) := '666666';

FOR i IN 1..best_employees.count LOOP
DBMS_OUTPUT.PUT_LINE('i='|| i || ', best_employees= ' ||best_employees(i)
|| ', worst_employees= ' ||worst_employees(i));
END LOOP;

END;
 



---------------------- 多维数组------------------------ 

DECLARE

TYPE emp_type IS RECORD
( emp_id employee_table.emp_id%TYPE,
emp_name employee_table.emp_name%TYPE,
emp_gender employee_table.emp_gender%TYPE );

TYPE emp_type_array IS TABLE OF
emp_type INDEX BY BINARY_INTEGER;

emp_rec_array emp_type_array;
emp_rec emp_type;

BEGIN
emp_rec.emp_id := 300000000;
emp_rec.emp_name := 'Barbara';
emp_rec.emp_gender := 'Female';

emp_rec_array(1) := emp_rec;

emp_rec.emp_id := 300000008;
emp_rec.emp_name := 'Rick';
emp_rec.emp_gender := 'Male';

emp_rec_array(2) := emp_rec;

FOR i IN 1..emp_rec_array.count LOOP
DBMS_OUTPUT.PUT_LINE('i='||i
||', emp_id ='||emp_rec_array(i).emp_id
||', emp_name ='||emp_rec_array(i).emp_name
||', emp_gender = '||emp_rec_array(i).emp_gender);
END LOOP; 

END;
-------------- Result --------------
i=1, emp_id =300000000, emp_name =Barbara, emp_gender = Female
i=2, emp_id =300000008, emp_name =Rick, emp_gender = Male

注：在PL/SQL 中是没有数组(Array) 概念的. 但是如果程序员想用Array 的话, 就得变通一下, 用TYPE 和Table of Record 来代替多维数组, 一样挺好用的。
emp_type 就好象一个table 中的一条record 一样, 里面有id, name,gender等。emp_type_array 象个table, 里面含有一条条这样的record (emp_type)，就象多维数组一样。