Коллекции
Другую возможность по группировке объектов дают "коллекции". Они позволяют хранить в поле "обычной" таблицы список. Элементами списка могут быть скаляры (числа, строки), объекты или ссылки на самостоятельно существующие объекты.
В Oracle они могут быть двух видов: вложенные таблицы и массивы типа VARRAY.
Массивы типа VARRAY
Массивы типа VARRAY потребительски во многом похожи на вложенные таблицы, но имеют и ряд существенных технических и внешних отличий. Например, они обязаны иметь ограничение на максимальное число элементов в конкретных массивах, наподобие типу VARCHAR2. Еще они не требуют для хранения данных служебной таблицы, наподобие вложенной таблицы. Есть и другие отличия. Фирма Oracle советует использовать вложенные таблицы, если нужно хранить неупорядоченные списки и VARRAY, если нужно хранить упорядоченные.
Пример использования для группировки сотрудников коллекции VARRAY может выглядеть так:
CREATE TYPE employee_vlist_typ AS VARRAY(20) OF employee_typ
/
CREATE TABLE department1 (
dname VARCHAR2(15),
emps employee_vlist_typ
);
Этим типом мы запретили отделам иметь более 20-и сотрудников.
Добавление нового отдела делается как и для вложенных таблиц:
INSERT INTO department1 VALUES (
'Operations',
employee_vlist_typ (
employee_typ ('Scott', 'Manager'),
employee_typ ('Smith', 'Salesman')
)
);
Приведенный выше код на PL/SQL для массива сотрудников VARRAY проработает так же.
Mea culpa
Обычно на занятиях я говорю студентам, что про Oracle рассказать все не может никто, и если вам такое обещают, не верьте этим людям сразу. Нет и у меня таких амбиций, но элемент самоуспокоенности, порожденный этим обстоятельством, все-таки нужно в себе давить. Об этом напомнил мне недавний урок.
Речь идет о внешних ключах. Вопросы слушателей заставили недавно освежить мои представления о них. В итоге я
(а) приношу извинения сразу всей своей прошлой аудитории за чересчур категоричные утверждения о поведении внешних ключей в Oracle
(б) привожу в этой статье некоторые свойства внешних ключей, расширяющие их общеизвестные возможности.
Все же, приводимые ниже возможности имеют специальный, а не универсальный характер. Кроме того каскадное удаление я по-прежнему считаю довольно рискованной в общем случае операцией.
Методы объектов
Выше было рассмотрено определение типа, содержащее описание атрибутов (“свойств”). Создадим тип сотрудников, в котором определен еще и метод:
CREATE TYPE employee_typ AS OBJECT (
ename VARCHAR2(50),
hiredate DATE,
deptno NUMBER,
home REF address_typ,
MEMBER FUNCTION days_at_company RETURN NUMBER)
/
Для описания тела метода-функции необходимо создать тело типа (аналогия пакет – тело пакета в PL/SQL):
CREATE TYPE BODY employee_typ IS
MEMBER FUNCTION days_at_company RETURN NUMBER IS
BEGIN
RETURN TRUNC(SYSDATE-hiredate);
END;
END;
/
Создадим таблицу объектов-сотрудников:
DROP TABLE emp;
CREATE TABLE emp OF employee_typ;
INSERT INTO emp VALUES (
'Scott',
SYSDATE,
10,
(SELECT REF(a) FROM addr_list1 a
WHERE VALUE(a) = address_typ('123458', 'Boston 123... ')));
Пример обращения к методу:
COLUMN home.location FORMAT A20
SELECT e.ename, e.home.location, e.days_at_company() FROM emp e;
Определение внешнего ключа
Внешний ключ - это ссылка полей одной таблицы на однотипные поля другой таблицы, обладающие свойством либо (а) уникальности, либо (б) первичного ключа. Исключительно для простоты дальше речь будет идти о простых ключах, то есть состоящих из одного поля. СУБД (в нашем случае - Oracle) обязана следить за тем, чтобы значение внешнего ключа позволило найти запись в таблице, на которую он ссылается (в родительской таблице), а уникальность "родительского поля" гарантирует, что это будет ровно одна запись. Вместе с тем наличия значения в поле внешнего ключа (в подчиненной таблице) не требуется, и автоматические проверки соответствия выполняются только для существующих значений в поле внешнего ключа.
Внешний ключ в Oracle вслед за стандартным SQL реализован как разновидность ограничения. Информация о нем включается в Oracle в системную таблицу USER_CONSTRAINTS.
Внешний ключ в демонстрационной схеме SCOTT в Oracle существует: это поле EMP.DEPTNO, ссылающееся на поле первичного ключа DEPT.DEPTNO. Совпадения имен полей не требуется, но когда оно возможно, это удобно, так как подчеркивает содержательную связь.
Завести внешний ключ можно сразу при создании таблицы, или же потом, командой
ALTER TABLE xxx ADD [CONSTRAINT имя] FOREIGN KEY (fff) REFERENCING ttt(kkk);
Преобразования коллекций
Как и следовало бы ожидать от СУБД Oracle, плотная, а не поверхностная работа с коллекциями в качестве средства моделирования групп объектов требует знания большого числа "деталей". Здесь не место разъяснять их все, но одну важную для коллекций возможность стоит привести. Имеется в виду разворачивание коллекции в список строк, столь привычный для традиционной работы.
Для того, чтобы посмотреть список сотрудников отдела 'Operations' в более привычном виде, следует воспользоваться специальной функцией TABLE:
SELECT * FROM TABLE(SELECT emps FROM department);
К аргументу функции TABLE (это вложенный SELECT) есть одна настоятельная просьба: возвращать одну и только коллекцию. Наши данные это обеспечивают, а иначе вложенный SELECT нужно было бы подправить.
Аналогичный пример для массива VARRAY:
SELECT ename FROM TABLE(SELECT emps FROM department1);
Замечательно, что это преобразование решает задачу и изменения списка средствами SQL:
INSERT INTO TABLE(SELECT emps FROM department)
VALUES ('Allen', 'Salesman');
SELECT * FROM TABLE(SELECT emps FROM department);
(Эта возможность не сработает, однако, для массива VARRAY, который в БД ведет себя, по сути, как скаляр, допуская изменение поля-списка как единого, уже сформированного целого). Если бы возможность такого преобразования отсутствовала, добавить сотрудника в отдел или удалить можно было бы только программным способом, проще всего в PL/SQL.
Естественно, никто не мешает осуществить и массовую вставку:
INSERT INTO TABLE(SELECT emps FROM department)
SELECT ename, job FROM emp;
Для обратного преобразования, из таблицы в коллекцию, потребуется более сложная конструкция:
SELECT
CAST (MULTISET(SELECT ename, job FROM emp) AS employee_nlist_typ)
FROM DUAL;
Однако такое преобразование на практике менее востребовано.
Простой пример
Рассмотрим схему БД, где хранятся данные о сотрудниках и отделах. Будем работать в схеме SCOTT, из которой на время нужно удалить таблицы EMP и DEPT (позже мы их восстановим).
Предположим, что и те, и другие имеют адреса: сотрудники - домашний, а отделы – юридический. Адрес имеет несколько полей (например, “индекс”, “район”, “населенный пункт”, “место”). В традиционной табличной реализации есть два способа промоделировать наличие адреса:
- включить одинаковые группы полей в таблицы сотрудников и отделов;
- создать отдельную таблицу адресов и включить в таблицы сотрудников и отделов ссылки на нее.
Первое решение неудобно тем, что адрес теряет свою идентичность: неудобно, например, сравнивать адреса, особенно в разных таблицах. Второе решение искусственно, если только не считать адреса самостоятельными объектами моделирования.
Объектные возможности последних версий Oracle дают возможность более приемлемой альтернативы. Для описания адреса создадим тип (здесь и далее предполагается использование в качестве рабочего инструмента SQL*Plus):
CREATE TYPE address_typ AS OBJECT (
zip CHAR(6),
location VARCHAR2(200))
/
Воспользуемся этим типом для описания сотрудников и отделов:
CREATE TABLE dept (
dname VARCHAR2(50),
deptno NUMBER CONSTRAINT pk_dept PRIMARY KEY,
addr address_typ);
CREATE TABLE emp (
ename VARCHAR2(50),
empno NUMBER CONSTRAINT pk_emp PRIMARY KEY,
deptno NUMBER CONSTRAINT fk_emp REFERENCES dept,
home address_typ);
Проверим описания созданных объектов:
DESCRIBE address_typ
DESCRIBE dept
DESCRIBE emp
Пример заведения сотрудников и отделов:
INSERT INTO dept VALUES (
'Sales',
10,
address_typ('123456', 'Boston 123... '));
INSERT INTO emp VALUES (
'Smith',
1001,
10,
address_typ('123333', 'Boston 567... '));
Здесь выражение ADDRESS_TYP('123333', 'Boston 567... ') означает обращение к конструктору объекта, то есть к функции, автоматически создаваемой СУБД при заведении нового типа для возможности создавать новые объекты этого типа с нужными значениями атрибутов. Понятие конструктора общепринято в объектном подходе. В приведенных предложениях INSERT простановку адреса можно оформить чуть иначе, добавив, в соответствии с духом объектного подхода, ключевое слово NEW перед обращением к конструктору:
INSERT INTO emp VALUES (
'Allen',
1002,
10,
NEW address_typ('123456', 'Boston 123... '));
Проверка:
COLUMN dname FORMAT A20
COLUMN ename FORMAT A20
COLUMN addr FORMAT A40
COLUMN home FORMAT A40
SELECT * FROM dept;
SELECT * FROM emp;
Другие примеры:
SELECT ename, home FROM emp;
SELECT e.ename, d.dname FROM emp e, dept d WHERE e.home = d.addr;
SELECT e.ename, e.home.zip FROM emp e;
UPDATE emp
SET home = address_typ('123457', 'Boston 777... ')
WHERE ename = 'Allen';
UPDATE emp e SET e.home.zip = '123458' WHERE ename = 'Allen';
Работа в PL/SQL
Вот как можно работать со вложенными таблицами в PL/SQL:
DECLARE
ee employee_nlist_typ;
BEGIN
SELECT emps INTO ee FROM department WHERE dname = 'Operations';
DBMS_OUTPUT.PUT_LINE(ee(1).ename);
DBMS_OUTPUT.PUT_LINE(ee(2).ename);
END;
/
В этом примере для упрощения использованы предпосылки о том, что (а) отдел с названием 'Operations' всего один и (в) сотрудников в нем - [по крайней мере] двое.
Создание таблицы объектов
Если адрес интересует нас как самостоятельная сущность, а не атрибут прочих сущностей, созданный для адреса тип можно использовать для создания таблиц объектов:
CREATE TABLE addr_list1 OF address_typ;
CREATE TABLE addr_list2 OF address_typ;
Таблицы объектов в Oracle было бы точнее называть списками объектов, так как это всегда таблицы ровно из одного столбца объектного типа.
Заполнение данными происходит как и ранее:
INSERT INTO addr_list1 VALUES
(NEW address_typ('123456', 'Boston 123... '));
или
INSERT INTO addr_list1 VALUES
(address_typ('123458', 'Boston 123... '));
INSERT INTO addr_list2 VALUES
(address_typ('123333', 'Boston 567... '));
Просмотр:
COLUMN location FORMAT A30
SELECT * FROM addr_list1;
SELECT VALUE(a) FROM addr_list1 a;
SELECT e.ename, e.home
FROM addr_list1 a, emp e
WHERE VALUE(a) = e.home;
(Функция VALUE специально придумана для возвращения значений объектов, а не атрибутов объектов по отдельности).
Ссылки на объект
Объекты, заведенные в объектных таблицах, имеют одно преимущество перед объектами, указанными как атрибут строки: на них можно ссылаться. Ссылка есть уникальный внутренний идентификатор объекта, и получить его можно с помощью функции REF:
COLUMN ref FORMAT A90
COLUMN value FORMAT A40
SELECT REF(a) ref, VALUE(a) FROM addr_list1 a;
Теперь можно поменять описание таблицы, например, DEPT, чтобы она заимствовала адреса отделов из имеющегося списка, а не хранила вместе со своими данными:
ALTER TABLE dept DROP (addr);
ALTER TABLE dept ADD (addr REF address_typ SCOPE IS addr_list1);
SELECT * FROM dept;
UPDATE dept d
SET d.addr =
(SELECT REF(a)
FROM addr_list1 a
WHERE VALUE(a)= address_typ('123458', 'Boston 123... '))
WHERE d.deptno = 10;
SELECT * FROM dept;
Фраза SCOPE IS при определении типа как ссылки на существующий объект необязательна, но позволяет фактически ссылаться только на объекты какой-нибудь объектной таблицы.
Раскрытие ссылки делается с помощью специальной функции DEREF:
COLUMN deref(addr) FORMAT A40
SELECT d.dname, DEREF(addr) FROM dept d;
Однако при обращении к нижележащим атрибутам раскрытие может выполняться и неявно (неявное преобразование типов, присутствующее в Oracle-диалекте SQL):
SELECT d.dname, d.addr.zip FROM dept d;
вместо более правильного
SELECT d.dname, DEREF(d.addr).zip FROM dept d;
Таблицы хранимых и синтезированных объектов
Первая возможность моделирования групп из объектов в Oracle известна по предыдущей статье: это таблицы "исконных" объектов (object tables) и таблицы "виртуальных", или "синтезированных" объектов (object views). Исконные объекты хранятся как самостоятельные сущности в БД, а синтезированные дают только видимость объектов (по потребительским свойствам почти не отличимую от истинных объектов) на основе данных, хранимых в обычных или объектных таблицах.
И те и другие позволяют иметь в БД неупорядоченные списки объектов. Ниже приводится пример создания двух списков сотрудников, проживающих в Москве и Ленинграде. Принадлежность сотрудников отделам задается специальной таблицей:
DROP TYPE employee_typ FORCE;
DROP TABLE e_moscow;
DROP TABLE e_leningrad;
DROP TABLE employment;
CREATE TYPE employee_typ AS OBJECT (
ename VARCHAR2(50),
job VARCHAR2(10))
/
CREATE TABLE e_moscow OF employee_typ;
CREATE TABLE e_leningrad OF employee_typ;
INSERT INTO e_moscow VALUES (
'Scott',
'Manager');
...
INSERT INTO e_leningrad VALUES (
'Smith',
'Salesman');
...
CREATE TABLE employment (
dname VARCHAR2(50),
employee REF employee_typ);
INSERT INTO employment VALUES (
'Operations',
(SELECT REF(m) FROM e_moscow m WHERE m.ename = 'Scott'));
...
Этот способ, однако, не лишен своих ограничений. Например, по данным таблицы EMPLOYMENT нельзя понять, проживает ли сотрудник в Москве или Ленинграде. Нельзя переселить сотрудника из Москвы в Ленинград (можно только удалить его из одной таблицы и создать в другой объект с теми же атрибутами) и так далее.
Удаление родительской записи может автоматически изменять подчиненные таблицы
Классическим проявлением ограничения "внешний ключ" является отказ СУБД удалить родительскую запись при наличии хотя бы одной подчиненной. В этом легко убедиться, войдя в схему SCOTT и набрав там
DELETE FROM dept WHERE deptno = 10;
Однако Oracle позволяет смоделировать и иную реакцию СУБД, все-таки разрешив удаление родительской записи. Для этого при создании внешнего ключа нужно специально указать фразу ON DELETE.
Указание ON DELETE CASCADE приведет к автоматическому удалению подчиненных записей:
CREATE TABLE x(a NUMBER PRIMARY KEY); CREATE TABLE y(b NUMBER PRIMARY KEY, c NUMBER REFERENCES x(a) ON DELETE CASCADE);
INSERT INTO x VALUES (1); INSERT INTO y VALUES (2,1); DELETE FROM x; SELECT * FROM y;
При этом автоматическое удаление может распространяться по цепочке:
CREATE TABLE z(d NUMBER PRIMARY KEY, e NUMBER REFERENCES y(b) ON DELETE CASCADE);
INSERT INTO x VALUES (1); INSERT INTO y VALUES (2, 1); INSERT INTO z VALUES (3, 2); DELETE FROM x; SELECT * FROM z;
(Автоматическим удалением по цепочке следует пользоваться с особой осторожностью).
Указание ON DELETE SET NULL приведет к автоматическому удалению значений в полях-ссылках подчиненных записей:
CREATE TABLE w(f NUMBER REFERENCES z(d) ON DELETE SET NULL);
INSERT INTO z VALUES (3, NULL); INSERT INTO w VALUES (3); DELETE FROM z; SELECT * FROM w;
Обратите внимание, что фраза CASCADE CONSTRAINTS в предложении DROP TABLE не соответствует ни первому, ни второму из вышеприведенных вариантов, попросту удаляя ограничение типа "внешний ключ", и не трогая значений подчиненных записей:
INSERT INTO x VALUES (1); INSERT INTO y VALUES (2, 1); DROP TABLE x CASCADE CONSTRAINTS; SELECT * FROM y;
DROP TABLE y CASCADE CONSTRAINTS; DROP TABLE z CASCADE CONSTRAINTS; DROP TABLE w CASCADE CONSTRAINTS;
Попутно обратите внимание, что если бы в предложениях DROP выше не фигурировала фраза CASCADE CONSTRAINTS, удалять таблицы пришлось бы в строго определенном порядке. Но это же обеспечивает в общем более "чистые" данные в БД, так что как правило фразы CASCADE CONSTRAINTS следует избегать.
Виртуальные объекты
Переводить в существующей БД табличные описания данных в объектные не всегда возможно, а иногда и не нужно. В силу разных обстоятельств может оказаться удобной имитация объектов на основе данных, хранимых в традиционных таблицах. Тогда к одним и тем же данным можно обращаться и через объектный интерфейс, и через табличный. Достигается это с помощью виртуальных объектов (object views), которых можно так назвать по аналогии с виртуальными таблицами (views).
Для примера вернем описания и наполнение традиционным таблицам схемы SCOTT: EMP и DEPT.
@?/sqlplus/admin/demobld
sqlplus scott/tiger
(Сценарий demobld.sql выводит нас из SQL*Plus).
Упростим для примера описание типа EMPLOYEE_TYP:
ALTER TYPE employee_typ DROP ATTRIBUTE (home);
ALTER TYPE employee_typ ADD ATTRIBUTE (empno NUMBER);
CONNECT scott/tiger
ALTER TYPE employee_typ COMPILE;
Построим таблицу виртуальных объектов типа EMPLOYEE_TYP по исходным данным, хранящимся в EMP:
CREATE VIEW emp_ov OF employee_typ
WITH OBJECT IDENTIFIER (empno) AS
SELECT e.ename, e.hiredate, e.deptno, e.empno FROM emp e;
По своему поведению виртуальные объекты ничем не отличаются от первичных. Проверка (“объектного доступа” к табличным данным):
SELECT e.ename, e.days_at_company () FROM emp_ov e;
SELECT VALUE(e) FROM emp_ov e;
SELECT REF(e) FROM emp_ov e;
UPDATE emp_ov e SET e.ename = INITCAP(e.ename)
WHERE e.empno = 7934;
SELECT ename FROM emp_ov;
Возможность выполнения традиционных DML-операторов над базовыми таблицами, естественно, сохраняется:
UPDATE emp SET ename = UPPER(ename) WHERE empno = 7934;
SELECT ename FROM emp;
Вложенные таблицы
Термин, выбранный для этого вида коллекций кажется не совсем удачным. Речь на самом деле идет о моделировании не таблиц, а списков. В отличие от предыдущего примера построим таблицу DEPARTMENTS с перечнем отделов, причем в строке о каждом отделе будем хранить список сотрудников. Предварительно, однако, нужно будет создать тип для такого поля-списка:
CREATE TYPE employee_nlist_typ AS TABLE OF employee_typ
/
CREATE TABLE department (
dname VARCHAR2(20),
emps employee_nlist_typ)
NESTED TABLE emps STORE AS emps_nt_tab;
Фраза NESTED TABLE - чисто техническая. Она обязана тому, что физически Oracle будет хранить в поле EMPS не список объектов-сотрудников, а список их системных идентификаторов, присвоенных при помещении самих сотрудников в специально создаваемую для таблицы DEPARTMENT служебную таблицу. Таким образом при использовании вложенных таблиц значения элементов хранимых списков физически всегда хранятся в специальной отдельной таблице, которой мы обязаны придумать название. В таком решении есть своя логика, так как для этой специальной таблицы мы можем указать собственные характеристики хранения и некоторые другие вещи.
Вот как можно заполнить таблицу отделов:
По терминологии предыдущей статьи сотрудники в таблице DEPARTMENT - "объектные атрибуты". Другой способ смоделировать ситуацию "сотрудники-отделы" с помощью коллекции - воспользоваться списком ссылок на сотрудников, реально существующих в собственных таблицах.
Внешний ключ может ссылаться на поля таблицы из другой схемы
Чаще всего родительская и подчиненные таблицы находятся в одной схеме БД Oracle. Не так известно, что родительская и подчиненная таблицы могут находиться в разных схемах. Так как схемы используются для разграничения доступа, возникает вопрос о полномочиях.
Для того, чтобы иметь возможность сослаться внешним ключом на поле таблицы в другой схеме, на это поле должна иметься привилегия REFERENCING. Особо нужно отметить, что с привилегиями SELECT, INSERT, UPDATE и DELETE привилегия REFERENCING никак не связана. Иными словами схема с подчиненной таблицей может ничего не знать о конкретных значениях ключа, на которые есть возможность ссылаться, равно как на наличие других полей в таблице. Пример:
CONNECT / AS SYSDBA
CREATE USER adam IDENTIFIED BY eva DEFAULT TABLESPACE users; GRANT CONNECT, RESOURCE TO adam;
CONNECT scott/tiger
GRANT SELECT ON emp TO adam; GRANT REFERENCES ON dept TO adam;
CONNECT adam/eva
CREATE TABLE emp AS SELECT * FROM scott.emp; ALTER TABLE emp ADD FOREIGN KEY (deptno) REFERENCES scott.dept (deptno); INSERT INTO emp (ename, deptno) VALUES ('ADAM', 10); INSERT INTO emp (ename, deptno) VALUES ('EVA', 50);
Последняя вставка будет вызывать ошибку до тех пор, пока в таблице SCOTT.DEPT не появится запись об отделе 50.
(Привилегия SELECT на таблицу EMP была выдана пользователю ADAM исключительно для возможности скопировать эту таблицу).
Обозначенная выше возможность довольно своеобразна, так как разрушает самодостаточность схемы с точки зрения формирования данных. Если она использована, то ни схема с родительской таблицей, ни схема с подчиненной таблицей уже не могут безоглядно править собственные данные и вынуждены координировать свои действия с данными из других схем.
Тем не менее практика баз данных настолько разнообразна, что указанная возможность иногда может оказаться востребованной.
к моделированию БД, безусловно, имеет
Объектный подход к моделированию БД, безусловно, имеет свою притягательность, хотя, как замечалось в предыдущей статье () ценность его преувеличивать не стоит, так как и он не лишен собственных проблем и ограничений (к слову сказать, известных задолго до середины 90-х годов, когда большая промышленность СУБД им вооружиться). Однако подход есть, и как-то решать задачу моделирования он обязан. В предыдущей статье говорилось о том, как в Oracle можно создавать и хранить отдельные объекты. В жизни этого недостаточно, и требуется иметь возможность моделировать группы объектов: наборы адресов, списки сотрудников и т. д.
Такая возможность предусматривалась, например, в сетевой модели данных, исторически предшествовавшей реляционной, и проектировалась в виде расширения реляционной создателем последней Э. Коддом (в силу ряда причин это расширение было проигнорировано разработчиками промышленных "реляционных" СУБД). Здесь будут рассмотрены возможности моделирования групп объектов, реализованные в Oracle последних версий (8, 9).
В версии 8 в Oracle появилась возможность хранения неатомарных (нескалярных) значений в поле таблицы, а именно объекта в смысле объектного подхода (в рамках так называемой “объектно-реляционной модели” Oracle). Некоторые существенные пробелы этой первой реализации были устранены в версии 9. Примеры ниже используют возможности версии 9.2.
Сразу надо предостеречь от преувеличений достоинств объектного подхода в базах данных вообще. Действительно, неискушенный читатель некоторых руководств или рекламных материалов быстро впадет в недоумение: зачем же такие маститые разработчики СУБД, как фирмы IBM, Informix или Oracle так долго занимались табличной организацией данных, когда все это время рядом существовала более совершенная, удобная и т. д. объектная, первая реализация которой фирмой Xerox известна с 1980 года?
Непредвзятый ответ состоит в том, что ни табличная организация (часто вольно называемая “реляционной” применительно к конкретным СУБД), ни объектная не являются универсально “хорошими”, и что имеются свои достоинства и недостатки у одной и у другой. Некоторые соображения относительно областей применения обоих подходов к хранению данных можно найти в статье “”.
В целом объектная реализация в Oracle традиционна для объектного подхода вообще. В основе лежит понятие объекта как совокупности свойств (атрибутов), причем действия с объектом регламентируются формулируемым набором методов (процедур или функций). Тип объекта задается сохраняемым в БД объектом TYPE.
Использование предыдущих значений при выполнении рекурсии
Рекурсивные запросы с фразой WITH позволяют программисту больше, нежели запросы с CONNECT BY (тоже рекурсивные). Например, они позволяют накапливать изменения, и не испытывают необходимости в функциях LEVEL или SYS_CONNECT_BY_PATH, имея возможность легко их моделировать.
Пример запроса по маршрутам из Москвы с подсчетом километража: WITH stepbystep ( node, way, distance ) AS ( SELECT node, parent '-' node, distance FROM route WHERE parent = 'Москва'
UNION ALL SELECT r.node , s.way '-' r.node , r.distance + s.distance FROM route r INNER JOIN stepbystep s ON ( s.node = r.parent ) ) SELECT way, distance FROM stepbystep /
Ответ: WAY DISTANCE ---------------------------------------- ---------- Москва-Ленинград 696 Москва-Новгород 538 Москва-Новгород-Ленинград 717 Москва-Ленинград-Выборг 831 Москва-Новгород-Ленинград-Выборг 852
Запрос по маршрутам из Выборга аналогичен, но с поправкою на симметрию, вызванной движением по иерархии снизу вверх, а не сверху вниз: WITH stepbystep ( parent, way, distance ) AS ( SELECT parent, node '-' parent, distance FROM route WHERE node = 'Выборг'
UNION ALL SELECT r.parent , s.way '-' r.parent , r.distance + s.distance FROM route r INNER JOIN stepbystep s ON ( s.parent = r.node ) ) SELECT way, distance FROM stepbystep /
Ответ: WAY DISTANCE ---------------------------------------- ---------- Выборг-Ленинград 135 Выборг-Ленинград-Москва 831 Выборг-Ленинград-Новгород 314 Выборг-Ленинград-Новгород-Москва 852
Обработка зациклености данных
Пример организации зациклености в сведениях о маршрутах: INSERT INTO route VALUES ( 'Новгород', 'Выборг', 135 );
Реакция на появление цикла (уже получается не иерархия) в этом случае отлична от имевшейся для CONNECT BY и будет: ERROR: ORA-32044: cycle detected while executing recursive WITH query
Упражнение. Проверить это самостоятельно.
Для предупреждения зацикливания вычислений вводится специальное указание CYCLE, где следует указать перечень (в общем случае) столбцов для распознавания хождения по кругу, придумать название столбца- индикатора (он автоматически включается в конечный ответ) и задать пару символов: для обозначения незацикленной строки и для обозначения строки, где было зафиксировано повторение значений в различительных столбцах: WITH stepbystep ( node, way, distance ) AS ( SELECT node, parent '-' node, distance FROM route WHERE parent = 'Москва' UNION ALL SELECT r.node , s.way '-' r.node , r.distance + s.distance FROM route r INNER JOIN stepbystep s ON ( s.node = r.parent ) ) CYCLE node SET cyclemark TO 'X' DEFAULT '-'
SELECT way, distance, cyclemark FROM stepbystep /
Ответ:
WAY DISTANCE C ------------------------------------------ ---------- - Москва-Ленинград 696 - Москва-Новгород 538 - Москва-Новгород-Ленинград 717 - Москва-Ленинград-Выборг 831 - Москва-Новгород-Ленинград-Выборг 852 - Москва-Ленинград-Выборг-Новгород 966 - Москва-Ленинград-Выборг-Новгород-Ленинград 1145 X
Москва-Новгород-Ленинград-Выборг-Новгород 987 X
Построение рекурсивных запросов с помощью вынесения подзапроса во фразу WITH
С версии 11.2 фраза WITH может использоваться для формулирования рекурсивных запросов, в соответствии (неполном) со стандартом SQL:1999. В этом качестве она способна решать ту же задачу, что и CONNECT BY, однако (а) делает это похожим с СУБД других типов образом, (б) обладает более широкими возможностями, (в) применима не только к запросам по иерархии и (г) записывается значительно более замысловато.
Общий алгоритм вычисления фразой WITH таков:
| Результат := пусто; Добавок := исходный SELECT ...; Пока Добавок не пуст выполнять: | ||
| Результат := | Результат
| |
| Добавок := рекурсивный SELECT ... FROM Добавок …; | ||
| конец цикла; |
Предложение SELECT для исходного множества строк Oracle называет опорным (anchor) членом фразы WITH. Предложение SELECT для получения добавочного множества строк Oracle называет рекурсивным членом.
Примеры рекурсивных запросов с помощью CONNECT BY
Запрос вниз по иерархии от узла 'Москва' (присутствует только в качестве предка): SQL> COLUMN way FORMAT a45 SQL> SELECT SYS_CONNECT_BY_PATH ( node, '/' ) way 2 FROM route 3 CONNECT BY PRIOR node = parent 4 START WITH parent = 'Москва'
5 ;
WAY --------------------------------------------------- /Ленинград /Ленинград/Выборг /Новгород /Новгород/Ленинград /Новгород/Ленинград/Выборг
Запрос вверх по иерархии от узла 'Выборг': SQL> SELECT SYS_CONNECT_BY_PATH ( node, '/' ) way 2 FROM route 3 CONNECT BY node = PRIOR parent
4 START WITH node = 'Выборг'
5 ;
WAY --------------------------------------------------- /Выборг /Выборг/Ленинград /Выборг/Ленинград /Выборг/Ленинград/Новгород
Простой пример
Простой пример употребления фразы WITH для построения рекурсивного запроса: WITH numbers ( n ) AS ( SELECT 1 AS n FROM dual -- исходное множество -- одна строка UNION ALL -- символическое «объединение» строк SELECT n + 1 AS n -- рекурсия: добавок к предыдущему результату FROM numbers -- предыдущий результат в качестве источника данных WHERE n < 5 -- если не ограничить, будет бесконечная рекурсия ) SELECT n FROM numbers -- основной запрос ;
Операция UNION ALL здесь используется символически, в рамках определенного контекста, для указания способа рекурсивного накопления результата.
Ответ: N ---------- 1 2 3 4 5
Строка с n = 1 получена из опорного запроса, а остальные строки из рекурсивного. Из примера видна оборотная сторона рекурсивных формулировок: при неаккуратном планировании они допускают «бесконечное» выполнение (на деле – пока хватит ресурсов СУБД для сеанса или же пока администратор не прервет запрос или сеанс). С фразой CONNECT BY «бесконечное» выполнение в принципе невозможно. Программист обязан отнестись к построению рекурсивного запроса ответственно.
Еще один вывод из этого примера: подобно случаю с CONNECT BY, вынесенный рекурсивный подзапрос применим вовсе необязательно только к иерархически организованным данным.
Пример с дополнительным разъяснением способа выполнения: SQL> WITH 2 anchor1234 ( n ) AS ( -- обычный 3 SELECT 1 FROM dual UNION ALL 4 SELECT 2 FROM dual UNION ALL 5 SELECT 3 FROM dual UNION ALL 6 SELECT 4 FROM dual 7 ) 8 , numbers ( n ) AS ( -- рекурсивный 9 SELECT n FROM anchor1234
10 UNION ALL 11 SELECT n + 1 AS n 12 FROM numbers
13 WHERE n < 5 14 ) 15 SELECT n FROM numbers
16 ;
N ---------- 1 ←опорный запрос 2 ←опорный запрос 3 ←опорный запрос 4 ←опорный запрос 2 ←рекурсия 1 3 ←рекурсия 1 4 ←рекурсия 1 5 ←рекурсия 1 3 ←рекурсия 2 4 ←рекурсия 2 5 ←рекурсия 2 4 ←рекурсия 3 5 ←рекурсия 3 5 ←рекурсия 4
Реферат
Рассматривается использование рекурсии в вынесенном во фразу WITH подзапросе в Oracle, разрешенное с версии 11.2. Возможности такой рекурсии сопоставляются с возможностями фразы CONNECT BY.
Рекурсивные запросы в Oracle
Владимир Пржиялковский,
Преподаватель технологий Oracle
Июль 2010 г.
Ты то стоишь,
То начинаешь
Все сначала.
Путь учения
Не прост.
Мэйдзи, Путь
Шел Кондрат
В Ленинград,
А навстречу – двенадцать ребят.
К. Чуковский
Упорядочение результата
Для придания порядка строкам результата в запросах с CONNECT BY используется особая конструкция ORDER BY SIBLINGS. Аналогично в вынесенном рекурсивном запросе используется специальное указание SEARCH. В рамках последнего, в частности, задается вымышленое имя столбца, в котором СУБД автоматически проставит числовые значения, и который включит автоматически в порождаемый набор столбцов, допуская в последующей обработке его использование во фразе ORDER BY для осуществления упорядочения. Пример: ROLLBACK;
WITH stepbystep ( node, way, distance ) AS ( SELECT node, parent '-' node, distance FROM route WHERE parent = 'Москва' UNION ALL SELECT r.node , s.way '-' r.node , r.distance + s.distance FROM route r INNER JOIN stepbystep s ON ( s.node = r.parent ) ) SEARCH DEPTH FIRST BY node DESC SET orderval
SELECT way, distance, orderval
FROM stepbystep ORDER BY orderval DESC
/
Ответ:
WAY DISTANCE ORDERVAL ---------------------------------------- ---------- ---------- Москва-Ленинград-Выборг 831 5 Москва-Ленинград 696 4 Москва-Новгород-Ленинград-Выборг 852 3 Москва-Новгород-Ленинград 717 2 Москва-Новгород 538 1
Подробности и прочие свойства построений указания SEARCH приведены в документации по Oracle.
Рекурсивные запросы используются для обращения
Рекурсивные запросы используются для обращения к иерархически связаным данным. Этого требуется не всегда: так, запросы по дереву можно свести к простому просмотру таблицы. Однако такое возможно, когда узлы дерева размечены особыми служебными значениями, а это делается рекурсивной процедурой. Если данные изменяются регулярно, рекурсивную процедуру приходится перевычислять часто, и в таких случаях идут на обычное хранение, а к рекурсии приходится прибегать в запросе.
До версии 11.2 в Oracle задача рекурсивных запросов к иерархически организованым данным решалась с помощью фразы CONNECT BY. В то же время в стандарте SQL:1999 была введена фраза WITH для вынесения подзапросов («факторизация» запроса), одна из двух вариантов которой решала задачу рекурсивныз запросов более общим (и стандартным !) образом. В Oracle первый вариант фразы WITH (простое вынесение подзапроса из основного текста) был воплощен в версии 9, а второй, рекурсивный, хотя и с некоторыми вольностями, – в версии 11.2.
Ниже для примера заводится таблица с иерархически связанными данными, далее показывается для сравнения обращение к этим данным с помощью CONNECT BY, а после приведены разные возможности употребления к этим данным рекурсивной фразы WITH. Подготовка данных Для дальнейших примеров создадим таблицу. Выполним: CREATE TABLE route ( node VARCHAR2 ( 20 ) , parent VARCHAR2 ( 20 ) , distance NUMBER ( 5 ) ) ; INSERT INTO route VALUES ( 'Ленинград', 'Москва', 696 ); INSERT INTO route VALUES ( 'Новгород', 'Москва', 538 ); INSERT INTO route VALUES ( 'Ленинград', 'Новгород', 179 ); INSERT INTO route VALUES ( 'Выборг', 'Ленинград', 135 );
Обратите внимание, что создана не «таблица с расстояниями», а таблица с направленными маршрутами, предоставляющая расстояния в километрах между городами с точки зрения Москвы (здесь – единственная вершина иерархии). Такое представление данных и приводимые ниже запросы плохо подходят для решения более общей задачи поиска маршрута между двумя произвольными точками.
Аннотация
Репозитарий XML DB фактически представляет из себя древовидно-организованую файловую систему внутри БД, при том что элементами ее являются ресурсы: каталоги (папки) и файлы. В статье показано, как можно работать с репозитарием XML DB.
Что дает репозитарий XML DB и как с ним работать
,
Преподаватель технологий Oracle
| ... Зажгу свечу пред каждым сундуком, И все их отопру, и стану сам Средь них глядеть на блещущие груды. А. С. Пушкин. Скупой рыцарь |
Доступ к ресурсам средствами SQL
Две производные таблицы (из исходных, в схеме XDB) позволяют узнать информацию о ресурсах XML DB: PATH_VIEW и RESOURCE_VIEW:
SQL> DESCRIBE resource_view Name Null? Type ----------- -------- ----------------------------------------------- RES SYS.XMLTYPE(XMLSchema "http://xmlns.oracle.com/xdb/XDBResource.xsd" Element "Resource") ANY_PATH VARCHAR2(4000) RESID RAW(16)
SQL> DESCRIBE path_view Name Null? Type ----------- -------- ----------------------------------------------- PATH VARCHAR2(1024) RES SYS.XMLTYPE(XMLSchema "http://xmlns.oracle.com/xdb/XDBResource.xsd" Element "Resource") LINK SYS.XMLTYPE RESID RAW(16)
Обе таблицы хранят список ресурсов с путями доступа, однако благодаря возможности определять связки (links), подобно как в файловой системе, путей доступа к одному ресурсу может оказаться несколько; их-то все и покажет таблица PATH_VIEW, в отличие от RESOURCE_VIEW.
Другие возможности
Любой ресурс репозитария можно перевести в режим версионного доступа (version control resource, VCR). С этой целью, и с целью самого доступа, следует использовать особый пакет DBMS_XDB_VERSION.
Любой ресурс репозитария можно снабдить собственным описанием (метаданными; в дополнение к "системным" метаданным), добавив в его описание XML ("системное") свои элементы. Для этой цели можно использовать разные средства:
в PL/SQL - процедуры пакета DBMS_XDB: APPENDRESOURCEMETADATA, UPDATERESOURCEMETADATA, DELETERESOURCEMETADATA, PURGERESOURCEMETADATA;
в SQL - операции INSERT, UPDATE, DELETE применительно к полю RES (производной) таблицы RESOURCE_VIEW;
методом PROPPATCH протокола WebDAV.
Подробности имеются в документации по Oracle.
Примеры запросов.
Список ресурсов в репозитарии:
SELECT any_path FROM resource_view;
Описание первого попавшегося ресурса:
SELECT res FROM resource_view WHERE ROWNUM = 1;
Для просмотра дерева ресурсов, помимо обычных, существуют специальные функции:
UNDER_PATH EQUALS_PATH PATH DEPTH
Выдать описания ресурсов, имеющихся в папке /sys/acls:
SELECT res FROM resource_view WHERE UNDER_PATH ( res, '/sys/acls' ) = 1 ;
Выдать относительные имена ресурсов, имеющихся в папке /sys/acls (в данном случае это будут имена файлов), и их полные имена:
SELECT path ( 1 ), any_path FROM resource_view WHERE UNDER_PATH ( res, '/sys/acls', 1 ) = 1 ;
Описание ресурса-папки /sys/acls:
SELECT res FROM resource_view WHERE EQUALS_PATH ( res, '/sys/acls' ) = 1 ;
Полное описание ресурса-файла /sys/acls/all_all_acl.xml:
SELECT r.res.GETCLOBVAL ( ) FROM resource_view r WHERE EQUALS_PATH ( res, '/sys/acls/all_all_acl.xml' ) = 1 ;
С запросами последнего типа следует соблюдать осторожность, так как элемент Contents результирующего документа XML хранит для ресурса-файла его содержимое, а оно может оказаться очень объемистым ("большой файл").
Содержание ресурса-файла /sys/acls/all_all_acl.xml с содержимым в формате XML:
SELECT r.res.EXTRACT ( '//Contents' ) FROM resource_view r WHERE EQUALS_PATH ( res, '/sys/acls/all_all_acl.xml' ) = 1 ;
Другие типы файлов могут хранить описание содержимого в элементе /Resource/Contents/text или /Resource/Contents/binary.
Работа с ресурсами в программе
Заводить, изменять свойства и удалять ресурсы в репозитарии XML DB можно с помощью пакета PL/SQL DBMS_XDB.
Примеры:
CONNECT scott/tiger
DECLARE retb BOOLEAN; BEGIN retb := DBMS_XDB.CREATEFOLDER ( '/public/myfolder' );
retb := DBMS_XDB.CREATERESOURCE ( '/public/myfolder/file1.txt' , 'First line' CHR ( 10 ) 'Second line' );
retb := DBMS_XDB.CREATERESOURCE ( '/public/myfolder/file2.xml' , '<doc><line>First line</line><line>Second line</line></doc>' );
END; /
Проверка:
SQL> SELECT r.res.EXTRACT ( 'Resource/Contents' ) AS xml 2 FROM resource_view r 3 WHERE EQUALS_PATH ( res, '/public/myfolder/file2.xml' ) = 1;
XML -------------------------------------------------------------- <Contents xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd"> <doc xmlns=""> <line>First line</line> <line>Second line</line> </doc> </Contents>
SQL> SELECT r.res.EXTRACT ( 'Resource/Contents/text/text()' ) AS text 2 FROM resource_view r 3 WHERE EQUALS_PATH ( res, '/public/myfolder/file1.txt' ) = 1;
TEXT -------------------------------------------------------------- First line Second line
Обратите внимание, что XML DB по-разному хранит файл в зависимости от его расширения (упражнение: убедитесь, что XML DB интерпретирует содержимое именно по расширению файла, а не оттого, что внутри). Соответствие расширений файлов типам MIME устанавливается и выясняется в файле-ресурсе /xdbconfig.xml.
Создание связи (link):
BEGIN DBMS_XDB.LINK ( '/public/myfolder/file1.txt' , '/public' , 'myfolderfile1.txt'
); END; /
Проверка:
SQL> SELECT r.res.EXTRACT ( 'Resource/Contents/text/text()' ) AS text 2 FROM resource_view r 3 WHERE EQUALS_PATH ( res, '/public/myfolderfile1.txt' ) = 1 SQL> /
TEXT ---------------------------------------------------------------- First line Second line
SQL> SELECT 2 p.path AS path 3 , p.link.extract('/LINK/ChildName/text()') AS link 4 FROM path_view p 5* WHERE UNDER_PATH ( p.res, '/public' ) = 1 SQL> /
PATH LINK --------------------------------------------- --------------------- /public/myfolder myfolder /public/myfolderfile1.txt file1.txt /public/myfolder/file1.txt file1.txt /public/myfolder/file2.xml file2.xml
Обратите внимание на два пути доступа в репозитарии к одному и тому же файлу (ресурсу).
Удаление:
CALL DBMS_XDB.DELETERESOURCE ( '/public/myfolderfile1.txt' ); CALL DBMS_XDB.DELETERESOURCE ( '/public/myfolder/file1.txt' ); CALL DBMS_XDB.DELETERESOURCE ( '/public/myfolder/file2.xml' ); CALL DBMS_XDB.DELETERESOURCE ( '/public/myfolder' );
Упражнение. Проверьте реакцию XML DB на попытку удалить несуществующий файл или непустую папку.
В отличие от производных таблиц (view) словаря-стправочника в Oracle, производные таблицы RESOURCE_VIEW и PATH_VIEW обновляемы (на деле это "объектно-реляционные" таблицы). Это позвляет, например, удалить связь также и командой DELETE, или переместить существующий ресурс в другую папку обычной операцией UPDATE:
UPDATE path_view SET путь = новый_путь WHERE equals_path ( res, путь ) = 1 ;
Пример помещения в репозитарий файла ОС:
CONNECT / AS SYSDBA
CREATE DIRECTORY courses AS 'c:\crs';
GRANT READ ON DIRECTORY courses TO scott;
CONNECT scott/tiger
DECLARE retb BOOLEAN; BEGIN retb := DBMS_XDB.CREATERESOURCE ( '/public/OracleXML.doc' , BFILENAME ( 'COURSES', 'OracleXML.doc' ) ); END; /
Проверка:
SQL> SELECT res AS resource_description 2 FROM resource_view 3 WHERE EQUALS_PATH ( res, '/public/OracleXML.doc' ) = 1 4 ;
RESOURCE_DESCRIPTION ----------------------------------------------------------------- <Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd"> <CreationDate>2006-09-05T12:47:57.547000</CreationDate> <ModificationDate>2006-09-05T12:47:57.547000</ModificationDate> <DisplayName>OracleXML.doc</DisplayName> <Language>en-US</Language> <CharacterSet>WINDOWS-1251</CharacterSet> <ContentType>application/msword</ContentType> <RefCount>1</RefCount> </Resource>
Технически файлы репозитария размещаются служебных таблицах БД (документы XML) или в объектах LOB (файлы всех остальных типов). Использование формата MIME для хранения двоичных файлов не является самым экономным, что относится к издержкам метода.
Разграничение доступа
Репозитарий XML DB в БД Oracle использует собственную схему защиты доступа, access control list (ACL), созданную в рамках модели ACL для WebDAV (). Основными понятиями ACL являются:
Участник безопасности (principal). В XML DB это пользователь БД, роль БД или пользователь/роль справочника каталогов LDAP. Привилегия. Может быть атомарной (atomic; например read-contents, update или dav:lock) и составной (aggregate, состоящей из других привилегий; например all, dav:all или dav:read-acl). (Полный перечень имеющихся в XML DB привилегий имеется в документации). Access control entry (ACE). Запись о предоставлении или запрету привилегии участнику. Делается в тексте ACL.
В XML DB имеются несколько встроенных ACL, заданных следующими ресурсами:
>/sys/acls/all_all_acl.xml
/sys/acls/all_owner_acl.xml
/sys/acls/bootstrap_acl.xml
/sys/acls/ro_all_acl.xml
ACL файла /public/OracleXML.doc в программе можно узнать так:
SELECT r.res.EXTRACT ( 'Resource/ACL' ) AS text FROM resource_view r WHERE EQUALS_PATH ( res, '/public/OracleXML.doc' ) = 1;
Пример замены ACL ресурса:
BEGIN DBMS_XDB.SETACL ( '/public/OracleXML.doc' , '/sys/acls/all_all_acl.xml' ); END; /
Создадим в БД роль и создадим в XML DB соответствующий ей файл ACL:
CONNECT / AS SYSDBA
CREATE ROLE mygroup;
CONNECT xdb/xdb
DECLARE aclxml VARCHAR2 ( 4000 ) := ' <acl description="All privileges to MYGROUP, no to others" xmlns="http://xmlns.oracle.com/xdb/acl.xsd" xmlns:dav="DAV:" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd http://xmlns.oracle.com/xdb/acl.xsd" > <ace> <principal>MYGROUP</principal> <grant>true</grant> <privilege> <all/> </privilege> </ace> </acl> ' ; retb BOOLEAN;
BEGIN retb := DBMS_XDB.CREATERESOURCE ( '/sys/acls/all_mygroup_acl.xml', aclxml ) ; END; /
Защитим файл /public/OracleXML.doc созданым ACL:
CONNECT scott/tiger
BEGIN DBMS_XDB.SETACL ( '/public/OracleXML.doc' , '/sys/acls/all_mygroup_acl.xml' ); END; /
COMMIT;
Проверим видимость ресурса пользователем SCOTT. Для удобства создадим сначала файл с запросом:
SELECT any_path FROM resource_view WHERE UNDER_PATH ( res, '/public' ) = 1 . SAVE publicpaths
Проверка:
SQL> CONNECT scott/tiger Connected. SQL> @publicpaths
no rows selected
SQL> CONNECT / as sysdba Connected. SQL> GRANT mygroup TO scott;
Grant succeeded.
SQL> CONNECT scott/tiger Connected. SQL> @publicpaths
ANY_PATH ------------------------------------------ /public/OracleXML.doc
При включении пользователя в группу ранее невидимый ресурс стал виден.
Тип XDBURITYPE для работы с ресурсами
Для работы с ресурсами репозитария можно использовать системный подтип XDBURITYPE абстрактного типа URITYPE. В частности, методы типа XDBURITYPE позволяют извлекать из репозитария содержимое ресурсов.
Пример определения длины файла /public/OracleXML.doc:
SELECT DBMS_LOB.GETLENGTH ( XDBURITYPE ( '/public/OracleXML.doc' ).GETBLOB ( ) ) AS bytes FROM dual ;
Результат:
BYTES ---------- 504320
Таким же образом можно извлечь большой файл (содержимое, а не длину) в переменную программы.
Одно из применений типа XDBURITYPE - дать возможность ссылаться на данные в репозитарии XBM DB из полей обычных таблиц.
Пример:
CREATE TABLE projects AS SELECT 1 AS pid , XDBURITYPE ( '/public/OracleXML.doc' ) AS description FROM dual ;
SELECT DBMS_LOB.GETLENGTH ( p.description.GETBLOB ( ) ) AS bytes FROM projects p;
Получим:
BYTES ---------- 504320
Обратите внимание, что сослаться на файл в репозитарии из БД можно и через тип HTTPURITYPE, однако в этом случае в ссылке появится имя компьютера и номер порта - признаки, внешние по отношению в содержимому БД, неконтролируемые средствами БД и, в отличие от ссылок извне, по сути ненужные.
в рамках БД Oracle, дает
XML DB, созданная в рамках БД Oracle, дает возможность следующего:
работать с репозитарием; регистрировать схему XML для ее использования работе с данными XML в БД; создавать внутри СУБД сервлеты для доступа к БД через интернет.
Репозитарий XML DB, фактически является древовидно-организованой файловой системой внутри БД, элементами которой выступают ресурсы: каталоги (папки) и произвольные (не обязательно текстовые) файлы. Папки можно заводить и удалять, а файлы - заводить, удалять и извлекать.
Технически ресурсы репозитария XML DB суть документы XML. Элемент Contents каждого такого документа представляет содержание ресурса, а все остальные элементы являются метаданными, описывающими ресурс.
Средствами доступа к ресурсам, составляющим репозитарий, могут служить:
системные таблицы RESOURCE_VIEW и PATH_VIEW программы на PL/SQL (пакет DBMS_XDB) и на Java протоколы HTTP, HTTPS, FTP, WebDAV.
Здесь рассматриваются две первые категории средств, а последняя оставлена для самостоятельных упражнений.
Автоматизация задач
Автоматизировать выполнение задач с RMAN можно как внешними средствами (язык командной оболочки), так и внутренними. Внутренние средства RMAN допускают указание файла сценария при вызове этой программы, а также организацию хранимого сценария.
Пусть в файле listbackup.rcm находятся строки:
CONNECT TARGET /
LIST BACKUP;
EXIT
Тогда следующие два эквивалентные по результату обращения в ОС приведут ко входу в RMAN, выполнению этого сценария и выходу:
RMAN CMDFILE=listback.rcm NOCATALOG
RMAN @listback.rcm NOCATALOG
При использовании каталога RMAN возможно к тому же использование хранимого сценария:
RMAN> REPLACE SCRIPT reportobsolete { REPORT OBSOLETE; }
Пример обращения в хранимому в каталоге сценарию:
RMAN> RUN { EXECUTE SCRIPT reportobsolete; }
Горячее полное резервирование БД
Горячее резервирование
| - | может выполняться в состоянии СУБД OPEN |
| - | может выполняться только при включенном режиме архивирования журналов |
Если выполнено и то, и другое, сами действия по резервированию выглядят как обычно. Пример в синтаксисе версии 9.0:
RMAN> BACKUP DATABASE FORMAT
2> 'd:\oracle\oradata\teacher\rman-backup\rman_%d_%t_%U.bus';
Полное резервирование отдельных файлов табличного пространства
Пример в синтаксисе версии 9.0:
RMAN> BACKUP DATAFILE 1, 2;
или
RMAN> BACKUP FORMAT
2> 'd:\oracle\oradata\teacher\rman-backup\rman_%d_%t_%U.bus'
3> 'd:\oracle\oradata\teacher\system01.dbf’,
4> 'd:\oracle\oradata\teacher\users01.dbf’;
Полное резервирование табличного пространства
Пример в синтаксисе версии 9.0:
RMAN> BACKUP TABLESPACE system, users FORMAT
2> 'd:\oracle\oradata\teacher\rman-backup\rman_%d_%t_%U.bus';
Пример копирования и восстановления базы данных
Простейший пример снятия резервной копии (холодное копирование – вся БД – работа без каталога) иллюстрируется следующей последовательностью команд (здесь команда CONNECT TARGET соединяет RMAN с СУБД версии 8):
RMAN NOCATALOG
RMAN> CONNECT TARGET internal/oracle
RMAN> SHUTDOWN IMMEDIATE
RMAN> STARTUP MOUNT
RMAN> RUN {
2> ALLOCATE CHANNEL d1 TYPE DISK;
3> BACKUP FULL FORMAT 'd:\oracle\oradata\teacher\rman-backup\rman_%d_%U.bus'
4> DATABASE;
4> }
RMAN>
В каталоге D:\ORACLE\ORADATA\TEACHER\RMAN-BACKUP появился файл RMAN_ TEACHER _02DGA6F0_1_1.BUS (реальное имя может варьироваться). Теперь можно удалить файлы с табличными пространствами и выполнить восстановление:
RMAN> RUN {
2> ALLOCATE CHANNEL d1 TYPE DISK;
3> RESTORE DATABASE;
4> RECOVER DATABASE;
5> ALTER DATABASE OPEN;
6> }
База восстановлена и открыта.
Резервирование архивных копий журнала
Файлы архивных копий журнала резервируются всегда в отдельные от прочих файлы резервного набора и в общем случае их нужно резервировать отдельной командой. Пример в синтаксисе версии 9.0:
RMAN> BACKUP ARCHIVELOG ALL;
Пример того, как в версии 9.0 архивные файлы можно включить в состав резервного набора БД:
RMAN> BACKUP DATABASE FORMAT
2> 'd:\oracle\oradata\teacher\rman-backup\rman_%U.bus' PLUS ARCHIVELOG;
Резервирование и восстановление с помощью программы RMAN
,
координатор Евро-Азиатской Группы Пользователей Oracle,
преподаватель
Резервирование изменений (неполное резервирование)
Для резервирования изменений в Oracle используется традиционная многоуровневая модель с конкретным числом уровней копии 5 (от 0 до 4). Точкой отсчета для копирования изменений обязана стать снятая ранее полная копия БД уровня 0.
Пример резервирования блоков, изменившихся со времени резервирования на уровнях 3, 2, 1 и 0 (разностное, «дифференциального» резервирование) в синтаксисе версии 9:
RMAN> BACKUP INCREMENTAL LEVEL 3 DATABASE;
Пример резервирования блоков, изменившихся со времени последнего резервирования на уровнях 2, 1 и 0 (разностно-накопительное, «кумулятивное» резервирование) с пропуском табличных пространств, закрытых для записи (синтаксис версии 9):
RMAN> BACKUP INCREMENTAL LEVEL 3 CUMULATIVE DATABASE
2> SKIP READONLY;
Разностно-накопительное (кумулятивное) резервирование уровня N отличается от разностного (дифференциального) тем, что резервирует изменения произошедшие после выполнения резервирования всех уровней < N, в то время как просто разностное – изменения, произошедшие после резервирования уровней <= N.
Резервирование контрольного файла
Обычное резервирование контрольного файла приходится выполнять отдельно. Пример явного резервирования в синтаксисе версии 9.0:
RMAN> BACKUP CURRENT CONTROLFILE;
В версии 9 можно, однако, перевести RMAN в режим, когда копии контрольного файла будут сниматься автоматически при всякой выдаче команд BACKUP или COPY:
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
Резервирование оперативных файлов журнала
Оперативные (онлайновые) файлы журнала автоматически не резервируются. Для сохранения либо следует их
а) копировать отдельно, либо
б) перед полным резервированием БД отправлять в архив.
Резервирование временного табличного пространства
Если временное табличное пространство локально управляемо, оно автоматически не резервируется. Восстанавливать (воссоздавать) при необходимости его придется самостоятельно.
Удаление резервных копий
Выполняется командой DELETE. В простейшем варианте удаление устаревших копий может выглядеть так:
RMAN> DELETE OBSOLETE;
Обратите внимание, что RMAN удалил ненужные файлы резервных наборов. Вам не нужно автоматизировать удаление старых файлов, как раньше!
Файлы резервных наборов могут оказаться испорченными или поврежденными. Это можно отметить в справочнике (в контрольном файле или в каталоге RMAN) с помощью команды CROSSCHECK, в результате чего они будут помечены там как EXPIRED. Последующая команда DELETE EXPIRED удалит ставшие ненужными из-за этого файлы:
RMAN> CROSSCHECK BACKUP;
…
RMAN> DELETE EXPIRED BACKUP OF DATABASE;
…
RMAN> DELETE BACKUP OF DATABASE;
Более сложный пример удаления устаревших резервных копий:
RMAN> DELETE OBSOLETE RECOVERY WINDOW OF 14 DAYS;
В версии RMAN для версии
В версии RMAN для версии 9 описанное выше резервирование можно было бы выполнить так:
RMAN> BACKUP DATABASE FORMAT
2> 'd:\oracle\oradata\teacher\rman-backup\rman_%d_%U.bus';
а восстановление так:
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;
Здесь подразумевается использование неявного канала по умолчанию, так что объявлять его стало необязательно.
Кроме этого в версии 9 появилась команда CONFIGURE, с помощью которой (помимо прочего) можно связать с каналом направление и маску имени файлов для резервного набора:
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT
2> 'd:\oracle\oradata\teacher\rman-backup\rman_%d_%U.bus';
В этом случае команда снятия резервной копии может выглядеть еще проще:
RMAN> BACKUP DATABASE;
Восстановление данных
| - | Для восстановления данных целевая БД должна находиться в состоянии NOMOUNT/ MOUNT/ OPEN в зависимости от характера восстановления, например | |
| - | NOMOUNT: для восстановления контрольных файлов БД (фактически – СУБД) | |
| - | MOUNT: для восстановления БД целиком или табличного пространства SYSTEM | |
| - | OPEN: для восстановление табличных пространств, помимо SYSTEM (в этом случае перед процедурой восстановления само табличное пространство потребуется перевести в состояние OFFLINE). | |
| - | Восстановление файлов (с данными и служебных) выполняется в RMAN командой RESTORE. | |
| - | Восстановление данных выполняется либо в RMAN, либо в SQL*Plus командами RECOVER при условии наличия восстановленных файлов. |
Восстановление до момента сбоя («последнего момента»)
Некоторые примеры восстановления:
RMAN> RECOVER DATABASE;
RMAN> RECOVER TABLESPACE users;
RMAN> RECOVER DATAFILE 'd:\oracle\oradata\teacher\users01.dbf’;
RMAN> RESTORE CONTROLFILE;
RMAN> RUN {
2> SET ARCHIVELOG DESTINATION TO ‘d:\oracle\oradata\archive’;
3> RESTORE ARCHIVELOG ALL; }
Восстановление пространств, закрытых на запись:
RMAN> SQL "ALTER TABLESPACE lookup_data OFFLINE";
RMAN> RECOVER TABLESPACE lookup_data;
RMAN> SQL "ALTER TABLESPACE lookup_data ONLINE";
Восстановление до указанного момента в прошлом
БД, работающую в режиме архивирования журнала, можно восстанавливать до определенного указанного момента с помощью фраз UNTIL {TIME … | SCN … | SEQUENCE … THREAD…}. Пример:
RMAN> RESTORE DATABASE; # восстановили файлы
RMAN> RECOVER DATABASE UNTIL SCN 375831; # восстановили БД
RMAN> ALTER DATABASE OPEN RESETLOGS; # сбросили журнал
Восстановление БД (вторая и третья строчки выше) можно выполнить и в SQL*Plus:
SQL > RECOVER DATABASE UNTIL CANCEL;
SQL> ALTER DATABASE OPEN RESETLOGS;
При таком восстановлении необходимо сбросить онлайновый журнал. После этого, как и при традиционном восстановлении со сбросом журналов (RESETLOGS), необходимо снять полную копию БД, так как с этого момента восстановление с более ранних резервных копий станет невозможным из-за того, что история журнальных записей прерывается.
Возможности RMAN
Возможности RMAN включают следующее:
| - | выполнение полного резервирования и резервирования изменений |
| - | выполнение холодного/горячего резервирования, причем во втором случае табличные пространства не переводятся в режим backup, что позволяет избежать дополнительной нагрузки на журнал |
| - | обнаружение поврежденных блоков |
| - | параллельное выполнения операций ввода/вывода |
| - | автоматическое протоколирование операций копирования и восстановления |
Уровни выполнения резервного копирования/восстановления с помощью RMAN:
база данных
табличные пространства
файлы табличных пространств
служебные файлы БД (контрольные, архивные)
Основные понятия
В число основных понятий RMAN входят следующие:
| - | Канал (channel). Серверный процесс, возникающий при установлении связи с устройством ввода/вывода (диск или магнитная лента) для записи или чтения файлов резервирования |
| - | Целевая БД (target database). БД, для которой снимается резервная копия, или которая восстанавливается по ранее снятой копии |
| - | Каталог (recovery catalog). Отдельная схема в БД (чаще в отдельной БД), которую можно заводить для хранения служебная информации о целевых базах, снятых копиях и процедурах восстановления. Альтернативой каталогу является индивидуальная работа с каждой целевой БД, когда служебная информация помещается в контрольный файл этой БД. |
| - | Копия (RMAN backup). Резервная копия какого-нибудь элемента БД, получаемая командой RMAN backup. |
| - | Резервный набор (backup set). Логически именует набор файлов, сформированных во время резервного копирования. |
| - | Резервный файл (backup piece). Двоичный файл с резервной информацией. |
Синтаксис командного языка RMAN в версии 9 имеет определенные отличия от версии 8, но все основные конструкции сохранены. Кроме этого, в RMAN для версии 9 допускается целый ряд упрощений записи команд.
Возможность работы с RMAN включена также в последние версии OEM без необходимости знания командного языка.
В тексте ниже для лаконичности предпочтение будет отдаваться синтаксису версии 9. Кроме этого для простоты рассматривается работа без каталога RMAN.
СУБД Oracle как единое
Программа RMAN появилась в версии 8 СУБД Oracle как единое для всех платформ средство организации резервного копирования и восстановления данных на физическом уровне. По отношению к традиционным базовым возможностям резервирования и восстановления в Oracle, у программы RMAN есть некоторые преимущества, делающие ее в некоторых ситуациях (например, при больших объемах данных) практически незаменимой. К сожалению, наличие этих преимуществ не лишает RMAN и ряда существенных недостатков: собственной системы понятий, собственного командного языка и интерфейса общения с администратором. И то, и другое, и третье выполнено в плохих традициях разработчиков Oracle – не вполне логично, запутано и непоследовательно, – что затрудняет освоение этой программы. Назначение этой статьи – помочь перешагнуть через эти недостатки ради выгод, которые можно извлечь из RMAN.
Выдача справочной информации
Выполняется специальными командами LIST и REPORT, а также разновидностью команды RESTORE. Примеры приводятся ниже.
Выдача подробного списка всех снятых копий:
RMAN> LIST BACKUP;
Выдача списка резервных наборов, содержащих табличное пространство SYSTEM:
RMAN> LIST BACKUP OF TABLESPACE system;
Вариант выдачи того же самого, но в обобщенном виде (версия 9):
RMAN> LIST BACKUP OF TABLESPACE system SUMMARY;
Выдача информации о копиях, снятых с архивов журналов:
RMAN> LIST BACKUP OF ARCHIVELOG ALL;
Выдача резервных копий, оказавшихся устаревшими:
RMAN> REPORT OBSOLETE;
Выдача файлов с данными БД, для восстановления которых потребуются архивы журналов 2-х дневной давности и более:
RMAN> REPORT NEED BACKUP DAYS 2 DATABASE;
Те же сведения, но только для пространства SYSTEM:
RMAN> REPORT NEED BACKUP DAYS 2 TABLESPACE system;
Выдача информации о том, годны ли файлы резервного набора для восстановления:
RMAN> RUN {ALLOCATE CHANNEL d1 TYPE DISK;
2> RESTORE DATABASE VALIDATE; }
