CREATE OR REPLACE PACKAGE pkg_test AS TYPE myrctype IS REF CURSOR; PROCEDURE get (p_id NUMBER, p_rc OUT myrctype); END pkg_test; /CREATE OR REPLACE PACKAGE BODY pkg_test AS PROCEDURE get (p_id NUMBER, p_rc OUT myrctype) IS sqlstr VARCHAR2 (500); BEGIN IF p_id = 0 THEN OPEN p_rc FOR SELECT ID, NAME, sex, address, postcode, birthday FROM student; ELSE sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id'; OPEN p_rc FOR sqlstr USING p_id; END IF; END get; END pkg_test; /
这段代码在Oracle的缺省用户scott下可以通过,你可以试试。CREATE OR REPLACE PACKAGE pkg_test AS TYPE cur is REF CURSOR; PROCEDURE pro_1(v_deptno NUMBER,c OUT cur); FUNCTION fun_1(v_deptno NUMBER) RETURN cur; END; /CREATE OR REPLACE PACKAGE BODY pkg_test AS PROCEDURE pro_1(v_deptno NUMBER,c OUT cur) AS v_name VARCHAR2(10); BEGIN OPEN c FOR SELECT ename FROM emp WHERE deptno = v_deptno; END;FUNCTION fun_1(v_deptno NUMBER) RETURN cur IS c_temp cur; BEGIN OPEN c_temp FOR SELECT ename FROM emp WHERE deptno = v_deptno; RETURN c_temp; END; END; /DECLARE p pkg_test.cur; v_ename varchar2(10); BEGIN p := pkg_test.fun_1(10); loop fetch p into v_ename; exit when p%notfound; dbms_output.put_line(v_ename); end loop; close p; END; /DECLARE p pkg_test.cur; v_ename varchar2(10); BEGIN pkg_test.pro_1(10,p); loop fetch p into v_ename; exit when p%notfound; dbms_output.put_line(v_ename); end loop; close p; END; /
AS
TYPE myrctype IS REF CURSOR; PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);
END pkg_test;
/CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)
IS
sqlstr VARCHAR2 (500);
BEGIN
IF p_id = 0 THEN
OPEN p_rc FOR
SELECT ID, NAME, sex, address, postcode, birthday
FROM student;
ELSE
sqlstr :=
'select id,name,sex,address,postcode,birthday
from student where id=:w_id';
OPEN p_rc FOR sqlstr USING p_id;
END IF;
END get;
END pkg_test;
/
匿名块中呢?
可否在匿名块中执地若干指令后再执行查讯返回结果集呢?
TYPE cur is REF CURSOR;
PROCEDURE pro_1(v_deptno NUMBER,c OUT cur);
FUNCTION fun_1(v_deptno NUMBER) RETURN cur;
END;
/CREATE OR REPLACE PACKAGE BODY pkg_test AS
PROCEDURE pro_1(v_deptno NUMBER,c OUT cur) AS
v_name VARCHAR2(10);
BEGIN
OPEN c FOR SELECT ename FROM emp WHERE deptno = v_deptno;
END;FUNCTION fun_1(v_deptno NUMBER) RETURN cur IS
c_temp cur;
BEGIN
OPEN c_temp FOR SELECT ename FROM emp WHERE deptno = v_deptno;
RETURN c_temp;
END;
END;
/DECLARE
p pkg_test.cur;
v_ename varchar2(10);
BEGIN
p := pkg_test.fun_1(10);
loop
fetch p into v_ename;
exit when p%notfound;
dbms_output.put_line(v_ename);
end loop;
close p;
END;
/DECLARE
p pkg_test.cur;
v_ename varchar2(10);
BEGIN
pkg_test.pro_1(10,p);
loop
fetch p into v_ename;
exit when p%notfound;
dbms_output.put_line(v_ename);
end loop;
close p;
END;
/