需求：oracle两张结构完全相同表，判断比较字段，如果字段不同，则将数据放入第三张表
参考博客：https://bbs.csdn.net/topics/350192411
https://blog.csdn.net/qq_15003505/article/details/80471649
———————————————————————————————————————————————————————
演示数据
create table t1(
user_id integer not null,
first_name varchar(20),
last_name varchar(20),
grade varchar(20),
constraint tA1_pkey primary key(user_id)
)
create table t2(
user_id integer not null,
first_name varchar(20),
last_name varchar(20),
grade varchar(20),
constraint tA2_pkey primary key(user_id)
)
insert into t1(user_id,first_name,last_name,grade)values(1,’Some’,’Dude’,’A’);
insert into t1(user_id,first_name,last_name,grade)values(2,’Other’,’Guy’,’B’);
insert into t1(user_id,first_name,last_name,grade)values(3,’You are’,’Welcome’,’B’);
insert into t1(user_id,first_name,last_name,grade)values(4,’What’,’Other’,’A’);
insert into t1(user_id,first_name,last_name,grade)values(5,’INeed’,’You’,’C’);
insert into t1(user_id,first_name,last_name,grade)values(6,’Mixed’,’Nuts’,’Z’) ;
insert into t1(user_id,first_name,last_name,grade)values(7,’Kirk’,’Land’,’B’);
insert into t1(user_id,first_name,last_name,grade)values(8,’Bit’,’Shooter’,’A’);
insert into t1(user_id,first_name,last_name,grade)values(9,’Sun’,’Microsystem’,’C’);
insert into t1(user_id,first_name,last_name,grade)values(10,’Extra’,’Fancy’,’B’);
insert into t2(user_id,first_name,last_name,grade)values(1,’Some’,’Dude’,’A’);
insert into t2(user_id,first_name,last_name,grade)values(2,’Other’,’Guy’,’B’);
insert into t2(user_id,first_name,last_name,grade)values(3,’You are’,’Welcome’,’B’);
insert into t2(user_id,first_name,last_name,grade)values(4,’What’,’Other’,’A’);
insert into t2(user_id,first_name,last_name,grade)values(5,’INeed’,’You’,’C’);
insert into t2(user_id,first_name,last_name,grade)values(6,’Mixed’,’Nuts’,’C’);
insert into t2(user_id,first_name,last_name,grade)values(7,’Kirk’,’Land’,’B’);
insert into t2(user_id,first_name,last_name,grade)values(8,’Bit’,’Shooter’,’A’);
insert into t2(user_id,first_name,last_name,grade)values(9,’Sun’,’Microsystem’,’C’);
insert into t2(user_id,first_name,last_name,grade)values(10,’Extra’,’Fancy’,’B’);
insert into t2(user_id,first_name,last_name,grade)values(11,’Jack’,’Fancy’,’B’);
commit;
创建表
create table t3 as
select a.user_id as cur_user_id,
a.first_name as cur_firstname,
a.last_name as cur_lastname,
a.grade as cur_grade,
b.first_name as before_firstname,
b.last_name as before_lastname,
b.grade as before_grade
from
(select user_id,
first_name,
last_name,
grade
from t2
) a
full join
(
select user_id,
first_name,
last_name,
grade
from t1) b
on a.user_id=b.user_id
delete from t3
————————————————————————————————————————————————————————-
处理方法
declare
user_id varchar(200);
cur_firstname varchar(200);
cur_lastname varchar(200);
cur_grade varchar(200);
before_user_id varchar(200);
before_firstname varchar(200);
before_lastname varchar(200);
before_grade varchar(200);
cursor c_job is
select a.user_id as cur_user_id,
a.first_name as cur_firstname,
a.last_name as cur_lastname,
a.grade as cur_grade,
b.first_name as before_firstname,
b.last_name as before_lastname,
b.grade as before_grade
from
(select user_id,
first_name,
last_name,
grade
from t2
) a
full join
(
select user_id,
first_name,
last_name,
grade
from t1) b
on a.user_id=b.user_id
where a.user_id is not null;
c_row c_job%rowtype;
begin
for c_row in c_job loop
if (c_row.cur_firstname<>c_row.before_firstname
or c_row.cur_lastname<>c_row.before_lastname
or c_row.cur_grade<>c_row.before_grade) then
insert into t3(cur_user_id,cur_firstname,cur_lastname,cur_grade,before_firstname,before_lastname,before_grade)
values(c_row.cur_user_id,
c_row.cur_firstname,
c_row.cur_lastname,
c_row.cur_grade,
c_row.before_firstname,
c_row.before_lastname,
c_row.before_grade);
end if;
end loop;
commit;
end;
