Данная статья основана на реальных событиях,
и все проблемы в ней не вымышленные. (С)
В начале хотелось бы отметить, что статья не призвана показать изобретение велосипеда, потому как многие приёмы уже давно существуют в культуре разработки баз данных. Однако обобщить, проанализировать проблемы, которые они могут решить и показать, как с ними можно работать. А проблем хватает несмотря на то, что нормативно-справочная информация (НСИ) не относится к бизнес-логике, а скорее находится в обслуживании у неё. Стандартный процесс по рисованию очередной таблички для хранения справочника очень скоро начинает обрастать костылями или трудоёмкими переделками.
Вот и в моём случае оказалась та же картина — система стоит на продуктиве более десяти лет, строилась по тому же принципу, если что нужно, рисуем и включаем в оборот. Таким образом были созданы несколько таблиц для хранения разного рода оборудования. Но вот пришёл час Х, когда стало необходимо добавить ещё пару таблиц для нового оборудования и при этом все (включая старые) должны входить в определённую группу. Это значит, что ссылки на разные таблицы должны быть включены в кросс-таблицу между группой и всеми пятью видами оборудования, то есть для каждого своё поля с констреинтом на соответствующую таблицу. А если ещё одно добавится, менять структуру. И обработку нужно делать в зависимости от того, какие поля заполнены. Вот и возникает первая проблема, как разные таблицы обобщить, что бы с ними одинаково можно было работать и не менять структуру, если добавляется ещё одна. Замечательная мысль, создаём отдельную табличку, которая призвана хранить абстрактное понятие оборудование с указанием типа, а тогда остальные таблички ссылаются по внешнему ключу на своего родителя. На этой радостной волне мы заливаем в созданную табличку записи из одной и пытаемся тоже сделать для другой. Но что-то пошло не так, сработало ограничение первичного ключа, к чему бы это? А к тому, что на заре бурной молодости системы для каждой табличке были свои сиквенсы. Конечно, со временем это безобразие поправили, но старые ключи всё равно остались. Более того, они корнями проросли по внешним ключам с другими таблицам. Фиксируем вторую проблему, связанную со сквозной нумерацией всех справочников.
На этом мучения с таблицами оборудования не закончились. Потому как по последним требованиям оборудование имеет различные характеристики, более того их число переменно, а одна характеристика может иметь несколько значений. А значит появляется третья проблема, а именно иметь возможность хранить переменное число характеристик какой-то записи.
Вроде как с этим справились, но заказчик не дремлет, у него всегда есть наготове что-нибудь новенькое. И вот приходит требование — все справочники историчные (например, название продукта было одним, а потом его переименовали, и по документам на разные даты нужно показывать актуальное название). Само по себе требование нормальное, ничего не скажешь. А если ещё в отделе разработки есть кто-то, кто проходит испытательный срок, так вообще всё в шоколаде, можно и не заметить, что это проблема. Однако проходит всё, как обычно — с полным авралом, а тут ещё этим нужно заниматься. Создаём таблички, дублирующие таблицы соответствующих справочников для того, чтобы там хранить хронологию изменений справочника. Но, создавая эти таблицы, мы заодно создаём себе четвёртую проблему, теперь в коде нужно в зависимости от даты обращаться то ли к основной таблице, то ли к исторической.
Ну мы же молодцы, мы и это победили))) Теперь, попивая чай из своей кружки, начинаешь дискутировать с другими коллегами на тему, что им приходилось решать, и понимаешь, что список проблем пополняется. В обсуждении стоит вопрос как хранить версии одной и той же записи. Хочу оговорится, что версия, это не то, что укладывается в таблицу историчности. В историчности понятно, до такого-то числа было одно название, а начиная с этой даты актуальным становится другое. А в версионности подразумевается, что запись была сначала сохранена с ошибкой, а через несколько часов это поняли и её изменили, и нужно знать все состояния этой записи. Во-первых, здесь должно быть дробление на время, не только сутки. А во-вторых, такие следы нужны в случае разборок. Например, заполняли прайс, ошиблись, успели товар продать по такой цене, а потом поправили, но в конце дня случился дебаланс. Однако решение для таких ситуаций меня лично напрягло, предлагалась все такие изменения хранить в самой таблице. Не буду устраивать холивар на сколько так правильно, но для меня точно обозначилась пятая проблема, а именно хранение изменений записей.
Итак, обобщая вышесказанное мы видим перед собой пять увесистых грабель. Теперь наша задача определить стратегию, позволяющую обойти и не наступить на них.
Сколько можно наступать на одни и те же грабли, давайте скинимся и купим новые
Начиная проектировать систему с нуля, никто не может предугадать путь её развития, а значит не сможет сказать на каком уровне придётся обобщать, как в описанном примере с оборудованием. Поэтому имеет смысл сразу задать абстрактную сущность, распространяемую на все таблицы НСИ. Таким образом все справочники будут иметь прообраз в едином справочнике с разделением на типы.
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
/* По названию таблицы возвращается тип НСИ
* @param p_table_name VARCHAR2 - название таблицы
* @return nsi.nsi_type_id%TYPE - тип из таблицы nsi_type
*/
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;
/* Возвращает следующий id из nsi_seq
* @return nsi.nsi_id%TYPE - id из nsi_seq
*/
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;
/* По типу справочника возвращает наименование таблицы
* @param p_nsi_type_id nsi_type.nsi_type_id%TYPE - тип из таблицы nsi_type
* @return nsi_type.table_name%TYPE - название таблицы
*/
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;
/* Для определённого справчоника возвращает описание из таблицы nsi
* @param p_nsi_id nsi.nsi_id%TYPE - ключ справочника
* @param p_nsi_type_id nsi_type.nsi_type_id%TYPE - тип справочника
* @return nsi.descr%TYPE - описание
*/
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 'Дата создания';
А так же в пакет добавлена функция логирования.
-- Ограничение CHECK nsi_log_oper_num_ch
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;
/* Добавление записи логирования операций.
* @param p_nsi_id nsi.nsi_id%TYPE - справочник
* @param p_nsi_type_id nsi_type.nsi_type_id%TYPE - тип справочника
* @param p_oper_num NUMBER - номер операции
* @param p_descr VARCHAR2 - описание
*/
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;
Таким образом разрешена пятая проблема, теперь для любой записи НСИ можно посмотреть, что с ней происходило.
Пытаемся добавить туда организацию.
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 для переданного запроса.
/* Для переданного запроса возвращается json
* @param p_query VARCHAR2 - запрос
* @return CLOB - нотация 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;
/* Сохранение текущего состояния справочника в таблицу историчности.
* @param p_nsi_id nsi.nsi_id%TYPE - справочник
* @param p_nsi_type_id nsi_type.nsi_type_id%TYPE - тип справочника
* @param p_end_date nsi_history.end_date%TYPE - дата окончания текущего содержимого справочника
* @param p_note nsi_history.note%TYPE - пояснение причины сохранения в истории
*/
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;
Таким образом любой справочник может воспользоваться этой функцией, чтобы увести в историю текущее состояние. Уже хорошо, хоть что-то полезное появилось от такого обобщения))) Для извлечения актуального состояния справочника добавим в пакет соответствующую pipeline-функцию. Записи справочника будут возвращаться в тип, расширенный системными полями.
-- Запись содержит поля таблицы nsi_organization и дополнены служебными полями nsi
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;
/* Возвращает список, актуальный на указанную дату.
* Если дата не задана, актуальной считается текущая дата.
* @param p_date DATE - дата, на которую необходимо получить состояние справочника
* @return nsi_organization_table - таблица с записями 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
---------------------------------------------------------------------------------------
-- обновим инн и посмотрим выборку на различные даты
-- различия присутствуют в полях inn, version, begin_date, end_date
-- текущая запись в таблице имеет версию 0
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-pattern, его и реализуем.
-- Ограничение CHECK nsi_attribute_type_ch
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) REFERENCES nsi (nsi_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 обработкой этой таблицы.
-- Запись содержит поля таблицы nsi_person и дополнены служебными полями 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;
/* Возвращает список, актуальный на указанную дату.
* Если дата не задана, актуальной считается текущая дата.
* @param p_date DATE - дата, на которую необходимо получить состояние справочника
* @return nsi_person_table - таблица с записями 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 добавим функции для работы с атрибутами справочников.
/* Для переданного id возвращает тип значения и описание атрибута.
* @param p_nsi_attribute_type_id nsi_attribute_type.nsi_attribute_type_id%TYPE - Тип атрибута
* @param p_value_type nsi_attribute_type.value_type%TYPE - Тип знаения
* @param p_descr nsi_attribute_type.descr%TYPE - Описание атрибута
*/
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;
/* Реализует вставку записи.
* @param p_nsi_attribute_type_id nsi_attribute.nsi_attribute_type_id%TYPE - Тип атрибута
* @param p_nsi_id nsi_attribute.nsi_id%TYPE - Справочник
* @param p_nsi_type_id nsi_attribute.nsi_type_id%TYPE - Тип справочника
* @param p_value_1 nsi_attribute.value_1%TYPE - Значение строкового типа
* @param p_value_2_3 nsi_attribute.value_2_3%TYPE - Значение числового типа
* @param p_value_4 nsi_attribute.value_4%TYPE - Значение типа даты
* @param p_begin_date nsi_attribute.begin_date%TYPE - Дата начала действия атрибута
* @param p_end_date nsi_attribute.end_date%TYPE - Дата окончания действия атрибута
*/
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;
/* Реализует обновление типа и значения атрибута.
* @param p_nsi_attribute_id nsi_attribute.nsi_attribute_id%TYPE - Ключ атрибута
* @param p_value_1 nsi_attribute.value_1%TYPE - Значение строкового типа
* @param p_value_2_3 nsi_attribute.value_2_3%TYPE - Значение числового типа
* @param p_value_4 nsi_attribute.value_4%TYPE - Значение типа даты
*/
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;
/* Реализует обновление периода действия атрибута.
* @param p_nsi_attribute_id nsi_attribute.nsi_attribute_id%TYPE - Ключ атрибута
* @param p_begin_date nsi_attribute.begin_date%TYPE - Дата начала действия атрибута
* @param p_end_date nsi_attribute.end_date%TYPE - Дата окончания действия атрибута
*/
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;
/* Реализует удаление записи.
* @param p_nsi_attribute_id nsi_person.nsi_attribute_id%TYPE - id записи nsi_attribute
*/
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
--------------------------------------------------------------------------------------------
Таким образом мы победим третью проблему.
Кроме таблиц справочников в системе НСИ также важны отношение между ними. Так, например крупные организации включают в себя различные подразделения, филиалы, отделы и т.п., которые можно выстроить в древовидную структуру. Для начала заведём в нашей системе ещё несколько организаций, которые будут в подчинении у уже существующей «Рога и копыта».
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, чтобы можно было настраивать структуру для различных таблиц.
/* Реализует вставку записи.
* @param p_nsi_parent_structure_id nsi_structure.nsi_parent_structure_id%TYPE - запись родителя
* @param p_nsi_id nsi_structure.nsi_id%TYPE - справочник
* @param p_nsi_type_id nsi_structure.nsi_type_id%TYPE - тип справочника
* @param p_ordnum nsi_structure.ordnum%TYPE - порядковый номер
* @param p_begin_date nsi_structure.begin_date%TYPE - дата начала действия записи
* @param p_end_date nsi_structure.end_date%TYPE - дата окончания действия записи
*/
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;
/* Реализует обновление порядкового номера записи.
* @param p_nsi_structure_id nsi_structure.nsi_structure_id%TYPE - ключ nsi_structure
* @param p_ordnum nsi_structure.ordnum%TYPE - порядковый номер
*/
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;
/* Реализует обновление периода действия записи.
* @param p_nsi_structure_id nsi_structure.nsi_structure_id%TYPE - ключ nsi_structure
* @param p_begin_date nsi_structure.begin_date%TYPE - дата начала действия
* @param p_end_date nsi_structure.end_date%TYPE - дата окончания действия
*/
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;
/* Реализует удаление записи.
* @param p_nsi_structure_id nsi_structure.nsi_structure_id%TYPE - ключ nsi_structure
*/
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
-----------------------------------------------------------------------------------
То, что мы строим дерево это замечательно, но все узлы этого дерева относятся к одной сущности, а наша задача реализовать построение отношения между разными сущностями. Это тоже не проблема, потому как структура не завязывается на какой-то определённый справочник, а работает в целом на всей системе НСИ. Для примера построим классификатор для должностей государственной гражданской службы и классификатор для должностей муниципалитета.
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);
-- Специалист 2 разряда
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 'Дата окончания действия';
/* Реализует проверку на пересечения периода действия записи кросс-таблицы.
* @param p_nsi_main_id nsi_cross.nsi_main_id%TYPE - справочник основной записи
* @param p_nsi_main_type_id nsi_cross.nsi_main_type_id%TYPE - тип справочника основной записи
* @param p_nsi_detail_id nsi_cross.nsi_detail_id%TYPE - справочник записи детализации
* @param p_nsi_detail_type_id nsi_cross.nsi_detail_type_id%TYPE - ттип справочника записи детализации
* @param p_begin_date DATE - дата начала действия записи
* @param p_end_date DATE - дата окончания действия записи
*/
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;
/* Реализует вставку записи.
* @param p_nsi_main_id nsi_cross.nsi_main_id%TYPE - справочник основной записи
* @param p_nsi_main_type_id nsi_cross.nsi_main_type_id%TYPE - тип справочника основной записи
* @param p_nsi_detail_id nsi_cross.nsi_detail_id%TYPE - справочник записи детализации
* @param p_nsi_detail_type_id nsi_cross.nsi_detail_type_id%TYPE - ттип справочника записи детализации
* @param p_begin_date DATE - дата начала действия записи
* @param p_end_date DATE - дата окончания действия записи
*/
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;
/* Реализует обновление периода действия записи.
* @param p_nsi_cross_id nsi_cross.nsi_cross_id%TYPE - ключ nsi_cross
* @param p_begin_date nsi_cross.begin_date%TYPE - дата начала действия
* @param p_end_date nsi_cross.end_date%TYPE - дата окончания действия
*/
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;
/* Реализует удаление записи.
* @param p_nsi_cross_id nsi_cross.nsi_cross_id%TYPE - ключ nsi_cross
*/
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;
Всё, теперь мы с уверенностью можем сказать, что закрыли первую проблему.
Конечно можно много чего пытаться прикрутить к этой системе, но я думаю, что поставленную задачу в начале статьи я выполнила, а остальное уже можно рассмотреть в процессе дискуссии.
Материал подготавливался на версии Oracle 18c, хотя нативное поддержание формата json уже присутствует в версии 12. Здесь ссылка с архивом скриптов.
Автор: Кудряшова Ирина
