在 db2 中使用动态表名

问题描述

目前在我的项目开发中需要根据某些条件生成记录计数，其中表名存储在单独的表中.例如说xx表存储列名下的表名是tableInfo.

Currently in my project development need of generating the record count based on certain criteria where the table names are stored in separate table.For instance say xx table stores the table name under the column name is tableInfo.

我已经以这样的方式编写了存储过程

I've written the stored procedure in such a way that

DECLARE FGCURSOR CURSOR FOR SELECT tableInfo FROM xx WHERE col1='PO';

OPEN FGCURSOR;

FETCH FROM FGCURSOR INTO FILEGROUPMEM;

WHILE SQLCODE <> 100
DO

SET COUNTVal =   'SELECT COUNT(*)  FROM  ' ||  FILEGROUPMEM || '  WHERE ICLS=  '  || CLASS  || '  AND  IVEN=  ' || VENDOR  || ' AND ISTY=  ' || STYLE || '  AND ICLR= ' || COLOR || ' AND ISIZ=  ' || SIZE   ;


IF(COUNTVal  >= 1) THEN
RETURN 1;
END IF;

FETCH FROM FGCURSOR INTO FILEGROUPMEM;

END WHILE;

CLOSE FGCURSOR;

在执行程序时遇到异常

消息:[SQL0420] CAST 参数中的字符无效.原因 .....: CAST 函数的参数中的字符不是正确的.恢复 ...:将结果数据类型更改为识别 CAST 参数中的字符，或更改参数包含结果数据类型的值的有效表示.再次尝试请求.

推荐答案

这行不正确:

SET COUNTVal =   'SELECT COUNT(*)  FROM  ' ||  FILEGROUPMEM || '  WHERE ICLS=  '  || CLASS  || '  AND  IVEN=  ' || VENDOR  || ' AND ISTY=  ' || STYLE || '  AND ICLR= ' || COLOR || ' AND ISIZ=  ' || SIZE   ;

要按照您尝试的方式使用它，您必须像这样使用静态 SQL 语句

To use it the way you are trying, you'd have to use a static SQL statement like so

exec sql SELECT COUNT(*) INTO :COUNTVal
  FROM  MYTBL
 WHERE ICLS=  :CLASS  AND  IVEN=  :VENDOR  AND ISTY=  :STYLE
       AND ICLR= :COLOR  AND ISIZ=  :SIZE;

但是，虽然静态语句可以使用变量，但 FROM 子句中的表名不能是变量.

However, while a static statement can use variables, the table name in the FROM clause can not be variable.

因此，您必须准备和使用动态语句.不幸的是，SELECT INTO 不能用于动态语句.VALUES INTO 可以动态使用.

Thus you have to prepare and use a dynamic statement. Unfortunately, SELECT INTO can not be used in a dynamic statement. VALUES INTO can be used dynamically.

set wSqlStmt = 'VALUES ( SELECT COUNT(*)  FROM  ' ||  FILEGROUPMEM
                || '  WHERE ICLS=  '  || CLASS  || '  AND  IVEN=  '
                || VENDOR  || ' AND ISTY=  ' || STYLE || '  AND ICLR= '
                || COLOR || ' AND ISIZ=  ' || SIZE ||') INTO ?';

exec sql PREPARE S1 FROM :wSqlStmt;

exec sql EXECUTE S1 USING COUNTVal;

警告以上代码可能会受到 SQL 注入攻击.为了防止 SQL 注入，动态 SQL 应该使用参数标记，而不是直接将输入连接到语句.虽然您不能对表名使用参数标记，但您可以像这样对其余变量使用:

WARNING the above code could be subject to SQL Injection attacks. To protect against SQL injection, dynamic SQL should use parameter markers instead of concatenating input directly to a statement. While you can't use a parameter marker for the table name, you can for the rest of the variables like so:

set wSqlStmt = 'VALUES ( SELECT COUNT(*)  FROM  ' ||  FILEGROUPMEM
                || '  WHERE ICLS=  ?  AND  IVEN=  ? '
                || '  AND ISTY= ? AND ICLR= ?'
                || '  AND ISIZ= ?) INTO ?';

exec SQL PREPARE S1 FROM :wSqlStmt;

exec SQL EXECUTE S1 USING :CLASS, :VENDOR, :STYLE, :COLOR, :SIZE, :COUNTVal;

这篇关于在 db2 中使用动态表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！