oracle兩張結構完全相同表，判斷比較字段，如果字段不同，

需求：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;