--包头
create or replace package findAll_emp
is
procedure findAll(p_cursor out sys_refcursor);
end findAll_emp;--包体
create or replace package body findAll_emp
is
procedure findAll(p_cursor out sys_refcursor)
is
begin
open p_cursor for select * from emp;
close p_cursor;
end findAll;
end findAll_emp;--测试
declare
p_cursor sys_refcursor;
myrecord emp%rowtype;
begin
findAll_emp.findAll(p_cursor);
loop
fetch p_cursor into myrecord;
exit when p_cursor%notfound;
dbms_output.put_line(myrecord.ename||' '||myrecord.sal);
end loop;
end;提示错误:
第 1 行出现错误:
ORA-01001: 无效的游标
ORA-06512: 在 line 7
create or replace package findAll_emp
is
procedure findAll(p_cursor out sys_refcursor);
end findAll_emp;--包体
create or replace package body findAll_emp
is
procedure findAll(p_cursor out sys_refcursor)
is
begin
open p_cursor for select * from emp;
close p_cursor;
end findAll;
end findAll_emp;--测试
declare
p_cursor sys_refcursor;
myrecord emp%rowtype;
begin
findAll_emp.findAll(p_cursor);
loop
fetch p_cursor into myrecord;
exit when p_cursor%notfound;
dbms_output.put_line(myrecord.ename||' '||myrecord.sal);
end loop;
end;提示错误:
第 1 行出现错误:
ORA-01001: 无效的游标
ORA-06512: 在 line 7
CREATE OR REPLACE PACKAGE BODY findAll_emp IS
PROCEDURE findAll(p_cursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_cursor FOR
SELECT * FROM emp;
--CLOSE p_cursor; --这儿不能关闭输出参数游标
END findAll;
END findAll_emp;
/
DECLARE
p_cursor SYS_REFCURSOR;
myrecord emp%ROWTYPE;
BEGIN
findAll_emp.findAll(p_cursor);
LOOP
FETCH p_cursor
INTO myrecord;
EXIT WHEN p_cursor%NOTFOUND;
dbms_output.put_line(myrecord.ename || ' ' || myrecord.sal);
END LOOP;
IF p_cursor%ISOPEN THEN --调用完成,关闭游标
CLOSE p_cursor;
END IF;
END;
/
这个地方为什么不能关闭游标呢?
http://topic.csdn.net/u/20110616/21/39420d99-7481-4f12-8082-716a0a4af982.html