PLS 00103遇到符号"END“
PLS 00103遇到符号"END“
提问于 2020-05-15 05:05:26
我遇到了问题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;我试着在两端来回移动，但开始没有效果...
由于保密协议，我已经更改了东西的名称，所以如果有任何混淆，我很乐意澄清，因为我可能遗漏了一些东西。
提前感谢您的帮助！
回答 2
Stack Overflow用户
回答已采纳
发布于 2020-05-15 05:50:52
您在代码中使用了不必要的BEGIN/END。如果您想要在特定部分中处理exceptions，则可能需要这样做。格式化和检查语法的最好方法是使用一个很好的代码编辑器。
我已经删除了所有不需要的BEGIN/END，这段代码应该可以很好地编译：
CREATE OR REPLACE 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
SELECT
item.email_id
INTO l_email_id
FROM
hr_email item
WHERE
item.email_nbr_str = pi_email_nbr_str;
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;
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 expire_stuff_prc;
/Stack Overflow用户
发布于 2020-05-15 05:46:39
编写存储过程的理想方法是处理每个块/语句的所有异常。你有一个额外的末端必须被移除，另外，我已经添加了当第一个和最后一个块进入任何异常时返回的消息。operation.Decision在某种意义上是按逻辑分组的DML操作，它可以是根据业务逻辑进行的单个选择或一组DML Block。
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;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/61807367
复制相关文章
相似问题
腾讯云开发者
