PL/SQL：遇到符号“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. 出现该错误的原因是因为label

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

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;

尽可能避免无条件分支。使用“转到”操作符是非常非常不好的做法。它破坏了可读性，这样的代码很难调试。这将使您和所有在您之后查看该代码的人感到头痛。此外，如果查询

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

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

但代码可以进一步清理。今天可以删除变量，其他变量也可以删除。正如现在一样，它只是将错误转换为更长的错误消息，没有更多的细节，而且最重要的是：它掩盖了真正错误发生的行号。

今天不需要变量，SYSDATE可以直接在插入语句的值子句中使用，也可以在删除其他变量时使用。

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;