存储过程的输出是游标（列表）



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;