Oracle compares two table structure differences and data differences. oracle differences

Oracle compares two table structure differences and data differences. oracle differences
In the work, we need to fulfill the following requirement: compare the two tables with the same structure and find the difference. Compare the two tables with the same data and find the difference data?

Analysis: because there are many fields in the table structure, it is a waste of time manually, and there is no guarantee that the error will not occur. For the table, there will be more data, not to mention the comparison.

To simplify the problem and facilitate the test, first create two test tables and insert some test data. The SQL statement is as follows:

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;

Solution: At the beginning, consider using the stored procedure and use the cyclic comparison method. First, you need to find the SQL statement that can obtain the table structure and check the information, in Oracle, all table structure information is stored in user_tab_columns, so it is easy to query the structure information of a single table:
Select column_name from user_tab_columns where table_name = 't_ ';
Why can't I find any results after running? After removing the query conditions, you can query the results. After checking the results, you can find that the content originally stored in user_tab_columns is in uppercase. In this case, the SQL statement is changed to the following to query the results:
Select column_name from user_tab_columns where table_name = 't_ ';
The discovery of writing such a stored procedure is still a bit complicated. If there is any ready-made process on the Internet, you may find it complicated after writing it yourself. A minus keyword was found online. popular Introduction: in oracle, union is used to set the intersect intersection minus difference set. Can I use a difference set to achieve that requirement?
Soon I wrote the SQL statement:

/* 1. compare table structure */(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. compare table data */(select * from t_A minus select * from t_ B) union (select * from t_ B minus select * from t_A)

Let's take a look at the Running Effect of SQL:

Table t_A structure and data:

Table t_ B structure and data:

Table Structure differences:

Data difference:

Reflection: Why didn't I use the differential set before?
1. I have not learned well in mathematics and have no mathematical thinking. The concept of intersection and difference sets has long been learned in middle school, but I have not established mathematical thinking. So... I have to make up for mathematics ~
2. oracle functions are not familiar. It seems that I need to find an oracle function manual. If it is okay, I will flip it over.