sql - PL/SQL : Encountered the symbol "END"

Question

即使我仔细检查了代码，我还是收到以下错误。我不知道我错过了什么。

行/列错误

---

31/1 PLS-00103:在期待其中一个时遇到符号“END” 以下:

代码:

CREATE OR REPLACE PROCEDURE sp_ssjm_newworkorder
(   workorderno IN NUMBER,
company     IN CHAR,
attention   IN CHAR,
datedue     IN DATE,
loggedby    IN CHAR
)

AS  id     NUMBER;
today         DATE:=SYSDATE;
BEGIN
SELECT  client_id   --grab client_id
INTO id 
FROM    ssjm_client
WHERE ssjm_client.name=company;

IF id IS NULL THEN  --check if client exists by checking if client_id is there
    dbms_output.put_line('Please create client first');
    GOTO the_end;
ELSE
    INSERT INTO ssjm_workorder      VALUES(workorderno,workorderno,company,loggedby,attention,'Received',today,datedue,id);
END IF;

EXCEPTION
    WHEN OTHERS THEN
    raise_application_error(-20999,'An error occured in' ||
        SQLCODE || '-ERROR-' || SQLERRM);

<<the_end>>

END sp_ssjm_newworkorder;

Answer 1

您的代码中有几个地方需要注意:

1. 出现该错误的原因是标签 <<the_end>>应该放在 EXCEPTION 之前部分。
2. 标签后需要运算符。因此，如果您想跳转到存储过程的末尾并且不需要其他操作 NULL应该使用运算符。

为此，您的代码应如下所示:

IF id IS NULL THEN  --check if client exists by checking if client_id is there
    dbms_output.put_line('Please create client first');
    GOTO the_end;
ELSE
    INSERT INTO ssjm_workorder
 VALUES(workorderno,workorderno,company,loggedby
       ,attention,'Received',today,datedue,id);
END IF;

<<the_end>>
NULL;

EXCEPTION
    WHEN OTHERS THEN
    raise_application_error(-20999,'An error occured in' ||
        SQLCODE || '-ERROR-' || SQLERRM);
END sp_ssjm_newworkorder;

一定要尽量避免无条件分支。使用 GOTO运算符是非常非常不好的做法。它扼杀了可读性，这样的代码很难调试。这会让您和在您之后查看该代码的每个人都头疼。此外，如果查询

SELECT  client_id   --grab client_id
INTO id 
FROM    ssjm_client
WHERE ssjm_client.name=company;

不返回异常行 NO_DATA_FOUND将立即引发并停止执行代码。所以IF id IS NULL THEN永远不会评估条件。您可以通过删除该条件并添加 NO_DATA_FOUND 来重写您的代码EXCEPTION 中的异常处理程序你的代码部分。当然，正如@Rob van Wijk 在评论中正确指出的那样

but code can be cleaned up further. today variable can be removed and the WHEN OTHERS should definitely be removed. As it is now, it just transforms an error to a longer error message without more detail and most importantly: it disguises the line number where the real error took place.

不需要 today变量，SYSDATE可以直接在values中使用insert 的条款声明，和 WHEN OTHERS也可以删除。

CREATE OR REPLACE PROCEDURE sp_ssjm_newworkorder
(
workorderno IN NUMBER,
company     IN CHAR,
attention   IN CHAR,
datedue     IN DATE,
loggedby    IN CHAR
)
AS  
  id     NUMBER;
BEGIN
  SELECT client_id   --grab client_id
    INTO id
    FROM ssjm_client
   WHERE ssjm_client.name=company;

    INSERT INTO ssjm_workorder      
      VALUES(workorderno,workorderno,company,loggedby
            ,attention,'Received',SYSDATE,datedue,id);

EXCEPTION
    when NO_DATA_FOUND
    then dbms_output.put_line('Please create client first');
END sp_ssjm_newworkorder;