-- Create table 源表
create table INPUT_FWMJ
(
JGBH CHAR(5) not null,
JGMC VARCHAR2(50) not null,
BMBH CHAR(10) not null,
BMMC VARCHAR2(50) not null,
FWMJ NUMBER(20,2) not null,
ZLBZ CHAR(1) not null
)
-- Create/Recreate primary, unique and foreign key constraints
alter table INPUT_FWMJ
add constraint 机构编号和部门编号 primary key (JGBH, BMBH)
--补录表
-- Create table
create table INPUT_FWMJ_CHANGE
(
TASKID NUMBER not null,
STATE VARCHAR2(100),
JGBHSOURCE CHAR(5),
BMBHSOURCE CHAR(10),
JGBH CHAR(5) not null,
JGMC VARCHAR2(50) not null,
BMBH CHAR(10) not null,
BMMC VARCHAR2(50) not null,
FWMJ NUMBER(20,2) not null,
ZLBZ CHAR(1) not null
)
-- Create/Recreate primary, unique and foreign key constraints
alter table INPUT_FWMJ_CHANGE
add constraint PK_INPUT_FWMJ_CHANGE primary key (JGBH, BMBH, TASKID)
--增加
insert
into input_fwmj(jgbh, jgmc, bmbh, bmmc, fwmj, zlbz)
select jgbh, jgmc, bmbh, bmmc, fwmj, zlbz
from input_FWMJ_change
where state = 'Added'
--删除
delete input_fwmj
where jgbh || bmbh in
(select JGBHSOURCE || BMBHSOURCE
from input_FWMJ_change
where input_FWMJ_change.State = 'Deleted')
--修改
update input_fwmj set
input_fwmj.fwmj =
(select input_FWMJ_change.fwmj
from input_FWMJ_change
where input_FWMJ_change.State = 'Updated'
and input_FWMJ_change.JGBHSOURCE =
input_fwmj.jgbh
and input_FWMJ_change.BMBHSOURCE =
input_fwmj.bmbh),
input_fwmj.zlbz =
(select input_FWMJ_change.zlbz
from input_FWMJ_change
where input_FWMJ_change.State = 'Updated'
and input_FWMJ_change.JGBHSOURCE =
input_fwmj.jgbh
and input_FWMJ_change.BMBHSOURCE =
input_fwmj.bmbh)
where exists((select 1
from input_FWMJ_change
where input_FWMJ_change.State = 'Updated'
and input_FWMJ_change.JGBHSOURCE =
input_fwmj.jgbh
and input_FWMJ_change.BMBHSOURCE =
input_fwmj.bmbh))