--這是在單個資料庫表中進行比較的方法，我如何比較不同的資料庫表？
CREATE OR REPLACE PROCEDURE compareTwoTables is
BEGIN
FOR i in (SELECT column_name
FROM all_tab_columns
WHERE table_name = 'table2'
MINUS
SELECT column_name
FROM all_tab_columns
WHERE table_name = 'table1') LOOP
dbms_output.put_line(i.column_name);
END LOOP;
END;
uj5u.com熱心網友回復：
您需要創建一個到遠程資料庫的資料庫鏈接。之后，您可以使用類似于以下內容的腳本：
DECLARE
l_table VARCHAR2(100) := &tbl;
l_res NUMBER;
BEGIN
FOR col IN (SELECT column_name, ownder, data_type, data_length, data_precision, data_scale, nullable, column_id
FROM all_tab_columns@<remote_server> --asuming you have created DB link
WHERE table_name = l_table)
LOOP
BEGIN
SELECT 1
INTO l_res
FROM all_tab_columns
WHERE NVL(column_name ,'x') = NVL(col.column_name ,'x')
NVL(ownder ,'x') = NVL(col.ownder ,'x')
NVL(data_type ,'x') = NVL(col.data_type ,'x')
NVL(data_length ,'x') = NVL(col.data_length ,'x')
NVL(data_precision,'x') = NVL(col.data_precision,'x')
NVL(data_scale ,'x') = NVL(col.data_scale ,'x')
NVL(nullable ,'x') = NVL(col.nullable ,'x')
NVL(column_id ,'x') = NVL(col.column_id; ,'x');
EXCEPTION WHEN no_data_found THEN
dbms_output.put_line(col.column_name);
END;
END LOOP;
END;
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/481851.html
上一篇：我想按年齡段制作圖表
下一篇：如何為一組表撰寫通用觸發器？