Table comparison, SQL, CTE. Сравнение таблиц.

Эта статья и её продолжение появились благодаря вопросам студентов на семинарах по СУБД. Продолжение будет посвящено рекурсивным запросам CTE. Статью готовил я, Присада Сергей Анатольевич, сейчас работаю в Финансовом университете при Правительстве РФ, почта sergey.prisada на яндексе.

Часто решаемая задача по сравнению наборов данных и поиску изменений. Все это решается простым SQL. В решении используется оператор работы со множествами MINUS, который, или его аналог, есть не во всех СУБД. Решение с JOIN будет в следующей статье.

В качестве примера создадим таблицу EMPLOYEES_TEST, заполним данными и создадим её копию EMPLOYEES_TEST_BKP. Затем удалим строки из исходной таблицы, обновим некоторые и вставим новые. Пример решается в Oracle DB. Если нет доступа, регистрируйтесь на apex.oracle.com или установите бесплатную Oracle XE. Код создания таблиц ниже в листинге.

-- создание исходной таблицы
CREATE TABLE "EMPLOYEES_TEST" ("EMPLOYEE_ID" NUMBER(6,0), "FIRST_NAME" VARCHAR2(20 CHAR), "LAST_NAME" VARCHAR2(25 CHAR), "EMAIL" VARCHAR2(25 CHAR), "PHONE_NUMBER" VARCHAR2(20 CHAR), "HIRE_DATE" DATE, "JOB_ID" VARCHAR2(10 CHAR), "SALARY" NUMBER(8,2), "COMMISSION_PCT" NUMBER(2,2), "MANAGER_ID" NUMBER(6,0), "DEPARTMENT_ID" NUMBER(4,0), "BONUS" VARCHAR2(5 CHAR));
REM INSERTING into EMPLOYEES_TEST
COMMIT;
SET DEFINE OFF;
-- вставка данных
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('100','Steven','King','SKING','515.123.4567',TO_DATE('17.06.02','DD.MM.RR'),'AD_PRES','24000',NULL,NULL,'90',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('101','Neena','Kochhar','NKOCHHAR','515.123.4568',TO_DATE('21.09.04','DD.MM.RR'),'AD_VP','17000',NULL,'100','90',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('102','Lex','De Haan','LDEHAAN','515.123.4569',TO_DATE('13.01.08','DD.MM.RR'),'AD_VP','17000',NULL,'100','90',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('200','Jennifer','Whalen','JWHALEN','515.123.4444',TO_DATE('17.09.02','DD.MM.RR'),'AD_ASST','4400',NULL,'101','10',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('205','Shelley','Higgins','SHIGGINS','515.123.8080',TO_DATE('07.06.09','DD.MM.RR'),'AC_MGR','12000',NULL,'101','110',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('206','William','Gietz','WGIETZ','515.123.8181',TO_DATE('07.06.09','DD.MM.RR'),'AC_ACCOUNT','8300',NULL,'205','110',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('149','Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018',TO_DATE('29.01.15','DD.MM.RR'),'SA_MAN','10500','0,2','100','80','1500');
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('174','Ellen','Abel','EABEL','011.44.1644.429267',TO_DATE('11.05.11','DD.MM.RR'),'SA_REP','11000','0,3','149','80','1700');
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('176','Jonathon','Taylor','JTAYLOR','011.44.1644.429265',TO_DATE('24.03.13','DD.MM.RR'),'SA_REP','8600','0,2','149','80','1250');
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('178','Kimberely','Grant','KGRANT','011.44.1644.429263',TO_DATE('24.05.14','DD.MM.RR'),'SA_REP','7000','0,15','149',NULL,NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('124','Kevin','Mourgos','KMOURGOS','650.123.5234',TO_DATE('16.11.14','DD.MM.RR'),'ST_MAN','5800',NULL,'100','50',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('141','Trenna','Rajs','TRAJS','650.121.8009',TO_DATE('17.10.10','DD.MM.RR'),'ST_CLERK','3500',NULL,'124','50',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('142','Curtis','Davies','CDAVIES','650.121.2994',TO_DATE('29.01.12','DD.MM.RR'),'ST_CLERK','3100',NULL,'124','50',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('143','Randall','Matos','RMATOS','650.121.2874',TO_DATE('15.03.13','DD.MM.RR'),'ST_CLERK','2600',NULL,'124','50',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('144','Peter','Vargas','PVARGAS','650.121.2004',TO_DATE('09.07.13','DD.MM.RR'),'ST_CLERK','2500',NULL,'124','50',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('103','Alexander','Hunold','AHUNOLD','590.423.4567',TO_DATE('03.01.05','DD.MM.RR'),'IT_PROG','9000',NULL,'102','60',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('104','Bruce','Ernst','BERNST','590.423.4568',TO_DATE('21.05.06','DD.MM.RR'),'IT_PROG','6000',NULL,'103','60',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('107','Diana','Lorentz','DLORENTZ','590.423.5567',TO_DATE('07.02.14','DD.MM.RR'),'IT_PROG','4200',NULL,'103','60',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('201','Michael','Hartstein','MHARTSTE','515.123.5555',TO_DATE('17.02.11','DD.MM.RR'),'MK_MAN','13000',NULL,'100','20',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('202','Pat','Fay','PFAY','603.123.6666',TO_DATE('17.08.13','DD.MM.RR'),'MK_REP','3900',NULL,'201','20',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('207','Sophia','Barbosa Souza','SBARBOSASOUZA','55.24.6280.6935',TO_DATE('12.03.09','DD.MM.RR'),'SR_SA_REP','9500','0,2','149','85',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('208','Diego','Silva Pinto','DSILVAPINTO','55.24.8806.9837',TO_DATE('25.10.09','DD.MM.RR'),'SA_REP','7500','0,15','149','85',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('209','Sarah','Alves Rocha','SALVESROCHA','55.24.9381.2160',TO_DATE('06.02.11','DD.MM.RR'),'SA_REP','7300','0,15','149','85',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('210','Lucas','Almeida Castro','ALMEIDACASTRO','55.24.6514-5378',TO_DATE('16.08.12','DD.MM.RR'),'SA_REP','7300','0,2','149','85',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('212','Nick','Hooper','NHOOPER','011.44.1886 6663',TO_DATE('01.09.12','DD.MM.RR'),'SR_SA_REP','9600','0,2','149','80',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('215','Donna','Steiner','DSTEINER','843.452.5959',TO_DATE('02.11.04','DD.MM.RR'),'SR_MK_REP','8600',NULL,'201','20',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('216','George','Bell','GBELL','724.647.4299',TO_DATE('01.04.14','DD.MM.RR'),'SR_ST_CLRK','3500',NULL,'124','50',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('217','Lisa','TAYLOR','LTAYLOR','417.730.8202',TO_DATE('09.02.13','DD.MM.RR'),'MK_REP','4000',NULL,'201','20',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('219','Michael','Stocks','MSTOCKS','662.314.6844',TO_DATE('16.12.15','DD.MM.RR'),'MK_REP','3700',NULL,'201','20',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('220','Tiffany','Heiden','THEIDEN','413.251.0684',TO_DATE('06.07.15','DD.MM.RR'),'ST_CLERK','2600',NULL,'124','50',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('222','Chen','Li','CLI','423.817.1481',TO_DATE('29.08.08','DD.MM.RR'),'IT_PROG','8000',NULL,'103','60',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('223','Alain','Fontaine','AFONTAINE','601.424.8816',TO_DATE('18.11.13','DD.MM.RR'),'IT_PROG','7800',NULL,'103','60',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('224','Matthias','Reinhard','MREINHARD','315.496.2466',TO_DATE('25.07.07','DD.MM.RR'),'AC_ACCOUNT','8100',NULL,'205','110',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('225','Katia','Hernandez','KHERNANDEZ','214.350.4575',TO_DATE('13.06.11','DD.MM.RR'),'AD_ASST','4300',NULL,'101','10',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('226','Guido','Ricci','GRICCI','305.269.9415',TO_DATE('17.05.15','DD.MM.RR'),'AD_ASST','4100',NULL,'101','10',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('227','Mizuto','Saikawa','MSAIKAWA','541.831.2444',TO_DATE('01.02.12','DD.MM.RR'),'AD_ASST','4400',NULL,'101','10',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('228','Nabil','Safwah','NSAFWAH','720.863.0485',TO_DATE('06.01.97','DD.MM.RR'),'MK_REP','5000',NULL,'201','20',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('231','Jelena','Duric','JDURIC','505.514.1583',TO_DATE('11.05.09','DD.MM.RR'),'AC_ACCOUNT','5400',NULL,'205','110',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('232','Jennifer','Loermans','JLOERMANS','213.447.9053',TO_DATE('14.04.13','DD.MM.RR'),'AC_ACCOUNT','5200',NULL,'205','110',NULL);
INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('235','Alice','Newton','ANEWTON','314.248.7709',TO_DATE('16.12.15','DD.MM.RR'),'MK_REP','4900',NULL,'201','20',NULL);
COMMIT;
ALTER TABLE "EMPLOYEES_TEST" MODIFY ("LAST_NAME" NOT NULL ENABLE);
ALTER TABLE "EMPLOYEES_TEST" MODIFY ("EMAIL" NOT NULL ENABLE);
ALTER TABLE "EMPLOYEES_TEST" MODIFY ("HIRE_DATE" NOT NULL ENABLE);
ALTER TABLE "EMPLOYEES_TEST" MODIFY ("JOB_ID" NOT NULL ENABLE);
COMMIT;
-- создание копии основной таблицы
CREATE TABLE EMPLOYEES_TEST_BKP AS SELECT * FROM EMPLOYEES_TEST; 
COMMIT;

Изменим исходную таблицу.

-- удалим строки по условию
DELETE FROM EMPLOYEES_TEST WHERE PHONE_NUMBER LIKE '%590%';
3 rows deleted.

-- обновим строки по условию
UPDATE EMPLOYEES_TEST SET SALARY = '30000'
WHERE PHONE_NUMBER LIKE '%23%';
10 rows updated.

-- вставим строки
INSERT INTO EMPLOYEES_TEST 
(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('300','Nabil','Safwah','NSAFWAH','720.863.0485',TO_DATE('26.01.01','DD.MM.RR'),'MK_REP','5000',NULL,'201','20',NULL);
1 row inserted.

INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('310','Jelena','Duric','JDURIC','505.514.1583',TO_DATE('21.05.03','DD.MM.RR'),'AC_ACCOUNT','6400',NULL,'205','110',NULL);
1 row inserted.

INSERT INTO EMPLOYEES_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,BONUS) VALUES ('320','Jennifer','Loermans','JLOERMANS','213.447.9053',
TO_DATE('24.04.03','DD.MM.RR'),'AC_ACCOUNT','7200',NULL,'205','110',NULL);
1 row inserted.

Теперь две таблицы отличаются. 

Задача: найти различающийся строки и вид CRUD операции над ними с применением SQL. Или так: какие операции выполнены над строками EMPLOYEES_TEST.

Код решения:

/* 
Код решения 
*/

WITH 
SRC AS
(SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, 
               EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, 
               COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID, BONUS
   FROM EMPLOYEES_TEST),

TARG AS
(SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, 
               EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, 
               COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID, BONUS
   FROM EMPLOYEES_TEST_BKP),
        
DELETED AS 
(SELECT * FROM
     (SELECT TARG.EMPLOYEE_ID FROM TARG
       MINUS
      SELECT SRC.EMPLOYEE_ID FROM SRC)),
       
INS AS 
(SELECT * FROM
      (SELECT SRC.EMPLOYEE_ID FROM SRC
        MINUS
       SELECT TARG.EMPLOYEE_ID FROM TARG)),

UPD AS
(SELECT * FROM
       (SELECT SRC.* FROM SRC
         MINUS
        SELECT TARG.* FROM TARG)
         WHERE EMPLOYEE_ID NOT IN (SELECT EMPLOYEE_ID FROM DELETED 
                                    UNION ALL 
                                   SELECT EMPLOYEE_ID FROM INS))

SELECT TARG.*, 'D' AS OPER FROM TARG WHERE TARG.EMPLOYEE_ID IN (SELECT DELETED.EMPLOYEE_ID FROM DELETED)
   UNION ALL
SELECT SRC.*, 'I' AS OPER FROM SRC WHERE SRC.EMPLOYEE_ID IN (SELECT INS.EMPLOYEE_ID FROM INS)
   UNION ALL
SELECT UPD.*, 'U' AS OPER FROM UPD;

Выполним запрос и видим результат, например строки EMPLYEE_ID 300,310,320 с атрибутом OPER = I /INSERT/ (операция произведенная над строкой).

2 
3 
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL 
ST_MAN 
515.123 
PHONE_NUMBER HIRE_DATE CJOB_ID 
SAI-ARY 
9000 
6000 
42ee 
5000 
6400 
7200 
3eeee 
30000 
3eeee 
30000 
3eeee 
30000 
3eeee 
30000 
3eeee 
30000 
COMMISSION PCT 
(NULL 
(NULL 
(NULL 
(NULL 
(NULL 
(NULL 
(NULL) 
(NULL 
(NULL 
(NULL 
(NULL 
(NULL 
(NULL 
(NULL 
(NULL 
(NULL 
MANAGER_ID 
102 
103 
103 
201 
205 
205 
L) 
lee 
lee 
lee 
101 
lee 
201 
101 
205 
103 
DEPARTMENT 
103 Alexander 
104 Bruce 
107 Diana 
300 
310 
6 —Jennifer 
Hunold 
Ernst 
Lo rentz 
Safwah 
Duric 
Loe rmans 
King 
Kochhar 
De Haan 
Mourgos 
Whalen 
Ha rtstein 
Fay 
Higgins 
Gietz 
Li 
AHUNOLD 
BERNST 
DLORENTZ 
NSAFWAH 
JDURIC 
SKING 
NKOCHHAR 
LDEHAAN 
KMOURGOS 
JWHALEN 
MHARTSTE 
PFAY 
SHIGGINS 
WGIETZ 
CLI 
JLOERMANS 213.447. 9053 
7 
8 
9 
10 
11 
12 
13 
14 
15 
16 
100 Steven 
101 Neena 
102 Lex 
124 Kevin 
2eø Jennifer 
201 Michael 
202 Pat 
205 Shelley 
206 
222 Chen 
590.423.4567 
590.423.4568 
590.423. 5567 
72e.863.0485 
505. 514. 1583 
515. 123.4567 
515. 123.4568 
515. 123.4569 
650. 123. 5234 
515.123. 5555 
603. 123.6666 
515. 123.8080 
515.123.8181 
423.817. 1481 
.4444 
03.01.05 
21.05.06 
07.02.14 
26.01. ei 
21.05.03 
24.04.03 
17 • 06 • 02 
21.09.04 
13. ei. 08 
16.11.14 
17.09.02 
17.02.11 
17.08.13 
07.06.09 
29.08.08 
AC 
AC _ 
AD 
AD 
AC 
AC 
PROG 
PROG 
PROG 
REP 
ACCOUNT 
ACCOUNT 
PRES 
ASST 
MAN 
REP 
MGR 
ACCOUNT 
PROG 
_ID BONUS COPER 
60 (NULL) 
60 (NULL) 
60 (NULL) 
20 (NULL) 
Ile (NULL) 
Ile (NULL) 
ge (NULL) 
(NULL) 
ge (NULL) 
50 (NULL) 
le (NULL) 
20 (NULL) 
20 (NULL) 
Ile (NULL) 
Ile (NULL) 
60 (NULL)

Разбор решения.

/*
Код решения с пояснениями.
для понимания сформулируем вопрос так: 
какие операции выполнены над строками EMPLOYEES_TEST

*/


WITH 

/* 
выбрать все строки из измененной таблицы, псевдоним SRC 
*/
SRC AS
(SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, 
	        PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT,
	        MANAGER_ID, DEPARTMENT_ID, BONUS
   FROM EMPLOYEES_TEST), 

/* 
выбрать все строки из копии исходной таблицы, псевдоним TARG 
*/
TARG AS
(SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, 
	        PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT,
	        MANAGER_ID, DEPARTMENT_ID, BONUS

   FROM EMPLOYEES_TEST_BKP),

/* 
если вычесть все первичные ключи исходной таблицы из измененной, то останутся удаленные строки
используем оператор работы со множествами MINUS
*/      
DELETED AS 
(SELECT * FROM
     (SELECT TARG.EMPLOYEE_ID FROM TARG
       MINUS
      SELECT SRC.EMPLOYEE_ID FROM SRC)), 
/* 
если вычесть все первичные ключи измененной таблицы из исходной, то останутся добавленные строки
используем оператор работы со множествами MINUS
*/           
INS AS 
(SELECT * FROM
      (SELECT SRC.EMPLOYEE_ID FROM SRC
        MINUS
       SELECT TARG.EMPLOYEE_ID FROM TARG)),

/* 
если вычесть строки (все атрибуты) исходной таблицы из измененной, первичные ключи которых не входят 
ни в удаленные ни в добавленные строки, то результатом будут обновленные строки
используем оператор работы со множествами MINUS для нахождения различающихся строк, с условием что их
первичные ключи не содержатся в объединенном множестве первичных ключей вычисленных в CTE DELETED и INS
объединенное множество получаем оператором UNION ALL
*/      
UPD AS
(SELECT * FROM
       (SELECT SRC.* FROM SRC
         MINUS
        SELECT TARG.* FROM TARG)
         WHERE EMPLOYEE_ID NOT IN (SELECT EMPLOYEE_ID FROM DELETED 
                                    UNION ALL 
                                   SELECT EMPLOYEE_ID FROM INS))

/* 
каждой строке из соответствующего CTE при выборке SELECT добавляем столбец OPER с типом CRUD операции 
I -  для INSERT, вставленных строк
U - для UPDATED, обновленных строк
D - для DELETED, удаленных строк
Объединяем все в один набор оператором работы со множествами UNION ALL, поскольку типы множеств совпадают (наименования,
 количество и типы данных столбцов одинаковые)
*/

/* удаленные строки остались только в TARG, их первичные ключи вычислены в CTE DELETED */
SELECT TARG.*, 'D' AS OPER FROM TARG WHERE TARG.EMPLOYEE_ID IN (SELECT DELETED.EMPLOYEE_ID FROM DELETED)

   UNION ALL

/* вставленные строки только в SRC, их первичные ключи вычислены в CTE INS */
SELECT SRC.*, 'I' AS OPER FROM SRC WHERE SRC.EMPLOYEE_ID IN (SELECT INS.EMPLOYEE_ID FROM INS)

   UNION ALL

/* обновленные строки полностью вычислены в UPD */
SELECT UPD.*, 'U' AS OPER FROM UPD;

Файлы с текстом кода.

Close Menu