今天写存储过程的时候,发现没有保证数据的一致性
原来代码:
CREATE OR REPLACE procedure PDI as
begin
insert into PDI(SEQ_NUM,
MSG_TS,
MSG_FLAG,
MAT_NO,
MSG_TS_INSERT
)
select
PDI_SEQ_ID.NEXTVAL,
MSG_TS,
MSG_FLAG,
MAT_NO,
sysdate
from xxx where MSG_FLAG = 'N';
Update xxx set MSG_FLAG = 'C' WHERE MSG_FLAG = 'N';
commit;
end;
/
修改后代码:
CREATE OR REPLACE procedure PDI as
begin
Update xxx set MSG_FLAG = 'Z' WHERE MSG_FLAG = 'N';
insert into PDI(SEQ_NUM,
MSG_TS,
MSG_FLAG,
MAT_NO,
MSG_TS_INSERT
)
select
PDI_SEQ_ID.NEXTVAL,
MSG_TS,
MSG_FLAG,
MAT_NO,
sysdate
from xxx where MSG_FLAG = 'Z';
Update xxx set MSG_FLAG = 'C' WHERE MSG_FLAG = 'Z';
commit;
end;
/
也就可以理解成有个中间表,(1)从A表读,插入到B表,(2)在把B表数据插入到C表,(3)把B表数据删除,
或者加锁,事务