Saturday, October 15, 2011

Return a resultset in stored procedure with SYS_REFCURSOR

The following stored procedure is used to check user login information. If user info is correct, update LAST_LOGIN_DATE

CREATE OR REPLACE
PROCEDURE CHECK_LOGIN
  (I_USER_ID VARCHAR2,
    I_PWD VARCHAR2,
    O_RS OUT SYS_REFCURSOR
  )
IS
 vCount INTEGER:=0; 
BEGIN  
   SELECT COUNT(*) INTO vCount 
 FROM USER
        WHERE USER_ID = I_USER_ID AND PWD = ORA_HASH(I_PWD);
   IF (vCount > 0) THEN
  BEGIN
   UPDATE USER
   SET LAST_LOGIN_DATE = SYSDATE
   WHERE USER_ID = I_USER_ID;
     
   OPEN O_RS FOR
   SELECT USER_ID, USER_NAME, USER_STATUS, EMAIL, WEBSITE
   FROM USER
   WHERE USER_ID = I_USER_ID AND PWD = ORA_HASH(I_PWD);
  END;  
 ELSE
  OPEN O_RS FOR
  SELECT USER_ID, USER_NAME, USER_STATUS, EMAIL, WEBSITE
                FROM USER
  WHERE 1 = 2;   
 END IF;
END CHECK_LOGIN;

No comments:

Post a Comment