获取Oracle过程中的OUT SYS_REFCURSOR值

时间:2022-07-04 17:21:47

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

原过程大致如下:

 PROCEDURE P_USER_LOGIN(P_USERLOGINID IN VARCHAR2,P_PASSWORD IN VARCHAR2,P_SHOPID IN VARCHAR2,P_CURSOR OUT SYS_REFCURSOR) AS
----....... OPEN P_CURSOR FOR
SELECT r_UserList.Userid AS USERID,
r_UserList.Userloginid AS USERLOGINID,
r_UserList.Username AS USERNAME,
r_UserList.Usertype AS USERTYPE,
r_UserList.Isposer AS Isposer,
r_UserShopRole.Posroleid AS Posroleid,
r_UserShopRole.Roleid AS ROLEID,
r_RoleList.Rolename AS ROLENAME,
l_Info AS INFO
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;