本文基于真实事件，
并且其中的所有问题都不是虚构的。 （C）首先，我想指出的是，本文并不是要展示自行车的发明，因为数据库开发文化中长期以来存在着许多技术。但是，总而言之，分析它们可以解决的问题并说明如何使用它们。尽管参考信息（NSI）不适用于业务逻辑，而是适用于业务逻辑，但仍然存在很多问题。随着拐杖或费时的改动，绘制另一张盘子来存储目录的标准过程很快就开始增长。因此，在我的情况下，原来的情况是这样的：该系统已经投入生产了十多年，它是基于相同的原理构建的，如果有必要，我们可以绘制并打开它。因此，创建了几个表来存储各种设备。但是到了第X个小时，这时有必要为新设备添加更多表，同时每个人（包括旧表）都应归入某个组。这意味着在组和所有五种类型的设备之间的交叉表中应包含指向不同表的链接，即，对于其自己的每个字段，在相应表上均具有常量。如果再添加一个，请更改结构。根据填充的字段需要进行处理。因此出现了第一个问题，如何汇总不同的表格，以便您可以平等地使用它们，并且如果添加另一个表格则不改变结构。一个好主意，我们创建了一个单独的标签，该标签旨在存储设备的抽象概念并带有类型指示，然后其余标签通过外键引用其父对象。在这一欢乐的浪潮中，我们将记录从其中一个填充到创建的板中，并尝试为另一个创建。但是出了点问题，主键限制起作用了，为什么呢？事实是，在系统动荡的青年时期来临之际，每个板块都有其自己的序列。当然，随着时间的流逝，这种耻辱得到了纠正，但是旧的键仍然存在。而且，它们与其他表一起起源于外键。我们解决了第二个问题，与所有目录的端到端编号相关联。设备桌的折磨并没有就此结束。因为根据最新要求，设备具有各种特性，而且其数量是可变的，并且一个特性可以具有多个值。因此出现了第三个问题，即能够存储可变数量的记录特征。似乎他们可以管理，但是客户时刻保持警惕，他总是准备着一些新的东西。然后需求就来了-所有目录都是历史目录（例如，产品名称是一个，然后将其重命名，并根据不同日期的文档，您需要显示当前名称）。要求本身很正常，您什么也不能说。而且，如果开发部门中还有其他人正在经历试用期，那么一切都覆盖在巧克力中，您可能不会注意到这是一个问题。但是，一切都照常进行-进行彻底的细分，然后您仍然需要这样做。我们创建复制对应目录表的板，以便将更改的时间顺序存储在该目录中。但是通过创建这些表，我们也为自己创建了第四个问题，现在在代码中，根据日期，一个人必须引用主表或历史表。好吧，我们是好伙伴，我们也赢得了胜利。讨论提出了有关如何存储同一记录的版本的问题。我想保留一下该版本不适合历史性表的内容。从历史上看，可以理解的是，直到这样一个日期为止，只有一个名字，而从这个日期开始，另一个名字就变得有意义。在版本控制中，可以理解该记录是首先保存的，但有一个错误，并且在几个小时后就可以理解并更改了该记录，您需要知道该记录的所有状态。首先，应该压碎一段时间，而不仅仅是一天。其次，在摊牌时需要这样的痕迹。例如，他们填写了价格表，犯了一个错误，设法以这样的价格出售商品，然后进行了更正，但最终还是出现了失衡。但是，针对此类情况的决定使我个人感到困扰，因此建议将所有此类更改存储在表本身中。我不会在适当的范围内安排好戏，但对我来说这已经很清楚了第五个问题，即记录更改的存储。因此，总结以上内容，我们看到面前有五个重磅耙子。现在，我们的任务是确定一种策略，使您能够绕开而不是踩踏它们。您可以踩到多少钱，让我们放弃并购买新的
从头开始设计系统，没有人能够预测其发展路径，因此无法说出将其概括到什么水平，就像在设备示例中所描述的那样。因此，立即设置一个分发给所有NSI表的抽象实体是有意义的。因此，所有目录将在一个目录中具有原型，并将其划分为多个类型。CREATE TABLE nsi_type (
nsi_type_id NUMBER(10) NOT NULL,
name VARCHAR2(50) NOT NULL,
descr VARCHAR2(100),
table_name VARCHAR2(50) NOT NULL,
CONSTRAINT nsi_type_pk PRIMARY KEY (nsi_type_id)
);
CREATE TABLE nsi (
nsi_id NUMBER(10) NOT NULL,
nsi_type_id NUMBER(10) NOT NULL,
descr VARCHAR2(100),
create_date DATE NOT NULL,
modif_date DATE NOT NULL,
begin_date DATE,
CONSTRAINT nsi_nsi_type_fk FOREIGN KEY (nsi_type_id) REFERENCES nsi_type (nsi_type_id),
CONSTRAINT nsi_uk UNIQUE(nsi_type_id, nsi_id)
);
CREATE SEQUENCE nsi_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;
随着添加新目录，将填充nsi_type系统表。nsi表存储密钥和系统字段。同时，我们创建自己的序列，从而解决第二个问题。我们还将创建一个包，其中包含用于处理目录的基本功能，并将逐步填充它。create or replace NONEDITIONABLE PACKAGE BODY pkg_nsi
IS
FUNCTION get_type_id(p_table_name IN VARCHAR2)
RETURN nsi_type.nsi_type_id%TYPE
AS
v_type_id nsi_type.nsi_type_id%TYPE;
BEGIN
SELECT nsi_type_id INTO v_type_id
FROM nsi_type
WHERE TRIM(LOWER(table_name)) = TRIM(LOWER(p_table_name));
RETURN v_type_id;
END get_type_id;
FUNCTION get_nsi_id
RETURN nsi.nsi_id%TYPE
AS
v_id nsi.nsi_id%TYPE;
BEGIN
SELECT nsi_seq.NEXTVAL INTO v_id FROM DUAL;
RETURN v_id;
END get_nsi_id;
FUNCTION get_table_name(p_nsi_type_id IN nsi_type.nsi_type_id%TYPE)
RETURN nsi_type.table_name%TYPE
AS
v_table_name nsi_type.table_name%TYPE;
BEGIN
SELECT table_name INTO v_table_name
FROM nsi_type
WHERE nsi_type_id = p_nsi_type_id;
RETURN v_table_name;
END get_table_name;
FUNCTION get_nsi_descr (
p_nsi_id IN nsi.nsi_id%TYPE,
p_nsi_type_id IN nsi.nsi_type_id%TYPE)
RETURN nsi.descr%TYPE
AS
v_nsi_descr nsi.descr%TYPE;
BEGIN
SELECT descr
INTO v_nsi_descr
FROM nsi
WHERE nsi_id = p_nsi_id
AND nsi_type_id = p_nsi_type_id;
RETURN v_nsi_descr;
END get_nsi_descr;
...
END pkg_nsi;
到目前为止，已经提供了辅助功能以提供必要的基础结构。因此，任务是创建组织目录，如果没有该目录，任何公司都将联系第三方组织-这些组织是供应商，客户和合作伙伴。立即将相应的类型添加到nsi_type表并定义nsi_organization表。CREATE TABLE nsi_organization (
nsi_id NUMBER(10) NOT NULL,
name VARCHAR2(50) NOT NULL,
full_name VARCHAR2(100) NOT NULL,
inn VARCHAR2(12) NOT NULL,
CONSTRAINT nsi_organization_pk PRIMARY KEY (nsi_id),
CONSTRAINT nsi_organization_nsi_fk FOREIGN KEY (nsi_id) REFERENCES nsi (nsi_id)
);
INSERT INTO nsi_type (nsi_type_id, name, descr, table_name)
VALUES (11, '', ' , , , ', 'nsi_organization');
现在，在为时已晚之前，您需要记住有关数字“五”的耙子。如果我们开始将记录添加到已创建的组织表中，那么此事件需要在某个地方修复。CREATE TABLE nsi_log (
nsi_log_id NUMBER(10) NOT NULL,
nsi_id NUMBER(10) NOT NULL,
table_name VARCHAR2(100),
oper_num NUMBER,
descr CLOB,
create_date DATE,
CONSTRAINT nsi_log_pk PRIMARY KEY (nsi_log_id),
CONSTRAINT nsi_log_oper_num_ch CHECK (oper_num IN (1, 2, 3, 4, 5, 6, 7))
);
COMMENT ON TABLE nsi_log IS '. ';
COMMENT ON COLUMN nsi_log.nsi_log_id IS '';
COMMENT ON COLUMN nsi_log.nsi_id IS '';
COMMENT ON COLUMN nsi_log.table_name IS ' ';
COMMENT ON COLUMN nsi_log.oper_num IS ' (1 - , 2 - , 3 - , 4 - , 5 - , 6 - , 7 - ).';
COMMENT ON COLUMN nsi_log.descr IS '';
COMMENT ON COLUMN nsi_log.create_date IS ' ';
日志记录功能也已添加到软件包中。
NSI_LOG_OPERNUM_INSERT NUMBER := 1;
NSI_LOG_OPERNUM_UPDATE NUMBER := 2;
NSI_LOG_OPERNUM_DELETE NUMBER := 3;
NSI_LOG_OPERNUM_ATTR_INSERT NUMBER := 4;
NSI_LOG_OPERNUM_ATTR_UPDATE NUMBER := 5;
NSI_LOG_OPERNUM_ATTR_DELETE NUMBER := 6;
NSI_LOG_OPERNUM_HISTORY_PUSH NUMBER := 7;
PROCEDURE log_oper (
p_nsi_id IN nsi.nsi_id%TYPE,
p_nsi_type_id IN nsi_type.nsi_type_id%TYPE,
p_oper_num IN NUMBER,
p_descr IN VARCHAR2)
AS
BEGIN
INSERT INTO nsi_log
(nsi_log_id, nsi_id, table_name, oper_num, descr, create_date)
VALUES
(get_nsi_id(), p_nsi_id, get_table_name(p_nsi_type_id), p_oper_num, p_descr, Sysdate);
END;
这样，第五个问题就解决了，现在对于任何NSI记录，您都可以看到发生了什么。我们正在尝试在那里添加一个组织。INSERT INTO nsi_organization (nsi_id, name, full_name, inn)
VALUES (1, ' " "', ' " "', '11223344');
当然，我们遇到了nsi_organization_nsi_fk常量。因此，所有查找表都应配备必要的触发器改进。CREATE OR REPLACE TRIGGER nsi_organization_trg_insert
BEFORE INSERT ON nsi_organization FOR EACH ROW
DECLARE
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
BEGIN
v_type_id := pkg_nsi.get_type_id('nsi_organization');
:NEW.nsi_id := pkg_nsi.get_nsi_id();
INSERT INTO nsi (nsi_id, nsi_type_id, descr, create_date, modif_date, begin_date)
VALUES (:NEW.nsi_id, v_type_id, :NEW.name, Trunc(Sysdate), Trunc(Sysdate), Trunc(Sysdate));
v_log_descr := 'name = ''' || :NEW.name || ''', full_name = ''' || :NEW.full_name || ''', inn = ''' || :NEW.inn || ''' ';
pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_INSERT, v_log_descr);
END;
CREATE OR REPLACE TRIGGER nsi_organization_trg_update
BEFORE UPDATE ON nsi_organization FOR EACH ROW
DECLARE
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
BEGIN
v_type_id := pkg_nsi.get_type_id('nsi_organization');
UPDATE nsi
SET modif_date = Trunc(Sysdate)
WHERE nsi_id = :NEW.nsi_id
AND nsi_type_id = v_type_id;
v_log_descr := 'name = ''' || :NEW.name || ''', full_name = ''' || :NEW.full_name || ''', inn = ''' || :NEW.inn || ''' ';
pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_UPDATE, v_log_descr);
END;
CREATE OR REPLACE TRIGGER nsi_organization_trg_delete
AFTER DELETE ON nsi_organization FOR EACH ROW
DECLARE
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
BEGIN
v_type_id := pkg_nsi.get_type_id('nsi_organization');
DELETE FROM nsi
WHERE nsi_id = :OLD.nsi_id
AND nsi_type_id = v_type_id;
v_log_descr := 'name = ''' || :OLD.name || ''', full_name = ''' || :OLD.full_name || ''', inn = ''' || :OLD.inn || ''' ';
pkg_nsi.log_oper (:OLD.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_DELETE, v_log_descr);
END;
现在，添加记录将毫无问题地工作（无需指定键）。同时，第一条记录将出现在nsi表中，并且此事件也将记录在日志记录表中。INSERT INTO nsi_organization (name, full_name, inn)
VALUES (' " "', ' " "', '11223344');
但就目前而言，您仅会注意到创建某种目录表的额外费用，而没有注意到单一方法的优势。然后我们回想第四个问题-我们需要将数据的历史性存储在目录的表中，以及检索给定日期的当前状态。CREATE TABLE nsi_history (
nsi_history_id NUMBER(10) NOT NULL,
nsi_id NUMBER(10) NOT NULL,
nsi_type_id NUMBER(10) NOT NULL,
version NUMBER(10) NOT NULL,
content CLOB NOT NULL,
note VARCHAR2(100),
begin_date DATE NOT NULL,
end_date DATE NOT NULL,
CONSTRAINT nsi_history_pk PRIMARY KEY (nsi_history_id),
CONSTRAINT nsi_history_nsi_type_fk FOREIGN KEY (nsi_type_id) REFERENCES nsi_type (nsi_type_id),
CONSTRAINT nsi_history_nsi_fk FOREIGN KEY (nsi_id) REFERENCES nsi (nsi_id),
CONSTRAINT nsi_history_content_json_chk CHECK (content IS JSON)
);
COMMENT ON TABLE nsi_history IS ' ';
COMMENT ON COLUMN nsi_history.nsi_history_id IS '';
COMMENT ON COLUMN nsi_history.nsi_id IS '';
COMMENT ON COLUMN nsi_history.nsi_type_id IS ' ';
COMMENT ON COLUMN nsi_history.version IS '';
COMMENT ON COLUMN nsi_history.content IS ' ';
COMMENT ON COLUMN nsi_history.note IS '';
COMMENT ON COLUMN nsi_history.begin_date IS ' ';
COMMENT ON COLUMN nsi_history.end_date IS ' ';
在软件包pkg_nsi中，我们添加了将记录保存到历史表的功能。我们将以json格式存储记录，因此该包还将有机会为传输的请求获取json。
FUNCTION get_json(p_query IN VARCHAR2)
RETURN CLOB
AS
v_theCursor integer default dbms_sql.open_cursor;
v_columnValue varchar2(4000);
v_status integer;
v_descTbl dbms_sql.desc_tab;
v_colCnt number;
v_res clob;
BEGIN
dbms_sql.parse(v_theCursor, p_query, dbms_sql.native);
dbms_sql.describe_columns( v_theCursor, v_colCnt, v_descTbl);
FOR i IN 1 .. v_colCnt LOOP
dbms_sql.define_column(v_theCursor, i, v_columnValue, 4000);
END LOOP;
v_status := dbms_sql.execute(v_theCursor);
WHILE ( dbms_sql.fetch_rows(v_theCursor) > 0 ) LOOP
FOR i IN 1 .. v_colCnt LOOP
dbms_sql.column_value( v_theCursor, i, v_columnValue );
IF i > 1 THEN
v_res := v_res || ', ';
END IF;
v_res := v_res || '"' || v_descTbl(i).col_name || '" : "' || replace(v_columnValue, '"', '\"') || '"';
END LOOP;
EXIT;
END LOOP;
RETURN '{' || v_res || '}';
exception
when others then dbms_sql.close_cursor( v_theCursor ); RAISE;
END get_json;
PROCEDURE nsi_history_push (
p_nsi_id IN nsi.nsi_id%TYPE,
p_nsi_type_id IN nsi_type.nsi_type_id%TYPE,
p_end_date IN nsi_history.end_date%TYPE,
p_note IN nsi_history.note%TYPE)
AS
v_table_name VARCHAR2(50);
v_content CLOB;
v_max_ver NUMBER;
v_begin_date DATE;
BEGIN
IF (p_end_date IS NULL) THEN
RAISE_APPLICATION_ERROR (NSI_ERROR_CODE,
'[nsi_history_push] .');
END IF;
IF (Trunc(p_end_date) > Trunc(Sysdate) ) THEN
RAISE_APPLICATION_ERROR (NSI_ERROR_CODE,
'[nsi_history_push] .');
END IF;
SELECT begin_date INTO v_begin_date
FROM nsi
WHERE nsi_id = p_nsi_id
AND nsi_type_id = p_nsi_type_id;
IF (Trunc(p_end_date) < Trunc(v_begin_date) ) THEN
RAISE_APPLICATION_ERROR (NSI_ERROR_CODE,
'[nsi_history_push] .');
END IF;
v_table_name := get_table_name(p_nsi_type_id);
v_content := get_json ('select * from ' || v_table_name || ' where nsi_id=' || p_nsi_id);
SELECT MAX(version) INTO v_max_ver
FROM nsi_history
WHERE nsi_id = p_nsi_id
AND nsi_type_id = p_nsi_type_id;
IF (v_max_ver IS NULL) THEN
v_max_ver := 0;
END IF;
v_max_ver := v_max_ver + 1;
UPDATE nsi
SET begin_date = Trunc(p_end_date) + 1
WHERE nsi_id = p_nsi_id
AND nsi_type_id = p_nsi_type_id;
INSERT INTO nsi_history
(nsi_history_id, nsi_id, nsi_type_id, version, content, note, begin_date, end_date)
VALUES (get_nsi_id, p_nsi_id, p_nsi_type_id, v_max_ver, v_content, p_note, v_begin_date, Trunc(p_end_date));
log_oper(p_nsi_id, p_nsi_type_id, NSI_LOG_OPERNUM_HISTORY_PUSH, v_content);
END nsi_history_push;
因此，任何目录都可以使用此功能来捕获历史记录中的当前状态。很好，至少可以从这种概括中得到一些有用的东西）））要提取目录的当前状态，请将相应的管道函数添加到包中。目录条目将以系统字段扩展的类型返回。
TYPE nsi_organization_rec IS RECORD(
nsi_id nsi_organization.nsi_id%TYPE,
name nsi_organization.name%TYPE,
full_name nsi_organization.full_name%TYPE,
inn nsi_organization.inn%TYPE,
nsi_type_id nsi.nsi_type_id%TYPE,
create_date nsi.create_date%TYPE,
modif_date nsi.create_date%TYPE,
version nsi_history.version%TYPE,
begin_date nsi.begin_date%TYPE,
end_date nsi_history.end_date%TYPE
);
TYPE nsi_organization_list IS TABLE OF nsi_organization_rec;
FUNCTION nsi_organization_table(p_date IN DATE := null)
RETURN nsi_organization_list PIPELINED
AS
v_date date;
BEGIN
v_date := Trunc(Sysdate);
IF p_date IS NOT NULL THEN
v_date := Trunc(p_date);
END IF;
FOR rec IN (
SELECT
o.nsi_id, o.name, o.full_name, o.inn,
n.nsi_type_id, n.create_date, n.modif_date,
0 AS version, n.begin_date, to_date(null) AS end_date
FROM
nsi_organization o INNER JOIN nsi n
ON (o.nsi_id = n.nsi_id)
WHERE
n.begin_date <= v_date
UNION ALL
SELECT
n.nsi_id,
json_value(h.content, '$.NAME') AS name,
json_value(h.content, '$.FULL_NAME') AS full_name,
json_value(h.content, '$.INN') AS inn,
n.nsi_type_id, n.create_date, n.modif_date,
h.version, h.begin_date, h.end_date
FROM
nsi_history h INNER JOIN nsi n
ON (h.nsi_id = n.nsi_id AND h.nsi_type_id = n.nsi_type_id)
WHERE
h.begin_date <= v_date
AND h.end_date >= v_date
) LOOP
PIPE ROW (rec);
END LOOP;
END nsi_organization_table;
适用于我们的nsi_organization表。select * from nsi where nsi_id=1;
"NSI_ID" "NSI_TYPE_ID" "DESCR" "CREATE_DATE" "MODIF_DATE" "BEGIN_DATE"
1 1 " "" """ 11.03.20 11.03.20 11.03.20
begin
pkg_nsi.nsi_history_push(202, 1, sysdate, ' ');
end;
select * from nsi_history;
"NSI_HISTORY_ID" "NSI_ID" "NSI_TYPE_ID" "VERSION" "CONTENT" "NOTE" "BEGIN_DATE" "END_DATE"
205 1 1 1 "{""NSI_ID"" : ""1"", ""NAME"" : "" \"" \"""", ""FULL_NAME"" : "" \"" \"""", ""INN"" : ""11223344""}" " " 11.03.20 11.03.20
select * from nsi where nsi_id=1;
"NSI_ID" "NSI_TYPE_ID" "DESCR" "CREATE_DATE" "MODIF_DATE" "BEGIN_DATE"
1 1 " "" """ 11.03.20 11.03.20 12.03.20
update nsi_organization set inn='99887766' where nsi_id=1;
select * from table(pkg_nsi.nsi_organization_table(sysdate));
"NSI_ID" "NAME" "FULL_NAME" "INN" "NSI_TYPE_ID" "CREATE_DATE" "MODIF_DATE" "VERSION" "BEGIN_DATE" "END_DATE"
1 " "" """ " "" """ "11223344" 1 11.03.20 11.03.20 1 11.03.20 11.03.20
select * from table(pkg_nsi.nsi_organization_table(sysdate+1));
"NSI_ID" "NAME" "FULL_NAME" "INN" "NSI_TYPE_ID" "CREATE_DATE" "MODIF_DATE" "VERSION" "BEGIN_DATE" "END_DATE"
1 " "" """ " "" """ "99887766" 1 11.03.20 11.03.20 0 12.03.20
nsi_organization_table函数非常有用，因为它满足了我们的要求，并最终解决了第四个问题。继续。由于引入用于处理所有目录的统一方法具有如此优势，因此我们还将使用它来存储可分配给任何目录中任何条目的其他信息。这种机制长期存在，称为EAV模式，我们正在实现它。
NSI_ATTRIBUTE_TYPE_STRING NUMBER := 1;
NSI_ATTRIBUTE_TYPE_INT NUMBER := 2;
NSI_ATTRIBUTE_TYPE_DOUBLE NUMBER := 3;
NSI_ATTRIBUTE_TYPE_DATE NUMBER := 4;
CREATE TABLE nsi_attribute_type (
nsi_attribute_type_id NUMBER(10) NOT NULL,
value_type NUMBER NOT NULL,
descr VARCHAR2(100) NOT NULL,
CONSTRAINT nsi_attribute_type_pk PRIMARY KEY (nsi_attribute_type_id),
CONSTRAINT nsi_attribute_type_ch CHECK (value_type IN (1, 2, 3, 4)),
CONSTRAINT nsi_attribute_type_fk FOREIGN KEY (nsi_attribute_type_id) REFERENCES nsi (nsi_id)
);
COMMENT ON TABLE nsi_attribute_type IS '. ';
COMMENT ON COLUMN nsi_attribute_type.nsi_attribute_type_id IS '';
COMMENT ON COLUMN nsi_attribute_type.value_type IS ' (1 - , 2 - , 3 - , 4 - )';
COMMENT ON COLUMN nsi_attribute_type.descr IS '';
CREATE TABLE nsi_attribute (
nsi_attribute_id NUMBER(10) NOT NULL,
nsi_attribute_type_id NUMBER(10) NOT NULL,
nsi_id NUMBER(10) NOT NULL,
nsi_type_id NUMBER(10) NOT NULL,
value_1 VARCHAR2(100),
value_2_3 NUMBER,
value_4 DATE,
begin_date DATE,
end_date DATE,
CONSTRAINT nsi_attribute_pk PRIMARY KEY (nsi_attribute_id),
CONSTRAINT nsi_attribute_type_fk FOREIGN KEY (nsi_attribute_type_id) REFERENCES nsi_attribute_type (nsi_attribute_type_id),
CONSTRAINT nsi_attribute_nsi_fk FOREIGN KEY (nsi_id, nsi_type_id) REFERENCES nsi (nsi_id, nsi_type_id)
);
COMMENT ON TABLE nsi_attribute IS '. ';
COMMENT ON COLUMN nsi_attribute.nsi_attribute_id IS '';
COMMENT ON COLUMN nsi_attribute.nsi_attribute_type_id IS ' ';
COMMENT ON COLUMN nsi_attribute.nsi_id IS '';
COMMENT ON COLUMN nsi_attribute.nsi_type_id is ' ';
COMMENT ON COLUMN nsi_attribute.value_1 IS ' ';
COMMENT ON COLUMN nsi_attribute.value_2_3 IS ' ';
COMMENT ON COLUMN nsi_attribute.value_4 IS ' ';
COMMENT ON COLUMN nsi_attribute.begin_date IS ' ';
COMMENT ON COLUMN nsi_attribute.end_date IS ' ';
通常，在文档的上下文中，在某些情况下必须使用专有名称，因此我们将创建一个包含个人的新表，并类似于组织，添加触发器处理和选择类型。CREATE TABLE nsi_person (
nsi_id NUMBER(10) NOT NULL,
surname VARCHAR2(30) NOT NULL,
name VARCHAR2(30) NOT NULL,
patronymic VARCHAR2(30) NOT NULL,
birthday DATE,
CONSTRAINT nsi_person_pk PRIMARY KEY (nsi_id),
CONSTRAINT nsi_person_nsi_fk FOREIGN KEY (nsi_id) REFERENCES nsi (nsi_id)
);
COMMENT ON TABLE nsi_person IS '. ';
COMMENT ON COLUMN nsi_person.nsi_id IS '';
COMMENT ON COLUMN nsi_person.surname IS '';
COMMENT ON COLUMN nsi_person.name IS '';
COMMENT ON COLUMN nsi_person.patronymic IS '';
COMMENT ON COLUMN nsi_person.birthday IS ' ';
CREATE OR REPLACE TRIGGER nsi_person_trg_insert
BEFORE INSERT ON nsi_person FOR EACH ROW
DECLARE
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
v_log_query VARCHAR(4000);
BEGIN
v_type_id := pkg_nsi.get_type_id('nsi_person');
:NEW.nsi_id := pkg_nsi.get_nsi_id();
INSERT INTO nsi (nsi_id, nsi_type_id, descr, create_date, modif_date, begin_date)
VALUES (:NEW.nsi_id, v_type_id, :NEW.name, Trunc(Sysdate), Trunc(Sysdate), Trunc(Sysdate));
v_log_query := 'SELECT ''' || :NEW.surname || ''' AS surname, ''' || :NEW.name || ''' AS name, ''' || :NEW.patronymic || ''' AS patronymic, to_date(''' || :NEW.birthday || ''', ''dd.mm.yy'') AS birthday FROM DUAL';
v_log_descr := pkg_nsi.get_json(v_log_query);
pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_INSERT, v_log_descr);
END;
CREATE OR REPLACE TRIGGER nsi_person_trg_update
BEFORE UPDATE ON nsi_person FOR EACH ROW
DECLARE
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
v_log_query VARCHAR(4000);
BEGIN
v_type_id := pkg_nsi.get_type_id('nsi_person');
UPDATE nsi
SET modif_date = Trunc(Sysdate)
WHERE nsi_id = :NEW.nsi_id
AND nsi_type_id = v_type_id;
v_log_query := 'SELECT ''' || :NEW.surname || ''' AS surname, ''' || :NEW.name || ''' AS name, ''' || :NEW.patronymic || ''' AS patronymic, to_date(''' || :NEW.birthday || ''', ''dd.mm.yy'') AS birthday FROM DUAL';
v_log_descr := pkg_nsi.get_json(v_log_query);
pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_UPDATE, v_log_descr);
END;
CREATE OR REPLACE TRIGGER nsi_person_trg_delete
AFTER DELETE ON nsi_person FOR EACH ROW
DECLARE
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
v_log_query VARCHAR(4000);
BEGIN
v_type_id := pkg_nsi.get_type_id('nsi_person');
DELETE FROM nsi_history
WHERE nsi_id = :OLD.nsi_id
AND nsi_type_id = v_type_id;
DELETE FROM nsi_attribute
WHERE nsi_id = :OLD.nsi_id
AND nsi_type_id = v_type_id;
DELETE FROM nsi
WHERE nsi_id = :OLD.nsi_id
AND nsi_type_id = v_type_id;
v_log_query := 'SELECT ''' || :OLD.surname || ''' AS surname, ''' || :OLD.name || ''' AS name, ''' || :OLD.patronymic || ''' AS patronymic, to_date(''' || :OLD.birthday || ''', ''dd.mm.yy'') AS birthday FROM DUAL';
v_log_descr := pkg_nsi.get_json(v_log_query);
pkg_nsi.log_oper (:OLD.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_DELETE, v_log_descr);
END;
仍然需要用此表的处理来补充pkg_nsi软件包。
TYPE nsi_person_rec IS RECORD(
nsi_id nsi_person.nsi_id%TYPE,
surname nsi_person.surname%TYPE,
name nsi_person.name%TYPE,
patronymic nsi_person.patronymic%TYPE,
birthday nsi_person.birthday%TYPE,
nsi_type_id nsi.nsi_type_id%TYPE,
create_date nsi.create_date%TYPE,
modif_date nsi.create_date%TYPE,
version nsi_history.version%TYPE,
begin_date nsi.begin_date%TYPE,
end_date nsi_history.end_date%TYPE
);
TYPE nsi_person_list IS TABLE OF nsi_person_rec;
FUNCTION nsi_person_table(p_date IN DATE := null)
RETURN nsi_person_list PIPELINED
AS
v_date date;
BEGIN
v_date := Trunc(Sysdate);
IF p_date IS NOT NULL THEN
v_date := Trunc(p_date);
END IF;
FOR rec IN (
SELECT
p.nsi_id, p.surname, p.name, p.patronymic, p.birthday,
n.nsi_type_id, n.create_date, n.modif_date,
0 AS version, n.begin_date, to_date(null) AS end_date
FROM
nsi_person p INNER JOIN nsi n
ON (p.nsi_id = n.nsi_id)
WHERE
n.begin_date <= v_date
UNION ALL
SELECT
n.nsi_id,
json_value(h.content, '$.SURNAME') AS surname,
json_value(h.content, '$.NAME') AS name,
json_value(h.content, '$.PATRONYMIC') AS patronymic,
to_date(json_value(h.content, '$.BIRTHDAY')) AS birthday,
n.nsi_type_id, n.create_date, n.modif_date,
h.version, h.begin_date, h.end_date
FROM
nsi_history h INNER JOIN nsi n
ON (h.nsi_id = n.nsi_id AND h.nsi_type_id = n.nsi_type_id)
WHERE
h.begin_date <= v_date
AND h.end_date >= v_date
) LOOP
PIPE ROW (rec);
END LOOP;
END nsi_person_table;
并将某人添加到此表。INSERT INTO nsi_person
(surname, name, patronymic, birthday)
VALUES ('', '', '', to_date('22.12.70', 'dd.mm.yy'));
为最抢手的一般情况创建属性。INSERT INTO nsi_attribute_type (nsi_attribute_type_id, value_type, descr)
VALUES (1, 1, ' . ');
INSERT INTO nsi_attribute_type (nsi_attribute_type_id, value_type, descr)
VALUES (2, 1, ' . ');
INSERT INTO nsi_attribute_type (nsi_attribute_type_id, value_type, descr)
VALUES (3, 1, ' . ');
在pkg_nsi包中，我们添加了用于处理目录属性的函数。
PROCEDURE get_attribute_type (
p_nsi_attribute_type_id IN nsi_attribute_type.nsi_attribute_type_id%TYPE,
p_value_type OUT nsi_attribute_type.value_type%TYPE,
p_descr OUT nsi_attribute_type.descr%TYPE)
AS
BEGIN
SELECT value_type, descr
INTO p_value_type, p_descr
FROM nsi_attribute_type
WHERE nsi_attribute_type_id = p_nsi_attribute_type_id;
END;
PROCEDURE nsi_attribute_insert (
p_nsi_attribute_type_id IN nsi_attribute.nsi_attribute_type_id%TYPE,
p_nsi_id IN nsi_attribute.nsi_id%TYPE,
p_nsi_type_id IN nsi_attribute.nsi_type_id%TYPE,
p_value_1 IN nsi_attribute.value_1%TYPE,
p_value_2_3 IN nsi_attribute.value_2_3%TYPE,
p_value_4 IN nsi_attribute.value_4%TYPE,
p_begin_date IN nsi_attribute.begin_date%TYPE,
p_end_date IN nsi_attribute.end_date%TYPE)
AS
v_id NUMBER;
v_log_descr nsi_log.descr%TYPE;
v_value_type nsi_attribute_type.value_type%TYPE;
v_descr nsi_attribute_type.descr%TYPE;
BEGIN
v_id := get_nsi_id;
get_attribute_type(p_nsi_attribute_type_id, v_value_type, v_descr);
IF (v_value_type = NSI_ATTRIBUTE_TYPE_STRING) THEN
INSERT INTO nsi_attribute
(nsi_attribute_id, nsi_attribute_type_id, nsi_id, nsi_type_id,
value_1, value_2_3, value_4, begin_date, end_date)
VALUES (v_id, p_nsi_attribute_type_id, p_nsi_id, p_nsi_type_id,
p_value_1, null, null, p_begin_date, p_end_date);
v_log_descr := p_value_1;
ELSIF (v_value_type IN (NSI_ATTRIBUTE_TYPE_INT, NSI_ATTRIBUTE_TYPE_DOUBLE)) THEN
INSERT INTO nsi_attribute
(nsi_attribute_id, nsi_attribute_type_id, nsi_id, nsi_type_id,
value_1, value_2_3, value_4, begin_date, end_date)
VALUES (v_id, p_nsi_attribute_type_id, p_nsi_id, p_nsi_type_id,
null, p_value_2_3, null, p_begin_date, p_end_date);
v_log_descr := p_value_2_3;
ELSE
INSERT INTO nsi_attribute
(nsi_attribute_id, nsi_attribute_type_id, nsi_id, nsi_type_id,
value_1, value_2_3, value_4, begin_date, end_date)
VALUES (v_id, p_nsi_attribute_type_id, p_nsi_id, p_nsi_type_id,
null, null, p_value_4, p_begin_date, p_end_date);
v_log_descr := p_value_4;
END IF;
v_log_descr := '[' || get_nsi_descr(p_nsi_id, p_nsi_type_id) || '] ' ||
' : ' || v_descr ||
' : ' || v_log_descr ||
' : ' || p_begin_date || ' - ' || p_end_date;
log_oper(p_nsi_id, p_nsi_type_id, NSI_LOG_OPERNUM_ATTR_INSERT, v_log_descr);
END;
PROCEDURE nsi_attribute_value (
p_nsi_attribute_id IN nsi_attribute.nsi_attribute_id%TYPE,
p_value_1 IN nsi_attribute.value_1%TYPE,
p_value_2_3 IN nsi_attribute.value_2_3%TYPE,
p_value_4 IN nsi_attribute.value_4%TYPE)
AS
v_nsi_id nsi.nsi_id%TYPE;
v_nsi_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
v_value_type nsi_attribute_type.value_type%TYPE;
v_descr nsi_attribute_type.descr%TYPE;
v_nsi_attribute_type_id nsi_attribute.nsi_attribute_type_id%TYPE;
BEGIN
SELECT nsi_attribute_type_id, nsi_id, nsi_type_id
INTO v_nsi_attribute_type_id, v_nsi_id, v_nsi_type_id
FROM nsi_attribute
WHERE nsi_attribute_id = p_nsi_attribute_id;
get_attribute_type(v_nsi_attribute_type_id, v_value_type, v_descr);
IF (v_value_type = NSI_ATTRIBUTE_TYPE_STRING) THEN
UPDATE nsi_attribute
SET value_1 = p_value_1,
value_2_3 = null,
value_4 = null
WHERE nsi_attribute_id = p_nsi_attribute_id;
v_log_descr := p_value_1;
ELSIF (v_value_type IN (NSI_ATTRIBUTE_TYPE_INT, NSI_ATTRIBUTE_TYPE_DOUBLE)) THEN
UPDATE nsi_attribute
SET value_1 = null,
value_2_3 = p_value_2_3,
value_4 = null
WHERE nsi_attribute_id = p_nsi_attribute_id;
v_log_descr := p_value_2_3;
ELSE
UPDATE nsi_attribute
SET value_1 = null,
value_2_3 = null,
value_4 = p_value_4
WHERE nsi_attribute_id = p_nsi_attribute_id;
v_log_descr := p_value_4;
END IF;
v_log_descr := '[' || get_nsi_descr(v_nsi_id, v_nsi_type_id) || '] ' ||
' : ' || v_descr ||
' : ' || v_log_descr;
log_oper(v_nsi_id, v_nsi_type_id, NSI_LOG_OPERNUM_ATTR_UPDATE, v_log_descr);
END;
PROCEDURE nsi_attribute_period (
p_nsi_attribute_id IN nsi_attribute.nsi_attribute_id%TYPE,
p_begin_date IN nsi_attribute.begin_date%TYPE,
p_end_date IN nsi_attribute.end_date%TYPE)
AS
v_nsi_id nsi.nsi_id%TYPE;
v_nsi_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
v_value_type nsi_attribute_type.value_type%TYPE;
v_descr nsi_attribute_type.descr%TYPE;
v_nsi_attribute_type_id nsi_attribute.nsi_attribute_type_id%TYPE;
BEGIN
SELECT nsi_id, nsi_type_id, nsi_attribute_type_id
INTO v_nsi_id, v_nsi_type_id, v_nsi_attribute_type_id
FROM nsi_attribute
WHERE nsi_attribute_id = p_nsi_attribute_id;
get_attribute_type(v_nsi_attribute_type_id, v_value_type, v_descr);
UPDATE nsi_attribute
SET begin_date = p_begin_date,
end_date = p_end_date
WHERE nsi_attribute_id = p_nsi_attribute_id;
v_log_descr := '[' || get_nsi_descr(v_nsi_id, v_nsi_type_id) || '] ' ||
' : ' || v_descr ||
' : ' || p_begin_date || ' - ' || p_end_date;
log_oper(v_nsi_id, v_nsi_type_id, NSI_LOG_OPERNUM_ATTR_UPDATE, v_log_descr);
END;
PROCEDURE nsi_attribute_delete (p_nsi_attribute_id nsi_attribute.nsi_attribute_id%TYPE)
AS
v_nsi_id nsi.nsi_id%TYPE;
v_nsi_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
v_value_type nsi_attribute_type.value_type%TYPE;
v_descr nsi_attribute_type.descr%TYPE;
v_nsi_attribute_type_id nsi_attribute.nsi_attribute_type_id%TYPE;
BEGIN
SELECT nsi_id, nsi_type_id, nsi_attribute_type_id
INTO v_nsi_id, v_nsi_type_id, v_nsi_attribute_type_id
FROM nsi_attribute
WHERE nsi_attribute_id = p_nsi_attribute_id;
get_attribute_type(v_nsi_attribute_type_id, v_value_type, v_descr);
DELETE FROM nsi_attribute
WHERE nsi_attribute_id = p_nsi_attribute_id;
v_log_descr := '[' || get_nsi_descr(v_nsi_id, v_nsi_type_id) || '] ' ||
' : ' || v_descr;
log_oper(v_nsi_id, v_nsi_type_id, NSI_LOG_OPERNUM_ATTR_DELETE, v_log_descr);
END;
现在分配适当的属性。begin
pkg_nsi.nsi_attribute_insert(1, 225, 6, '', null, null, sysdate, null);
pkg_nsi.nsi_attribute_insert(2, 225, 6, '', null, null, sysdate, null);
pkg_nsi.nsi_attribute_insert(3, 225, 6, '', null, null, sysdate, null);
end;
"NSI_ATTRIBUTE_ID" "NSI_ATTRIBUTE_TYPE_ID" "NSI_ID" "NSI_TYPE_ID" "VALUE_1" "VALUE_2_3" "VALUE_4" "BEGIN_DATE" "END_DATE"
230 1 225 6 "" 11.03.20
232 2 225 6 "" 11.03.20
234 3 225 6 "" 11.03.20
begin
pkg_nsi.nsi_attribute_value(230, '', null, null);
end;
"NSI_ATTRIBUTE_ID" "NSI_ATTRIBUTE_TYPE_ID" "NSI_ID" "NSI_TYPE_ID" "VALUE_1" "VALUE_2_3" "VALUE_4" "BEGIN_DATE" "END_DATE"
230 1 225 6 "" 11.03.20
232 2 225 6 "" 11.03.20
234 3 225 6 "" 11.03.20
begin
pkg_nsi.nsi_attribute_period(230, sysdate-1, null);
pkg_nsi.nsi_attribute_period(232, sysdate-1, null);
pkg_nsi.nsi_attribute_period(234, sysdate-1, null);
end;
"NSI_ATTRIBUTE_ID" "NSI_ATTRIBUTE_TYPE_ID" "NSI_ID" "NSI_TYPE_ID" "VALUE_1" "VALUE_2_3" "VALUE_4" "BEGIN_DATE" "END_DATE"
230 1 225 6 "" 10.03.20
232 2 225 6 "" 10.03.20
234 3 225 6 "" 10.03.20
因此，我们将克服第三个问题。除了NSI系统中的参考表外，它们之间的关系也很重要。因此，例如，大型组织包括各种单位，分支机构，部门等，它们可以构建为树状结构。首先，我们将在我们的系统中创建更多组织，这些组织将隶属于现有的“角逐”。INSERT INTO nsi_organization (name, full_name, inn)
VALUES (' ', ' ', '1111111111');
INSERT INTO nsi_organization (name, full_name, inn)
VALUES (' ', ' ', '2222222222');
INSERT INTO nsi_organization (name, full_name, inn)
VALUES (' ', ' ', '3333333333');
281 1 13.03.20 13.03.20 13.03.20
283 1 13.03.20 13.03.20 13.03.20
285 1 13.03.20 13.03.20 13.03.20
1 1 " " 11.03.20 13.03.20 12.03.20
现在有必要表明这些组织之间的关系。为此，您需要一个具有树结构的表并指示操作周期，因为所有内容都会随时间变化，因此您需要考虑到这一点。CREATE TABLE nsi_structure (
nsi_structure_id NUMBER(10) NOT NULL,
nsi_parent_structure_id NUMBER(10),
nsi_id NUMBER(10) NOT NULL,
nsi_type_id NUMBER(10) NOT NULL,
ordnum NUMBER,
begin_date DATE NOT NULL,
end_date DATE,
CONSTRAINT nsi_structure_pk PRIMARY KEY (nsi_structure_id),
CONSTRAINT nsi_parent_struct_fk FOREIGN KEY (nsi_parent_structure_id) REFERENCES nsi_structure (nsi_structure_id),
CONSTRAINT nsi_struct_nsi_fk FOREIGN KEY (nsi_id, nsi_type_id) REFERENCES nsi (nsi_id, nsi_type_id)
);
COMMENT ON TABLE nsi_structure IS '. ';
COMMENT ON COLUMN nsi_structure.nsi_structure_id IS '';
COMMENT ON COLUMN nsi_structure.nsi_parent_structure_id IS ' ';
COMMENT ON COLUMN nsi_structure.nsi_id IS '';
COMMENT ON COLUMN nsi_structure.nsi_type_id IS ' ';
COMMENT ON COLUMN nsi_structure.ordnum IS ' ';
COMMENT ON COLUMN nsi_structure.begin_date IS ' ';
COMMENT ON COLUMN nsi_structure.end_date IS ' ';
当然，您应该扩展pkg_nsi软件包的功能，以便可以自定义各种表的结构。
FUNCTION nsi_structure_insert (
p_nsi_parent_structure_id IN nsi_structure.nsi_parent_structure_id%TYPE,
p_nsi_id IN nsi_structure.nsi_id%TYPE,
p_nsi_type_id IN nsi_structure.nsi_type_id%TYPE,
p_ordnum IN nsi_structure.ordnum%TYPE,
p_begin_date IN nsi_structure.begin_date%TYPE,
p_end_date IN nsi_structure.end_date%TYPE)
RETURN nsi_structure.nsi_structure_id%TYPE
AS
v_id NUMBER;
v_log_descr nsi_log.descr%TYPE;
v_type_id nsi.nsi_type_id%TYPE;
BEGIN
v_id := get_nsi_id;
v_type_id := get_type_id('nsi_structure');
INSERT INTO nsi_structure (
nsi_structure_id, nsi_parent_structure_id,
nsi_id, nsi_type_id, ordnum, begin_date, end_date)
VALUES (
v_id, p_nsi_parent_structure_id,
p_nsi_id, p_nsi_type_id, p_ordnum, Trunc(p_begin_date), Trunc(p_end_date));
v_log_descr := '[' || get_nsi_descr(p_nsi_id, p_nsi_type_id) || '] ';
v_log_descr := v_log_descr || ' ' || p_begin_date || ' - ' || p_end_date;
log_oper (v_id, v_type_id, NSI_LOG_OPERNUM_INSERT, v_log_descr);
RETURN v_id;
END nsi_structure_insert;
PROCEDURE nsi_structure_ordnum (
p_nsi_structure_id IN nsi_structure.nsi_structure_id%TYPE,
p_ordnum IN nsi_structure.ordnum%TYPE)
AS
v_nsi_id nsi_structure.nsi_id%TYPE;
v_nsi_type_id nsi_structure.nsi_type_id%TYPE;
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
BEGIN
v_type_id := get_type_id('nsi_structure');
SELECT nsi_id, nsi_type_id
INTO v_nsi_id, v_nsi_type_id
FROM nsi_structure
WHERE nsi_structure_id = p_nsi_structure_id;
UPDATE nsi_structure
SET ordnum = p_ordnum
WHERE nsi_structure_id = p_nsi_structure_id;
v_log_descr := '[' || get_nsi_descr(v_nsi_id, v_nsi_type_id) || '] ';
v_log_descr := v_log_descr || ' ' || p_ordnum;
log_oper (p_nsi_structure_id, v_type_id, NSI_LOG_OPERNUM_UPDATE, v_log_descr);
END;
PROCEDURE nsi_structure_period (
p_nsi_structure_id IN nsi_structure.nsi_structure_id%TYPE,
p_begin_date IN nsi_structure.begin_date%TYPE,
p_end_date IN nsi_structure.end_date%TYPE)
AS
v_nsi_id nsi_structure.nsi_id%TYPE;
v_nsi_type_id nsi_structure.nsi_type_id%TYPE;
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
BEGIN
v_type_id := get_type_id('nsi_structure');
SELECT nsi_id, nsi_type_id
INTO v_nsi_id, v_nsi_type_id
FROM nsi_structure
WHERE nsi_structure_id = p_nsi_structure_id;
UPDATE nsi_structure
SET begin_date = Trunc(p_begin_date),
end_date = Trunc(p_end_date)
WHERE nsi_structure_id = p_nsi_structure_id;
v_log_descr := '[' || get_nsi_descr(v_nsi_id, v_nsi_type_id) || '] ';
v_log_descr := v_log_descr || ' ' || p_begin_date || ' - ' || p_end_date;
log_oper (p_nsi_structure_id, v_type_id, NSI_LOG_OPERNUM_UPDATE, v_log_descr);
END;
PROCEDURE nsi_structure_delete (p_nsi_structure_id IN nsi_structure.nsi_structure_id%TYPE)
AS
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
BEGIN
v_type_id := pkg_nsi.get_type_id('nsi_structure');
FOR rec IN (
SELECT nsi_structure_id, nsi_parent_structure_id,
nsi_id, nsi_type_id, ordnum, begin_date, end_date
FROM nsi_structure
START WITH nsi_structure_id = p_nsi_structure_id
CONNECT BY PRIOR nsi_structure_id = nsi_parent_structure_id
)
LOOP
v_log_descr := '[' || pkg_nsi.get_nsi_descr(rec.nsi_id, rec.nsi_type_id) || '] ';
v_log_descr := v_log_descr || ' ' || rec.begin_date || ' - ' || rec.end_date;
pkg_nsi.log_oper (rec.nsi_structure_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_DELETE, v_log_descr);
END LOOP;
DELETE FROM nsi_structure
WHERE nsi_structure_id = p_nsi_structure_id;
END;
这种手段问世之后，人们就可以安全地建立组织之间的关系。declare
id number;
root_id number;
begin
root_id := pkg_nsi.nsi_structure_insert(null, 1, 1, null, to_date('13.02.20', 'dd.mm.yy'), null);
id := pkg_nsi.nsi_structure_insert(root_id, 281, 1, null, to_date('13.02.20', 'dd.mm.yy'), to_date('15.02.20', 'dd.mm.yy'));
id := pkg_nsi.nsi_structure_insert(root_id, 283, 1, null, to_date('13.02.20', 'dd.mm.yy'), null);
id := pkg_nsi.nsi_structure_insert(id, 285, 1, null, to_date('13.02.20', 'dd.mm.yy'), null);
end;
SELECT *
FROM nsi_structure
START WITH (nsi_parent_structure_id IS NULL)
CONNECT BY (nsi_parent_structure_id = PRIOR nsi_structure_id);
316 1 1 13.02.20
318 316 281 1 13.02.20 15.02.20
320 316 283 1 13.02.20
322 320 285 1 13.02.20
begin
pkg_nsi.nsi_structure_ordnum(320, 1);
pkg_nsi.nsi_structure_ordnum(318, 2);
end;
SELECT *
FROM nsi_structure
START WITH (nsi_parent_structure_id IS NULL)
CONNECT BY (nsi_parent_structure_id = PRIOR nsi_structure_id)
ORDER SIBLINGS BY ordnum;
316 1 1 13.02.20
320 316 283 1 1 13.02.20
322 320 285 1 13.02.20
318 316 281 1 2 13.02.20 15.02.20
begin
pkg_nsi.nsi_structure_period(320, to_date('14.02.20', 'dd.mm.yy'), to_date('14.02.20', 'dd.mm.yy'));
end;
SELECT *
FROM nsi_structure
START WITH (nsi_parent_structure_id IS NULL)
CONNECT BY (nsi_parent_structure_id = PRIOR nsi_structure_id)
ORDER SIBLINGS BY ordnum;
316 1 1 13.02.20
320 316 283 1 1 14.02.20 14.02.20
322 320 285 1 13.02.20
318 316 281 1 2 13.02.20 15.02.20
由于目录与结构是分开的，因此每次都要考虑到组织之间的关系变得麻烦时，因此我们可以简化我们的工作。CREATE OR REPLACE VIEW V_NSI_ORGANIZATION AS
SELECT
s.nsi_structure_id, s.nsi_parent_structure_id,
s.ordnum, s.begin_date, s.end_date,
s.nsi_id, s.nsi_type_id, o.name, o.full_name, o.inn
FROM nsi_structure s INNER JOIN nsi_organization o
ON (s.nsi_id = o.nsi_id)
START WITH (nsi_parent_structure_id IS NULL)
CONNECT BY (nsi_parent_structure_id = PRIOR nsi_structure_id)
ORDER SIBLINGS BY ordnum;
SELECT * FROM v_nsi_organization;
316 13.02.20 1 1 " " " " 99887766
320 316 1 14.02.20 14.02.20 283 1 2222222222
322 320 13.02.20 285 1 3333333333
318 316 2 13.02.20 15.02.20 281 1 1111111111
我们正在构建一棵树的事实非常棒，但是该树的所有节点都属于一个实体，我们的任务是实现在不同实体之间建立关系。这也不是问题，因为该结构没有绑定到任何特定的参考书，而是作为一个整体在整个NSI系统上起作用。例如，我们将为国家公务员职位构造一个分类器，为市政府职位构造一个分类器。CREATE TABLE nsi_classifier (
nsi_id NUMBER(10) NOT NULL,
code VARCHAR2(10),
name VARCHAR2(200) NOT NULL,
CONSTRAINT nsi_classifier_pk PRIMARY KEY (nsi_id),
CONSTRAINT nsi_classifier_nsi_fk FOREIGN KEY (nsi_id) REFERENCES nsi (nsi_id)
);
COMMENT ON TABLE nsi_classifier IS '. ';
COMMENT ON COLUMN nsi_classifier.nsi_id IS '';
COMMENT ON COLUMN nsi_classifier.code IS '';
COMMENT ON COLUMN nsi_classifier.name IS '';
CREATE OR REPLACE TRIGGER nsi_classifier_trg_insert
BEFORE INSERT ON nsi_classifier FOR EACH ROW
DECLARE
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
v_log_query VARCHAR(4000);
BEGIN
v_type_id := pkg_nsi.get_type_id('nsi_classifier');
:NEW.nsi_id := pkg_nsi.get_nsi_id();
INSERT INTO nsi (nsi_id, nsi_type_id, descr, create_date, modif_date, begin_date)
VALUES (:NEW.nsi_id, v_type_id, :NEW.name, Trunc(Sysdate), Trunc(Sysdate), Trunc(Sysdate));
v_log_query := 'SELECT ''' || :NEW.name || ''' AS name, ''' || :NEW.code || ''' AS code FROM DUAL';
v_log_descr := pkg_nsi.get_json(v_log_query);
pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_INSERT, v_log_descr);
END;
CREATE OR REPLACE TRIGGER nsi_classifier_trg_update
BEFORE UPDATE ON nsi_classifier FOR EACH ROW
DECLARE
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
v_log_query VARCHAR(4000);
BEGIN
v_type_id := pkg_nsi.get_type_id('nsi_classifier');
UPDATE nsi
SET modif_date = Trunc(Sysdate)
WHERE nsi_id = :NEW.nsi_id
AND nsi_type_id = v_type_id;
v_log_query := 'SELECT ''' || :NEW.name || ''' AS name, ''' || :NEW.code || ''' AS code FROM DUAL';
v_log_descr := pkg_nsi.get_json(v_log_query);
pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_UPDATE, v_log_descr);
END;
CREATE OR REPLACE TRIGGER nsi_classifier_trg_delete
AFTER DELETE ON nsi_classifier FOR EACH ROW
DECLARE
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
v_log_query VARCHAR(4000);
BEGIN
v_type_id := pkg_nsi.get_type_id('nsi_classifier');
DELETE FROM nsi_history
WHERE nsi_id = :OLD.nsi_id
AND nsi_type_id = v_type_id;
DELETE FROM nsi_attribute
WHERE nsi_id = :OLD.nsi_id
AND nsi_type_id = v_type_id;
DELETE FROM nsi
WHERE nsi_id = :OLD.nsi_id
AND nsi_type_id = v_type_id;
v_log_query := 'SELECT ''' || :OLD.name || ''' AS name, ''' || :OLD.code || ''' AS code FROM DUAL';
v_log_descr := pkg_nsi.get_json(v_log_query);
pkg_nsi.log_oper (:OLD.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_DELETE, v_log_descr);
END;
CREATE TABLE nsi_post_group (
nsi_id NUMBER(10) NOT NULL,
name VARCHAR2(50) NOT NULL,
CONSTRAINT nsi_post_group_pk PRIMARY KEY (nsi_id),
CONSTRAINT nsi_post_group_nsi_fk FOREIGN KEY (nsi_id) REFERENCES nsi (nsi_id)
);
COMMENT ON TABLE nsi_post_group is '. ';
COMMENT ON COLUMN nsi_post_group.nsi_id is '';
COMMENT ON COLUMN nsi_post_group.name is '';
CREATE OR REPLACE TRIGGER nsi_post_group_trg_insert
BEFORE INSERT ON nsi_post_group FOR EACH ROW
DECLARE
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
v_log_query VARCHAR(4000);
BEGIN
v_type_id := pkg_nsi.get_type_id('nsi_post_group');
:NEW.nsi_id := pkg_nsi.get_nsi_id();
INSERT INTO nsi (nsi_id, nsi_type_id, descr, create_date, modif_date, begin_date)
VALUES (:NEW.nsi_id, v_type_id, :NEW.name, Trunc(Sysdate), Trunc(Sysdate), Trunc(Sysdate));
v_log_query := 'SELECT ''' || :NEW.name || ''' AS name FROM DUAL';
v_log_descr := pkg_nsi.get_json(v_log_query);
pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_INSERT, v_log_descr);
END;
CREATE OR REPLACE TRIGGER nsi_post_group_trg_update
BEFORE UPDATE ON nsi_post_group FOR EACH ROW
DECLARE
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
v_log_query VARCHAR(4000);
BEGIN
v_type_id := pkg_nsi.get_type_id('nsi_post_group');
UPDATE nsi
SET modif_date = Trunc(Sysdate)
WHERE nsi_id = :NEW.nsi_id
AND nsi_type_id = v_type_id;
v_log_query := 'SELECT ''' || :NEW.name || ''' AS name FROM DUAL';
v_log_descr := pkg_nsi.get_json(v_log_query);
pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_UPDATE, v_log_descr);
END;
CREATE OR REPLACE TRIGGER nsi_post_group_trg_delete
AFTER DELETE ON nsi_post_group FOR EACH ROW
DECLARE
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
v_log_query VARCHAR(4000);
BEGIN
v_type_id := pkg_nsi.get_type_id('nsi_post_group');
DELETE FROM nsi_history
WHERE nsi_id = :OLD.nsi_id
AND nsi_type_id = v_type_id;
DELETE FROM nsi_attribute
WHERE nsi_id = :OLD.nsi_id
AND nsi_type_id = v_type_id;
DELETE FROM nsi
WHERE nsi_id = :OLD.nsi_id
AND nsi_type_id = v_type_id;
v_log_query := 'SELECT ''' || :OLD.name || ''' AS name FROM DUAL';
v_log_descr := pkg_nsi.get_json(v_log_query);
pkg_nsi.log_oper (:OLD.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_DELETE, v_log_descr);
END;
CREATE TABLE nsi_post_category (
nsi_id NUMBER(10) NOT NULL,
name VARCHAR2(50) NOT NULL,
CONSTRAINT nsi_post_category_pk PRIMARY KEY (nsi_id),
CONSTRAINT nsi_post_category_nsi_fk FOREIGN KEY (nsi_id) REFERENCES nsi (nsi_id)
);
COMMENT ON TABLE nsi_post_category is '. ';
COMMENT ON COLUMN nsi_post_category.nsi_id is '';
COMMENT ON COLUMN nsi_post_category.name is '';
CREATE OR REPLACE TRIGGER nsi_post_category_trg_insert
BEFORE INSERT ON nsi_post_category FOR EACH ROW
DECLARE
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
v_log_query VARCHAR(4000);
BEGIN
v_type_id := pkg_nsi.get_type_id('nsi_post_category');
:NEW.nsi_id := pkg_nsi.get_nsi_id();
INSERT INTO nsi (nsi_id, nsi_type_id, descr, create_date, modif_date, begin_date)
VALUES (:NEW.nsi_id, v_type_id, :NEW.name, Trunc(Sysdate), Trunc(Sysdate), Trunc(Sysdate));
v_log_query := 'SELECT ''' || :NEW.name || ''' AS name FROM DUAL';
v_log_descr := pkg_nsi.get_json(v_log_query);
pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_INSERT, v_log_descr);
END;
CREATE OR REPLACE TRIGGER nsi_post_category_trg_update
BEFORE UPDATE ON nsi_post_category FOR EACH ROW
DECLARE
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
v_log_query VARCHAR(4000);
BEGIN
v_type_id := pkg_nsi.get_type_id('nsi_post_category');
UPDATE nsi
SET modif_date = Trunc(Sysdate)
WHERE nsi_id = :NEW.nsi_id
AND nsi_type_id = v_type_id;
v_log_query := 'SELECT ''' || :NEW.name || ''' AS name FROM DUAL';
v_log_descr := pkg_nsi.get_json(v_log_query);
pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_UPDATE, v_log_descr);
END;
CREATE OR REPLACE TRIGGER nsi_post_category_trg_delete
AFTER DELETE ON nsi_post_category FOR EACH ROW
DECLARE
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
v_log_query VARCHAR(4000);
BEGIN
v_type_id := pkg_nsi.get_type_id('nsi_post_category');
DELETE FROM nsi_history
WHERE nsi_id = :OLD.nsi_id
AND nsi_type_id = v_type_id;
DELETE FROM nsi_attribute
WHERE nsi_id = :OLD.nsi_id
AND nsi_type_id = v_type_id;
DELETE FROM nsi
WHERE nsi_id = :OLD.nsi_id
AND nsi_type_id = v_type_id;
v_log_query := 'SELECT ''' || :OLD.name || ''' AS name FROM DUAL';
v_log_descr := pkg_nsi.get_json(v_log_query);
pkg_nsi.log_oper (:OLD.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_DELETE, v_log_descr);
END;
CREATE TABLE nsi_post (
nsi_id NUMBER(10) NOT NULL,
code_OKPDTR VARCHAR2(10),
name VARCHAR2(50) NOT NULL,
CONSTRAINT nsi_post_pk PRIMARY KEY (nsi_id),
CONSTRAINT nsi_post_nsi_fk FOREIGN KEY (nsi_id) REFERENCES nsi (nsi_id)
);
COMMENT ON TABLE nsi_post IS '. ';
COMMENT ON COLUMN nsi_post.nsi_id IS '';
COMMENT ON COLUMN nsi_post.code_OKPDTR IS ' ';
COMMENT ON COLUMN nsi_post.name IS '';
CREATE OR REPLACE TRIGGER nsi_post_trg_insert
BEFORE INSERT ON nsi_post FOR EACH ROW
DECLARE
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
v_log_query VARCHAR(4000);
BEGIN
v_type_id := pkg_nsi.get_type_id('nsi_post');
:NEW.nsi_id := pkg_nsi.get_nsi_id();
INSERT INTO nsi (nsi_id, nsi_type_id, descr, create_date, modif_date, begin_date)
VALUES (:NEW.nsi_id, v_type_id, :NEW.name, Trunc(Sysdate), Trunc(Sysdate), Trunc(Sysdate));
v_log_query := 'SELECT ''' || :OLD.name || ''' AS name, ''' || :OLD.code_OKPDTR || ''' AS code_OKPDTR FROM DUAL';
v_log_descr := pkg_nsi.get_json(v_log_query);
pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_INSERT, v_log_descr);
END;
CREATE OR REPLACE TRIGGER nsi_post_trg_update
BEFORE UPDATE ON nsi_post FOR EACH ROW
DECLARE
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
v_log_query VARCHAR(4000);
BEGIN
v_type_id := pkg_nsi.get_type_id('nsi_post');
UPDATE nsi
SET modif_date = Trunc(Sysdate)
WHERE nsi_id = :NEW.nsi_id
AND nsi_type_id = v_type_id;
v_log_query := 'SELECT ''' || :OLD.name || ''' AS name, ''' || :OLD.code_OKPDTR || ''' AS code_OKPDTR FROM DUAL';
v_log_descr := pkg_nsi.get_json(v_log_query);
pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_UPDATE, v_log_descr);
END;
CREATE OR REPLACE TRIGGER nsi_post_trg_delete
AFTER DELETE ON nsi_post FOR EACH ROW
DECLARE
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
v_log_query VARCHAR(4000);
BEGIN
v_type_id := pkg_nsi.get_type_id('nsi_post');
DELETE FROM nsi_history
WHERE nsi_id = :OLD.nsi_id
AND nsi_type_id = v_type_id;
DELETE FROM nsi_attribute
WHERE nsi_id = :OLD.nsi_id
AND nsi_type_id = v_type_id;
DELETE FROM nsi
WHERE nsi_id = :OLD.nsi_id
AND nsi_type_id = v_type_id;
v_log_query := 'SELECT ''' || :OLD.name || ''' AS name, ''' || :OLD.code_OKPDTR || ''' AS code_OKPDTR FROM DUAL';
v_log_descr := pkg_nsi.get_json(v_log_query);
pkg_nsi.log_oper (:OLD.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_DELETE, v_log_descr);
END;
它仅用于填写和收集必要的分类器。INSERT INTO nsi_classifier (name) VALUES (' ');
INSERT INTO nsi_classifier (name) VALUES (' ');
INSERT INTO nsi_post_group (name) VALUES ('');
INSERT INTO nsi_post_group (name) VALUES ('');
INSERT INTO nsi_post_group (name) VALUES ('');
INSERT INTO nsi_post_group (name) VALUES ('');
INSERT INTO nsi_post_group (name) VALUES ('');
INSERT INTO nsi_post_category (name) VALUES ('');
INSERT INTO nsi_post_category (name) VALUES (' ()');
INSERT INTO nsi_post_category (name) VALUES ('');
INSERT INTO nsi_post_category (name) VALUES (' ');
INSERT INTO nsi_post (code_OKPDTR, name)
VALUES ('24742', ' ');
INSERT INTO nsi_post (code_OKPDTR, name)
VALUES ('26480', '');
INSERT INTO nsi_post (code_OKPDTR, name)
VALUES ('23509', '');
INSERT INTO nsi_post (code_OKPDTR, name)
VALUES ('20419', ' ');
INSERT INTO nsi_post (code_OKPDTR, name)
VALUES ('26541', '');
INSERT INTO nsi_post (code_OKPDTR, name)
VALUES ('26544', ' 2 ');
commit;
declare
post_id number;
classif_id number;
categ_id number;
group_id number;
begin
classif_id := pkg_nsi.nsi_structure_insert(null, 331, 5, null, to_date('13.02.20', 'dd.mm.yy'), null);
categ_id := pkg_nsi.nsi_structure_insert(classif_id, 347, 4, 1, to_date('13.02.20', 'dd.mm.yy'), null);
group_id := pkg_nsi.nsi_structure_insert(categ_id, 355, 3, 1, to_date('13.02.20', 'dd.mm.yy'), null);
group_id := pkg_nsi.nsi_structure_insert(categ_id, 357, 3, 2, to_date('13.02.20', 'dd.mm.yy'), null);
post_id := pkg_nsi.nsi_structure_insert(group_id, 335, 2, 1, to_date('13.02.20', 'dd.mm.yy'), null);
group_id := pkg_nsi.nsi_structure_insert(categ_id, 359, 3, 3, to_date('13.02.20', 'dd.mm.yy'), null);
categ_id := pkg_nsi.nsi_structure_insert(classif_id, 349, 4, 2, to_date('13.02.20', 'dd.mm.yy'), null);
group_id := pkg_nsi.nsi_structure_insert(categ_id, 355, 3, 1, to_date('13.02.20', 'dd.mm.yy'), null);
group_id := pkg_nsi.nsi_structure_insert(categ_id, 357, 3, 2, to_date('13.02.20', 'dd.mm.yy'), null);
group_id := pkg_nsi.nsi_structure_insert(categ_id, 359, 3, 3, to_date('13.02.20', 'dd.mm.yy'), null);
post_id := pkg_nsi.nsi_structure_insert(group_id, 337, 2, 1, to_date('13.02.20', 'dd.mm.yy'), null);
categ_id := pkg_nsi.nsi_structure_insert(classif_id, 351, 4, 3, to_date('13.02.20', 'dd.mm.yy'), null);
group_id := pkg_nsi.nsi_structure_insert(categ_id, 355, 3, 1, to_date('13.02.20', 'dd.mm.yy'), null);
group_id := pkg_nsi.nsi_structure_insert(categ_id, 357, 3, 2, to_date('13.02.20', 'dd.mm.yy'), null);
group_id := pkg_nsi.nsi_structure_insert(categ_id, 359, 3, 3, to_date('13.02.20', 'dd.mm.yy'), null);
post_id := pkg_nsi.nsi_structure_insert(group_id, 341, 2, 1, to_date('13.02.20', 'dd.mm.yy'), null);
group_id := pkg_nsi.nsi_structure_insert(categ_id, 361, 3, 4, to_date('13.02.20', 'dd.mm.yy'), null);
categ_id := pkg_nsi.nsi_structure_insert(classif_id, 353, 4, 4, to_date('13.02.20', 'dd.mm.yy'), null);
group_id := pkg_nsi.nsi_structure_insert(categ_id, 357, 3, 1, to_date('13.02.20', 'dd.mm.yy'), null);
group_id := pkg_nsi.nsi_structure_insert(categ_id, 359, 3, 2, to_date('13.02.20', 'dd.mm.yy'), null);
group_id := pkg_nsi.nsi_structure_insert(categ_id, 361, 3, 3, to_date('13.02.20', 'dd.mm.yy'), null);
group_id := pkg_nsi.nsi_structure_insert(categ_id, 363, 3, 4, to_date('13.02.20', 'dd.mm.yy'), null);
post_id := pkg_nsi.nsi_structure_insert(group_id, 345, 2, 1, to_date('13.02.20', 'dd.mm.yy'), null);
commit;
end;
SELECT *
FROM nsi_structure s
START WITH (nsi_id = 331)
CONNECT BY (nsi_parent_structure_id = PRIOR nsi_structure_id)
ORDER SIBLINGS BY ordnum;
"NSI_STRUCTURE_ID" "NSI_PARENT_STRUCTURE_ID" "NSI_ID" "NSI_TYPE_ID" "ORDNUM" "BEGIN_DATE" "END_DATE"
385 331 5 13.02.20
387 385 347 4 1 13.02.20
389 387 355 3 1 13.02.20
391 387 357 3 2 13.02.20
393 391 335 2 1 13.02.20
395 387 359 3 3 13.02.20
397 385 349 4 2 13.02.20
399 397 355 3 1 13.02.20
401 397 357 3 2 13.02.20
403 397 359 3 3 13.02.20
405 403 337 2 1 13.02.20
407 385 351 4 3 13.02.20
409 407 355 3 1 13.02.20
411 407 357 3 2 13.02.20
413 407 359 3 3 13.02.20
415 413 341 2 1 13.02.20
417 407 361 3 4 13.02.20
419 385 353 4 4 13.02.20
421 419 357 3 1 13.02.20
423 419 359 3 2 13.02.20
425 419 361 3 3 13.02.20
427 419 363 3 4 13.02.20
429 427 345 2 1 13.02.20
哦，它的可读性！CREATE OR REPLACE VIEW V_NSI_CLASSIFIRE_GGS AS
SELECT
s.nsi_structure_id, s.nsi_parent_structure_id,
s.ordnum, s.begin_date, s.end_date,
n.nsi_id, n.nsi_type_id, n.descr
FROM nsi_structure s INNER JOIN nsi n
ON (s.nsi_id = n.nsi_id)
START WITH (s.nsi_id = 331)
CONNECT BY (nsi_parent_structure_id = PRIOR nsi_structure_id)
ORDER SIBLINGS BY ordnum;
SELECT * FROM V_NSI_CLASSIFIRE_GGS ;
"NSI_STRUCTURE_ID" "NSI_PARENT_STRUCTURE_ID" "NSI_ID" "NSI_TYPE_ID" "ORDNUM" "BEGIN_DATE" "END_DATE"
385 13.02.20 331 5
387 385 1 13.02.20 347 4
389 387 1 13.02.20 355 3
391 387 2 13.02.20 357 3
393 391 1 13.02.20 335 2
395 387 3 13.02.20 359 3
397 385 2 13.02.20 349 4 ()
399 397 1 13.02.20 355 3
401 397 2 13.02.20 357 3
403 397 3 13.02.20 359 3
405 403 1 13.02.20 337 2
407 385 3 13.02.20 351 4
409 407 1 13.02.20 355 3
411 407 2 13.02.20 357 3
413 407 3 13.02.20 359 3
415 413 1 13.02.20 341 2
417 407 4 13.02.20 361 3
419 385 4 13.02.20 353 4
421 419 1 13.02.20 357 3
423 419 2 13.02.20 359 3
425 419 3 13.02.20 361 3
427 419 4 13.02.20 363 3
429 427 1 13.02.20 345 2 2
不应忘记，除了包含关系（包括树形关系）之外，还存在相交关系，即交叉表。这增加了检查时间交点的附加条件。CREATE TABLE nsi_cross (
nsi_cross_id NUMBER(10) NOT NULL,
nsi_main_id NUMBER(10) NOT NULL,
nsi_main_type_id NUMBER(10) NOT NULL,
nsi_detail_id NUMBER(10) NOT NULL,
nsi_detail_type_id NUMBER(10) NOT NULL,
begin_date DATE NOT NULL,
end_date DATE,
CONSTRAINT nsi_cross_pk PRIMARY KEY (nsi_cross_id),
CONSTRAINT nsi_cross_main_nsi_fk FOREIGN KEY (nsi_main_type_id, nsi_main_id) REFERENCES nsi (nsi_type_id, nsi_id),
CONSTRAINT nsi_cross_detail_nsi_fk FOREIGN KEY (nsi_detail_type_id, nsi_detail_id) REFERENCES nsi (nsi_type_id, nsi_id)
);
COMMENT ON TABLE nsi_cross IS '. - ';
COMMENT ON COLUMN nsi_cross.nsi_cross_id IS '';
COMMENT ON COLUMN nsi_cross.nsi_main_id IS ' ';
COMMENT ON COLUMN nsi_cross.nsi_main_type_id IS ' ';
COMMENT ON COLUMN nsi_cross.nsi_detail_id IS ' ';
COMMENT ON COLUMN nsi_cross.nsi_detail_type_id IS ' ';
COMMENT ON COLUMN nsi_cross.begin_date IS ' ';
COMMENT ON COLUMN nsi_cross.end_date IS ' ';
PROCEDURE nsi_cross_check_period (
p_nsi_cross_id IN nsi_cross.nsi_cross_id%TYPE,
p_begin_date IN nsi_cross.begin_date%TYPE,
p_end_date IN nsi_cross.end_date%TYPE)
AS
v_cnt NUMBER;
v_nsi_main_id nsi_cross.nsi_main_id%TYPE;
v_nsi_main_type_id nsi_cross.nsi_main_type_id%TYPE;
v_nsi_detail_id nsi_cross.nsi_detail_id%TYPE;
v_nsi_detail_type_id nsi_cross.nsi_detail_type_id%TYPE;
BEGIN
IF (p_end_date IS NOT NULL) AND (Trunc(p_begin_date) > Trunc(p_end_date)) THEN
RAISE_APPLICATION_ERROR (NSI_ERROR_CODE,
'[nsi_cross_check_period] ' || Trunc(p_begin_date) || ' - ' || Trunc(p_end_date));
END IF;
SELECT MIN(nsi_main_id), MIN(nsi_main_type_id),
MIN(nsi_detail_id), MIN(nsi_detail_type_id)
INTO v_nsi_main_id, v_nsi_main_type_id,
v_nsi_detail_id, v_nsi_detail_type_id
FROM nsi_cross
WHERE nsi_cross_id = p_nsi_cross_id;
v_cnt := 0;
IF (v_nsi_main_id IS NOT NULL) THEN
IF (p_end_date IS NOT NULL) THEN
SELECT COUNT(*)
INTO v_cnt
FROM nsi_cross
WHERE nsi_main_id = v_nsi_main_id
AND nsi_main_type_id = v_nsi_main_type_id
AND nsi_detail_id = v_nsi_detail_id
AND nsi_detail_type_id = v_nsi_detail_type_id
AND nsi_cross_id <> p_nsi_cross_id
AND begin_date <= Trunc(p_end_date)
AND ((end_date IS NULL) OR (end_date >= Trunc(p_end_date)));
ELSE
SELECT COUNT(*)
INTO v_cnt
FROM nsi_cross
WHERE nsi_main_id = v_nsi_main_id
AND nsi_main_type_id = v_nsi_main_type_id
AND nsi_detail_id = v_nsi_detail_id
AND nsi_detail_type_id = v_nsi_detail_type_id
AND nsi_cross_id <> p_nsi_cross_id
AND ((
(end_date IS NOT NULL) AND (end_date >= Trunc(p_begin_date))
) OR (end_date IS NULL)
);
END IF;
END IF;
IF (v_cnt > 0) THEN
RAISE_APPLICATION_ERROR (NSI_ERROR_CODE,
'[nsi_cross_check_period] ' || p_begin_date || ' - ' || p_end_date);
END IF;
END;
PROCEDURE nsi_cross_insert (
p_nsi_main_id IN nsi_cross.nsi_main_id%TYPE,
p_nsi_main_type_id IN nsi_cross.nsi_main_type_id%TYPE,
p_nsi_detail_id IN nsi_cross.nsi_detail_id%TYPE,
p_nsi_detail_type_id IN nsi_cross.nsi_detail_type_id%TYPE,
p_begin_date IN nsi_cross.begin_date%TYPE,
p_end_date IN nsi_cross.end_date%TYPE)
AS
v_id NUMBER;
v_log_descr nsi_log.descr%TYPE;
v_type_id nsi.nsi_type_id%TYPE;
BEGIN
v_id := get_nsi_id;
v_type_id := get_type_id('nsi_cross');
INSERT INTO nsi_cross (
nsi_cross_id, nsi_main_id, nsi_main_type_id,
nsi_detail_id, nsi_detail_type_id,
begin_date, end_date)
VALUES (
v_id, p_nsi_main_id, p_nsi_main_type_id,
p_nsi_detail_id, p_nsi_detail_type_id,
Trunc(p_begin_date), Trunc(p_end_date));
nsi_cross_check_period (v_id, p_begin_date, p_end_date);
v_log_descr := '[' || get_nsi_descr(p_nsi_main_id, p_nsi_main_type_id) || ' <=> ' || get_nsi_descr(p_nsi_detail_id, p_nsi_detail_type_id) || '] ';
v_log_descr := v_log_descr || ' ' || p_begin_date || ' - ' || p_end_date;
log_oper (v_id, v_type_id, NSI_LOG_OPERNUM_INSERT, v_log_descr);
END nsi_cross_insert;
PROCEDURE nsi_cross_period (
p_nsi_cross_id IN nsi_cross.nsi_cross_id%TYPE,
p_begin_date IN nsi_cross.begin_date%TYPE,
p_end_date IN nsi_cross.end_date%TYPE)
AS
v_main_id nsi_cross.nsi_main_id%TYPE;
v_main_type_id nsi_cross.nsi_main_type_id%TYPE;
v_detail_id nsi_cross.nsi_detail_id%TYPE;
v_detail_type_id nsi_cross.nsi_detail_type_id%TYPE;
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
BEGIN
v_type_id := get_type_id('nsi_cross');
SELECT nsi_main_id, nsi_main_type_id,
nsi_detail_id, nsi_detail_type_id
INTO v_main_id, v_main_type_id,
v_detail_id, v_detail_type_id
FROM nsi_cross
WHERE nsi_cross_id = p_nsi_cross_id;
nsi_cross_check_period (p_nsi_cross_id, p_begin_date, p_end_date);
UPDATE nsi_cross
SET begin_date = Trunc(p_begin_date),
end_date = Trunc(p_end_date)
WHERE nsi_cross_id = p_nsi_cross_id;
v_log_descr := '[' || get_nsi_descr(v_main_id, v_main_type_id) || ' <=> ' || get_nsi_descr(v_detail_id, v_detail_type_id) || '] ';
v_log_descr := v_log_descr || ' ' || p_begin_date || ' - ' || p_end_date;
log_oper (p_nsi_cross_id, v_type_id, NSI_LOG_OPERNUM_UPDATE, v_log_descr);
END;
PROCEDURE nsi_cross_delete (p_nsi_cross_id IN nsi_cross.nsi_cross_id%TYPE)
AS
v_type_id nsi.nsi_type_id%TYPE;
v_log_descr nsi_log.descr%TYPE;
BEGIN
v_type_id := pkg_nsi.get_type_id('nsi_cross');
FOR rec IN (
SELECT nsi_cross_id, nsi_main_id, nsi_main_type_id,
nsi_detail_id, nsi_detail_type_id,
begin_date, end_date
FROM nsi_cross
WHERE nsi_cross_id = p_nsi_cross_id
)
LOOP
v_log_descr := '[' || pkg_nsi.get_nsi_descr(rec.nsi_main_id, rec.nsi_main_type_id) || ' <=> ' || pkg_nsi.get_nsi_descr(rec.nsi_detail_id, rec.nsi_detail_type_id) || '] ';
v_log_descr := v_log_descr || ' ' || rec.begin_date || ' - ' || rec.end_date;
pkg_nsi.log_oper (rec.nsi_cross_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_DELETE, v_log_descr);
END LOOP;
DELETE FROM nsi_cross
WHERE nsi_cross_id = p_nsi_cross_id;
END;
就是这样，现在我们可以自信地说我们已经解决了第一个问题。当然，您可以尝试将很多东西附加到该系统上，但是我认为我已经在本文开头完成了任务，其余部分可以在讨论过程中考虑到。尽管在版本12中已经提供了对json格式的本机支持，但该材料是在Oracle 18c版上编写的。这是脚本档案的链接。