请教大家，我在toad里调试下面的存储过程，当执行EXECUTE IMMEDIATE v_insert_sql1就能插入数据，但是如果EXECUTE IMMEDIATE v_insert_sql 的话，就不能执行，我调试的时候，把光标放在v_insert_sql1这个变量上就有值，而放在v_insert_sql这个变量上就显示null，可是v_insert_sql就比v_insert_sql1多插了个字段而已阿，v_insert_sql的长度也足够长，这个是什么问题呢？跪求解答，谢谢大家
CREATE PROCEDURE "DB2INST1"."PRO_HIS_TRACE" ( OUT "O_INSER" VARCHAR(1024) )
SPECIFIC "SQL110726174143300"
LANGUAGE SQL
NOT DETERMINISTIC
COMMIT ON RETURN NO
EXTERNAL ACTION
MODIFIES SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
OLD SAVEPOINT LEVEL
begin
declare v_ship_id varchar(20);
declare v_source_timestamp varchar(22);
declare v_longitude decimal(9,6);
declare v_latitude decimal(9,6);
declare v_hcid varchar(64);
declare v_insert_sql varchar(32000) default '';
declare v_insert_sql1 varchar(1024) default '';
declare v_insert_sql2 varchar(1024) default '';
DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; -- db2错误信息
DECLARE SQLCODE INT DEFAULT 0; -- db2错误代码
DECLARE SQLStmt varchar(1024) default '';
declare v_err_no integer;
DECLARE V_SQL_CODE INT DEFAULT 0;
DECLARE V_SQL_MSG VARCHAR(500) DEFAULT '';
DECLARE V_JOB_ROWS INT DEFAULT 0;
DECLARE V_SQL_STATE CHAR(5) DEFAULT '00000';
DECLARE stmt STATEMENT;
DECLARE c2 CURSOR WITH HOLD FOR stmt ;
declare c1 cursor WITH HOLD for select a.ship_id,a.source_timestamp,a.LONGITUDE,a.LATITUDE from posit_realtime a left join his_trace b on a.ship_id=b.ship_id;
/* declare exit handler for sqlexception
begin
set v_err_no=sqlcode;
rollback;
end;*/
DECLARE EXIT HANDLER FOR SQLEXCEPTION
H1:BEGIN
GET DIAGNOSTICS EXCEPTION 1 V_SQL_MSG = MESSAGE_TEXT; -- 捕获错误描述
SELECT SQLSTATE,SQLCODE INTO V_SQL_STATE,V_SQL_CODE FROM SYSIBM.SYSDUMMY1; -- 捕获错误信息及其代码
ROLLBACK; -- 回滚
END H1;
SET SQLStmt='select b.hcid from posit_realtime a left join his_voyagehistory b on a.ship_id=b.mmsi where a.ship_id=?';
PREPARE stmt FROM SQLStmt;
open c1;
cursor1loop:
loop
fetch c1 into v_ship_id,v_source_timestamp,v_longitude,v_latitude;
if sqlcode = 100 then
leave cursor1Loop;
else
OPEN c2 USING v_ship_id;
FETCH c2 INTO v_hcid;
set v_insert_sql='insert into his_trace(SHIP_ID,VOYAGE_ID,SOURCE_TIMESTAMP) values('||''''||v_ship_id||''''||','||null||','||''''||v_source_timestamp||''''||')';
set v_insert_sql1='insert into his_trace(SHIP_ID,SOURCE_TIMESTAMP) values('||''''||v_ship_id||''''||','||''''||v_source_timestamp||''''||')';
EXECUTE IMMEDIATE v_insert_sql1;
commit;
CLOSE c2;
end if;
end loop;
close c1;
end;
------解决方案--------------------------------------------------------
你将NULL修改为空字符串，
select ', ' || null || ', '
看看结果
------解决方案--------------------------------------------------------
为null的时候，说明你不需要插入那个字段，插入语句里直接忽略这个字段