时间戳

首页 > 解决方案 > PLS-00103 遇到符号“END”

sql - PLS-00103 遇到符号“END”

问题描述

我遇到了问题 PL-00103：在以下过程的倒数第二行遇到符号“END”。该过程的目的是在发送新电子邮件时查看电子邮件的先前版本是否存在......并使旧电子邮件过期。（电子邮件在创建时默认设置为较远的到期日期，因此我检查到期日期是否比当前日期更早）。 

PROCEDURE EXPIRE_STUFF_PRC
(
    PI_EMAIL_NBR_STR                 IN  VARCHAR2,
    PO_SUCCESS_FLG                  OUT VARCHAR2, 
    PO_OUT_MSG                      OUT VARCHAR2
) AS 
L_SUCCESS                   VARCHAR2(1) := 'N';
L_EMAIL_ID              NUMBER;
L_PREV_EMAIL_VER_ID     NUMBER := 0;
L_PREV_EMAIL_EXP_DT      DATE;
BEGIN
    BEGIN
    SELECT
        ITEM.EMAIL_ID
        INTO L_EMAIL_ID
    FROM HR_EMAIL        ITEM
    WHERE ITEM.EMAIL_NBR_STR = PI_EMAIL_NBR_STR;
END;

BEGIN
    SELECT
        VER_ID.EMAIL_VER_ID
        INTO L_PREV_EMAIL_VER_ID        
    FROM (
        SELECT
            EMAIL_VER_ID
            FROM HR_EMAIL_VER
            WHERE EMAIL_ID = L_EMAIL_ID
            ORDER BY EMAIL_VER_ID DESC
    ) VER_ID
    WHERE ROWNUM = 2; 
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN PO_SUCCESS_FLG := 'Y'; 
        PO_OUT_MSG := 'LESS THAN 2 VERSIONS'; 
    END;
END;
BEGIN
    IF (L_PREV_EMAIL_VER_ID > 0) THEN 
        SELECT
            VER.EXP_DT
            INTO L_PREV_EMAIL_EXP_DT
        FROM HR_EMAIL_VER            VER
        WHERE VER.EMAIL_VER_ID = L_PREV_EMAIL_VER_ID;
        IF (L_PREV_EMAIL_EXP_DT > SYSDATE) THEN
            UPDATE HR_EMAIL_VER
            SET EXP_DT = SYSDATE
            WHERE EMAIL_VER_ID = L_PREV_EMAIL_VER_ID;
        END IF;

        PO_SUCCESS_FLG := 'Y';
        PO_OUT_MSG := 'SUCCESS';

    END IF;          
END;
END;
END EXPIRE_STUFF_PRC;

我试图绕过 END 和 BEGIN 无济于事......

由于保密协议，我已经更改了东西的名称，所以如果有任何混淆，我很乐意澄清，因为我可能错过了一些东西。

预先感谢您的任何帮助！

标签： sqloracleplsql

解决方案

编写存储过程的理想方法是处理每个块/语句的所有异常。你有一个额外的 END 必须被删除，另外我添加了当第一个和最后一个块进入任何异常时要返回的消息。块在某种意义上是逻辑上分组的DML操作，它可以只是单个选择或一组DML操作。根据业务逻辑做出决定。

PROCEDURE EXPIRE_STUFF_PRC
(
    PI_EMAIL_NBR_STR                 IN  VARCHAR2,
    PO_SUCCESS_FLG                  OUT VARCHAR2, 
    PO_OUT_MSG                      OUT VARCHAR2
) AS 
L_SUCCESS                   VARCHAR2(1) := 'N';
L_EMAIL_ID              NUMBER;
L_PREV_EMAIL_VER_ID     NUMBER := 0;
L_PREV_EMAIL_EXP_DT      DATE;

BEGIN
    BEGIN
    SELECT
        ITEM.EMAIL_ID
        INTO L_EMAIL_ID
    FROM HR_EMAIL        ITEM
    WHERE ITEM.EMAIL_NBR_STR = PI_EMAIL_NBR_STR;

    EXCEPTION WHEN OTHERS THEN 
    PO_SUCCESS_FLG := 'Y'; 
    PO_OUT_MSG := 'COULD NOT FETCH EMAIL_ID'; 
   END;

BEGIN
    SELECT
        VER_ID.EMAIL_VER_ID
        INTO L_PREV_EMAIL_VER_ID        
    FROM (
        SELECT
            EMAIL_VER_ID
            FROM HR_EMAIL_VER
            WHERE EMAIL_ID = L_EMAIL_ID
            ORDER BY EMAIL_VER_ID DESC
    ) VER_ID
    WHERE ROWNUM = 2; 
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN PO_SUCCESS_FLG := 'Y'; 
        PO_OUT_MSG := 'LESS THAN 2 VERSIONS'; 
    END;

BEGIN
    IF (L_PREV_EMAIL_VER_ID > 0) THEN 
        SELECT
            VER.EXP_DT
            INTO L_PREV_EMAIL_EXP_DT
        FROM HR_EMAIL_VER            VER
        WHERE VER.EMAIL_VER_ID = L_PREV_EMAIL_VER_ID;
        IF (L_PREV_EMAIL_EXP_DT > SYSDATE) THEN
            UPDATE HR_EMAIL_VER
            SET EXP_DT = SYSDATE
            WHERE EMAIL_VER_ID = L_PREV_EMAIL_VER_ID;
        END IF;

        PO_SUCCESS_FLG := 'Y';
        PO_OUT_MSG := 'SUCCESS';

    END IF;    
    EXCEPTION 
        WHEN OTHERS THEN 
        PO_SUCCESS_FLG := 'Y';
        PO_OUT_MSG := 'UNABLE TO UPDATE HR_EMAIL_VER';   
END;
END EXPIRE_STUFF_PRC;

推荐阅读