sql - 一些语法错误 - 不知道在哪里

Question

这是我编写的脚本，它在 EXCEPTION 块中有奇怪的语法错误。如果我删除异常块，脚本将正确编译。但我刚写回它就给我错误

Error(58,11): PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following:     ( begin case declare else elsif end exit for goto if loop mod    null pragma raise return select update while with    <an identifier> <a double-quoted delimited-identifier>    <a bind variable> << continue close current delete fetch lock    insert open rollback savepoint set sql execute commit forall    merge pipe purge 

这是脚本

LOOP
  BEGIN
    SAVEPOINT check_point;

    EXIT WHEN DBMS_SQL.FETCH_ROWS (cursor_handle) = 0;
    DBMS_SQL.COLUMN_VALUE (cursor_handle, 1,  cc , col_err, actual_len);
    DBMS_SQL.COLUMN_VALUE (cursor_handle, 2,  di, col_err, actual_len);

    IF INSTR (cc, '_') <> 0 THEN
      cc := Trim (cc);
      cc := Upper(cc);
      cc := substr(cc,4,2);

      EXECUTE IMMEDIATE 'UPDATE  ' || dest || ' SET cc = :v1 WHERE di = :v2' 
        USING cc, di;

      if SQL%ROWCOUNT > 0 THEN
        inserts := inserts + 1;
        counter := counter + 1;
        IF counter > 500 THEN
          counter := 0;
          COMMIT;
        END IF;
      END IF;

      EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
          dups := dups+1;
          ROLLBACK TO check_point;
        WHEN VALUE_ERROR THEN
          valerr := valerr +1;
          ROLLBACK TO check_point;
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('errno: ' || TO_CHAR(SQLCODE) || ' Msg: ' || SQLERRM);
          otherexc := otherexc +1;
        IF otherexc > 50 THEN 
          EXIT;
        END IF;
        ROLLBACK TO check_point;              
    END IF;
  END;
END LOOP;

我知道问这样的问题很烦人，但我无法弄清楚那是什么错误。我是 Pl/SQL 的雷曼兄弟。

Answer 1

错误似乎是您的 EXCEPTION 子句在 IF INSTR (cc, '_') <> 0 内IF 语句，但您似乎希望将 EXCEPTION 与循环顶部的 BEGIN 语句相匹配。我相信你想移动END IF;为 IF INSTR (cc, '_') <> 0在我在这里做的异常(exception)之前

LOOP
  BEGIN
    SAVEPOINT check_point;

    EXIT WHEN DBMS_SQL.FETCH_ROWS (cursor_handle) = 0;
    DBMS_SQL.COLUMN_VALUE (cursor_handle, 1,  cc , col_err, actual_len);
    DBMS_SQL.COLUMN_VALUE (cursor_handle, 2,  di, col_err, actual_len);

    IF INSTR (cc, '_') <> 0 THEN
      cc := Trim (cc);
      cc := Upper(cc);
      cc := substr(cc,4,2);

      EXECUTE IMMEDIATE 'UPDATE  ' || dest || ' SET cc = :v1 WHERE di = :v2' 
        USING cc, di;

      if SQL%ROWCOUNT > 0 THEN
        inserts := inserts + 1;
        counter := counter + 1;
        IF counter > 500 THEN
          counter := 0;
          COMMIT;
        END IF; -- IF counter > 500
      END IF; -- IF SQL%ROWCOUNT > 0
    END IF; -- INSTR (cc, '_') <> 0


  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      dups := dups+1;
      ROLLBACK TO check_point;
    WHEN VALUE_ERROR THEN
      valerr := valerr +1;
      ROLLBACK TO check_point;
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('errno: ' || TO_CHAR(SQLCODE) || ' Msg: ' || SQLERRM);
      otherexc := otherexc +1;
      IF otherexc > 50 THEN 
        EXIT;
      END IF;
      ROLLBACK TO check_point;              
  END;
END LOOP;

然而，话虽如此，我可能会稍微重写一下代码。每 500 行提交一次几乎肯定是一个错误。我非常怀疑您的 WHEN OTHERS 异常处理程序——我真的认为您至少希望将错误写入表或填充错误集合，而不是写入可能永远也可能永远不会的 DBMS_OUTPUT 缓冲区被显示。

LOOP
  SAVEPOINT check_point;

  EXIT WHEN DBMS_SQL.FETCH_ROWS (cursor_handle) = 0;
  DBMS_SQL.COLUMN_VALUE (cursor_handle, 1,  cc , col_err, actual_len);
  DBMS_SQL.COLUMN_VALUE (cursor_handle, 2,  di, col_err, actual_len);

  IF INSTR (cc, '_') <> 0 THEN
    cc := Trim (cc);
    cc := Upper(cc);
    cc := substr(cc,4,2);

    BEGIN
      EXECUTE IMMEDIATE 'UPDATE  ' || dest || ' SET cc = :v1 WHERE di = :v2' 
        USING cc, di;

      if SQL%ROWCOUNT > 0 THEN
        inserts := inserts + 1;
      END IF; 
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        dups := dups+1;
        ROLLBACK TO check_point;
      WHEN VALUE_ERROR THEN
        valerr := valerr +1;
        ROLLBACK TO check_point;
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('errno: ' || TO_CHAR(SQLCODE) || ' Msg: ' || SQLERRM);
        otherexc := otherexc +1;
        IF otherexc > 50 THEN 
          EXIT;
        END IF;
        ROLLBACK TO check_point;              
    END;

  END IF; -- INSTR (cc, '_') <> 0
END LOOP;