Oracle two tables with exactly the same structure, judge and compare fields, if the fields are different
Requirements: oracle two tables with exactly the same structure, judge the comparison fields, and if the fields are different, put the data into the third table
Reference blog: https://bbs.csdn.net/topics/350192411
Https://blog.csdn.net/qq_15003505/article/details/80471649
- -
Demo data
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)
Insert into T1 (user_id,first_name,last_name,grade) values (2)
Insert into T1 (user_id,first_name,last_name,grade) values (3meme you are','Welcome','B')
Insert into T1 (user_id,first_name,last_name,grade) values.
Insert into T1 (user_id,first_name,last_name,grade) values.
Insert into T1 (user_id,first_name,last_name,grade) values (6)
Insert into T1 (user_id,first_name,last_name,grade) values.
Insert into T1 (user_id,first_name,last_name,grade) values.
Insert into T1 (user_id,first_name,last_name,grade) values (9pm, Sunshine, Microsystem, and C')
Insert into T1 (user_id,first_name,last_name,grade) values (10 Magneto extras, Fancyies, B')
Insert into T2 (user_id,first_name,last_name,grade) values (1)
Insert into T2 (user_id,first_name,last_name,grade) values (2)
Insert into T2 (user_id,first_name,last_name,grade) values (3meme you are','Welcome','B')
Insert into T2 (user_id,first_name,last_name,grade) values.
Insert into T2 (user_id,first_name,last_name,grade) values.
Insert into T2 (user_id,first_name,last_name,grade) values (6)
Insert into T2 (user_id,first_name,last_name,grade) values.
Insert into T2 (user_id,first_name,last_name,grade) values (8pr)
Insert into T2 (user_id,first_name,last_name,grade) values (9pm, Sunshine, Microsystem, and C')
Insert into T2 (user_id,first_name,last_name,grade) values (10 Magneto extras, Fancyies, B')
Insert into T2 (user_id,first_name,last_name,grade) values (11pr) jackpot, fancyboat, and B')
Commit
Create a tabl
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
-
Treatment method
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_firstnamec_row.before_firstname
Or c_row.cur_lastnamec_row.before_lastname
Or c_row.cur_gradec_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
