在存储过程中遇到符号“异常”错误

Question

我正在Oracle数据库12c企业版发布版12.1.0.2.0-64位产品中编写一个过程。

循环中有一个异常，因为如果抛出异常，我不希望该过程退出该循环。

create or replace procedure PARSE_REGISTER_MESSAGE
  IS

        HOTELS_TO_PROCESS number := 5000;     

        cursor unparsed_messages is

         SELECT REGISTER_psd_id, message

         FROM
            ( SELECT REGISTER_psd_id, message
              FROM cc_owner.REGISTER_psd
              WHERE parsed != 1
                    OR parsed IS NULL
              ORDER BY CREATION_DATE DESC)

         WHERE rownum < HOTELS_TO_PROCESS;

    BEGIN

     FOR psd_rec in unparsed_messages
     LOOP

p_msg.parse_msg (psd_rec.REGISTER_psd_id, null, psd_rec.message);

         EXCEPTION

        WHEN OTHERS
        THEN 
        DECLARE
            l_code   INTEGER := SQLCODE;
        BEGIN

            of_owner.p_db_trc.add_error
                 ( 'PARSE_REGISTER_MESSAGE','', 
                    l_code, 
                    sys.DBMS_UTILITY.format_error_stack, 
                    sys.DBMS_UTILITY.format_error_backtrace, 
                    sys.DBMS_UTILITY.format_call_stack ); 

        END;

    END LOOP;

END;

但是，由于以下错误，我无法编译该包：

错误(25，10)：请-00103:在期望下列情况之一时遇到符号“异常”： ( begin声明结束退出为goto循环mod空实用化提高返回选择更新，而使用<<继续关闭当前删除获取锁插入打开回滚保存点设置sql提交用于所有合并管道清除

我也试过：

create or replace procedure PARSE_REGISTER_MESSAGE
  IS

        HOTELS_TO_PROCESS number := 5000;     

        cursor unparsed_messages is

         SELECT REGISTER_psd_id, message

         FROM
            ( SELECT REGISTER_psd_id, message
              FROM cc_owner.REGISTER_psd
              WHERE parsed != 1
                    OR parsed IS NULL
              ORDER BY CREATION_DATE DESC)

         WHERE rownum < HOTELS_TO_PROCESS;

         psd_rec unparsed_messages%ROWTYPE;

    BEGIN

     FOR psd_rec in unparsed_messages
     LOOP
        BEGIN

          p_msg.parse_msg (psd_rec.REGISTER_psd_id, null, psd_rec.message);

         EXCEPTION

        WHEN OTHERS
        THEN 
        DECLARE
            l_code   INTEGER := SQLCODE;
        BEGIN

            of_owner.p_db_trc.add_error
                 ( 'PARSE_REGISTER_MESSAGE','', 
                    l_code, 
                    sys.DBMS_UTILITY.format_error_stack, 
                    sys.DBMS_UTILITY.format_error_backtrace, 
                    sys.DBMS_UTILITY.format_call_stack ); 

        END;

    END LOOP;

END;

但是我得到了一个错误：

Error(48,4): PLS-00103: Encountered the symbol ";" when expecting one of the following:     loop 

Answer 1

PLSQL block / procedure的语法是：

DECLARE
 -- Here you declare all the varaible used in block
BEGIN
 -- Here you write the body of the Block
EXCEPTION
 -- Here you write the exceptions which you want to handle.
END;

现在，当我查看您的代码时，您已经在Exception中编写了FOR LOOP块，只有使用上面的语法，它才能工作。在您的情况下，Exception块的作用域不是由Oracle标识的，因此它会引发错误。

 FOR psd_rec IN unparsed_messages
   LOOP
      p_msg.parse_msg (psd_rec.REGISTER_psd_id, NULL, psd_rec.MESSAGE);         

      EXCEPTION  --<-- Wrong way of using Excepton block. Scope of this Exception block is not resolved
        WHEN OTHERS
        THEN
        DECLARE
            l_code   INTEGER := SQLCODE;
        BEGIN
            of_owner.p_db_trc.add_error
                 ( 'PARSE_REGISTER_MESSAGE','',
                    l_code,
                    sys.DBMS_UTILITY.format_error_stack,
                    sys.DBMS_UTILITY.format_error_backtrace,
                    sys.DBMS_UTILITY.format_call_stack );

        END;

您必须修改代码如下所示，以将Exception块包含在for循环中；

  CREATE OR REPLACE PROCEDURE PARSE_REGISTER_MESSAGE
    IS
       HOTELS_TO_PROCESS   NUMBER := 5000;
       l_code              INTEGER := SQLCODE;

       CURSOR unparsed_messages
       IS
          SELECT REGISTER_psd_id, MESSAGE
            FROM (  SELECT REGISTER_psd_id, MESSAGE
                      FROM cc_owner.REGISTER_psd
                     WHERE parsed != 1 OR parsed IS NULL
                  ORDER BY CREATION_DATE DESC)
           WHERE ROWNUM < HOTELS_TO_PROCESS;
    BEGIN
       FOR psd_rec IN unparsed_messages
       LOOP
          BEGIN
             p_msg.parse_msg (psd_rec.REGISTER_psd_id, NULL, psd_rec.MESSAGE);
          EXCEPTION
             WHEN OTHERS
             THEN
                of_owner.p_db_trc.add_error (
                   'PARSE_REGISTER_MESSAGE',
                   '',
                   l_code,
                   sys.DBMS_UTILITY.format_error_stack,
                   sys.DBMS_UTILITY.format_error_backtrace,
                   sys.DBMS_UTILITY.format_call_stack);
          END;
       END LOOP;
 EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);    
END;

您第二次尝试丢失了END语句，这就是您出错的原因。见下文：

CREATE OR REPLACE PROCEDURE PARSE_REGISTER_MESSAGE
IS
   HOTELS_TO_PROCESS   NUMBER := 5000;
   l_code              INTEGER := SQLCODE;

   CURSOR unparsed_messages
   IS
      SELECT REGISTER_psd_id, MESSAGE
        FROM (  SELECT REGISTER_psd_id, MESSAGE
                  FROM cc_owner.REGISTER_psd
                 WHERE parsed != 1 OR parsed IS NULL
              ORDER BY CREATION_DATE DESC)
       WHERE ROWNUM < HOTELS_TO_PROCESS;

   psd_rec             unparsed_messages%ROWTYPE;
BEGIN
   FOR psd_rec IN unparsed_messages
   LOOP
      BEGIN
         p_msg.parse_msg (psd_rec.REGISTER_psd_id, NULL, psd_rec.MESSAGE);
      EXCEPTION
         WHEN OTHERS
         THEN
            BEGIN
               of_owner.p_db_trc.add_error (
                  'PARSE_REGISTER_MESSAGE',
                  '',
                  l_code,
                  sys.DBMS_UTILITY.format_error_stack,
                  sys.DBMS_UTILITY.format_error_backtrace,
                  sys.DBMS_UTILITY.format_call_stack);
            END;
      END;
   END LOOP;
END;

Answer 2

尝试使用Begin后循环关键字，因为缺少一个BEGIN

对于psd_rec在unparsed_messages循环中开始p_msg.parse_msg (psd_rec.REGISTER_psd_id，null，psd_rec.message)；

     EXCEPTION

    WHEN OTHERS
    THEN 
    DECLARE
        l_code   INTEGER := SQLCODE;
    BEGIN

        of_owner.p_db_trc.add_error
             ( 'PARSE_REGISTER_MESSAGE','', 
                l_code, 
                sys.DBMS_UTILITY.format_error_stack, 
                sys.DBMS_UTILITY.format_error_backtrace, 
                sys.DBMS_UTILITY.format_call_stack ); 

    END;