1

Topic: Hierarchical tables, requests, operation dataful in Oracle

Basis: Oracle Database 11g Release 2
/*
* On operation it was necessary to face hierarchical tables, them , usage and operation dataful.
* !!! A question here in what:!!!
* Where it is possible to esteem and study methods, scripts for operation and hierarchical tables.
* Yesterday all the day long hung on the Internet, and I pound it is not enough, all as that is separate and in the core on MySQL.
* Or the drop if to you not difficult.
* And it is necessary: to Delete a branch, to Interpose a branch, to Move a branch, to make changes to a branch.
* And it is necessary to use already and it was necessary yesterday sad(.
* On an example:
* 1. To move Open Company "" in Moscow
* 2. To delete a branch all branch/Liski/LiskiPress
* 3. To interpose a branch / city/Smolensk/Gorodskaja hospital/chamber No6
*/
-- That we have:

-- We create the test table
CREATE TABLE TEST_TABLE_HIERARCH (
id NUMBER;
pid NUMBER;
title VARCHAR2 (256));
-- We fill with its data
BEGIN
INSERT ALL
INTO TEST_TABLE_HIERARCH (id, pid, title) VALUES (1, null, ' Russia ')
INTO TEST_TABLE_HIERARCH (id, pid, title) VALUES (2, 1, ' Voronezh ')
INTO TEST_TABLE_HIERARCH (id, pid, title) VALUES (3, 2, ' Open Company "" ')
INTO TEST_TABLE_HIERARCH (id, pid, title) VALUES (4, 1, ' Moscow ')
INTO TEST_TABLE_HIERARCH (id, pid, title) VALUES (5, 1, ' Liski ')
INTO TEST_TABLE_HIERARCH (id, pid, title) VALUES (6, 3, ' Principal office ')
INTO TEST_TABLE_HIERARCH (id, pid, title) VALUES (7, 3, ' Office 1 ')
INTO TEST_TABLE_HIERARCH (id, pid, title) VALUES (8, 3, ' Office 2 ')
INTO TEST_TABLE_HIERARCH (id, pid, title) VALUES (9, 8, ' the Server 1 ')
INTO TEST_TABLE_HIERARCH (id, pid, title) VALUES (10, 5, ' LiskiPress ')
SELECT * FROM dual;
COMMIT;
END;
-- There are sample / methods  from the hierarchical table and its check on cycling etc.
SELECT
CONNECT_BY_ISLEAF as isleaf, - enclosure presence further
LEVEL, - nesting level
id;
pid;
title;
PRIOR title as prior_, - the parent
CONNECT_BY_ROOT title as root, - a root
LPAD (' ', 3*level) || title as dir _, - evident an enclosure example
SYS_CONNECT_BY_PATH (title, ' / ') AS v_path - enclosure connection at line with sharing "/"
FROM TEST_TABLE_HIERARCH
START WITH pid IS NULL - from what position we start to select hierarchy
CONNECT BY PRIOR id = pid - connection of the son id with the parent id
ORDER SIBLINGS BY title; - it is sorted in hierarchy
-- Let's deduce trees/branches of this table
SELECT SYS_CONNECT_BY_PATH (title, ' / ') as v_path
FROM TEST_TABLE_HIERARCH
START WITH pid IS NULL
CONNECT BY PRIOR id = pid
ORDER SIBLINGS BY title;
-- We have result:
V_PATH
--------------------------------------------
/rossija
/Rossija/Voronezh
/// "Timarenis"
/// "Timarenis" / the main office
/// "Timarenis" / office 1
/// "Timarenis" / office 2
/// "Timarenis" / office the 2/server 1
/Rossija/Liski
///
/Rossija/Moskva

2

Re: Hierarchical tables, requests, operation dataful in Oracle

1.

update TEST_TABLE_HIERARCH set PID = 4 where ID = 3;

2.

delete from TEST_TABLE_HIERARCH
where id in (
select id from TEST_TABLE_HIERARCH
START WITH id = 5
CONNECT BY PRIOR id = pid
);

3.

insert into TEST_TABLE_HIERARCH VALUES (11, 1, ' Smolensk ');
insert into TEST_TABLE_HIERARCH VALUES (12, 11, ' City hospital ');
insert into TEST_TABLE_HIERARCH VALUES (13, 12, ' Chamber N6 ');

3

Re: Hierarchical tables, requests, operation dataful in Oracle

Thanks!!!!

4

Re: Hierarchical tables, requests, operation dataful in Oracle

Thanks!!!

5

Re: Hierarchical tables, requests, operation dataful in Oracle

It is necessary, a vanishing species, grateful students.

6

Re: Hierarchical tables, requests, operation dataful in Oracle

Alas children, I not the student. Though it would be pleasant to become them again. I it ceased to be 25 years ago. smile))).
Decided to return reversely to the main trade. smile). I know that is difficult, but I am not afraid of difficulties. Dare both you high peaks and to you it will be rendered. On - to it all I express the gratitude for the help rendered to me now and further.
Yours faithfully
GSVG.