主页 / dba / 问题 / 344945
Accepted
coladict
Asked: 2025-01-27 17:04:12 +0800 CST

DB2 LUW SQLCODE=-740，SQLSTATE=51034 在 UPDATE 上使用函数修改 sql 数据

  • 772

我有一个修改 SQL 数据的用户定义函数。它插入到一个表中并返回返回行的 ID。尝试像这样使用它

UPDATE table_X SET REV = REVISION(:timestamp, :user) WHERE ...conditions;

DB2 给出了错误：Routine "REDACTED.REVISION" (specific name "REDACTED") is defined with the MODIFIES SQL DATA option, which is not valid in the context where the routine is invoked.

IBM针对此错误的文档明确说明了函数的MODIFIES SQL DATA使用方法。

这仅仅是 DB2 中的一个错误还是我遗漏了什么？

db2

1 个回答

  1. Best Answer

    如果您有 DB2 for LUW（而不是您的链接所指的 DB2 for Z/OS），那么您应该遵循正确的文档。
    请参阅CREATE FUNCTION（SQL 标量、表或行）语句链接，其中包含有关该子句的以下注释MODIFIES SQL DATA：

    仅对已编译标量函数定义和内联表函数定义有效。定义为 MODIFIES SQL DATA 的已编译标量函数只能用作复合 SQL（已编译）语句中赋值语句右侧的唯一元素。

    这意味着它不能在UPDATE语句中使用，例如，只能在(BEGIN ... END，而不是BEGIN ... END) 复合SET语句中使用。compiledATOMIC

    CREATE OR REPLACE FUNCTION TEST_MOD()
RETURNS INT
MODIFIES SQL DATA
BEGIN
  RETURN 1;
END
DB20000I  The SQL command completed successfully.

CREATE TABLE IF NOT EXISTS TEST_MOD(I INT)
DB20000I  The SQL command completed successfully.

BEGIN
  DECLARE V_RES INT;
  SET V_RES = TEST_MOD();
END
DB20000I  The SQL command completed successfully.

BEGIN
  UPDATE TEST_MOD SET I = TEST_MOD();
END
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0740N  Routine "DB2ADMIN.TEST_MOD" (specific name "SQL250127142445606") is
defined with the MODIFIES SQL DATA option, which is not valid in the context
where the routine is invoked.  LINE NUMBER=2.  SQLSTATE=51034

BEGIN ATOMIC
  DECLARE V_RES INT;
  SET V_RES = TEST_MOD();
END
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0740N  Routine "DB2ADMIN.TEST_MOD" (specific name "SQL250127142445606") is
defined with the MODIFIES SQL DATA option, which is not valid in the context
where the routine is invoked.  LINE NUMBER=3.  SQLSTATE=51034
    • 2

相关问题