2 ljl963125 ljl963125 于 2014.04.24 23:12 提问

db2 存储过程加了atomic不回滚是什么原因

CREATE PROCEDURE "LOAN".PROCDEALDAILYBAL ( IN V_DATE VARCHAR(10) )
SPECIFIC "PROCDEALDAILYBAL"
DYNAMIC RESULT SETS 1
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
MODIFIES SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
begin

declare V_DEALED varchar(1) default '1';

declare V_INVALID_YEAR integer;
declare V_INVALID_DAY varchar(5);
declare V_INVALID_DATE varchar(10);
declare V_INVALID_DATE_RN varchar(10);

declare V_CONSUM_SQL varchar(512);

declare USEREXCEPTION CONDITION FOR SQLSTATE '99998';

declare sqlcode INTEGER DEFAULT 0;--

declare sqlstate CHAR(5);--

declare V_MESSAGE CHAR(100);--

declare MESSAGE_TEXT VARCHAR(100);--

declare EXIT HANDLER FOR SQLEXCEPTION
begin

values ('调用失败,SQLCode:' || CHAR(sqlcode) || 'SQLState:' || CHAR(sqlstate)) INTO V_MESSAGE;--
insert into ITG_DATA_UPLOAD(WORKDATE,DATATIME,PACKETERR,PACKETFLAG) values (to_char(current timestamp ,'yyyyMMddHH24miss'),V_DATE,V_MESSAGE,'0');--

SIGNAL SQLSTATE '99999';--
SET MESSAGE_TEXT = V_DATE || ' 日数据更新失败!';--

end;--

declare GLOBAL TEMPORARY TABLE session.USER_CONSUM (

CUSTOMID VARCHAR(32),
CONSUM DECIMAL(18,8)
) NOT LOGGED WITH REPLACE;--

declare GLOBAL TEMPORARY TABLE session.DAILY_BAL (

CUSTOMID VARCHAR(32),
CUSTOMNAME VARCHAR(64),
BALANCE DECIMAL(18,8)
) NOT LOGGED WITH REPLACE;--

set V_INVALID_YEAR = integer(to_char(to_date(V_DATE,'yyyy-MM-dd') -1 years,'yyyy'));
set V_INVALID_DAY = substr(V_DATE,6);
set V_INVALID_DATE = to_char(to_date(V_DATE,'yyyy-MM-dd') -1 years,'yyyy') || substr(V_DATE,5);
set V_INVALID_DATE_RN = to_char(to_date(V_INVALID_DATE,'yyyy-MM-dd') -1 days,'yyyy-MM-dd');

select count(1) into V_DEALED from ITG_DATA_UPLOAD where DATATIME = V_DATE AND PACKETFLAG = '1';--
if V_DEALED <> '0' then
SIGNAL USEREXCEPTION;--
end if;

if ((mod(V_INVALID_YEAR,400) = 0 ) or ( mod(V_INVALID_YEAR , 4) = 0 and mod(V_INVALID_YEAR , 100) != 0 )) and (V_INVALID_DAY = '03-01') then
set V_CONSUM_SQL = 'insert into session.USER_CONSUM select customid,COALESCE(sum(COALESCE(integraldec,0)),0) - COALESCE(sum(COALESCE(integraladd,0)),0) from itg_details where trandate >=''' || V_INVALID_DATE_RN || ''' group by customid';
else
set V_CONSUM_SQL = 'insert into session.USER_CONSUM select customid,COALESCE(sum(COALESCE(integraldec,0)),0) - COALESCE(sum(COALESCE(integraladd,0)),0) from itg_details where trandate >=''' || V_INVALID_DATE || ''' group by customid';
end if;--

EXECUTE IMMEDIATE V_CONSUM_SQL;--

--事物开始

p1: begin atomic
insert into session.DAILY_BAL
select a.acustomid,a.acustomname,
case when COALESCE(a.ALSTYEAR,0) >= COALESCE(b.CONSUM,0) then COALESCE(a.AYEAR,0) - ( COALESCE(a.ALSTYEAR,0) - COALESCE(b.CONSUM,0) ) else COALESCE(a.AYEAR,0) end
from ITG_BALANCE_DAILY a left join session.USER_CONSUM b
on a.acustomid = b.customid;--

 merge into ITG_BALANCE a USING (select * from session.DAILY_BAL) b
 on (a.customid=b.customid) when matched then update set a.balance = a.balance + b.balance
 when not matched then insert (a.customid,a.customname,a.balance) values (b.customid,b.customname,b.balance);--

 --插入日志表
 insert into ITG_DATA_UPLOAD(WORKDATE,DATATIME,PACKETERR,PACKETFLAG) values(to_char(current timestamp ,'yyyyMMddHH24miss')||'1111111111111111',V_DATE,'数据更新成功','1');--

end p1;

end

在p1 模块加了atomic 我在插入日志表这块故意让他出错 但是merge into 这部分却没有回滚,不知道为什么 求解

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!