为什么此触发器的 FOR 循环出现错误？

Question

我正试图在SQL解释器中创建一个触发器，但我遇到了一些令人头疼的问题。

CREATE TRIGGER log_permission_role_rel_update AFTER UPDATE ON permission_role_rel
REFERENCING NEW ROW as newrow OLD ROW as oldrow 
FOR EACH ROW
BEGIN ATOMIC
    /* delete old row */
    FOR SELECT * FROM TABLE(MYSCHEMA.simulate_by_permission_role_rel(oldrow.id_role, oldrow.id_permission)) simulationA
    DO
    CALL MYSCHEMA.log_proc('DELETE', 'SECVAL', 'test', '{}');
    END FOR;
    /* insert new row */
    FOR SELECT * FROM TABLE(MYSCHEMA.simulate_by_permission_role_rel(newrow.id_role, newrow.id_permission)) simulationB
    DO
    CALL MYSCHEMA.log_proc('INSERT', 'SECVAL', '{}', 'test');
    END FOR;
END;

上面的语句导致一个错误，声称指令的结尾无效，并将第二个CALL的结尾突出显示为罪魁祸首。

SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token <END-OF-INSTRUCTION> was not valid. Valid tokens: ;.

两个FOR循环都是独立工作的，只是当它们都在同一个触发器下时就不一样了。我真的不知道还能做什么。我在这里做错了什么？为什么一个FOR循环可以工作，而两个不行？

谢谢。

编辑（编辑以获取更多信息）

我在iSeries i5/OS7.1上的DB2下运行。我还通过iNavigator运行所有SQL语句。

编辑#2

所有的触发器似乎都是用这些属性创建的：

...
FOR EACH ROW 
MODE DB2SQL 
SET OPTION  ALWBLK = *ALLREAD , 
ALWCPYDTA = *OPTIMIZE , 
COMMIT = *NONE , 
DECRESULT = (31, 31, 00) , 
DFTRDBCOL = MYSCHEMA, 
DYNDFTCOL = *NO , 
DYNUSRPRF = *USER , 
SRTSEQ = *HEX   
BEGIN
...

程序：

CREATE PROCEDURE MYSCHEMA.LOG_PROC ( 
    IN OP VARCHAR(6) , 
    IN TABLENAME VARCHAR(128) , 
    IN OLDVAL VARCHAR(255) , 
    IN NEWVAL VARCHAR(255) ) 
    LANGUAGE SQL 
    SPECIFIC MYSCHEMA.LOG_PROC 
    NOT DETERMINISTIC 
    MODIFIES SQL DATA 
    CALLED ON NULL INPUT 
    SET OPTION  ALWBLK = *ALLREAD , 
    ALWCPYDTA = *OPTIMIZE , 
    COMMIT = *NONE , 
    DECRESULT = (31, 31, 00) , 
    DFTRDBCOL = MYSCHEMA , 
    DYNDFTCOL = *NO , 
    DYNUSRPRF = *USER , 
    SRTSEQ = *HEX   
    BEGIN 
    INSERT INTO AA_LEGACYLOG ( OPERATION , TABLENAME , OLDVALUE , NEWVALUE ) VALUES ( OP , TABLENAME , OLDVAL , NEWVAL ) ; 
END  ; 

功能：

CREATE FUNCTION MYSCHEMA.SIMULATE_BY_PERMISSION_ROLE_REL ( 
    ARG_ID_ROLE INTEGER , 
    ARG_ID_PERMISSION INTEGER ) 
    RETURNS TABLE ( 
    USRCODE VARCHAR(10) , 
    SECURABLE VARCHAR(12) , 
    LG_INDX VARCHAR(1) , 
    LG_VALO VARCHAR(10) , 
    LG_ATRB VARCHAR(6) )   
    LANGUAGE SQL 
    SPECIFIC MYSCHEMA.SIMULATE_BY_PERMISSION_ROLE_REL 
    NOT DETERMINISTIC 
    READS SQL DATA 
    CALLED ON NULL INPUT 
    SET OPTION  ALWBLK = *ALLREAD , 
    ALWCPYDTA = *OPTIMIZE , 
    COMMIT = *NONE , 
    DECRESULT = (31, 31, 00) , 
    DFTRDBCOL = MYSCHEMA , 
    DYNDFTCOL = *NO , 
    DYNUSRPRF = *USER , 
    SRTSEQ = *HEX   
    RETURN ( 
        SELECT DISTINCT A . USER_REF , P . SECURABLE , P . LG_INDX , P . LG_VALO , P . LG_ATRB 
        FROM ROLE R , PERMISSION P , ACTOR A , ACTOR_ROLE_REL 
        WHERE R . ID = ARG_ID_ROLE 
        AND P . ID = ARG_ID_PERMISSION   
        AND ACTOR_ROLE_REL . ID_ACTOR = ACTOR . ID 
        AND ACTOR_ROLE_REL . ID_ROLE = R . ID 
        GROUP BY A . USER_REF , P . SECURABLE , P . LG_INDX , P . LG_VALO , P . LG_ATRB  
    )  ; 

Answer 1

在System i V7R1中创建函数时，这可能是错误SQL0104的重复；我给出的链接是讨论中的具体答案，但最好阅读整个讨论。该讨论得出了我在下面得出的类似结论，即问题是由于缺乏应用于OP使用的IBM I 7.1系统的PTFs\维护，在不包括可选cursor-name CURSOR FOR子句的情况下，解析for语句IBM I 7.10->Database->Reference->SQL Reference->SQL control statements->for语句的问题\缺陷；即可选子句实际上是强制，直到应用修复为止。

下面的脚本应该足够，就像OP给出的一样[省略了每个SET OPTION中的DFTRDBCOL和UDTF上的硬编码返回]，运行良好，可以在IBM i 7.1上创建触发器而没有任何终止错误，并从2015/317开始进行累积维护，使用RUNSQLSTM服务器端工具\SQL脚本处理器和iACS Run SQL[我没有一个Win操作系统可以在上面加载和尝试iNav]客户端工具/SQL脚本处理器。注意：脚本末尾的最后一个DROP只是一个将失败的额外语句，放在那里是为了强调触发器中BEGIN的end后面多余的分号不会造成任何困难，我甚至添加了另一个分号作为语句分隔符，这也不会造成任何问题。我想要么是因为OP的问题，缺陷被纠正/消除了，要么是

drop   table qtemp.AA_LEGACYLOG
;
create table qtemp.AA_LEGACYLOG
  ( OPERATION         VARCHAR(  6)
  , TABLENAME         VARCHAR(128)
  , OLDVALUE          VARCHAR(255)
  , NEWVALUE          VARCHAR(255)
  )
;
drop   PROCEDURE  LOG_PROC
;
CREATE PROCEDURE  LOG_PROC
 (  IN OP VARCHAR(6)
 ,  IN TABLENAME VARCHAR(128)
 ,  IN OLDVAL VARCHAR(255)
 ,  IN NEWVAL VARCHAR(255)
 )  LANGUAGE SQL
    SPECIFIC  LOG_PROC
    NOT DETERMINISTIC
    MODIFIES SQL DATA
    CALLED ON NULL INPUT
    SET OPTION  ALWBLK = *ALLREAD
             ,  ALWCPYDTA = *OPTIMIZE
             ,  COMMIT = *NONE
             ,  DECRESULT = (31, 31, 00)
             ,  DYNDFTCOL = *NO
             ,  DYNUSRPRF = *USER
             ,  SRTSEQ = *HEX
BEGIN
    INSERT INTO QTEMP.AA_LEGACYLOG
      ( OPERATION , TABLENAME , OLDVALUE , NEWVALUE )  VALUES
      (        OP , TABLENAME , OLDVAL   , NEWVAL   )
    ;
END
;
drop table    permission_role_rel
;
create table  permission_role_rel
( ID_ROLE       INTEGER
, ID_PERMISSION INTEGER
)
;
drop   FUNCTION  SIMULATE_BY_PERMISSION_ROLE_REL
;
CREATE FUNCTION  SIMULATE_BY_PERMISSION_ROLE_REL
  ( ARG_ID_ROLE INTEGER
  , ARG_ID_PERMISSION INTEGER
  ) RETURNS TABLE
  ( USRCODE VARCHAR(10)
  , SECURABLE VARCHAR(12)
  , LG_INDX VARCHAR(1)
  , LG_VALO VARCHAR(10)
  , LG_ATRB VARCHAR(6)
  ) LANGUAGE SQL
    SPECIFIC  SIMULATE_BY_PERMISSION_ROLE_REL
    NOT DETERMINISTIC
    READS SQL DATA
    CALLED ON NULL INPUT
    SET OPTION  ALWBLK = *ALLREAD
  , ALWCPYDTA = *OPTIMIZE
  , COMMIT = *NONE
  , DECRESULT = (31, 31, 00)
  , DYNDFTCOL = *NO
  , DYNUSRPRF = *USER
  , SRTSEQ = *HEX
RETURN
 ( select 'code', 'capable', 'I', 'VALO', 'ATTR'
   from qsys2.qsqptabl
 )
;
CREATE TRIGGER log_permission_role_rel_update
       AFTER UPDATE ON permission_role_rel
REFERENCING NEW ROW as newrow
            OLD ROW as oldrow
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
    /* delete old row */
    FOR SELECT *
        FROM TABLE
          ( simulate_by_permission_role_rel(oldrow.id_role, oldrow.id_permission)
          ) simulationA
    DO
      CALL log_proc('DELETE', 'SECVAL', 'test', '{}') ;
    END FOR;
   /* insert new row */
    FOR SELECT *
        FROM TABLE
          ( simulate_by_permission_role_rel(newrow.id_role, newrow.id_permission)
          ) simulationB
    DO
      CALL log_proc('INSERT', 'SECVAL', '{}', 'test');
    END FOR;
END;
;
drop table qtemp.permission_test
;

Answer 2

我看到函数中有分号结束行，还有分号结束函数声明。用一个@符号结束您的函数声明，如下所示：

CREATE FUNCTION MYSCHEMA.SIMULATE_BY_PERMISSION_ROLE_REL ( 
    ARG_ID_ROLE INTEGER , 
    ARG_ID_PERMISSION INTEGER ) 
    RETURNS TABLE ( 
    USRCODE VARCHAR(10) , 
    SECURABLE VARCHAR(12) , 
    LG_INDX VARCHAR(1) , 
    LG_VALO VARCHAR(10) , 
    LG_ATRB VARCHAR(6) )   
    LANGUAGE SQL 
    SPECIFIC MYSCHEMA.SIMULATE_BY_PERMISSION_ROLE_REL 
    NOT DETERMINISTIC 
    READS SQL DATA 
    CALLED ON NULL INPUT 
    SET OPTION  ALWBLK = *ALLREAD , 
    ALWCPYDTA = *OPTIMIZE , 
    COMMIT = *NONE , 
    DECRESULT = (31, 31, 00) , 
    DFTRDBCOL = MYSCHEMA , 
    DYNDFTCOL = *NO , 
    DYNUSRPRF = *USER , 
    SRTSEQ = *HEX   
    RETURN ( 
        SELECT DISTINCT A . USER_REF , P . SECURABLE , P . LG_INDX , P . LG_VALO , P . LG_ATRB 
        FROM ROLE R , PERMISSION P , ACTOR A , ACTOR_ROLE_REL 
        WHERE R . ID = ARG_ID_ROLE 
        AND P . ID = ARG_ID_PERMISSION   
        AND ACTOR_ROLE_REL . ID_ACTOR = ACTOR . ID 
        AND ACTOR_ROLE_REL . ID_ROLE = R . ID 
        GROUP BY A . USER_REF , P . SECURABLE , P . LG_INDX , P . LG_VALO , P . LG_ATRB  
    ) @

然后将其保存到文件c:\db\simulate_by_permission_role_rel.sql 中

然后使用以下命令执行：

db2 connect to mydb
db2 -td@ -f c:dbsimulate_by_permission_role_rel.sql
db2 connect reset

-td@neneneba告诉db2命令行接口将@符号解释为语句终止符。-f命令告诉db2cli要执行哪个文件。