| CREATE OR REPLACE PACKAGE pak_dover4 IS
/*
Спецификация типов основной таблицы
*/
TYPE DOVER IS RECORD(
KOD NUMBER(10),
NDOC NUMBER(7),
DATAV DATE,
DATA_DEIS DATE,
KOD_FIZ_LIC2 NUMBER(7),
KOD_ORG2 VARCHAR2(7)
); --этот тип, повторяет тип T_D_DOVER4 за тем исключением,
--что в нем отсутствует атрибут N1_NAIMEN ссылающийся
--на тип вложенной таблицы
TYPE DOVER_REF IS REF CURSOR RETURN DOVER;
TYPE DOVER_TAB IS TABLE OF DOVER INDEX BY BINARY_INTEGER;
/*
Спецификация типов вложенной таблицы
*/
TYPE N_DOVER IS RECORD(
kod NUMBER(8,0),
kod_naimen2 NUMBER(7),
kol NUMBER(5,3),
ed_izm VARCHAR2(8)
); --этот тип, повторяет тип T_D_N1_DOVER4 за тем исключением
TYPE N_DOVER_REF IS REF CURSOR RETURN N_DOVER;
TYPE N_DOVER_TAB IS TABLE OF N_DOVER INDEX BY BINARY_INTEGER;
/*
Описание процедур для основной таблицы
*/
PROCEDURE query_dover (tbl IN OUT dover_ref, d_kod IN NUMBER);
PROCEDURE insert_dover (tbl IN OUT dover_tab);
PROCEDURE update_dover (tbl IN OUT dover_tab);
PROCEDURE delete_dover (tbl IN OUT dover_tab);
PROCEDURE lock_dover (tbl IN OUT dover_tab);
/*
Описание процедур для вложенной таблицы
*/
PROCEDURE n_query_dover (tbl IN OUT n_dover_ref, nom IN NUMBER);
PROCEDURE n_insert_dover (tbl IN OUT n_dover_tab, nom IN NUMBER);
PROCEDURE n_update_dover (tbl IN OUT n_dover_tab, nom IN NUMBER);
PROCEDURE n_delete_dover (tbl IN OUT n_dover_tab, nom IN NUMBER);
PROCEDURE n_lock_dover (tbl IN OUT n_dover_tab, nom IN NUMBER);
END;
/
CREATE OR REPLACE PACKAGE BODY pak_dover4 IS
/*
Описание процедур для основной таблицы
*/
--запрос
PROCEDURE query_dover (tbl IN OUT dover_ref, d_kod IN NUMBER) IS
BEGIN
OPEN tbl FOR SELECT
kod, ndoc, datav, data_deis,
DEREF(ref_fiz_lic2).kod as kod_fiz_lic2,
DEREF(ref_org2).kod as kod_org2
FROM D_DOVER4
WHERE kod = d_kod;
END;
--вставка
PROCEDURE insert_dover (tbl IN OUT dover_tab) IS
BEGIN DECLARE
x BINARY_INTEGER;
BEGIN
FOR x IN 1..tbl.COUNT
LOOP
INSERT INTO D_DOVER4(kod, ndoc, datav, data_deis, ref_fiz_lic2, ref_org2, n1_naimen)
VALUES
(tbl(x).kod, tbl(x).ndoc, tbl(x).datav, tbl(x).data_deis,
(SELECT REF(s) FROM s_fiz_lic2 s WHERE s.kod = tbl(x).kod_fiz_lic2),
(SELECT REF(s) FROM s_org2 s WHERE s.kod = tbl(x).kod_org2),
tt_d_n1_dover4()
);
END LOOP;
END;
END;
--обновление
PROCEDURE update_dover (tbl IN OUT dover_tab) IS
BEGIN DECLARE
x BINARY_INTEGER;
BEGIN
FOR x IN 1..tbl.COUNT
LOOP
UPDATE d_dover4 SET
kod=tbl(x).kod, ndoc=tbl(x).ndoc, datav=tbl(x).datav,
data_deis=tbl(x).data_deis,
ref_fiz_lic2=(SELECT REF(s) FROM s_fiz_lic2 s WHERE s.kod = tbl(x).kod_fiz_lic2),
ref_org2=(SELECT REF(s) FROM s_org2 s WHERE s.kod = tbl(x).kod_org2) WHERE kod=tbl(x).kod;
END LOOP;
END;
END;
--удаление
PROCEDURE delete_dover (tbl IN OUT dover_tab) IS
BEGIN DECLARE
x BINARY_INTEGER;
BEGIN
FOR x IN 1..tbl.COUNT
LOOP
DELETE d_dover4 WHERE kod=tbl(x).kod;
END LOOP;
END;
END;
--блокировка
PROCEDURE lock_dover
(tbl IN OUT dover_tab) IS
BEGIN DECLARE
x BINARY_INTEGER;
temp number(10,0);
BEGIN
FOR x IN 1..tbl.COUNT
LOOP
SELECT kod INTO temp FROM d_dover4 WHERE kod=tbl(x).kod
FOR UPDATE;
END LOOP;
END;
END;
/*
Описание процедур для вложенной таблицы
*/
--запрос
PROCEDURE n_query_dover (tbl IN OUT n_dover_ref, nom IN NUMBER) IS
BEGIN
OPEN tbl FOR SELECT
kod, DEREF(ref_s_naimen2).kod as kod_naimen2,
kol, ed_izm FROM TABLE (SELECT n1_naimen from d_dover4 WHERE kod=nom);
END;
--вставка
PROCEDURE n_insert_dover (tbl IN OUT n_dover_tab, nom IN NUMBER) IS
BEGIN DECLARE
x BINARY_INTEGER;
BEGIN
FOR x IN 1..tbl.COUNT
LOOP
INSERT INTO TABLE (SELECT n.n1_naimen FROM d_dover4 n WHERE kod=nom)
( KOD, ref_s_naimen2, kol, ed_izm )
VALUES
(seq_d_dover4_n1.nextval,
(SELECT REF(s) FROM s_naimen2 s WHERE kod = tbl(x).kod_naimen2),
tbl(x).kol,
tbl(x).ed_izm
);
END LOOP;
END;
END;
--обновление
PROCEDURE n_update_dover (tbl IN OUT n_dover_tab, nom IN NUMBER)IS
BEGIN DECLARE
x BINARY_INTEGER;
BEGIN
FOR x IN 1..tbl.COUNT
LOOP
UPDATE THE(SELECT n1_naimen FROM d_dover4 d WHERE kod=nom)
SET
kod=tbl(x).KOD,
ref_s_naimen2=(SELECT REF(s) FROM s_naimen2 s WHERE kod = tbl(x).kod_naimen2), kol=tbl(x).kol, ed_izm=tbl(x).ed_izm
WHERE kod=tbl(x).kod;
END LOOP;
END;
END;
--удаление
PROCEDURE n_delete_dover (tbl IN OUT n_dover_tab, nom IN NUMBER) IS
BEGIN DECLARE
x BINARY_INTEGER;
BEGIN
FOR x IN 1..tbl.COUNT
LOOP
DELETE THE(SELECT n1_naimen FROM d_dover4 n WHERE kod=nom)
WHERE kod=tbl(x).kod;
END LOOP;
END;
END;
--блокирование
PROCEDURE n_lock_dover (tbl IN OUT n_dover_tab, nom IN NUMBER) IS
BEGIN DECLARE
x BINARY_INTEGER;
temp NUMBER(10,0);
BEGIN
FOR x IN 1..tbl.COUNT
LOOP
SELECT kod
INTO temp
FROM THE(SELECT n1_naimen FROM d_dover4 WHERE kod=nom) WHERE kod=tbl(x).kod
FOR UPDATE;
END LOOP;
END;
END;
END;
/
|
2018-02-13
344
