- 论坛徽章:
- 0
|
怎样SQL存储过程中执行动态SQL语句,急急急!!
(转贴)
mcf你好,你所说的我都试了,我的代码如下
--文件名称:refresh.db2
--目 标:以刷新的方式把odd(Operational Data Definition )中的数据整理到ODS(Operational
-- Data Store)中
--摘 要:
--当前版本:1.0
--作 者:周海明
--完成日期:
--取代版本:
--原作者 :
--完成日期:
create procedure refresh(in odd_table varchar(100), in ods_table varchar(100), out errorCode integer, out errorLabel varchar(255))
language sql
P1 begin
declare SQLCODE integer default 0;
declare stmt varchar(1024);
declare EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
set errorCode = SQLCODE;
set errorCode = 0;
set stmt = 'delete from ?';
set errorLabel = 'refresh:The line number is 28.';
--使用prepare语句为这个语句生成存取方案
prepare prep_stmt from stmt;
set errorLabel = 'rerresh:The line number is 31.';
--使用execute语句执行动态sql语句
execute prep_stmt using ods_table;
set stmt = 'insert into ? '||
'select * from ?';
set errorLabel = 'refresh:The line number is 38.';
--使用prepare语句为这个语句生成存取方案
prepare prep_stmt from stmt;
set errorLabel = 'refresh:The line number is 41.';
--使用execute语句执行动态sql语句
execute prep_stmt using ods_table, odd_table;
set errorLable = ''; --当全部sql语句都正确执行时把errorLable变量赋值为空串
end P1
但我在DB2 8.1版本的开发中心编译它的时候系统报错,报错信息如下
TEST.refresh ― 构建已启动。
创建 存储过程 返回 -198。
[IBM][CLI Driver][DB2/NT] SQL0198N PREPARE 或 EXECUTE IMMEDIATE 语句的语句字符串为空白或空。 SQLSTATE=42617
TEST.refresh ― 构建失败。
TEST.refresh ― 回滚成功完成。
我查了查SQL0198N错误信息的内容,如下
sqlcode: -197
sqlstate: 42877
SQL0198N The statement string of the PREPARE or EXECUTE IMMEDIATE statement is blank or empty.
Explanation: The host variable that was the object of the PREPARE or EXECUTE IMMEDIATE statement either contained all blanks or was an empty string.
The PREPARE or EXECUTE IMMEDIATE could not be completed.
User Response: Correct the logic of the program to ensure that a valid SQL statement is provided in the operand of the PREPARE or EXECUTE IMMEDIATE statement before it is executed.
我编译其它不含动态SQL的SQL过程都能通过,不知道这有什么问题,请高手帮忙回答一下!
|
|