Oracle Pl/SQL 通过 SQL*PLUS 触发编译错误

Question

我在通过 SQL*PLUS 编译 Oracle 触发器时遇到问题 - 我不认为我很笨，但我看不出问题是什么。

我们有一个安装程序脚本，它本质上是一个批处理文件，它通过在多个脚本上调用 SQLPLUS 来创建/刷新数据库中的所有对象，每个脚本包含一个 View 、触发器等。首先创建表和 View ，然后是触发器。 V_BS_GRIDFIELDS下面的 View 可能会或可能不会在此时创建，或者可能稍后由不同的过程创建。该 View 是一个可更新的 View ，因此我们在其上放置了一个触发器来将更新推送到不同的表，如下所示:

CREATE OR REPLACE FORCE TRIGGER TR_INSTUPD_BS
  INSTEAD OF INSERT OR UPDATE OR DELETE 
  ON V_BS_GRIDFIELDS
FOR EACH ROW
BEGIN

  IF INSERTING OR DELETING THEN
    NULL;
  END IF;

  IF UPDATING THEN
    -- Can only change these fields
    IF (:OLD.VISIBLE <> :NEW.VISIBLE) OR (:OLD.COMPULSORY <> :NEW.COMPULSORY) THEN 

      -- Source Table = BS_GRIDFIELDS
      IF (:OLD.SOURCE_TYPE = 0) THEN

        UPDATE BS_GRIDFIELDS BS_GF
           SET BS_GF.VISIBLE    = :NEW.VISIBLE,
               BS_GF.COMPULSORY = :NEW.COMPULSORY
         WHERE BS_GF.FIELD_NAME = :OLD.FIELD_NAME;

      END IF;
    END IF;
  END IF;
END;

问题是 oracle SQL*PLUS 似乎在第 6 行第一个空行之后停止编译触发器:

SQL> @"TR_INSTUPD_BS.sql";
SP2-0734: unknown command beginning "IF INSERTI..." - rest of line ignored.
SP2-0042: unknown command "NULL" - rest of line ignored.
SP2-0042: unknown command "END IF" - rest of line ignored.
SP2-0734: unknown command beginning "IF UPDATIN..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "IF (:OLD.V..." - rest of line ignored.
SP2-0734: unknown command beginning "IF (:OLD.S..." - rest of line ignored.
SP2-0552: Bind variable "OLD" not declared.
SP2-0042: unknown command "END IF" - rest of line ignored.
SP2-0042: unknown command "END IF" - rest of line ignored.
SP2-0042: unknown command "END IF" - rest of line ignored.
SP2-0042: unknown command "END" - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.

如果删除第 6 行的空行，它似乎在第 7 行的第一个分号处停止编译:

SQL> @"TR_INSTUPD_BS.sql";

Warning: Trigger created with compilation errors.

SP2-0042: unknown command "END IF" - rest of line ignored.
SP2-0734: unknown command beginning "IF UPDATIN..." - rest of line ignored.
SP2-0734: unknown command beginning "IF (:OLD.V..." - rest of line ignored.
SP2-0734: unknown command beginning "IF (:OLD.S..." - rest of line ignored.
SP2-0552: Bind variable "OLD" not declared.
SP2-0042: unknown command "END IF" - rest of line ignored.
SP2-0042: unknown command "END IF" - rest of line ignored.
SP2-0042: unknown command "END IF" - rest of line ignored.
SP2-0042: unknown command "END" - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL>

我们有很多以这种方式创建的触发器，并且它们都有空格、分号等，并且可以正常创建。我已经在 Oracle 9、10、11 上测试并看到了同样的问题。有人能解释一下吗？

谢谢。

Answer 1

在默认设置中，SQL*Plus 不会正确处理空行，您需要发出以下命令:

SQL> SET SQLBLANKLINES on

见 this other SO .

更新:我回答得太快了，空行似乎不是这里的问题。我在我的数据库上尝试了你的代码，问题似乎来自 FORCE关键词。 10gR2 documentation没有提到这个关键字。当您删除触发器时，它会编译。