create or replace PACKAGE PLSQLPACKAGE AS
--必须在包中声明输出用游标
type Test_CURSOR is ref cursor ;
PROCEDURE PROC3( p_CURSOR OUT Test_CURSOR);
END PLSQLPACKAGE;
create or replace
PACKAGE BODY PLSQLPACKAGE AS
PROCEDURE PROC3 ( p_CURSOR OUT Test_CURSOR) AS
BEGIN
--在过程中打开游标
OPEN p_CURSOR FOR SELECT * FROM TB_USERTEST;
END PROC3;
END PLSQLPACKAGE;
--测试用代码
DECLARE
P_CURSOR PLSQLPACKAGE.Test_CURSOR;
user_id_var number(5,0);
user_name_var varchar2(20);
user_add_var varchar2(20);
user_phone_var varchar2(20);
BEGIN
PLSQLPACKAGE.PROC3(
P_CURSOR => P_CURSOR
);
fetch P_CURSOR into user_id_var, user_name_var, user_add_var, user_phone_var;
while P_CURSOR%FOUND loop
DBMS_OUTPUT.PUT_LINE('ID = ' || user_id_var || ' NAME = ' || user_name_var || ' ADD = ' || user_add_var || ' PHONE = ' || user_phone_var);
fetch P_CURSOR into user_id_var, user_name_var, user_add_var, user_phone_var;
end loop;
--关闭游标
close P_CURSOR;
END;