获取Oracle过程中的OUT SYS_REFCURSOR值

 一个项目中的实例：获取Oracle过程中的返回SYS_REFCURSOR。注意：如果SYS_REFCURSOR为一个表或视图。可以通过表名%ROWTYPE获取每行数据，而不必另外定义type。

原过程大致如下：

 1 PROCEDURE P_USER_LOGIN(P_USERLOGINID IN VARCHAR2,P_PASSWORD IN VARCHAR2,P_SHOPID IN VARCHAR2,P_CURSOR OUT SYS_REFCURSOR) AS
 2    ----.......
 3 
 4 OPEN P_CURSOR FOR
 5          SELECT r_UserList.Userid           AS USERID,
 6                 r_UserList.Userloginid      AS USERLOGINID,
 7                 r_UserList.Username         AS USERNAME,
 8                 r_UserList.Usertype         AS USERTYPE,
 9                 r_UserList.Isposer          AS Isposer,
10                 r_UserShopRole.Posroleid    AS Posroleid,
11                 r_UserShopRole.Roleid       AS ROLEID,
12                 r_RoleList.Rolename         AS ROLENAME,
13                 l_Info                      AS INFO
14            FROM dual;

15   ----.......

 

 

测试：

/*
--以下为检测登录的demo语句
declare
  type_cur SYS_REFCURSOR;
  -- T_ROW type_cur%ROWTYPE;
  type Type_ROW  is record(
  USERID      nvarchar2(30),
  USERLOGINID nvarchar2(30),
  USERNAME    nvarchar2(30),
  USERTYPE    nvarchar2(20),
  Isposer     nvarchar2(10),
  Posroleid   nvarchar2(10),
  ROLEID      nvarchar2(10),
  ROLENAME    nvarchar2(30),
  INFO        nvarchar2(500)
  );
  T_Row Type_ROW;
 
    
begin

DBUSRMCC.LOGIN_MANAGER.P_USER_LOGIN('235',
                             '0201F1233',
                             'A00001',
                             type_cur);
 LOOP  
    FETCH type_cur  INTO T_ROW;  
    EXIT WHEN type_cur%NOTFOUND;  
    DBMS_OUTPUT.PUT_LINE(T_ROW.USERLOGINID||':'||T_ROW.INFO );  
   
  END LOOP;  
  CLOSE type_cur;  ----必须要明确的关闭游标  
end;

 

转载于:https://www.cnblogs.com/downmoon/p/7702038.html