💡 ⚾️ 🌃 複雑な制約制約-2 ⚓️ 👩‍🏭 🍞

投稿に対する建設的なパブリックコメント複雑なORACLEデータベースの制約の実装が提示された複雑な制約により、トピックの詳細が掘り下げられました。さらに、機能は戦闘操作にあり、その中に粗さを残すことはいでしょう。
新機能
マルチユーザーモードを提供することができたようです。親愛なるxtenderは、以前の実装のこの欠点を非常に正しく指摘しました。
テストデータセット（真空中）のテストケース（球形象）のビジネスルールは複雑です。より複雑な例は、ソリューションの最悪のスケーラビリティではないことをより明確に反映しています。異なるテーブルのトリガーのテキストはほぼ同じです。関係するテーブル（emp、dept、emp_attr_vals）の構成を変更せずに任意の数の新しいビジネス修正を追加するには、emp_logic関数のみを変更する必要があります。おそらく、達人はこのバージョンの定数をTomK（高速リフレッシュmview）に実装できますが、すべてに制限があります。トム自身は、1つの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) );

トランザクションを処理するには、サンプル（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;

ベーステーブルmatvyu。

 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);

マテリアライズドビューを作成します。

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

マルチユーザーモードの操作は、次のように提供されます。ベーステーブルからの各トランザクションの開始時に、完了したトランザクション情報のマザーによるvar_chk.cls_emp_chkプロシージャが行ごとに削除されます。自律型トランザクションを使用すると、現在のトランザクションの結果（コミット/ロールバック）に関係なく、クリアが提供されます。以前のバージョンでは、テーブル全体がクリアされたため、現在のトランザクションが終了するまでブロックされていました。

 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;

外出先で自転車を確認してください。
2つのセッションをチェックインします。プロセスを制御するために、現在のプロンプトがプラスプロンプトに表示され、現在のセッションがカッコ内に表示されます。
テスト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.セッションの1つで誤った情報を修正しようとしたときのロックへの期待。

セッション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>

証明するために必要でした。当然、私たちは給料の大きさについて話しているが、これは定数とは何の関係もない。

複雑な制約制約-2

More articles: