{"id":278948,"date":"2016-04-25T02:05:03","date_gmt":"2016-04-24T22:05:03","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=278948"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=278948","title":{"rendered":"\u0421\u043b\u043e\u0436\u043d\u043e\u0435 \u043e\u0433\u0440\u0430\u043d\u0438\u0447\u0435\u043d\u0438\u0435 \u0446\u0435\u043b\u043e\u0441\u0442\u043d\u043e\u0441\u0442\u0438 (constraint) \u2014 2"},"content":{"rendered":"

\u041a\u043e\u043d\u0441\u0442\u0440\u0443\u043a\u0442\u0438\u0432\u043d\u044b\u0435 \u043a\u043e\u043c\u043c\u0435\u043d\u0442\u0430\u0440\u0438\u0438 \u043e\u0431\u0449\u0435\u0441\u0442\u0432\u0430 \u043a \u043f\u043e\u0441\u0442\u0443 \u0421\u043b\u043e\u0436\u043d\u043e\u0435 \u043e\u0433\u0440\u0430\u043d\u0438\u0447\u0435\u043d\u0438\u0435 \u0446\u0435\u043b\u043e\u0441\u0442\u043d\u043e\u0441\u0442\u0438 (constraint)<\/a> \u0432 \u043a\u043e\u0442\u043e\u0440\u043e\u043c \u0431\u044b\u043b\u0430 \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0430 \u0440\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u044f \u0441\u043b\u043e\u0436\u043d\u043e\u0433\u043e \u043a\u043e\u043d\u0441\u0442\u0440\u0435\u0439\u043d\u0442\u0430 \u0411\u0414 ORACLE \u0437\u0430\u0441\u0442\u0430\u0432\u0438\u043b\u0438 \u0433\u043b\u0443\u0431\u0436\u0435 \u043f\u043e\u0433\u0440\u0443\u0437\u0438\u0442\u044c\u0441\u044f \u0432 \u0442\u0435\u043c\u0443. <\/a>\u0422\u0435\u043c \u0431\u043e\u043b\u0435\u0435 \u0447\u0442\u043e \u0444\u0443\u043d\u043a\u0446\u0438\u043e\u043d\u0430\u043b \u0441\u0442\u043e\u0438\u0442 \u0432 \u0431\u043e\u0435\u0432\u043e\u0439 \u044d\u043a\u0441\u043f\u043b\u0443\u0430\u0442\u0430\u0446\u0438\u0438 \u0438 \u043e\u0441\u0442\u0430\u0432\u043b\u044f\u0442\u044c \u0432 \u043d\u0435\u043c \u0448\u0435\u0440\u043e\u0445\u043e\u0432\u0430\u0442\u043e\u0441\u0442\u0438 \u0431\u044b\u043b\u043e \u0431\u044b \u043d\u0435\u043a\u0440\u0430\u0441\u0438\u0432\u043e.
\u0427\u0442\u043e \u043d\u043e\u0432\u043e\u0433\u043e.
\u041f\u043e\u0445\u043e\u0436\u0435 \u0443\u0434\u0430\u043b\u043e\u0441\u044c \u043e\u0431\u0435\u0441\u043f\u0435\u0447\u0438\u0442\u044c \u043c\u043d\u043e\u0433\u043e\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044c\u0441\u043a\u0438\u0439 \u0440\u0435\u0436\u0438\u043c. \u0423\u0432\u0430\u0436\u0430\u0435\u043c\u044b\u0439 xtender \u0441\u043e\u0432\u0435\u0440\u0448\u0435\u043d\u043d\u043e \u0441\u043f\u0440\u0430\u0432\u0435\u0434\u043b\u0438\u0432\u043e \u043e\u0442\u043c\u0435\u0442\u0438\u043b \u044d\u0442\u043e\u0442 \u043d\u0435\u0434\u043e\u0441\u0442\u0430\u0442\u043e\u043a \u043f\u0440\u0435\u0434\u044b\u0434\u0443\u0449\u0435\u0439 \u0440\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u0438.
\u0411\u0438\u0437\u043d\u0435\u0441\u043f\u0440\u0430\u0432\u0438\u043b\u0430 \u043a\u043e\u043d\u0442\u0440\u043e\u043b\u044c\u043d\u043e\u0433\u043e \u043f\u0440\u0438\u043c\u0435\u0440\u0430 ( \u0441\u0444\u0435\u0440\u0438\u0447\u0435\u0441\u043a\u0438\u0439 \u0441\u043b\u043e\u043d ) \u043d\u0430 \u0442\u0435\u0441\u0442\u043e\u0432\u043e\u043c \u043d\u0430\u0431\u043e\u0440\u0435 \u0434\u0430\u043d\u043d\u044b\u0445 ( \u0432 \u0432\u0430\u043a\u0443\u0443\u043c\u0435 ) \u0443\u0441\u043b\u043e\u0436\u043d\u0435\u043d\u044b. \u0411\u043e\u043b\u0435\u0435 \u0441\u043b\u043e\u0436\u043d\u044b\u0439 \u043f\u0440\u0438\u043c\u0435\u0440 \u043d\u0430\u0433\u043b\u044f\u0434\u043d\u0435\u0435 \u043e\u0442\u0440\u0430\u0436\u0430\u0435\u0442 \u043d\u0435 \u0441\u0430\u043c\u0443\u044e \u043f\u043b\u043e\u0445\u0443\u044e \u043c\u0430\u0441\u0448\u0442\u0430\u0431\u0438\u0440\u0443\u0435\u043c\u043e\u0441\u0442\u044c \u0440\u0435\u0448\u0435\u043d\u0438\u044f \u2014 \u0442\u0435\u043a\u0441\u0442 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u043e\u0432 \u043d\u0430 \u0440\u0430\u0437\u043d\u044b\u0445 \u0442\u0430\u0431\u043b\u0438\u0446\u0430\u0445 \u043f\u0440\u0430\u043a\u0442\u0438\u0447\u0435\u0441\u043a\u0438 \u0438\u0434\u0435\u043d\u0442\u0438\u0447\u0435\u043d. \u0414\u043e\u0431\u0430\u0432\u043b\u0435\u043d\u0438\u0435 \u043b\u044e\u0431\u043e\u0433\u043e \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u0430 \u043d\u043e\u0432\u044b\u0445 \u0431\u0438\u0437\u043d\u0435\u0441\u043f\u0440\u0430\u0432\u0438\u043b \u0431\u0435\u0437 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u044f \u0441\u043e\u0441\u0442\u0430\u0432\u0430 \u0443\u0447\u0430\u0441\u0442\u0432\u0443\u044e\u0449\u0438\u0445 \u0442\u0430\u0431\u043b\u0438\u0446 (emp, dept, emp_attr_vals) \u043f\u043e\u0442\u0440\u0435\u0431\u0443\u0435\u0442 \u043c\u043e\u0434\u0438\u0444\u0438\u043a\u0430\u0446\u0438\u0438 \u0442\u043e\u043b\u044c\u043a\u043e \u0444\u0443\u043d\u043a\u0446\u0438\u0438 emp_logic. \u041d\u0430\u0432\u0435\u0440\u043d\u043e \u0433\u0443\u0440\u0443 \u0441\u043c\u043e\u0433\u0443\u0442 \u0440\u0435\u0430\u043b\u0438\u0437\u043e\u0432\u0430\u0442\u044c \u044d\u0442\u043e\u0442 \u0432\u0430\u0440\u0438\u0430\u043d\u0442 \u043a\u043e\u043d\u0441\u0442\u0440\u0435\u0439\u043d\u0442\u0430 \u043f\u043e \u0422\u043e\u043c\u0443\u041a (fast refresh mview) \u043d\u043e \u0432\u0441\u0435\u043c\u0443 \u0435\u0441\u0442\u044c \u043f\u0440\u0435\u0434\u0435\u043b. \u0421\u0430\u043c \u0422\u043e\u043c \u0441\u0447\u0438\u0442\u0430\u0435\u0442 \u0447\u0442\u043e \u043a\u043e\u0433\u0434\u0430 \u0437\u0430\u0434\u0430\u0447\u0443 \u043d\u0435 \u0440\u0435\u0430\u043b\u0438\u0437\u043e\u0432\u0430\u0442\u044c \u043e\u0434\u043d\u0438\u043c sql (mview) \u043f\u043e\u0440\u0430 \u0440\u0435\u0448\u0430\u0442\u044c \u043d\u0430 plsql.
\u0423 \u043f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u0435\u043c\u043e\u0433\u043e \u0440\u0435\u0448\u0435\u043d\u0438\u044f \u0435\u0441\u0442\u044c \u043d\u0435\u0434\u043e\u0441\u0442\u0430\u0442\u043e\u043a \u2014 \u043d\u0435 \u043e\u0431\u043a\u0430\u0442\u0430\u043d\u043e \u043d\u0430 \u043f\u0440\u043e\u0434\u0430\u043a\u0448\u043d \u043f\u043e\u0434 \u043d\u0430\u0433\u0440\u0443\u0437\u043a\u043e\u0439.<\/p>\n

\u0420\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u044f.
\u0422\u0430\u0431\u043b\u0438\u0446\u044b \u0431\u0438\u0437\u043d\u0435\u0441\u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u0438.<\/p>\n

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;<\/code><\/pre>\n
  <\/p>\n
\u0414\u0430\u043d\u043d\u044b\u0435<\/b><\/p>\n
\n
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'); <\/code><\/pre>\n<\/div>\n<\/div>\n
 CREATE TABLE "EMP"  ( \t"EMPNO" NUMBER(4,0),  \t"ENAME" VARCHAR2(10),  \t"JOB" VARCHAR2(9),  \t"MGR" NUMBER(4,0),  \t"HIREDATE" DATE,  \t"COMM" NUMBER(17,2),  \t"DEPTNO" NUMBER(2,0) );<\/code><\/pre>\n
  <\/p>\n
\u0414\u0430\u043d\u043d\u044b\u0435<\/b><\/p>\n
\n
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'); <\/code><\/pre>\n
  <\/div>\n<\/div>\n
CREATE UNIQUE INDEX "\u0415M\u0420_\u0420K" ON "EMP" ("EMPNO"); ALTER TABLE "EMP" MODIFY ("EMPNO" NOT NULL ENABLE); ALTER TABLE "EMP" ADD CONSTRAINT "\u0415M\u0420_\u0420K" PRIMARY KEY ("EMPNO") ENABLE;  ALTER TABLE "EMP" ADD CONSTRAINT "EMP_FK_DEPT" FOREIGN KEY ("DEPTNO") \t  REFERENCES "DEPT" ("DEPTNO") ENABLE;  CREATE TABLE "EMP_ATTR_VALS" ( \t"DATE_BEG" DATE, \t"DATE_END" DATE, \t"VAL" VARCHAR2(64), \t"EMP_ATTR" VARCHAR2(32), \t"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;<\/code><\/pre>\n
  <\/p>\n
\u0414\u0430\u043d\u043d\u044b\u0435<\/b><\/p>\n
\n
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');<\/code><\/pre>\n
  <\/div>\n<\/div>\n
  \u0422\u0430\u0431\u043b\u0438\u0446\u0430 \u043b\u043e\u0433\u0430.  <\/p>\n
CREATE TABLE "EMP_CHK_LOG" ( \t"TS" TIMESTAMP (6), \t"STATUS" NUMBER, \t"XML" CLOB, \t"ERR_TXT" VARCHAR2(512) );<\/code><\/pre>\n
  \u0414\u043b\u044f \u0440\u0430\u0431\u043e\u0442\u044b \u0441 \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u044f\u043c\u0438 \u0441\u0445\u0435\u043c\u0435 c \u043f\u0440\u0438\u043c\u0435\u0440\u043e\u043c (ZX) \u043f\u043e\u0442\u0440\u0435\u0431\u0443\u0435\u0442\u0441\u044f \u043d\u0435\u043a\u043e\u0442\u043e\u0440\u044b\u0439 \u0434\u043e\u0441\u0442\u0443\u043f \u043a \u0441\u0445\u0435\u043c\u0435 SYS.  <\/p>\n
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;<\/code><\/pre>\n
  \u0411\u0430\u0437\u043e\u0432\u0430\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u0430 \u043c\u0430\u0442\u0432\u044c\u044e.  <\/p>\n
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" \tPRIMARY KEY ("LTRANS_ID") ENABLE; ALTER TABLE "EMP_CHK" MODIFY ("LTRANS_ID" NOT NULL ENABLE);<\/code><\/pre>\n
  \u0421\u043e\u0437\u0434\u0430\u0435\u043c materialized view.  <\/p>\n
CREATE MATERIALIZED VIEW chk_emp_mv   REFRESH COMPLETE ON COMMIT AS       SELECT emp_no, ltrans_id FROM emp_chk;<\/code><\/pre>\n
  \u041c\u043d\u043e\u0433\u043e\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044c\u0441\u043a\u0438\u0439 \u0440\u0435\u0436\u0438\u043c \u0440\u0430\u0431\u043e\u0442\u044b \u043e\u0431\u0435\u0441\u043f\u0435\u0447\u0435\u043d \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u043c \u043e\u0431\u0440\u0430\u0437\u043e\u043c. \u0412 \u043d\u0430\u0447\u0430\u043b\u0435 \u043a\u0430\u0436\u0434\u043e\u0439 \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u0438 \u0438\u0437 \u0431\u0430\u0437\u043e\u0432\u043e\u0439 \u0442\u0430\u0431\u043b\u0438\u0446\u044b \u043c\u0430\u0442\u0432\u044c\u044e \u043f\u0440\u043e\u0446\u0435\u0434\u0443\u0440\u043e\u0439 var_chk.cls_emp_chk \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u044f \u0437\u0430\u0432\u0435\u0440\u0448\u0435\u043d\u043d\u044b\u0445 \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u0439 \u0443\u0434\u0430\u043b\u044f\u044e\u0442\u0441\u044f \u043f\u043e\u0441\u0442\u0440\u043e\u0447\u043d\u043e. \u0418\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0435 \u0430\u0432\u0442\u043e\u043d\u043e\u043c\u043d\u043e\u0439 \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u0438 \u043e\u0431\u0435\u0441\u043f\u0435\u0447\u0438\u0432\u0430\u0435\u0442 \u043e\u0447\u0438\u0441\u0442\u043a\u0443 \u043d\u0435\u0437\u0430\u0432\u0438\u0441\u0438\u043c\u043e \u043e\u0442 \u0438\u0441\u0445\u043e\u0434\u0430 (commit\/rollback) \u0442\u0435\u043a\u0443\u0449\u0435\u0439 \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u0438. \u0412 \u043f\u0440\u0435\u0434\u044b\u0434\u0443\u0449\u0435\u043c \u0432\u0430\u0440\u0438\u0430\u043d\u0442\u0435 \u043e\u0447\u0438\u0449\u0430\u043b\u0430\u0441\u044c \u0430 \u0441\u043b\u0435\u0434\u043e\u0432\u0430\u0442\u0435\u043b\u044c\u043d\u043e \u0438 \u0431\u043b\u043e\u043a\u0438\u0440\u043e\u0432\u0430\u043b\u0430\u0441\u044c \u0432\u0441\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u0430 \u0434\u043e \u043e\u043a\u043e\u043d\u0447\u0430\u043d\u0438\u044f \u0442\u0435\u043a\u0443\u0449\u0435\u0439 \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u0438. <\/p>\n
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 -- \u0412\u043e\u0437\u0432\u0440\u0430\u0449\u0430\u0435\u0442 1 - \u043a\u043e\u043d\u0442\u0440\u043e\u043b\u044c \u0432\u043a\u043b\u044e\u0447\u0435\u043d --            0 - \u043a\u043e\u043d\u0442\u0440\u043e\u043b\u044c \u0432\u044b\u043a\u043b\u0435\u0447\u0435\u043d            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;<\/code><\/pre>\n
  \u041f\u043e \u043a\u0430\u0436\u0434\u043e\u043c\u0443 DML\u044e \u043d\u0430 \u043b\u044e\u0431\u043e\u0439 \u0438\u0437 \u0442\u0430\u0431\u043b\u0438\u0446 \u0431\u0438\u0437\u043d\u0435\u0441\u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u0438 \u0432 \u0431\u0430\u0437\u043e\u0432\u0443\u044e \u0442\u0430\u0431\u043b\u0438\u0446\u0443 \u043c\u0430\u0442\u0432\u044c\u044e \u043f\u0438\u0448\u0435\u0442\u0441\u044f \u0438\u0434\u0435\u043d\u0442\u0438\u0444\u0438\u043a\u0430\u0442\u043e\u0440 \u043a\u043e\u043d\u0442\u0440\u043e\u043b\u0438\u0440\u0443\u0435\u043c\u043e\u0439 \u0441\u0443\u0449\u043d\u043e\u0441\u0442\u0438 (\u0432 \u043d\u0430\u0448\u0435\u043c \u043f\u0440\u0438\u043c\u0435\u0440\u0435 \u0441\u043e\u0442\u0440\u0443\u0434\u043d\u0438\u043a) \u0438 \u0438\u0434\u0435\u043d\u0442\u0438\u0444\u0438\u043a\u0430\u0442\u043e\u0440 \u0442\u0435\u043a\u0443\u0449\u0435\u0439 \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u0438.<\/p>\n
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;<\/code><\/pre>\n
  \u0424\u0443\u043d\u043a\u0446\u0438\u044f \u0431\u0438\u0437\u043d\u0435\u0441\u043a\u043e\u043d\u0442\u0440\u043e\u043b\u044f. <\/p>\n
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:='\u041e\u0448\u0438\u0431\u043a\u0430. \u0412 \u0444\u0438\u043b\u0438\u0430\u043b\u0435 '||v_emp_loc||' \u0431\u043e\u043b\u044c\u0448\u0435 2\u0445 \u043a\u043b\u0435\u0440\u043a\u043e\u0432';        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:='\u041e\u0448\u0438\u0431\u043a\u0430. \u041f\u0435\u0440\u0435\u0441\u0435\u0447\u0435\u043d\u0438\u0435 \u0434\u0430\u0442 \u0432 \u0438\u0441\u0442\u043e\u0440\u0438\u0438 \u043f\u0430\u0440\u0430\u043c\u0435\u0442\u0440\u0430 '||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:='\u041e\u0448\u0438\u0431\u043a\u0430. \u0422\u043e\u043b\u044c\u043a\u043e \u043f\u0440\u0435\u0437\u0438\u0434\u0435\u043d\u0442 \u043c\u043e\u0436\u0435\u0442 \u0438\u043c\u0435\u0442\u044c \u0437\u0430\u0440\u043f\u043b\u0430\u0442\u0443 \u0431\u043e\u043b\u044c\u0448\u0435 999';        RETURN 1; END IF;  RETURN 0;  END emp_logic;<\/code><\/pre>\n
  \u0422\u0440\u0438\u0433\u0433\u0435\u0440\u0430 \u043d\u0430 \u043c\u0430\u0442\u0432\u044c\u044e.<\/p>\n
  \u0417\u0430\u043f\u0443\u0441\u043a\u0430\u044e\u0449\u0438\u0439 \u043e\u0447\u0438\u0441\u0442\u043a\u0443 \u0434\u0430\u043d\u043d\u044b\u0445 \u043f\u0440\u0435\u0434\u044b\u0434\u0443\u0449\u0435\u0439 \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u0438 \u0438\u0437 \u0431\u0430\u0437\u043e\u0432\u043e\u0439 \u0442\u0430\u0431\u043b\u0438\u0446\u044b \u043c\u0430\u0442\u0432\u044c\u044e.<\/p>\n
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;<\/code><\/pre>\n
  \u0417\u0430\u043f\u0443\u0441\u043a\u0430\u044e\u0449\u0438\u0439 \u0444\u0443\u043d\u043a\u0446\u0438\u044e \u0431\u0438\u0437\u043d\u0435\u0441\u043a\u043e\u043d\u0442\u0440\u043e\u043b\u044f \u0438 \u043f\u0438\u0448\u0443\u0449\u0438\u0439 \u0432 \u043b\u043e\u0433.  <\/p>\n
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;  -- \u0431\u0438\u0437\u043d\u0435\u0441 \u043b\u043e\u0433\u0438\u043a\u0430 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;<\/code><\/pre>\n
  \u041f\u0440\u043e\u0432\u0435\u0440\u0438\u043c \u0432\u0435\u043b\u043e\u0441\u0438\u043f\u0435\u0434 \u043d\u0430 \u0445\u043e\u0434\u0443.
  \u041f\u0440\u043e\u0432\u0435\u0440\u044f\u0442\u044c \u0431\u0443\u0434\u0435\u043c \u0432 \u0434\u0432\u0430 \u0441\u0435\u0430\u043d\u0441\u0430. \u0414\u043b\u044f \u043a\u043e\u043d\u0442\u0440\u043e\u043b\u044f \u043f\u0440\u043e\u0446\u0435\u0441\u0441\u0430 \u0432 \u043f\u0440\u0438\u0433\u043b\u0430\u0448\u0435\u043d\u0438\u0435 plus \u0432\u044b\u0432\u0435\u0434\u0435\u043d\u043e \u0442\u0435\u043a\u0443\u0449\u0435\u0435 \u0432\u0440\u0435\u043c\u044f, \u0430 \u0432 \u0441\u043a\u043e\u0431\u043a\u0430\u0445 sid \u0442\u0435\u043a\u0443\u0449\u0435\u0433\u043e \u0441\u0435\u0430\u043d\u0441\u0430.
  \u0422\u0435\u0441\u0442 1. \u041e\u0436\u0438\u0434\u0430\u043d\u0438\u044f \u043d\u0430 \u0431\u043b\u043e\u043a\u0438\u0440\u043e\u0432\u043e\u043a.<\/p>\n
  \u0421\u0435\u0430\u043d\u0441 194695 \u043e\u0442\u043a\u0440\u044b\u0432\u0430\u0435\u0442 \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u044e.   <\/p>\n
20:43:48 zx(194695)@orcl> update emp set emp.comm = 100 where empno = 7566; 1 row updated.  20:50:02 zx(194695)@orcl><\/code><\/pre>\n
  \u041f\u0440\u0438 \u043d\u0435\u0437\u0430\u043a\u0440\u044b\u0442\u043e\u0439 \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u0438 \u0441\u0435\u0430\u043d\u0441\u0430 194695 \u0441\u0435\u0430\u043d\u0441 194697 \u043e\u0442\u043a\u0440\u044b\u0432\u0430\u0435\u0442 \u0438 \u0444\u0438\u043a\u0441\u0438\u0440\u0443\u0435\u0442 \u043d\u043e\u0432\u0443\u044e \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u044e.  <\/p>\n
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><\/code><\/pre>\n
  \u0421\u0435\u0430\u043d\u0441 194695 \u0443\u0441\u043f\u0435\u0448\u043d\u043e \u0444\u0438\u043a\u0441\u0438\u0440\u0443\u0435\u0442 \u0441\u0432\u043e\u044e \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u044e.  <\/p>\n
20:50:02 zx(194695)@orcl> commit; Commit complete.  20:51:12 zx(194695)@orcl> <\/code><\/pre>\n
  \u0422\u0435\u0441\u0442 2. \u041e\u0436\u0438\u0434\u0430\u043d\u0438\u044f \u043d\u0430 \u0431\u043b\u043e\u043a\u0438\u0440\u043e\u0432\u043a\u0430\u0445 \u043f\u0440\u0438 \u043f\u043e\u043f\u044b\u0442\u043a\u0435 \u0444\u0438\u043a\u0441\u0430\u0446\u0438\u0438 \u0432 \u043e\u0434\u043d\u043e\u043c \u0438\u0437 \u0441\u0435\u0430\u043d\u0441\u043e\u0432 \u043d\u0435\u043a\u043e\u0440\u0440\u0435\u043a\u0442\u043d\u043e\u0439 \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u0438.<\/p>\n
  \u0421\u0435\u0430\u043d\u0441 194695 \u043e\u0442\u043a\u0440\u044b\u0432\u0430\u0435\u0442 \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u044e.   <\/p>\n
21:25:43 zx(194695)@orcl> update emp set emp.comm = 200 where empno = 7566; 1 row updated.  21:25:48 zx(194695)@orcl><\/code><\/pre>\n
  \u041f\u0440\u0438 \u043d\u0435\u0437\u0430\u043a\u0440\u044b\u0442\u043e\u0439 \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u0438 \u0441\u0435\u0430\u043d\u0441\u0430 194695 \u0441\u0435\u0430\u043d\u0441 194697 \u043e\u0442\u043a\u0440\u044b\u0432\u0430\u0435\u0442 \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u044e \u043a\u043e\u0442\u043e\u0440\u0430\u044f \u043f\u0440\u0438 \u043f\u043e\u043f\u044b\u0442\u043a\u0435 \u0444\u0438\u043a\u0441\u0430\u0446\u0438\u0438 \u043e\u0442\u0432\u0435\u0440\u0433\u0430\u0435\u0442\u0441\u044f \u0431\u0438\u0437\u043d\u0435\u0441\u043a\u043e\u043d\u0441\u0442\u0440\u0435\u0439\u043d\u0442\u043e\u043c.<\/p>\n
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: \u041e\u0448\u0438\u0431\u043a\u0430. \u041f\u0435\u0440\u0435\u0441\u0435\u0447\u0435\u043d\u0438\u0435 \u0434\u0430\u0442 \u0432 \u0438\u0441\u0442\u043e\u0440\u0438\u0438 \u043f\u0430\u0440\u0430\u043c\u0435\u0442\u0440\u0430 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><\/code><\/pre>\n
  \u0421\u0435\u0430\u043d\u0441 194695 \u0443\u0441\u043f\u0435\u0448\u043d\u043e \u0444\u0438\u043a\u0441\u0438\u0440\u0443\u0435\u0442 \u0441\u0432\u043e\u044e \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u044e.  <\/p>\n
21:25:50 zx(194695)@orcl> commit; Commit complete.  21:26:53 zx(194695)@orcl><\/code><\/pre>\n
  \u0422\u0435\u0441\u0442 3. \u041a\u043e\u043d\u0442\u0440\u043e\u043b\u044c \u043e\u0442\u0440\u0430\u0431\u043e\u0442\u043a\u0438 \u0431\u0438\u0437\u043d\u0435\u0441\u043f\u0440\u0430\u0432\u0438\u043b\u0430 \u043e\u0433\u0440\u0430\u043d\u0438\u0447\u0435\u043d\u0438\u044f \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u0430 \u043a\u043b\u0435\u0440\u043a\u043e\u0432 \u0432 \u0444\u0438\u043b\u0438\u0430\u043b\u0435.<\/p>\n
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: \u041e\u0448\u0438\u0431\u043a\u0430. \u0412 \u0444\u0438\u043b\u0438\u0430\u043b\u0435 DALLAS \u0431\u043e\u043b\u044c\u0448\u0435 2\u0445 \u043a\u043b\u0435\u0440\u043a\u043e\u0432 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><\/code><\/pre>\n
  \u0422\u0435\u0441\u0442 4. \u041e\u0442\u0440\u0430\u0431\u043e\u0442\u043a\u0430 \u0431\u0438\u0437\u043d\u0435\u0441\u043f\u0440\u0430\u0432\u0438\u043b\u0430 \u043a\u043e\u043d\u0442\u0440\u043e\u043b\u044f \u0440\u0430\u0437\u043c\u0435\u0440\u0430 \u0437\u0430\u0440\u043f\u043b\u0430\u0442\u044b.<\/p>\n
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: \u041e\u0448\u0438\u0431\u043a\u0430. \u0422\u043e\u043b\u044c\u043a\u043e \u043f\u0440\u0435\u0437\u0438\u0434\u0435\u043d\u0442 \u043c\u043e\u0436\u0435\u0442 \u0438\u043c\u0435\u0442\u044c \u0437\u0430\u0440\u043f\u043b\u0430\u0442\u0443 \u0431\u043e\u043b\u044c\u0448\u0435 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><\/code><\/pre>\n
  \u0427\u0442\u043e \u0438 \u0442\u0440\u0435\u0431\u043e\u0432\u0430\u043b\u043e\u0441\u044c \u0434\u043e\u043a\u0430\u0437\u0430\u0442\u044c. \u0415\u0441\u0442\u0435\u0441\u0442\u0432\u0435\u043d\u043d\u043e \u0440\u0435\u0447\u044c \u0438\u0434\u0435\u0442 \u043e \u0440\u0430\u0437\u043c\u0435\u0440\u0435 \u0437\u0430\u0440\u043f\u043b\u0430\u0442\u044b \u0447\u0442\u043e \u043a \u043a\u043e\u043d\u0441\u0442\u0440\u0435\u0439\u043d\u0442\u0443 \u043e\u0442\u043d\u043e\u0448\u0435\u043d\u0438\u044f \u043d\u0435 \u0438\u043c\u0435\u0435\u0442 ).               <\/p>\n
<\/div>\n
 \u0441\u0441\u044b\u043b\u043a\u0430 \u043d\u0430 \u043e\u0440\u0438\u0433\u0438\u043d\u0430\u043b \u0441\u0442\u0430\u0442\u044c\u0438  https:\/\/habrahabr.ru\/post\/282335\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
       \u041a\u043e\u043d\u0441\u0442\u0440\u0443\u043a\u0442\u0438\u0432\u043d\u044b\u0435 \u043a\u043e\u043c\u043c\u0435\u043d\u0442\u0430\u0440\u0438\u0438 \u043e\u0431\u0449\u0435\u0441\u0442\u0432\u0430 \u043a \u043f\u043e\u0441\u0442\u0443 \u0421\u043b\u043e\u0436\u043d\u043e\u0435 \u043e\u0433\u0440\u0430\u043d\u0438\u0447\u0435\u043d\u0438\u0435 \u0446\u0435\u043b\u043e\u0441\u0442\u043d\u043e\u0441\u0442\u0438 (constraint)<\/a> \u0432 \u043a\u043e\u0442\u043e\u0440\u043e\u043c \u0431\u044b\u043b\u0430 \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0430 \u0440\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u044f \u0441\u043b\u043e\u0436\u043d\u043e\u0433\u043e \u043a\u043e\u043d\u0441\u0442\u0440\u0435\u0439\u043d\u0442\u0430 \u0411\u0414 ORACLE \u0437\u0430\u0441\u0442\u0430\u0432\u0438\u043b\u0438 \u0433\u043b\u0443\u0431\u0436\u0435 \u043f\u043e\u0433\u0440\u0443\u0437\u0438\u0442\u044c\u0441\u044f \u0432 \u0442\u0435\u043c\u0443. <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-278948","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/278948","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=278948"}],"version-history":[{"count":0,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/278948\/revisions"}],"wp:attachment":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=278948"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=278948"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=278948"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}