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;