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;--可变数组取表declarebegin 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;declarebegin proc_stock(1000000);end;--用游标访问 14.578秒 13.5 13.8declare 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 2declare 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
---------------------- 单维数组------------------------DECLARETYPE emp_ssn_array IS TABLE OF NUMBERINDEX BY BINARY_INTEGER;best_employees emp_ssn_array;worst_employees emp_ssn_array;BEGINbest_employees(1) := '123456';best_employees(2) := '888888';worst_employees(1) := '222222';worst_employees(2) := '666666';FOR i IN 1..best_employees.count LOOPDBMS_OUTPUT.PUT_LINE('i='|| i || ', best_employees= ' ||best_employees(i)|| ', worst_employees= ' ||worst_employees(i));END LOOP;END; ---------------------- 多维数组------------------------ DECLARETYPE 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 OFemp_type INDEX BY BINARY_INTEGER;emp_rec_array emp_type_array;emp_rec emp_type;BEGINemp_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 LOOPDBMS_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 = Femalei=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)，就象多维数组一样。