User
CREATE TABLE object (
id NUMBER(11),
parent_id NUMBER(11),
type VARCHAR2(16) NOT NULL,
name VARCHAR2(255) NOT NULL,
CONSTRAINT pk_object PRIMARY KEY (id),
CONSTRAINT fk_object_parent FOREIGN KEY (parent_id) REFERENCES object (id) ON DELETE CASCADE ENABLE
);
id | parent_id | type | name
------------------------------------------------------
1 | NULL | country | Россия
2 | 1 | region | Московская область
3 | 1 | region | Новосибирская область
4 | 2 | city | Москва
5 | 3 | city | Новосибирск
-- Выбрать все города России
SELECT *
FROM object
WHERE type = 'city'
START WITH id = 1 CONNECT BY PRIOR id = parent_id;
-- Выбрать страну, в которой находится Новосибирск
SELECT *
FROM object
WHERE type = 'country'
START WITH id = 5 CONNECT BY PRIOR parent_id = id;
Habrahabr::instance()->sup(tr("Good morning, %1").arg(username));
