在PL / SQL触发器中使用Select语句的语法是什么？

CREATE OR REPLACE TRIGGER MYTRIGGER
AFTER INSERT ON SOMETABLE
FOR EACH ROW    

DECLARE
 v_emplid varchar2(10);    

BEGIN
 SELECT
  personnum into v_emplid
 FROM PERSON
 WHERE PERSONID = :new.EMPLOYEEID;

dbms_output.put(v_emplid);

/* INSERT INTO SOMEOTHERTABLE USING v_emplid and some of the other values from the trigger table*/

END MYTRIGGER;

DBA_ERRORS有这个错误：
PL / SQL：ORA-00923：在预期的位置找不到FROM关键字

解决方法

SQL> create table someTable( employeeid number );

Table created.

SQL> create table person( personid number,personnum varchar2(10) );

Table created.

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE TRIGGER MYTRIGGER
  2    AFTER INSERT ON SOMETABLE
  3    FOR EACH ROW
  4  DECLARE
  5   v_emplid varchar2(10);
  6  BEGIN
  7   SELECT personnum
  8     into v_emplid
  9     FROM PERSON
 10    WHERE PERSONID = :new.EMPLOYEEID;
 11    dbms_output.put(v_emplid);
 12    /* INSERT INTO SOMEOTHERTABLE USING v_emplid and some of the other values
 from the trigger table*/
 13* END MYTRIGGER;
 14  /

Trigger created.

SQL> insert into person values( 1,'123' );

1 row created.

SQL> insert into sometable values( 1 );

1 row created.

2)您可能希望将V_EMPLID声明为Person.PersonNum％TYPE类型,以便您可以确定数据类型是正确的,这样如果表的数据类型发生更改,您将无需更改代码.

3)我假设您知道您的触发器无法查询或更新定义了触发器的表(因此不会对someTable进行查询或插入).

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!