oracle 应输入以下内容之一时遇到符号“SELECT”：

u91tlkcl 于 2022-12-18 发布在 Oracle

关注(0)|答案(2)|浏览(257)

我需要你们的帮助。我正在使用oracle apex for university，我的任务是创建触发器。我很难创建它们，我甚至不知道这个触发器有什么问题。有人能帮助我吗？
我收到错误

Encountered the symbol "SELECT" when expecting one of the following: ( - + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string> pipe <an alternatively-quoted string literal with character set specification> <an alternat
Error at line 3: PLS-00049: bad bind variable 'NEW'
Error at line 3: PLS-00103: Encountered the symbol "" when expecting one of the following: . ( * @ % & = - + ; < / > at for in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between group having intersect minus order start union where connect || indicator multiset member submultiset
Error at line 3: PLS-00049: bad bind variable 'CONTRACT' Error at line 3: PLS-00049: bad bind variable 'CONTRACT'   -

CREATE OR REPLACE TRIGGER restrict_orders
    BEFORE INSERT ON orders
        FOR EACH ROW
        BEGIN
        DECLARE countOrders number;
        set countOrders := SELECT count(contract) FROM orders WHERE :new:contract := old:contract AND (:old.status := 'PREPARING' OR :old.status := 'IN PROGRESS');
            IF (countOrders :> 3)
            THEN
                RAISE_APPLICATION_ERROR (-20202, 'You have too much active orders.');
            END IF;
    END;

oracle

来源：https://stackoverflow.com/questions/74735728/encountered-the-symbol-select-when-expecting-one-of-the-following

2条答案

按热度按时间

icnyk63a1#

相当多的错误;看看这个有没有帮助。
样表：

SQL> select * From orders;

  CONTRACT STATUS
---------- ---------
         1 PREPARING

触发器：

SQL> CREATE OR REPLACE TRIGGER restrict_orders
  2    BEFORE INSERT ON orders
  3    FOR EACH ROW
  4  DECLARE
  5    countOrders number;
  6  BEGIN
  7    SELECT count(contract)
  8      INTO countOrders
  9      FROM orders
 10      WHERE contract = :new.contract
 11        AND status IN ('PREPARING', 'IN PROGRESS');
 12
 13    IF countOrders > 3 THEN
 14       RAISE_APPLICATION_ERROR (-20202, 'You have too many active orders.');
 15    END IF;
 16  END;
 17  /

Trigger created.

测试：

SQL> insert into orders (contract, status) values (1, 'PREPARING');

1 row created.

SQL> insert into orders (contract, status) values (1, 'PREPARING');

1 row created.

SQL> insert into orders (contract, status) values (1, 'PREPARING');

1 row created.

SQL> insert into orders (contract, status) values (1, 'PREPARING');
insert into orders (contract, status) values (1, 'PREPARING')
*
ERROR at line 1:
ORA-20202: You have too many active orders.
ORA-06512: at "SCOTT.RESTRICT_ORDERS", line 11
ORA-04088: error during execution of trigger 'SCOTT.RESTRICT_ORDERS'

型
Kind of works.然而，注意如果你试图一次插入多行，它会失败，因为table会发生 mutating（所以你必须采取另一种更复杂的方法），但是-如果你一次只插入一行，你就可以了：

SQL> insert into orders (contract, status)
  2  select 1, 'PREPARING'   from dual union all
  3  select 2, 'IN PROGRESS' from dual;
insert into orders (contract, status)
            *
ERROR at line 1:
ORA-04091: table SCOTT.ORDERS is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.RESTRICT_ORDERS", line 4
ORA-04088: error during execution of trigger 'SCOTT.RESTRICT_ORDERS'

SQL>

赞(0）回复(0）举报 2022-12-18

uyhoqukh2#

你需要更正你的语法应该是-

CREATE OR REPLACE TRIGGER restrict_orders
BEFORE INSERT ON orders
FOR EACH ROW
DECLARE countOrders number;
BEGIN
     SELECT count(contract)
       INTO countOrders
       FROM orders
      WHERE :new.contract = :old.contract 
        AND :old.status IN ('PREPARING', 'IN PROGRESS');

     IF (countOrders >= 3) THEN
        RAISE_APPLICATION_ERROR (-20202, 'You have too much active orders.');
     END IF;
END;

赞(0）回复(0）举报 2022-12-18

我来回答

oracle 应输入以下内容之一时遇到符号“SELECT”：

2条答案

相关问题

热门标签

最新问答