Use MINUS and OVER functions to directly compare two record sets through views

1 Preface
-------------------------------------------------------------
During the program design process, there are often differences between the two record sets. For example, identify the difference between the original order information and the subsequent order information, and display the difference data to the user.
There are multiple implementation methods, such as programming the stored procedure to return the cursor, and compare the two batches of data in the stored procedure... and so on. Of course, the returned differential data can be in a variety of ways, either a cursor, a temporary table, or other methods.
This article mainly discusses how to use the MINUS and OVER functions of ORACLE to directly compare two record sets through views.
-------------------------------------------------------------
2 implementation steps
-------------------------------------------------------------
2.1 use the MINUS function to determine the incremental differences between the original table and the comparison table. The two record sets exist in the form of tables, namely, table A and table B. Here, Table A is the original table, and table B is the comparison table generated later, that is, the data table to be compared with table A>
Incremental difference refers to the records in Table A that are not in Table B. That is to say, the records in Table A are modified or deleted.
 
2.2 Use the MINUS function to compare the incremental differences between the table and the original table
That is, the records existing in Table B, which do not exist in Table A, that is, the records added in Table B or modified in table.
 
2.3 connects the incremental differential table of the A-B and the incremental differential table of the B-A, and uses the OVER function to determine the number of data repetitions
If the number of data repetitions is 2, the record is marked as "modified ";
If the data repeat is 1 and appears in the incremental variance table for the A-B, the record is identified as deleted ";
If the data repeat is 1 and appears in the incremental variance table for the B-A, the record is identified as new"
 
-------------------------------------------------------------
3. instance Drill
-------------------------------------------------------------
-- 3.1 create A data table and an instance Environment. Set the original record set to data table A and the comparison record set to data table B. Of course, in actual application, the view is usually used for comparison, rather than the data table>
-- Test environment Configuration
Drop Table;
Drop Table B;
Create Table a (a1 Numeric (28), a2 Varchar2 (10 ));
Create Table B (b1 nUMERIC (28), b2 VarChar2 (10 ));
Insert Into a Values (1, 'A ');
Insert Into a Values (2, 'ba ');
Insert Into a Values (3, 'CA ');
Insert Into a Values (4, 'da ');
Insert Into B Values (1, 'A ');
Insert Into B Values (2, 'bba ');
Insert Into B Values (3, 'CA ');
Insert Into B Values (5, 'dda ');
Insert Into B Values (6, 'eda ');
Commit;
Select * from;
Select * From B;
 
-- 3.2 Create a comparison View
Create Or replace View VW_Test_Minus
-- Mark the number of repeated occurrences (Times = 1-> delete or add, Times = 2-> modify)
SELECT A1
, A2
, T -- id of Table A/Table B
, ROW_NUMBER () OVER (partition by A1 order by A1) RN -- Record repeat times
FROM
(
Select a1, a2, 'A' table T -- view records that exist in Table A and do not exist in Table B (modify or delete)
From
(
(Select * from)
Minus
(Select * From B)
) A2b
Union -- Union A records set different from Table B
Select b1, b2, 'B table' T -- view the existence of Table B and records not found in Table A (modify or add)
From
(
(Select * from B)
Minus
(Select * From)
) B2a
) F;
/
 
-- 3.3 comparison result set
Select a1
, A2
, T
, Rn
, Decode (Rn -- id record change
, 2, 'modify'
, Decode (T
, 'Table A', 'delete'
, 'Add') Mark
From VW_Test_Minus
Where Rn = (Select Count (*) From VW_Test_Minus V Where V. a1 = VW_Test_Minus.a1)
;
 
-------------------------------------------------------------
4 postscript
-------------------------------------------------------------
Many DBAs hate the coders who want to use a SELECT statement to implement complex user requirements. The MINUS and OVER functions are used to achieve data comparison and improve execution efficiency, there may be problems.
The purpose of this article is not to discuss the program running efficiency, but to give a discussion, which attracts the attention of the OVER function and the cognition of the MINUS function.

Author billdwl"