需求：oracle兩張結(jié)構(gòu)完全相同表，判斷比較字段，如果字段不同，則將數(shù)據(jù)放入第三張表

創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供黃石港網(wǎng)站建設(shè)、黃石港做網(wǎng)站、黃石港網(wǎng)站設(shè)計(jì)、黃石港網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計(jì)與制作、黃石港企業(yè)網(wǎng)站模板建站服務(wù)，十年黃石港做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價值的思路和整體網(wǎng)絡(luò)服務(wù)。

參考博客：https://bbs.csdn.net/topics/350192411

? ? ? ? ? ? ? ? ?https://blog.csdn.net/qq_15003505/article/details/80471649

---------------------------------------------------------------------------------------------------------------------------------------------------------------------

演示數(shù)據(jù)

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;

創(chuàng)建表

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;

網(wǎng)站欄目：oracle兩張結(jié)構(gòu)完全相同表，判斷比較字段，如果字段不同，
當(dāng)前網(wǎng)址：http://www.rwnh.cn/article6/gopsog.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供企業(yè)網(wǎng)站制作、ChatGPT、關(guān)鍵詞優(yōu)化、品牌網(wǎng)站制作、軟件開發(fā)、營銷型網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)