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),就象多维数组一样。