Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.