هذه المقالة مبنية على أحداث حقيقية ،
وكل المشاكل فيها ليست خيالية. (ج)في البداية ، أود أن أشير إلى أن المقالة لا تهدف إلى إظهار اختراع الدراجة ، لأن العديد من التقنيات موجودة منذ فترة طويلة في ثقافة تطوير قاعدة البيانات. ومع ذلك ، لتلخيص وتحليل المشكلات التي يمكنهم حلها وإظهار كيف يمكنك العمل معهم. وهناك مشاكل كافية على الرغم من حقيقة أن المعلومات المرجعية (NSI) لا تنطبق على منطق الأعمال ، بل هي في خدمتها. تبدأ العملية القياسية لرسم لوحة أخرى لتخزين الدليل في النمو قريبًا مع العكازات أو التعديلات التي تستغرق وقتًا طويلاً.لذا ، في حالتي ، اتضح أن نفس الصورة - النظام كان على إنتاجية لأكثر من عشر سنوات ، تم بناؤه على نفس المبدأ ، إذا لزم الأمر ، نرسمه ونقوم بتشغيله. وهكذا ، تم إنشاء العديد من الجداول لتخزين أنواع مختلفة من المعدات. ولكن بعد ذلك جاءت الساعة X ، عندما أصبح من الضروري إضافة زوجين من الجداول لمعدات جديدة وفي نفس الوقت يجب تضمين الجميع (بما في ذلك القديمة) في مجموعة معينة. هذا يعني أنه يجب تضمين الروابط إلى الجداول المختلفة في الجدول الترافقي بين المجموعة وجميع أنواع المعدات الخمسة ، أي لكل مجال من الحقول الخاصة به مع ثابت في الجدول المقابل. وإذا تم إضافة واحد آخر ، قم بتغيير الهيكل. ويجب إجراء المعالجة اعتمادًا على الحقول المملوءة. لذا تنشأ المشكلة الأولى ،كيفية تلخيص الجداول المختلفة بحيث يمكنك العمل بها على قدم المساواة وعدم تغيير الهيكل إذا تم إضافة جدول آخر . فكرة رائعة ، نقوم بإنشاء ملصق منفصل مصمم لتخزين المفهوم المجرد للمعدات مع إشارة إلى النوع ، ثم تشير التسميات المتبقية إلى أصلها بواسطة مفتاح خارجي. في هذه الموجة المبهجة ، نملأ السجلات من واحدة في اللوحة التي تم إنشاؤها ونحاول صنعها أيضًا. ولكن حدث خطأ ، نجح تقييد المفتاح الأساسي ، فلماذا؟ وحقيقة أنه في فجر الشباب المضطرب للنظام ، كان لكل لوحة تسلسلها الخاص. بالطبع ، بمرور الوقت تم تصحيح هذا الخزي ، لكن المفاتيح القديمة لا تزال باقية. علاوة على ذلك ، فقد انتشروا جذوراً على المفاتيح الخارجية مع الجداول الأخرى. نقوم بإصلاح المشكلة الثانية ،المرتبطة بترقيم كافة الدلائل .لم ينتهي العذاب مع طاولات المعدات عند هذا الحد. لأنه وفقًا لأحدث المتطلبات ، فإن المعدات لها خصائص مختلفة ، علاوة على ذلك ، فإن عددها متغير ، ويمكن أن تحتوي خاصية واحدة على قيم عديدة. لذلك تظهر مشكلة ثالثة ، وهي القدرة على تخزين عدد متغير من خصائص السجل .يبدو أنهم تمكنوا من ذلك ، ولكن العميل في حالة تأهب ، لديه دائمًا شيء جديد جاهز. ثم يأتي الطلب - كل الدلائل تاريخية (على سبيل المثال ، كان اسم المنتج واحدًا ، ثم تمت إعادة تسميته ، ووفقًا للمستندات الخاصة بالتواريخ المختلفة ، يلزمك إظهار الاسم الحالي). الشرط نفسه طبيعي ، لا يمكنك قول أي شيء. وإذا كان هناك شخص آخر في قسم التطوير يمر بفترة تجريبية ، فإن كل شيء مغطى بالشوكولاتة ، فقد لا تلاحظ أن هذه مشكلة. ومع ذلك ، كل شيء يسير كالمعتاد - مع انهيار كامل ، وبعد ذلك لا تزال بحاجة إلى القيام بذلك. نقوم بإنشاء لوحات مكررة لجداول الدلائل المقابلة من أجل تخزين التسلسل الزمني للتغييرات في الدليل هناك. ولكن من خلال إنشاء هذه الجداول ، فإننا نخلق أيضًا مشكلة رابعة لأنفسنا ،الآن في الكود ، تبعاً للتاريخ ، يجب على المرء أن يشير إما إلى الجدول الرئيسي أو الجدول التاريخي .حسنًا ، نحن زملاء جيدون ، لقد فزنا بهذا أيضًا))) الآن ، أثناء شرب الشاي من الكوب الخاص بك ، تبدأ في المناقشة مع زملائك الآخرين حول ما كان عليهم حله ، وأنت تفهم أن قائمة المشاكل يتم تحديثها. تثير المناقشة السؤال حول كيفية تخزين نسخ من نفس السجل. أريد أن أبدي تحفظًا بأن النسخة ليست شيئًا يتناسب مع جدول التاريخ. في التاريخ ، من المفهوم ، حتى هذا التاريخ كان هناك اسم واحد ، واعتبارًا من هذا التاريخ ، يصبح آخر ذا صلة. وفي الإصدار ، من المفهوم أنه تم حفظ السجل لأول مرة مع وجود خطأ ، وبعد بضع ساعات تم فهمه وتغييره ، وتحتاج إلى معرفة جميع حالات هذا السجل. أولاً ، يجب أن يكون هناك سحق لفترة ، وليس يوم واحد فقط. وثانيًا ، هناك حاجة إلى مثل هذه الآثار في حالة حدوث مواجهة. على سبيل المثال ، قاموا بملء قائمة الأسعار ، وارتكبوا خطأ ، وتمكنوا من بيع البضائع بهذا السعر ، ثم قاموا بتصحيحها ،ولكن في نهاية اليوم كان هناك عدم توازن. ومع ذلك ، أزعجني قرار هذه الحالات شخصيًا ، فقد تم اقتراح تخزين كل هذه التغييرات في الجدول نفسه. لن أقوم بترتيب holivar على مقدار الحق ، ولكن بالنسبة لي أصبح واضحًاالمشكلة الخامسة ، وهي تخزين التغييرات القياسية .لذا ، لتلخيص ما سبق ، نرى أمامنا خمس مكابس ثقيلة. الآن مهمتنا هي تحديد استراتيجية تسمح لك بالتجول وليس الوقوف عليها.كم يمكنك أن تخطو في نفس المدمة ، دعنا نتخلص ونشتري أخرى جديدة
من خلال البدء في تصميم نظام من الصفر ، لا يمكن لأحد أن يتنبأ بمسار تطوره ، وبالتالي لا يمكنه تحديد المستوى الذي سيكون من الضروري التعميم فيه ، كما هو الحال في المثال الموضح مع المعدات. لذلك ، من المنطقي تعيين كيان تجريدي يتم توزيعه على جميع جداول 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 ، فإن العلاقة بينهما مهمة أيضًا. لذا ، على سبيل المثال ، تشمل المنظمات الكبيرة وحدات وفروع وإدارات مختلفة ، وما إلى ذلك ، والتي يمكن بناؤها في هيكل شجرة. بادئ ذي بدء ، سننشئ العديد من المؤسسات في نظامنا والتي ستكون تابعة لـ "Horns and Hooves" الموجودة.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;
هذا كل شيء ، الآن يمكننا القول بثقة أننا أغلقنا المشكلة الأولى .بالطبع ، يمكنك محاولة إرفاق الكثير من الأشياء بهذا النظام ، ولكن أعتقد أنني أكملت المهمة في بداية المقالة ، ويمكن بالفعل النظر في الباقي في عملية المناقشة.تم تحضير المادة على Oracle الإصدار 18c ، على الرغم من وجود دعم أصلي لتنسيق json بالفعل في الإصدار 12. وهنا ارتباط لأرشيف البرنامج النصي.