Эта статья и её продолжение появились благодаря вопросам студентов на семинарах по СУБД. Продолжение будет посвящено рекурсивным запросам 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/ (операция произведенная над строкой).
Разбор решения.
/* Код решения с пояснениями. для понимания сформулируем вопрос так: какие операции выполнены над строками 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;
Файлы с текстом кода.