转自:http://xuejiangtao.iteye.com/blog/1168570
通过bulk collect减少loop处理的开销,采用bulk collect可以将查询结果一次性地加载到collections中,而不是通过cursor一条一条地处理。 可以在select into,fetch into,returning into语句使用bulk collect。 注意在使用bulk collect时,所有的into变量都必须是collections。
举几个简单的例子:
--在select into语句中使用bulk collect
DECLARE
TYPE SalList IS TABLE OF emp.sal%TYPE;
sals SalList;
BEGIN
-- Limit the number of rows to 100.
SELECT sal BULK COLLECT INTO sals FROM emp
WHERE ROWNUM <= 100;
-- Retrieve 10% (approximately) of the rows in the table.
SELECT sal BULK COLLECT INTO sals FROM emp SAMPLE 10;
END;
--在fetch into中使用bulk collect
DECLARE
TYPE DeptRecTab IS TABLE OF dept%ROWTYPE;
dept_recs DeptRecTab;
CURSOR c1 IS
SELECT deptno, dname, loc FROM dept WHERE deptno > 10;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO dept_recs;
END;
--在returning into中使用bulk collect
CREATE TABLE emp2 AS SELECT * FROM employees;
DECLARE
TYPE NumList IS TABLE OF employees.employee_id%TYPE;
enums NumList;
TYPE NameList IS TABLE OF employees.last_name%TYPE;
names NameList;
BEGIN
DELETE FROM emp2 WHERE department_id = 30
RETURNING employee_id, last_name BULK COLLECT INTO enums, names;
dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN enums.FIRST .. enums.LAST
LOOP
dbms_output.put_line('Employee #' || enums(i) || ': ' || names(i));
END LOOP;
END;
Oracle数组
转自:http://www.cnblogs.com/lll3344/archive/2011/03/09/1978366.html
Oracle数组一般可以分为固定数组和可变数组
固定数组
declare
type v_ar is varray(10) of varchar2(30);
my_ar v_ar:=v_ar('g','m','d','龚','帅');
begin
for i in 1..my_ar.count
loop
dbms_output.put_line(my_ar(i));
end loop;
end;
可变数组
一维数组
declare
type v_table is table of varchar2(30) index by binary_integer;
--类型可以是前面的类型定义,index by binary_integer子句代表以符号整数为索引,
--这样访问表类型变量中的数据方法就是“表变量名(索引符号整数)”。
my_table v_table;
begin
for i in 1..20
loop
my_table(i):=i;
dbms_output.put_line(my_table(i));
end loop;
end;
多维数组--多条记录
declare
type v_table is table of liutest%rowtype index by binary_integer;
my_table v_table;
begin
select * bulk collect into my_table from liutest;
for i in 1..my_table.count/10 --my_table.count/10取到的值为四舍五入值
loop
dbms_output.put_line('name--'||my_table(i).name);
dbms_output.put_line('address---'||my_table(i).address);
dbms_output.put_line('age----'||my_table(i).age);
end loop;
end;
多维数组--单条记录
declare
type v_table is table of liutest%rowtype index by binary_integer;
my_table v_table;
begin
select * into my_table(1) from liutest where id=1;
--my_table(i) i可以为任意整数,但取值时必须保持以i一致;
dbms_output.put_line('--name--'||my_table(1).name||'--address--'||my_table(1).address);
end;
自定义数组
create or replace type varray_list as varray(30) of varchar2(50);
--使用自定义数组
create or replace procedure show_list(p_varlist in varray_list)
is
v_str varchar2(50);
begin
for i in 1..p_varlist.count
loop
v_str:=p_varlist(i);
dbms_output.put_line('v_str='||v_str);
dbms_output.put_line('p_varlist('||i||')='||p_varlist(i));
end loop;
end;
declare
my_var varray_list:=varray_list('g','m','d','龚','帅');
begin
show_list(my_var);
end;