Сложное ограничение целостности (constraint) — 2

2016-04-24 в 22:01, admin, рубрики: oracle, oracle sql

Конструктивные комментарии общества к посту Сложное ограничение целостности (constraint) в котором была представлена реализация сложного констрейнта БД ORACLE заставили глубже погрузиться в тему. Тем более что функционал стоит в боевой эксплуатации и оставлять в нем шероховатости было бы некрасиво.
Что нового.
Похоже удалось обеспечить многопользовательский режим. Уважаемый xtender совершенно справедливо отметил этот недостаток предыдущей реализации.
Бизнесправила контрольного примера ( сферический слон ) на тестовом наборе данных ( в вакууме ) усложнены. Более сложный пример нагляднее отражает не самую плохую масштабируемость решения — текст триггеров на разных таблицах практически идентичен. Добавление любого количества новых бизнесправил без изменения состава участвующих таблиц (emp, dept, emp_attr_vals) потребует модификации только функции emp_logic. Наверно гуру смогут реализовать этот вариант констрейнта по ТомуК (fast refresh mview) но всему есть предел. Сам Том считает что когда задачу не реализовать одним sql (mview) пора решать на plsql.
У предлагаемого решения есть недостаток — не обкатано на продакшн под нагрузкой.

Реализация.
Таблицы бизнесинформации.

CREATE TABLE "DEPT" 
("DEPTNO" NUMBER(2,0),
 "DNAME" VARCHAR2(14),
 "LOC" VARCHAR2(13)
);
CREATE UNIQUE INDEX "DEPT_PK" ON "DEPT" ("DEPTNO");
ALTER TABLE "DEPT" ADD CONSTRAINT "DEPT_PK" PRIMARY KEY ("DEPTNO") ENABLE;

Данные

Insert into DEPT (DEPTNO,DNAME,LOC) values ('10','ACCOUNTING','NEW YORK');
Insert into DEPT (DEPTNO,DNAME,LOC) values ('20','RESEARCH','DALLAS');
Insert into DEPT (DEPTNO,DNAME,LOC) values ('30','SALES','CHICAGO');
Insert into DEPT (DEPTNO,DNAME,LOC) values ('31','OPERATIONS','CHICAGO');

 CREATE TABLE "EMP"  (
	"EMPNO" NUMBER(4,0), 
	"ENAME" VARCHAR2(10), 
	"JOB" VARCHAR2(9), 
	"MGR" NUMBER(4,0), 
	"HIREDATE" DATE, 
	"COMM" NUMBER(17,2), 
	"DEPTNO" NUMBER(2,0)
);

Данные

Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7369','SMITH','CLERK','7902',to_date('17.12.1980','DD.MM.RRRR '),'100','20');
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7499','ALLEN','SALESMAN','7698',to_date('20.02.1981','DD.MM.RRRR '),'300','31');
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7521','WARD','SALESMAN','7698',to_date('22.02.1981','DD.MM.RRRR '),'10','30');
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7566','JONES','MANAGER','7777',to_date('02.04.1981','DD.MM.RRRR '),'4','20');
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7698','BLAKE','MANAGER','7777',to_date('01.05.1981','DD.MM.RRRR '),null,'30');
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7782','CLARK','MANAGER','7777',to_date('09.06.1981','DD.MM.RRRR '),null,'10');
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7708','SCOTT','ANALYST','7566',to_date('09.12.1982','DD.MM.RRRR '),'7708','20');
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7777','KING','PRESIDENT',null,to_date('17.11.1981','DD.MM.RRRR '),null,'10');
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7844','TURNER','SALESMAN','7698',to_date('10.09.1981','DD.MM.RRRR '),'0','30');
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7876','ADAMS','CLERK','7639',to_date('12.01.1982','DD.MM.RRRR '),null,'20');
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7900','JAMES','CLERK','7698',to_date('03.12.1981','DD.MM.RRRR '),null,'31');
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7902','FORD','ANALYST','7566',to_date('03.12.1981','DD.MM.RRRR '),null,'20');
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7934','MILLER','CLERK','7782',to_date('23.01.1982','DD.MM.RRRR '),null,'10');

CREATE UNIQUE INDEX "ЕMР_РK" ON "EMP" ("EMPNO");
ALTER TABLE "EMP" MODIFY ("EMPNO" NOT NULL ENABLE);
ALTER TABLE "EMP" ADD CONSTRAINT "ЕMР_РK" PRIMARY KEY ("EMPNO") ENABLE;
 ALTER TABLE "EMP" ADD CONSTRAINT "EMP_FK_DEPT" FOREIGN KEY ("DEPTNO")
	  REFERENCES "DEPT" ("DEPTNO") ENABLE;

CREATE TABLE "EMP_ATTR_VALS" (
	"DATE_BEG" DATE,
	"DATE_END" DATE,
	"VAL" VARCHAR2(64),
	"EMP_ATTR" VARCHAR2(32),
	"EMPNO" NUMBER
);
ALTER TABLE "EMP_ATTR_VALS" MODIFY ("DATE_BEG" NOT NULL ENABLE);
ALTER TABLE "EMP_ATTR_VALS" MODIFY ("DATE_END" NOT NULL ENABLE);
ALTER TABLE "EMP_ATTR_VALS" MODIFY ("VAL" NOT NULL ENABLE);
ALTER TABLE "EMP_ATTR_VALS" MODIFY ("EMP_ATTR" NOT NULL ENABLE);
ALTER TABLE "EMP_ATTR_VALS" MODIFY ("EMPNO" NOT NULL ENABLE);
ALTER TABLE "EMP_ATTR_VALS" ADD CONSTRAINT "EMP_AV_FK"
          FOREIGN KEY ("EMPNO") REFERENCES "EMP" ("EMPNO") ENABLE;

Данные

Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('09.01.2016','DD.MM.RRRR'),'100','SALARY','7369');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('10.01.2016','DD.MM.RRRR'),to_date('29.02.2016','DD.MM.RRRR'),'100','SALARY','7369');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.03.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'120','SALARY','7369');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2016','DD.MM.RRRR'),'A','EDUCATION','7369');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'B','EDUCATION','7369');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('09.01.2016','DD.MM.RRRR'),'100','SALARY','7499');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('10.01.2016','DD.MM.RRRR'),to_date('29.02.2016','DD.MM.RRRR'),'100','SALARY','7499');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.03.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'120','SALARY','7499');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2016','DD.MM.RRRR'),'A','EDUCATION','7499');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'B','EDUCATION','7499');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('09.01.2016','DD.MM.RRRR'),'100','SALARY','7876');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('10.01.2016','DD.MM.RRRR'),to_date('29.02.2016','DD.MM.RRRR'),'100','SALARY','7876');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.03.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'120','SALARY','7876');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2016','DD.MM.RRRR'),'A','EDUCATION','7876');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'B','EDUCATION','7876');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('09.01.2016','DD.MM.RRRR'),'100','SALARY','7900');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('10.01.2016','DD.MM.RRRR'),to_date('29.02.2016','DD.MM.RRRR'),'100','SALARY','7900');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.03.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'120','SALARY','7900');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2016','DD.MM.RRRR'),'A','EDUCATION','7900');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'B','EDUCATION','7900');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('09.01.2016','DD.MM.RRRR'),'100','SALARY','7934');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('10.01.2016','DD.MM.RRRR'),to_date('29.02.2016','DD.MM.RRRR'),'100','SALARY','7934');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.03.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'120','SALARY','7934');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2016','DD.MM.RRRR'),'A','EDUCATION','7934');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'B','EDUCATION','7934');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('29.02.2016','DD.MM.RRRR'),'150','SALARY','7521');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.03.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'180','SALARY','7521');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2016','DD.MM.RRRR'),'B','EDUCATION','7521');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'C','EDUCATION','7521');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2017','DD.MM.RRRR'),'150','SALARY','7844');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2017','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'200','SALARY','7844');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2016','DD.MM.RRRR'),'B','EDUCATION','7844');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'D','EDUCATION','7844');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.05.2016','DD.MM.RRRR'),'200','SALARY','7708');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.05.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'300','SALARY','7708');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.07.2016','DD.MM.RRRR'),'B','EDUCATION','7708');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.07.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'D','EDUCATION','7708');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.05.2016','DD.MM.RRRR'),'200','SALARY','7902');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.05.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'300','SALARY','7902');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'D','EDUCATION','7902');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.05.2016','DD.MM.RRRR'),'350','SALARY','7566');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.05.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'400','SALARY','7566');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'D','EDUCATION','7566');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.05.2016','DD.MM.RRRR'),'350','SALARY','7698');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.05.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'400','SALARY','7698');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'D','EDUCATION','7698');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.05.2016','DD.MM.RRRR'),'350','SALARY','7782');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.05.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'450','SALARY','7782');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'F','EDUCATION','7782');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'10000','SALARY','7777');
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'A','EDUCATION','7777');

Таблица лога.

CREATE TABLE "EMP_CHK_LOG" (
	"TS" TIMESTAMP (6),
	"STATUS" NUMBER,
	"XML" CLOB,
	"ERR_TXT" VARCHAR2(512)
);

Для работы с транзакциями схеме c примером (ZX) потребуется некоторый доступ к схеме SYS.

CREATE OR REPLACE VIEW sys.v_transaction AS  
    SELECT xidusn, xidslot, xidsqn,ses_addr, start_scn
    FROM v$transaction;
GRANT SELECT ON sys.v_transaction TO zx;
GRANT EXECUTE on sys. dbms_transaction TO zx;
GRANT CREATE MATERIALIZED VIEW TO zx;

Базовая таблица матвью.

CREATE TABLE "EMP_CHK" (
"EMP_NO" NUMBER,
"LTRANS_ID" VARCHAR2(64));

CREATE UNIQUE INDEX "EMP_CHK_PK" ON "EMP_CHK" ("LTRANS_ID");
ALTER TABLE "EMP_CHK" ADD CONSTRAINT "EMP_CHK_PK"
	PRIMARY KEY ("LTRANS_ID") ENABLE;
ALTER TABLE "EMP_CHK" MODIFY ("LTRANS_ID" NOT NULL ENABLE);

Создаем materialized view.

CREATE MATERIALIZED VIEW chk_emp_mv
  REFRESH COMPLETE ON COMMIT AS
      SELECT emp_no, ltrans_id FROM emp_chk;

Многопользовательский режим работы обеспечен следующим образом. В начале каждой транзакции из базовой таблицы матвью процедурой var_chk.cls_emp_chk информация завершенных транзакций удаляются построчно. Использование автономной транзакции обеспечивает очистку независимо от исхода (commit/rollback) текущей транзакции. В предыдущем варианте очищалась а следовательно и блокировалась вся таблица до окончания текущей транзакции.

create or replace PACKAGE var_chk
AS
  TYPE tab_str  IS TABLE OF  VARCHAR2(64);
  first_dml_in_commit NUMBER;
  var_emp_val CLOB;

  FUNCTION chk_on  RETURN NUMBER;
  PROCEDURE write_log (p_status NUMBER
                      ,p_err_txt VARCHAR2);
  PROCEDURE cls_emp_chk;                      
END var_chk;
create or replace PACKAGE BODY var_chk
AS
--------------------
FUNCTION chk_on  RETURN NUMBER IS
-- Возвращает 1 - контроль включен
--            0 - контроль выклечен           
BEGIN
  RETURN 1;
END chk_on;
---------------------
PROCEDURE write_log (p_status NUMBER
                    ,p_err_txt VARCHAR2) is
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

INSERT INTO emp_chk_log
 (ts,status,xml,err_txt)
  VALUES (sysdate,p_status,var_emp_val,SUBSTR(p_err_txt,1,512));

COMMIT;
END write_log;
---------------------
PROCEDURE cls_emp_chk IS
    v_trans_arr tab_str := tab_str();
    v_i NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;    
BEGIN
FOR cur1 IN (
    SELECT  ltrans_id FROM   emp_chk) loop
   v_trans_arr.extend;
   v_trans_arr(v_trans_arr.last) := cur1.ltrans_id;   
END LOOP;    

FOR i IN 1 .. v_trans_arr.count
LOOP    
    SELECT count(*) INTO v_i  
    FROM sys.v_transaction t
    WHERE  to_char(t.xidusn)||'.'||to_char(t.xidslot)||'.'||to_char(t.xidsqn)
          = v_trans_arr(i);
    IF v_i = 0  
    THEN
     DELETE FROM  emp_chk WHERE ltrans_id = v_trans_arr(i);
    END IF;
COMMIT;    
END LOOP;  

END;
---------------------
BEGIN
  first_dml_in_commit :=1;
  dbms_lob.createtemporary(var_emp_val,true);
END var_chk;

create or replace TRIGGER dept_bs
  BEFORE DELETE OR INSERT OR UPDATE
  ON  dept
BEGIN
IF var_chk.chk_on != 1 THEN
  RETURN;
END IF;

IF var_chk.first_dml_in_commit = 1 
THEN VAR_CHK.cls_EMP_CHK;
END IF;
var_chk.first_dml_in_commit := 0 ;

END dept_bs;

create or replace TRIGGER emp_bs
  BEFORE DELETE OR INSERT OR UPDATE
  ON  emp
BEGIN
IF var_chk.chk_on != 1 THEN
  RETURN;
END IF;

IF var_chk.first_dml_in_commit = 1
THEN var_chk.cls_emp_chk;
END IF;
var_chk.first_dml_in_commit := 0;

END emp_bs;

create or replace TRIGGER emp_attr_vals_bs
  BEFORE DELETE OR INSERT OR UPDATE
  ON  emp_attr_vals
BEGIN
IF var_chk.chk_on != 1 THEN
  RETURN;
END IF;

IF var_chk.first_dml_in_commit = 1
THEN var_chk.cls_emp_chk;
END IF;
var_chk.first_dml_in_commit := 0 ;

END emp_attr_vals_bs;

По каждому DMLю на любой из таблиц бизнесинформации в базовую таблицу матвью пишется идентификатор контролируемой сущности (в нашем примере сотрудник) и идентификатор текущей транзакции.

create or replace TRIGGER emp_ar
  AFTER DELETE OR INSERT OR UPDATE
  ON  emp
  FOR EACH ROW
BEGIN
IF var_chk.chk_on != 1 THEN
  RETURN;
END IF;

MERGE INTO emp_chk a
 USING (SELECT nvl(:new.empno, :old.empno) AS emp_no,
               sys.dbms_transaction.local_transaction_id  AS ltrans_id
          FROM dual) b
 ON (a.emp_no = b.emp_no)
 WHEN NOT MATCHED THEN  
   INSERT (a.emp_no, a.ltrans_id) VALUES (b.emp_no, b.ltrans_id)
 WHEN  MATCHED THEN  
   UPDATE SET a.ltrans_id = b.ltrans_id;
END emp_ar;

create or replace TRIGGER dept_ar
  AFTER DELETE OR INSERT OR UPDATE
  ON  dept
  FOR  EACH  ROW
BEGIN
IF var_chk.chk_on != 1 THEN
  RETURN;
END IF;

MERGE INTO emp_chk a
 USING (
    SELECT emp.empno AS emp_no,
           dbms_transaction.local_transaction_id AS ltrans_id
     FROM emp 
     WHERE emp.deptno = NVL(:new.deptno, :old.deptno)
        ) b
 ON (a.emp_no = b.emp_no)
 WHEN NOT MATCHED THEN  
     INSERT (a.emp_no, a.ltrans_id) VALUES (b.emp_no, b.ltrans_id)
 WHEN  MATCHED THEN  
     UPDATE SET a.ltrans_id = b.ltrans_id;
END dept_ar;

create or replace TRIGGER emp_attr_vals_ar
  AFTER DELETE OR INSERT OR UPDATE
  ON  emp_attr_vals
  FOR  EACH  ROW
BEGIN
IF var_chk.chk_on != 1 THEN
  RETURN;
END IF;

MERGE INTO emp_chk a
 USING (
    SELECT emp.empno AS emp_no,
           dbms_transaction.local_transaction_id AS ltrans_id
      FROM emp 
     WHERE emp.empno = NVL(:new.empno, :old.empno)
        ) b
 ON (a.emp_no = b.emp_no)
 WHEN NOT MATCHED THEN  
   INSERT (a.emp_no, a.ltrans_id) VALUES (b.emp_no, b.ltrans_id)
 WHEN  MATCHED THEN  
   UPDATE SET a.ltrans_id = b.ltrans_id;
END emp_attr_vals_ar;

Функция бизнесконтроля.

create or replace FUNCTION emp_logic
                 (p_emp_no NUMBER
                 ,p_errtxt OUT VARCHAR2 )
 RETURN NUMBER IS
 v_emp_count NUMBER;
 v_emp_loc VARCHAR2(16);
 v_job VARCHAR2(32);
 v_sal NUMBER;
BEGIN
-----------------------------------
SELECT dept.loc,COUNT(*) 
  INTO v_emp_loc,v_emp_count
FROM emp, dept
WHERE emp.deptno = dept.deptno
  AND dept.LOC in (
                SELECT dept.loc 
                  FROM emp, dept
                 WHERE emp.deptno = dept.deptno
                   AND emp.empno = p_emp_no
                 )
  AND emp.job = 'CLERK'
GROUP BY dept.loc;

IF v_emp_count > 2 
THEN p_errtxt:='Ошибка. В филиале '||v_emp_loc||' больше 2х клерков';
       RETURN 1;
END IF;
-------------------------------------
FOR cur_d IN (
SELECT SUM(delt) sdelt, emp_attr
FROM(
     SELECT t.date_beg, t.date_end,t.emp_attr,
            NVL(lead(t.date_beg) 
                  over(partition by t.empno, t.emp_attr
                       order by t.date_beg, t.date_end)-1,
                t.date_end) - t.date_end  delt
       FROM emp_attr_vals t
      WHERE t.empno = p_emp_no
    )
 GROUP BY emp_attr
) LOOP
IF cur_d.sdelt != 0 
THEN p_errtxt:='Ошибка. Пересечение дат в истории параметра '||cur_d.emp_attr;
       RETURN 1;
END IF;
END LOOP;
---------------------------------
SELECT emp.job,
      nvl((SELECT to_number(val) FROM emp_attr_vals eav
            WHERE eav.emp_attr = 'SALARY'
              AND SYSDATE BETWEEN eav.date_beg AND eav.date_end
              AND emp.empno = eav.empno
          ),0) sal
INTO  v_job ,v_sal
FROM emp 
WHERE emp.empno = p_emp_no;
IF v_job != 'PRESIDENT' AND  v_sal > 999
THEN p_errtxt:='Ошибка. Только президент может иметь зарплату больше 999';
       RETURN 1;
END IF;

RETURN 0;

END emp_logic;

Триггера на матвью.

Запускающий очистку данных предыдущей транзакции из базовой таблицы матвью.

create or replace TRIGGER chk_emp_mv_bs
  BEFORE DELETE OR INSERT OR UPDATE
  ON  chk_emp_mv
BEGIN
IF var_chk.chk_on != 1 THEN
  RETURN;
END IF;
  var_chk.first_dml_in_commit := 1 ;
END chk_emp_mv_bs;

Запускающий функцию бизнесконтроля и пишущий в лог.

create or replace TRIGGER chk_emp_mv_as
  AFTER DELETE OR INSERT OR UPDATE
  ON chk_emp_mv
DECLARE
  v_result NUMBER;
  v_errtxt VARCHAR2(512);
  v_ltrans_id VARCHAR2(64);
BEGIN
IF var_chk.chk_on != 1 
THEN  RETURN;
END IF;

v_ltrans_id:=dbms_transaction.local_transaction_id;

FOR cur IN (SELECT t.emp_no FROM chk_emp_mv t where t.ltrans_id=v_ltrans_id) LOOP
SELECT
XMLELEMENT("EMP",
XMLAGG(XMLELEMENT("EMPNO",tb.empno,
         XMLELEMENT("ENAME", tb.ename),
         XMLELEMENT("JOB", tb.job),
         XMLELEMENT("MGR", tb.mgr),
         XMLELEMENT("DEPTNO", tb.deptno),
         XMLELEMENT("COMM", tb.comm),
         XMLELEMENT("HIREDATE", TO_CHAR(tb.hiredate,'dd.mm.yyyy'))
         ,(SELECT
             XMLELEMENT("DEPT",
               XMLAGG(XMLELEMENT("DEPTNO",d.deptno,
                      XMLELEMENT("DNAME",d.dname),
                      XMLELEMENT("LOC",d.loc)
                        )))
               FROM dept d WHERE d.deptno = tb.deptno
         )
        ,(SELECT
            XMLELEMENT("ATTR_VAL",
               XMLAGG(XMLELEMENT("EMP_ATTR",k.emp_attr,
                      XMLELEMENT("VAL",k.val),
                      XMLELEMENT("DATE_BEG",k.date_beg),
                      XMLELEMENT("DATE_END",k.date_end)
                        )))
               FROM emp_attr_vals k 
               WHERE k.empno = tb.empno  --7369
        )         
  ))
).GETCLOBVAL() INTO var_chk.var_emp_val
FROM (
      SELECT * FROM EMP
       WHERE emp.empno = cur.emp_no
     ) tb
GROUP BY empno,mgr,job,hiredate,ename,deptno,comm;

-- бизнес логика
v_result := 0;
v_result := emp_logic(cur.emp_no,v_errtxt);
var_chk.write_log(v_result,v_errtxt);
IF v_result = 1 THEN
  RAISE_APPLICATION_ERROR (-20555,v_errtxt);
END IF;

END LOOP;

END chk_emp_mv_as;

Проверим велосипед на ходу.
Проверять будем в два сеанса. Для контроля процесса в приглашение plus выведено текущее время, а в скобках sid текущего сеанса.
Тест 1. Ожидания на блокировок.

Сеанс 194695 открывает транзакцию.

20:43:48 zx(194695)@orcl> update emp set emp.comm = 100 where empno = 7566;
1 row updated.

20:50:02 zx(194695)@orcl>

При незакрытой транзакции сеанса 194695 сеанс 194697 открывает и фиксирует новую транзакцию.

20:50:22 zx(194697)@orcl> update emp set emp.comm = 100 where empno = 7708;
1 row updated.

20:50:53 zx(194697)@orcl> commit;
Commit complete.

20:50:59 zx(194697)@orcl>

Сеанс 194695 успешно фиксирует свою транзакцию.

20:50:02 zx(194695)@orcl> commit;
Commit complete.

20:51:12 zx(194695)@orcl>

Тест 2. Ожидания на блокировках при попытке фиксации в одном из сеансов некорректной информации.

Сеанс 194695 открывает транзакцию.

21:25:43 zx(194695)@orcl> update emp set emp.comm = 200 where empno = 7566;
1 row updated.

21:25:48 zx(194695)@orcl>

При незакрытой транзакции сеанса 194695 сеанс 194697 открывает транзакцию которая при попытке фиксации отвергается бизнесконстрейнтом.

21:25:58 zx(194697)@orcl> update EMP_ATTR_VALS set date_end = TO_DATE('12012016','ddmmyyyy') where EMPNO=7369 and EMP_ATTR ='SALARY' and DATE_BEG = TO_DATE('01012016','ddmmyyyy');
1 row updated.

21:26:32 zx(194697)@orcl> 
21:26:33 zx(194697)@orcl> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-20555: Ошибка. Пересечение дат в истории параметра SALARY ORA-06512: at "ZX.CHK_EMP_MV_AS",
line 53
ORA-04088: error during execution of trigger 'ZX.CHK_EMP_MV_AS'

21:26:43 zx(194697)@orcl>

Сеанс 194695 успешно фиксирует свою транзакцию.

21:25:50 zx(194695)@orcl> commit;
Commit complete.

21:26:53 zx(194695)@orcl>

Тест 3. Контроль отработки бизнесправила ограничения количества клерков в филиале.

21:46:50 zx(194695)@orcl> update emp set job = 'MANAGER' where empno = 7708;
1 row updated.

21:47:06 zx(194695)@orcl> commit;
Commit complete.

21:47:11 zx(194695)@orcl> update emp set job = 'CLERK' where empno = 7708;
1 row updated.

21:47:32 zx(194695)@orcl> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-20555: Ошибка. В филиале DALLAS больше 2х клерков ORA-06512: at
"ZX.CHK_EMP_MV_AS", line 53
ORA-04088: error during execution of trigger 'ZX.CHK_EMP_MV_AS'

21:47:37 zx(194695)@orcl>

Тест 4. Отработка бизнесправила контроля размера зарплаты.

22:29:29 zx(194695)@orcl> update emp_attr_vals eav set eav.val='30000' where EAV.EMP_ATTR='SALARY' and sysdate BETWEEN eav.date_beg and EAV.DATE_END and eav.empno = 7777;
1 row updated.

22:29:36 zx(194695)@orcl> commit;
Commit complete.

22:29:42 zx(194695)@orcl> update emp_attr_vals eav set eav.val='2000' where EAV.EMP_ATTR='SALARY' and sysdate BETWEEN eav.date_beg and EAV.DATE_END and eav.empno = 7708;
1 row updated.

22:30:27 zx(194695)@orcl> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-20555: Ошибка. Только президент может иметь зарплату больше 999 ORA-06512: at "ZX.CHK_EMP_MV_AS", line 53
ORA-04088: error during execution of trigger 'ZX.CHK_EMP_MV_AS'

22:30:32 zx(194695)@orcl>

Что и требовалось доказать. Естественно речь идет о размере зарплаты что к констрейнту отношения не имеет ).

Автор: mamonm

Источник

Информация

Обсуждаемое

Рекомендуем

Сложное ограничение целостности (constraint) — 2

Архив

Информация

Обсуждаемое

Рекомендуем

Сложное ограничение целостности (constraint) — 2

Рекомендованный контент

Новости

Актуальные темы

Архив