Лекция 12: Служебные виды объектов. Работа с редакциями объектов

Вспомогательные виды хранимых объектов

Основными инструментами моделирования предметной области в БД является в Oracle таблица данных и представление данных. Однако в любой настоящей БД под управлением Oracle всегда имеется определенное количество хранимых объектов других видов. Это вспомогательные, служебные объекты, призванные предоставить потребителю БД определенное удобство ее использования и определенную свободу действий. Основные категории таких объектов, формально хранимых в БД, но для описания предметной области бесполезных, описываются ниже.

Генератор последовательности из чисел

В этом царстве люди нарождались и неведомо куды девались.

Сказки и легенды Пушкинских мест

В первых описаниях реляционной модели было замечено, что требуемый для отношений в этой модели ключ не всегда легко задать. Тогда же предложили для решения проблемы заводить в отношениях "искусственный" (artificial) ключ, то есть служащий исключительно цели сообщения отношению необходимого правила ключа и не имеющий никакого содержательного (моделирующего предметную область) смысла. В жизни для этого обычно заводят ключ из одного атрибута числового типа, но так делается ради удобства: в отдельных случаях может оказаться целесообразным употребить несколько столбцов, равно как и прочие типы.

SQL не требует в таблицах первичного ключа (и вообще никакого), однако допускает его существование; для таблиц с первичным ключом сказанное переносится в SQL.

Создание и использование генератора

Когда разработчик решает завести в таблице искусственный первичный ключ, перед ним встает техническая задача: обеспечить заполнение столбцов ключа уникальными значениями. Иногда можно найти простой выход из положения, например, для одностолбцового ключа типа DATE можно брать значения из SYSDATE. Пригодность такого решения определяется конкретикой использования таблицы в приложении. Но оно не подойдет для всех таблиц и для числового столбца, наиболее популярного в роли первичного ключа. Некоторые типы СУБД для одностолбцового числового ключа вводят особый "автоинкрементный" тип данных. Oracle же, и отчасти IBM, предлагают брать в таких случаях значения из специального объекта хранения — датчика чисел (sequence; полное название — sequence generator, по-русски породителя, или генератора, последовательности чисел). Оба решения в разное время post factum попали в стандарт SQL, так что не исключено появления автоинкрементного столбца в будущих версиях Oracle, причем с теми же свойствами, что у нынешнего самостоятельного генератора (см. ниже).

Примеры создания генератора последовательности чисел и запросов к нему на выдачу очередного (NEXTVAL) и текущего (CURRVAL) значений:

CREATE SEQUENCE proj_numbers;
INSERT INTO proj ( projno, pname )
VALUES ( proj_numbers.NEXTVAL, 'DELTA' );
UPDATE proj SET projno = proj_numbers.NEXTVAL WHERE projno = 16;
SELECT proj_numbers.CURRVAL FROM dual;

Замечания

• Порождаемые числа уникальны в рамках БД в целом и отдельных сеансов в частности.
• С точки зрения БД генератор последовательности — хранимый объект (подобно таблице) и может использоваться разными сеансами по мере надобности. По этой причине получаемая отдельным сеансом последовательность чисел не обязана быть плотной и может содержать разрывы.
• CURRVAL выдает значение в рамках сеанса, доступное только после предшествующей выдачи NEXTVAL. Фактически это последнее значение NEXTVAL, полученное в конкретном сеансе (но не вообще от генератора).
• Последовательность чисел порождается СУБД безотносительно к открытию и завершению транзакций.

Более сложный пример определения генератора:

CREATE SEQUENCE dept_numbers
MINVALUE 0 MAXVALUE 2000 -- минимальное и максимальное допустимые значения
START WITH 1000          -- первое выдаваемое число
INCREMENT BY -10         -- шаг изменения чисел в последовательности
CYCLE                    -- дойдя до границы, переключиться на противоположную
CACHE 20                 -- способ ускорить выдачу при особо частых обращениях
;

Свойство CYCLE способно привести через определенное время к повторениям значений и фактически отменит основное качество такого генератора. По умолчанию действует свойство NOCYCLE.

Удаление:

DROP SEQUENCE dept_numbers;

Использование генератора в выражениях SQL вовсе не обязательно требует дополнительного программирования. Примеры применения генератора чисел в множественных операциях DML:

CREATE SEQUENCE seq;
CREATE TABLE emps AS SELECT 0 id, ename FROM emp;
UPDATE emps SET id = seq.NEXTVAL;
CREATE TABLE empss AS SELECT seq.NEXTVAL id, ename FROM emp;

Изменение свойств генератора

Большую часть свойств генератора можно изменять командой ALTER SEQUENCE. Например:

ALTER SEQUENCE proj_numbers NOCACHE MINVALUE -1000 NOMAXVALUE;

Однако изменить таким же путем текущее значение нельзя. В то же время это иногда требуется, например после импорта данных. В таких случаях можно сделать следующее.

1. Во-первых, можно удалить генератор и воссоздать его заново с требуемым текущим значением. При этом есть шанс ошибиться с правильным воспроизведением прочих свойств.
2. Во-вторых, в качестве искусственной меры можно временно изменить шаг приращения на подходящую величину, обратиться к NEXTVAL, добившись нужного текущего значения, и вернуть приращение назад.
3. В-третьих, пользователь SYS способен внести желаемую величину непосредственно в основную таблицу словаря-справочника SEQ$. Это можно рекомендовать в последнюю очередь.

Существующие значения свойств генератора можно взять из таблицы словаря-справочника USER_SEQUENCES. Например, последнее значение можно получить так:

SELECT increment_by FROM user_sequences WHERE sequence_name = 'SEQ';

Вычтем эту величину из целевой; результат укажем в команде ALTER SEQUENCE seq INCREMENT BY …; сделаем запрос к seq.NEXTVAL и вернем начальное значение приращения командой ALTER SEQUENCE.

Этот вариант замены текущего значения следует признать наиболее безопасным. Он легко автоматизируется, однако, применяя его, нужно обеспечить отсутствие обращений к генератору со стороны посторонних сеансов в промежутках между совершаемыми операциями.

Каталог операционной системы

Объект вида "каталог" (directory; если точнее — то "указатель" на группу файлов-"документов") используется для регулирования доступа СУБД к файлам в каталогах файловой системы ОС.

Пример создания или изменения:

CREATE OR REPLACE DIRECTORY extfiles_dir AS 'c:\crs';

Примеры употребления:

SELECT
  DBMS_LOB.GETLENGTH ( BFILENAME ( 'EXTFILES_DIR', 'sql.pdf' ) ) 
  AS "Bytes in the file:"
FROM dual
;
ALTER TABLE proj ADD ( description BFILE );
INSERT INTO proj ( projno, pname, description )
VALUES (
  3000
, 'YOTA'
, BFILENAME ( 'EXTFILES_DIR', 'sql.pdf' )
);
SELECT pname, DBMS_LOB.GETLENGTH ( description ) FROM proj;

Объект вида "каталог" отличается от большинства объектов прочих видов тем, что является "внесхемным", наподобие некоторых других объектов, таких как создаваемые с уточнением PUBLIC (другой пример: PUBLIC SYNONYM). Технически это оформляется так: эти объекты всегда принадлежат пользователю SYS, кем бы они не создавались, причем создающий их фактически пользователь должен иметь системную привилегию CREATE ANY DIRECTORY (о привилегиях см. ниже). Но, в отличие от объектов PUBLIC некоторых других категорий, объекты DIRECTORY не доступны пользователям БД автоматически, и их доступность регулируется объектными привилегиями READ и WRITE. Так, пример выше проработает, если вместо команды CREATE ... extfiles_dir ... (как выше) выдать

CONNECT / AS SYSDBA
CREATE OR REPLACE DIRECTORY extfiles_dir AS 'c:\crs';
GRANT READ ON DIRECTORY extfiles_dir TO scott;
CONNECT scott/tiger

… и уже далее — код "примера употребления".

Связь с другой БД

Связь с другой БД (database link) позволяет пользователю, подсоединенному к одной БД, обращаться к объектам из другой БД, организуя тем самым распределенные вычисления в базах данных.

Пример (в предположении, что ORCL — "имя службы БД", заданное средствами Oracle Net, чаще всего просто имя другой БД):

CREATE DATABASE LINK anotherdb 
   CONNECT TO scott
   IDENTIFIED BY tiger
   USING 'orcl'
;
SELECT ename, dname 
FROM   emp e, dept@anotherdb d 
WHERE  e.deptno = d.deptno
;

Как и большинство хранимых объектов, ссылки на БД принадлежат конкретным схемам. Однако ссылки, создаваемые с указанием PUBLIC, доступны для использования всеми пользователями, так как определяются вне схем, на уровне базы данных:

CREATE PUBLIC DATABASE LINK anotherdb;

Эта одновременно и пример недоопределенной ссылки, которая может быть уточнена в отдельных схемах, возможно, в каждой схеме своим собственным указанием имени пользователя и пароля.

Замечания

1. Чтобы команда CREATE [PUBLIC] DATABASE LINK выше проработала, пользователь, выдающий ее, должен иметь полномочие (привилегию) CREATE DATABASE LINK или же CREATE PUBLIC DATABASE LINK.
2. Чтобы предложение SELECT выше проработало, нужно средствами Oracle Net обеспечить сетевое имя ORCL для удаленной БД (в общем случае оно не обязано совпадать с именем с базы).
3. Oracle будет неправильно обрабатывать публичные ссылки с совпадающей локальной частью в имени, например A и A.B. То есть прежде чем создавать ссылку A, проверьте, не существуют ли уже ссылки вида A.B, и наоборот.

Есть ограничение на число возможных открытых ссылок на другую БД в пределах сеанса. Оно задается статичным параметром СУБД OPEN_LINKS, умолчательное значение которого равно 4.

Подпрограммы

Хранимыми программными единицами в Oracle являются процедуры и функции (общее название — подпрограммы), триггерные процедуры, пакеты, типы данных (учитывая программную логику их методов). Это объекты хранения в БД типов PROCEDURE, FUNCTION, TRIGGER, PACKAGE/PACKAGE BODY, TYPE/TYPE BODY.

Для обращения к подпрограммам (самостоятельным или в составе пакета) средствами SQL c версии 9 Oracle используется специальный оператор CALL (заимствован из ANSI SQL):

SQL> SET SERVEROUTPUT ON
SQL> CALL DBMS_OUTPUT.PUT_LINE ( 'This is a procedure call' );
Пример обращения оператором CALL к функции:
SQL> VARIABLE s NUMBER
SQL> CALL sys.standard.sin ( 1 ) INTO :s;
Call completed.
SQL> PRINT s
         S
----------
.841470985

В SQL*Plus первый пример даст тот же результат, что и

SQL> EXECUTE DBMS_OUTPUT.PUT_LINE ( 'This is a procedure call' )

Это равносильно выдаче

SQL> BEGIN DBMS_OUTPUT.PUT_LINE ( 'This is a procedure call' ); END;
  2  /

Последняя форма более универсальна, так как она допустима в программах на включающих языках. В то же время, если дело идет только об обращении к процедуре, оператор CALL несколько более производителен.

Второй пример, с обращением к SIN, в SQL*Plus можно переиначить так:

SQL> EXECUTE SELECT sin ( 1 ) INTO :s FROM dual
PL/SQL procedure successfully completed.
SQL> PRINT s
         S
----------
.841470985

или сразу (но уже не специфично для SQL*Plus):

SQL> SELECT sin ( 1 ) FROM dual;
    SIN(1)
----------
.841470985

Поцедуры, в отличие от функций, не могут употребляться в составе выражений в операторах DML. Создание процедур, функций, пакетов, а также триггерных процедур и типов (в полном объеме) относится к теме программирования Oracle с помощью PL/SQL.