db2 改变一个表名字_在db2中使用动态表名

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

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.

解决方案

This line is not correct:

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

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;

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

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;

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;