在工作中需要完成這麼一個需求:比較兩個表的表結構是否形相同,並找出差異.比較兩個表中的數據是否相同,並找出差異數據?
分析:由於表結構中字段比較多,手工比較很浪費時間,而且不能保證不出錯誤.對於表中的數據那就能多了,更不能靠這種方式比較.
為了思考問題簡單和方便測試,首先先建立兩個測試表,並插入一些測試數據吧,sql如下:
[sql] view plaincopyprint?
create table t_A ( id VARCHAR2(36) not null, name VARCHAR2(100), age NUMBER, sex VARCHAR2(2) ); insert into t_A (id, name, age, sex) values ('1', '1', 1, '1'); insert into t_A (id, name, age, sex) values ('2', '2', 2, '2'); commit; create table t_B ( id VARCHAR2(36) not null, name VARCHAR2(100), age NUMBER, clazz VARCHAR2(36) ); insert into t_B (id, name, age, clazz) values ('1', '1', 1, '1'); insert into t_B (id, name, age, clazz) values ('2', '2', 1, '3'); insert into t_B (id, name, age, clazz) values ('3', '3', 3, '3'); commit;
解決過程:剛開始考慮使用存儲過程,用循環比較的方式處理,首先需要找出能得到表結構的sql,查閱資料得知,在Oracle中所有表結構信息都存儲在user_tab_columns中,那麼查詢單個表的表結構信息很簡單: select column_name from user_tab_columns where table_name = 't_A'; 運行後發現查不到結果,為什麼呢?去掉查詢條件後能查詢出結果,核對後發現原來在user_tab_columns中存儲的內容都是大寫的,原來如此,sql改為如下就可以查詢出結果了: select column_name from user_tab_columns where table_name = 'T_A'; 寫這樣一個存儲過程發現還是有點復雜的,網上找找有沒有現成的,自己寫了一會發現很復雜.網上找的時候找到了一個minus關鍵字.科普一下:在oracle中union 並集 intersect 交集 minus 差集;我可以用差集來實現那個需求嗎? 很快就寫出了sql:
[sql] view plaincopyprint?
/*1.比較表結構 */ (select column_name from user_tab_columns where table_name = 'T_A' minus select column_name from user_tab_columns where table_name = 'T_B') union (select column_name from user_tab_columns where table_name = 'T_B' minus select column_name from user_tab_columns where table_name = 'T_A'); /* 2.比較表數據 */ (select * from t_A minus select * from t_B) union (select * from t_B minus select * from t_A)
看看sql的運行效果吧:
表t_A結構及數據:
表t_B結構及數據:
表結構差異:
數據差異:
反思:為什麼我之前沒想到用差集呢? 1.數學沒有學好,沒有數學的思維.並集交集和差集的概念早就在中學學過,但數學思維沒有建立,所以....得補補數學啦~ 2.oracle函數不熟,看來我需要找一本oracle函數手冊,沒事的時候就翻翻.