sql - 插入和更新后的 PL SQL 循环表

Question

我正在尝试在插入另一个名为“约会”的表后循环遍历一个名为“经纪人”的表，并更新经纪人表上的值，这是我所拥有的。当我尝试创建触发器时，出现错误“使用编译错误创建触发器”

CREATE OR REPLACE TRIGGER broker_level_trigger
AFTER INSERT ON appointment

DECLARE
    counter integer := 1;

BEGIN
for o in (SELECT * FROM broker)
loop
    SELECT COUNT(appointment.broker_id) INTO app_number FROM appointment INNER JOIN broker ON broker.broker_id = appointment.broker_id WHERE broker.broker_id = counter;
    IF app_number > 15 THEN
    UPDATE broker SET broker_level = 'gold' WHERE broker_id = counter;
    counter := counter + 1;
end loop;

end;
/ 

经纪人表有一个名为broker_level的字段，它根据约会而变化，我希望它在约会上的broker_id字段超过15时发生变化

Answer 1

show err 是一件很棒的事情。 (我创建了您使用的虚拟表)。

SQL> CREATE OR REPLACE TRIGGER broker_level_trigger
  2    AFTER INSERT ON appointment
  3  DECLARE
  4    counter integer := 1;
  5  BEGIN
  6    for o in (SELECT * FROM broker)
  7    loop
  8      SELECT COUNT(appointment.broker_id)
  9        INTO app_number
 10        FROM appointment INNER JOIN broker
 11          ON broker.broker_id = appointment.broker_id
 12        WHERE broker.broker_id = counter;
 13
 14      IF app_number > 15 THEN
 15         UPDATE broker SET
 16           broker_level = 'gold'
 17           WHERE broker_id = counter;
 18         counter := counter + 1;
 19
 20    end loop;
 21  end;
 22  /

Warning: Trigger created with compilation errors.

SQL> show err
Errors for TRIGGER BROKER_LEVEL_TRIGGER:

LINE/COL ERROR
-------- -----------------------------------------------------------------
18/7     PLS-00103: Encountered the symbol "LOOP" when expecting one of
         the following:
         if

如果您仔细观察，您会发现 IF 错过了 END IF。让我们添加它:

SQL> CREATE OR REPLACE TRIGGER broker_level_trigger
  2    AFTER INSERT ON appointment
  3  DECLARE
  4    counter integer := 1;
  5  BEGIN
  6    for o in (SELECT * FROM broker)
  7    loop
  8      SELECT COUNT(appointment.broker_id)
  9        INTO app_number
 10        FROM appointment INNER JOIN broker
 11          ON broker.broker_id = appointment.broker_id
 12        WHERE broker.broker_id = counter;
 13
 14      IF app_number > 15 THEN
 15         UPDATE broker SET
 16           broker_level = 'gold'
 17           WHERE broker_id = counter;
 18         counter := counter + 1;
 19      END IF;                          --> missing
 20    end loop;
 21  end;
 22  /

Warning: Trigger created with compilation errors.

SQL> show err
Errors for TRIGGER BROKER_LEVEL_TRIGGER:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/5      PL/SQL: SQL Statement ignored
7/12     PLS-00201: identifier 'APP_NUMBER' must be declared
8/7      PL/SQL: ORA-00904: : invalid identifier
12/5     PL/SQL: Statement ignored
12/8     PLS-00201: identifier 'APP_NUMBER' must be declared

APP_NUMBER 现已丢失；您使用它，但从未声明它。我们现在就开始吧:

SQL> CREATE OR REPLACE TRIGGER broker_level_trigger
  2    AFTER INSERT ON appointment
  3  DECLARE
  4    counter integer := 1;
  5    app_number number;                   --> missing
  6  BEGIN
  7    for o in (SELECT * FROM broker)
  8    loop
  9      SELECT COUNT(appointment.broker_id)
 10        INTO app_number
 11        FROM appointment INNER JOIN broker
 12          ON broker.broker_id = appointment.broker_id
 13        WHERE broker.broker_id = counter;
 14
 15      IF app_number > 15 THEN
 16         UPDATE broker SET
 17           broker_level = 'gold'
 18           WHERE broker_id = counter;
 19         counter := counter + 1;
 20      END IF;                          --> missing
 21    end loop;
 22  end;
 23  /

Trigger created.

SQL>

就这样吧。

<小时/>

如果您不使用 SQL*Plus，而是使用其他工具，则始终可以查询 user_errors:

Warning: Trigger created with compilation errors.

SQL> select line, position, text from user_errors where name = 'BROKER_LEVEL_TRIGGER' order by sequence;

 LINE  POSITION TEXT
----- --------- ------------------------------------------------------------
    8        12 PLS-00201: identifier 'APP_NUMBER' must be declared
    9         7 PL/SQL: ORA-00904: : invalid identifier
    7         5 PL/SQL: SQL Statement ignored
   13         8 PLS-00201: identifier 'APP_NUMBER' must be declared
   13         5 PL/SQL: Statement ignored

SQL>