CREATE TABLE emp( eid varchar2(10), empname varchar2(20) );INSERT INTO emp(eid,empname) values('01','蒋介石'); INSERT INTO emp(eid,empname) values('02','毛泽东'); INSERT INTO emp(eid,empname) values('03','林彪'); INSERT INTO emp(eid,empname) values('04','蒋经国'); INSERT INTO emp(eid,empname) values('05','张三'); CREATE OR REPLACE PROCEDURE p_record(v_eid in VARCHAR2, resultItem out sys_refcursor) AS BEGIN OPEN resultItem FOR 'SELECT eid, empname FROM emp WHERE eid in('''||replace(v_eid,',',''',''')||''')'; END; /set serveroutput on; var c_cur refcursor; exec p_record('03,02',:c_cur); print c_cur;
采用bulk collect可以将查询结果一次性地加载到collections中。而不是通过cursor一条一条地处理。可以在select into,fetch into,returning into语句使用bulk collect。注意在使用bulk collect时,所有的into变量都必须是collections.举几个简单的例子:--在select into语句中使用bulk collectDECLARE 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; /
-- 藐视惧怕游标的人......
eid varchar2(10),
empname varchar2(20)
);INSERT INTO emp(eid,empname) values('01','蒋介石');
INSERT INTO emp(eid,empname) values('02','毛泽东');
INSERT INTO emp(eid,empname) values('03','林彪');
INSERT INTO emp(eid,empname) values('04','蒋经国');
INSERT INTO emp(eid,empname) values('05','张三');
CREATE OR REPLACE PROCEDURE p_record(v_eid in VARCHAR2, resultItem out sys_refcursor)
AS
BEGIN
OPEN resultItem FOR 'SELECT eid, empname FROM emp WHERE eid in('''||replace(v_eid,',',''',''')||''')';
END;
/set serveroutput on;
var c_cur refcursor;
exec p_record('03,02',:c_cur);
print c_cur;
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;
/