Oracle 函数一则

一个验证机制。
如果用户登陆成功，
就给该用户加分，
并且写入另外一张登陆表

CREATE OR REPLACE FUNCTION FUN_AUTHORIZE(
          usr IN TBL_USERS.USR_NAME%TYPE,
          pwd IN TBL_USERS.USR_PWD%TYPE)
  RETURN BOOLEAN AS

  var_usr_id TBL_USERS.USR_ID%TYPE;

  var_date TBL_LOGIN_LOG.LOG_TIME%TYPE := SYSTIMESTAMP;

  var_points TBL_USERS.USR_POINTS%TYPE;

  CURSOR cur_usr (p_usr IN TBL_USERS.USR_NAME%TYPE,
                  p_pwd IN TBL_USERS.USR_PWD%TYPE)
    IS
      SELECT USR_ID
      FROM TBL_USERS
      WHERE TBL_USERS.USR_NAME = p_usr
      AND TBL_USERS.USR_PWD = p_pwd;
BEGIN
  OPEN cur_usr(usr, pwd);
  FETCH cur_usr INTO var_usr_id;
  CLOSE cur_usr;

  IF var_usr_id IS NULL THEN
    RETURN FALSE;
  ELSE
    SELECT TBL_USERS.USR_POINTS
    INTO var_points
    FROM TBL_USERS
    WHERE USR_ID = var_usr_id;

    var_points := var_points + 5;

    UPDATE TBL_USERS
    SET TBL_USERS.USR_POINTS = var_points
    WHERE TBL_USERS.USR_ID = var_usr_id;

    INSERT INTO TBL_LOGIN_LOG(USR_ID,LOG_TIME, LOG_VALID)
    VALUES(var_usr_id, var_date, 'Y');

    RETURN TRUE;
  END IF;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN FALSE;
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    RETURN FALSE;
END FUN_AUTHORIZE;