想想比较效率问题procedure tuning-优快云博客

本文链接：https://blog.youkuaiyun.com/ThomasBoxing/article/details/783076

本文介绍了一个具体的PL/SQL过程实现案例，该过程用于验证用户登录信息，并检查用户的活动状态及系统配置的有效期。通过复杂的数据库交互逻辑，确保了系统的安全性与可靠性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE OR REPLACE PROCEDURE P_EM_BANK_USR_S#ID
/*
COPYRIGHT(C) 2006 compay name

FIRST CREATION (Tang Liquan 20060608)
*/
(
NM_IN        IN     TBL_EM_BANK_USR.NM%TYPE,
PWD_IN    IN    TBL_EM_BANK_USR.PWD%TYPE,
APP_CD_IN   IN    VARCHAR2,
USR_CD_IN   IN    TBL_EM_USR_ABSENT.USR_CD%TYPE,
CD_IN    IN    TBL_EM_SYS_CONFIG.CD%TYPE,
    REF_CURSOR          OUT     TYPES.CURSOR_TYPE
)
AS
V_SYSDATE  DATE:=SYSDATE;
V_BEGINTIME       TBL_EM_USR_ABSENT.BEGINTIME%TYPE;
V_ENDTIME       TBL_EM_USR_ABSENT.ENDTIME%TYPE;
V_DT_CREATE       TBL_EM_SYS_CONFIG.DT_CREATED%TYPE;
V_VALUE        TBL_EM_SYS_CONFIG.VALUE%TYPE;
V_CD_U        TBL_EM_BANK_USR_USR_GRP.CD%TYPE;
V_CD_UG        TBL_EM_BANK_USR_USR_GRP.CD%TYPE;

BEGIN

-- Query VALIDATE time

SELECT UA.BEGINTIME,UA.ENDTIME INTO V_BEGINTIME,V_ENDTIME
   FROM TBL_EM_USR_ABSENT UA
    WHERE UA.USR_CD=USR_CD_IN
       FOR UPDATE NOWAIT;

SELECT SC.DT_CREATED,SC.VALUE INTO V_DT_CREATE,V_VALUE
   FROM TBL_EM_SYS_CONFIG SC
   WHERE SC.CD=CD_IN
      FOR UPDATE NOWAIT;

IF V_ENDTIME>=V_SYSDATE AND V_SYSDATE>=V_BEGINTIME AND (V_DT_CREATE+V_VALUE)>=V_SYSDATE THEN

OPEN REF_CURSOR FOR
SELECT T_EM_BANK_USR.CD
     FROM
     (
      SELECT BUUG.CD,
          BUUG.BAK_USR_CD,
       BUUG.GRP_CD,
       BUUG.VERSION
           FROM TBL_EM_BANK_USR BU LEFT OUTER JOIN TBL_EM_BANK_USR_USR_GRP BUUG
        ON BU.CD=BUUG.GRP_CD
      WHERE BU.NM=NM_IN AND BU.PWD=PWD_IN
          AND BU.IS_DEL=0 AND BU.IS_LOCK=0
   ) T_EM_BANK_USR

    INNER JOIN

   (
     SELECT BUUG2.CD,
         BUUG2.BAK_USR_CD,
      BUUG2.GRP_CD,
      BUUG2.VERSION
     FROM TBL_EM_BANK_USER_GRP BUG LEFT OUTER JOIN TBL_EM_BANK_USR_USR_GRP BUUG2
          ON BUG.CD=BUUG2.BAK_USR_CD
         WHERE BUG.APP_CD=APP_CD_IN
   ) T_EM_BANK_USER_GRP
     ON T_EM_BANK_USR.CD= T_EM_BANK_USER_GRP.CD;

ELSE
ERR.RAISE_ERR(ERR.NO_ROW_DB);
END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN
ERR.RAISE_ERR(ERR.NO_ROW_DELETED);

WHEN OTHERS THEN
RAISE;

END;

和

CREATE OR REPLACE PROCEDURE P_EM_BANK_USR_S#ID
/*
COPYRIGHT(C) 2006 compay name
FIRST CREATION (Tang Liquan 20060608)
*/
(
NM_IN        IN     TBL_EM_BANK_USR.NM%TYPE,
PWD_IN    IN    TBL_EM_BANK_USR.PWD%TYPE,
APP_CD_IN   IN    VARCHAR2,
USR_CD_IN   IN    TBL_EM_USR_ABSENT.USR_CD%TYPE,
CD_IN    IN    TBL_EM_SYS_CONFIG.CD%TYPE,
    REF_CURSOR          OUT     TYPES.CURSOR_TYPE
)
AS
V_SYSDATE  DATE:=SYSDATE;
V_BEGINTIME       TBL_EM_USR_ABSENT.BEGINTIME%TYPE;
V_ENDTIME       TBL_EM_USR_ABSENT.ENDTIME%TYPE;
V_DT_CREATE       TBL_EM_SYS_CONFIG.DT_CREATED%TYPE;
V_VALUE        TBL_EM_SYS_CONFIG.VALUE%TYPE;
V_CD_U        TBL_EM_BANK_USR_USR_GRP.CD%TYPE;
V_CD_UG        TBL_EM_BANK_USR_USR_GRP.CD%TYPE;

BEGIN

SELECT BUUG.CD INTO V_CD_U
     FROM TBL_EM_BANK_USR BU LEFT OUTER JOIN TBL_EM_BANK_USR_USR_GRP BUUG
        ON BU.CD=BUUG.GRP_CD
         WHERE BU.NM=NM_IN AND BU.PWD=PWD_IN
       FOR UPDATE NOWAIT;

SELECT BUG.CD INTO V_CD_UG
     FROM TBL_EM_BANK_USER_GRP BUG LEFT OUTER JOIN TBL_EM_BANK_USR_USR_GRP BUUG
        ON BUG.CD=BUUG.BAK_USR_CD
         WHERE BUG.APP_CD=APP_CD_IN
       FOR UPDATE NOWAIT;

IF V_CD_U=V_CD_UG THEN
-- Query VALIDATE time

SELECT UA.BEGINTIME,UA.ENDTIME INTO V_BEGINTIME,V_ENDTIME
   FROM TBL_EM_USR_ABSENT UA
    WHERE UA.USR_CD=USR_CD_IN
       FOR UPDATE NOWAIT;

SELECT SC.DT_CREATED,SC.VALUE INTO V_DT_CREATE,V_VALUE
   FROM TBL_EM_SYS_CONFIG SC
   WHERE SC.CD=CD_IN
      FOR UPDATE NOWAIT;



IF V_ENDTIME>V_SYSDATE AND V_SYSDATE>V_BEGINTIME AND V_SYSDATE <(V_DT_CREATE+V_VALUE) THEN