在db2中使用动态表名 [英] Using dynamic table name in 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;

在执行该步骤时得到异常

Getting the exception on executing the procedure saying that

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

Message: [SQL0420] Character in CAST argument not valid. Cause . . . . . : A character in the argument for the CAST function was not correct. Recovery . . . : Change the result data type to one that recognizes the characters in the CAST argument, or change the argument to contain a valid representation of a value for the result data type. Try the request again.

推荐答案

此行不正确:

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 Injection攻击.为了防止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中使用动态表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！