скрипты

CREATE TABLE MKB10 (
MKB_ID INTEGER NOT NULL,
TREE_ID INTEGER,
MKB_COD VARCHAR(10)
);


ALTER TABLE MKB10 ADD CONSTRAINT PK_MKB10 PRIMARY KEY (MKB_ID);
CREATE INDEX MKB10_IDX1 ON MKB10 (TREE_ID);

SET TERM ^ ;

create or alter procedure MKB_PARENT (
ID integer)
returns (
TREE_ID integer)
as
BEGIN
TREE_ID = :ID;
SUSPEND;

FOR SELECT TREE_ID FROM MKB10 WHERE MKB_id= :ID INTO :tree_id
DO
FOR SELECT TREE_ID FROM MKB_PARENT(:TREE_ID) INTO :TREE_ID
DO SUSPEND;
END
^

SET TERM ; ^
GRANT SELECT ON MKB10 TO PROCEDURE MKB_PARENT;
GRANT EXECUTE ON PROCEDURE MKB_PARENT TO PROCEDURE MKB_PARENT;
GRANT EXECUTE ON PROCEDURE MKB_PARENT TO SYSDBA;



INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (2, 1, 'A00-B99');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (3, 2, 'A00-A09');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (4, 3, 'A00');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (5, 4, 'A00.0');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (6, 4, 'A00.1');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (7, 4, 'A00.9');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (8, 3, 'A01');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (9, 8, 'A01.0');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (10, 8, 'A01.1');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (11, 8, 'A01.2');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (12, 8, 'A01.3');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (13, 8, 'A01.4');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (14, 3, 'A02');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (15, 14, 'A02.0');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (16, 14, 'A02.1');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (17, 14, 'A02.2+');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (18, 14, 'A02.8');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (19, 14, 'A02.9');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (20, 3, 'A03');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (21, 20, 'A03.0');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (22, 20, 'A03.1');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (23, 20, 'A03.02');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (24, 20, 'A03.3');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (25, 20, 'A03.8');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (26, 20, 'A03.9');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (27, 3, 'A04');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (28, 27, 'A04.0');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (29, 27, 'A04.1');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (30, 27, 'A04.2');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (31, 27, 'A04.3');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (32, 27, 'A04.4');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (33, 27, 'A04.5');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (34, 27, 'A04.6');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (35, 27, 'A04.7');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (36, 27, 'A04.8');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (37, 27, 'A04.9');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (38, 3, 'A05');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (39, 38, 'A05.0');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (40, 38, 'A05.1');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (41, 38, 'A05.2');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (42, 38, 'A05.3');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (43, 38, 'A05.4');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (44, 38, 'A05.8');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (45, 38, 'A05.9');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (46, 3, 'A06');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (47, 46, 'A06.0');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (48, 46, 'A06.1');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (49, 46, 'A06.2');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (50, 46, 'A06.3');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (51, 46, 'A06.4');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (52, 46, 'A06.5+');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (53, 46, 'A06.6+');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (54, 46, 'A06.7');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (55, 46, 'A06.8');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (56, 46, 'A06.9');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (57, 3, 'A07');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (58, 57, 'A07.0');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (59, 57, 'A07.1');
INSERT INTO MKB10 (MKB_ID, TREE_ID, MKB_COD)
VALUES (60, 59, 'AAAA');

COMMIT WORK;