Введение в Oracle SQL

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

Разбить на страницы
Показывать лекцию целиком

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

Основными инструментами моделирования предметной области в БД является в 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;

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

  • Во-первых, можно удалить генератор и воссоздать его заново с требуемым текущим значением. При этом есть шанс ошибиться с правильным воспроизведением прочих свойств.
  • Во-вторых, в качестве искусственной меры можно временно изменить шаг приращения на подходящую величину, обратиться к NEXTVAL, добившись нужного текущего значения, и вернуть приращение назад.
  • В-третьих, пользователь 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;

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

    Замечания

  • Чтобы команда CREATE [PUBLIC] DATABASE LINK выше проработала, пользователь, выдающий ее, должен иметь полномочие (привилегию) CREATE DATABASE LINK или же CREATE PUBLIC DATABASE LINK.
  • Чтобы предложение SELECT выше проработало, нужно средствами Oracle Net обеспечить сетевое имя ORCL для удаленной БД (в общем случае оно не обязано совпадать с именем с базы).
  • 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.

    Индексы

    Индексы в БД в Oracle

    Индексы (от латинского "указатель", обращающий внимание на запрещенные католической церковью книги или их фрагменты) являются хранимыми вспомогательными объектами, используемыми при отработке запросов на SQL. В базах данных смысл индексов — воплотить функцию, позволяющую по значению (или набору значений) получить адреса строк таблицы с этими значениями. В промышленных БД всех типов подобные функции обеспечиваются с помощью индекса как специальной хранимой структуры, хотя умозрительно требования в такой структуре нет, а нужна лишь функциональность.

    Наиболее употребимы в Oracle B*-древовидные индексы. Они могут создаваться:

  • автоматически, СУБД — как средство проверки ограничений целостности "первичный ключ" и "уникальность" в таблицах,
  • либо вручную, разработчиком — ради ускорения доступа к строкам таблицы.

    • Во втором случае ("вручную") для создания индексов используется специальная команда SQL. Примеры:

    CREATE INDEX emp_idx ON emp ( ename );
CREATE UNIQUE INDEX name_loc_idx ON dept_copy ( dname, loc );

    На выбор столбцов для древовидного индекса есть ограничения.

  • Разрешено создавать индекс не более чем на 32 столбца.
  • Нельзя индексировать столбцы некоторых типов (например, семейства LOB или же LONG/LONG RAW).
    •

    Влияние индексов на эффективность работы с БД противоречиво. Индексы:

  • способны сокращать время обращения к строке таблицы;
  • способны увеличивать время обращения к строке таблицы (при "неудачном" распределении индексированных строк по блокам);
  • требуют места в БД;
  • способны замедлять обновления таблиц.
    •

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

    Некоторые общие и простые соображения по поводу использования древовидных индексов:

  • Индекс неэффективен при малом количестве различных индексированых значений (например, пол: "М" и "Ж"), когда они представлены примерно равными количествами.
  • При отсутствии значений (NULL) сразу во всех индексируемых столбцах (если индекс построен по нескольким столбцам) строка не индексируется. Поиск "по отсутствующим значениям" будет игнорировать индекс и выполняться полным просмотром таблицы.
    •

    Второй по важности тип индекса появился в версии Oracle 8.1 и существует для Enterprise Edition. Это поразрядный (bitmap) индекс. Он используется исключительно для ускорения доступа к данным таблицы и дает отдачу во вполне определенных обстоятельствах.

    Доменный индекс (иначе — прикладной, предметный) программируется разработчиком приложения для конкретного типа объектов, однако несколько видов доменных индексов приходит в готовом виде с ПО Oracle, будучи уже запрограммированными разработчиками СУБД.

    Для всех видов индексов допускаются частные случаи конфигурации.

    Индексы для проверки заявляемых ограничений целостности

    Употребление индексов ради ускорения доступа составляет предмет оптимизации запросов и не является темой настоящего материала. Здесь приводятся некоторые сведения об индексах, используемых СУБД для технической поддержки ограничений целостности.

    При обычном объявлении в таблице первичного ключа или свойства уникальности столбцов СУБД автоматически создаст служебный уникальный древовидный индекс. В случае многостолбцовой уникальности допускается задать несколько ограничений на одних и тех же столбцах, но обязательно перечисляемых в разном порядке. Для всех таких ограничений будет использоваться один и тот же индекс — соответствующий первому по порядку создания ограничению. В результате следующих действий два "разных" ограничения AB и BA будут внутренне проверяться одним и тем же индексом AB:

    CREATE TABLE t ( a NUMBER, b NUMBER, c NUMBER );
ALTER TABLE t ADD CONSTRAINT ab UNIQUE ( a, b );
ALTER TABLE t ADD CONSTRAINT ba UNIQUE ( b, a );

    В автоматику создания служебного индекса можно вмешаться. Так, желаемые свойства автоматически создаваемому индексу можно сообщить, вложив в предложение CREATE TABLE или ALTER TABLE … ADD ограничение (где формулируется ограничение целостности) конструкцию CREATE INDEX, например:

    CREATE TABLE t ( 
  c NUMBER PRIMARY KEY USING INDEX ( CREATE INDEX pk_t ON t ( c ) )
, d VARCHAR2 ( 100 ) 
);

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

    Если на необходимые столбцы индекс был заведен ранее, в качестве служебного можно взять уже имеющийся:

    CREATE TABLE t ( c NUMBER );
CREATE INDEX pk_t ON t ( c );
ALTER TABLE t ADD PRIMARY KEY ( c ) USING INDEX pk_t;

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

    В последнем предложении конструкцию USING INDEX можно было бы не употреблять. Однако же если бы индекса PK_T заранее не существовало, эту же конструкцию можно было использовать для заведения индекса с желаемыми характеристиками, применив следующую формулировку:

    ... USING INDEX [имя_индекса] [свойства_индекса] ...

    или даже:

    ... USING INDEX ( CREATE INDEX имя_индекса [свойства_индекса] ) ...

    Обратите внимание, что индекс в этом случае не обязан быть уникальным. (Упражнение. Проверьте свойство уникальности у индекса PK_T). Более того, если ограничение создается как DEFERRABLE, индекс обязан быть неуникальным, и именно таковым он при том создается СУБД автоматически.

    Если при заведения ограничения используется существующий индекс, явно создаваемый или же создаваемый ради ограничения с возможностью отложенной проверки (DEFERRABLE), то в отличие от автоматического он не будет удаляться вместе с удалением ограничения. (Упражнение. Проверьте это.) В этом есть своя логика, но это же может приводить к недоразумениям, когда по удалению ограничения целостности в БД сохранится "остаточный" индекс, не всегда нужный по делу.

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

    CREATE TABLE tr ( d NUMBER REFERENCING t ( c ) );
CREATE INDEX fk_t ON tr ( d );
DROP INDEX fk_t;

    Упражнение. Проверьте, что создание индекса на внешний ключ не оказывает влияния на логику поведения последнего.

    Решение о создании индекса на столбцы внешнего ключа принимается исходя из конкретных обстоятельств.

    Таблицы с временным хранением строк

    Отличаются от обычных таблиц БД тем, что время хранения строк в них ограничено концом либо транзакции, либо сеанса связи с СУБД — по выбору разработчика БД. Описания же таких таблиц (метаданные) хранятся в словаре-справочнике БД на общих основаниях с описаниями обычных таблиц, то есть вплоть до выдачи команды DROP TABLE. Эти свойства объясняют выбор фирмой Oracle названия: GLOBAL TEMPORARY в отличие от таблиц LOCAL TEMPORARY, имеющихся со времен SQL-92 (но не в Oracle), полный жизненный цикл которых ограничен программным блоком.

    Пример создания таблицы с временем хранения строк, ограниченным транзакцией:

    CREATE GLOBAL TEMPORARY TABLE temp AS SELECT * FROM emp WHERE 1 = 2;
-- строк нет
INSERT INTO temp SELECT * FROM emp;
-- строки появились
SELECT * FROM temp;
-- проверка
COMMIT;
-- строки пропали
SELECT * FROM temp;
-- проверка

    Упражнение. Как изменится результат команды CREATE выше, если в формулировке SELECT опустить фразу WHERE?

    Примеры создания таблиц с явно указанными временами хранения строк — до конца текущего сеанса или же до конца текущей транзакции:

    CREATE GLOBAL TEMPORARY TABLE tx ( c NUMBER ) ON COMMIT PRESERVE ROWS;
CREATE GLOBAL TEMPORARY TABLE ts ( c NUMBER ) ON COMMIT DELETE ROWS;

    ON COMMIT DELETE ROWS не требует явного указания, так как подразумевается по умолчанию.

    Если не считать "короткого" времени жизни строк, по своим потребительским свойствам таблицы с временным хранением строк почти не отличаются от обычных. Например, для них можно строить индекс (напомним: ведь их описание хранится постоянно).

    Таблицы обоих видов предоставляют каждому сеансу собственное множество строк, независимое от строк, заведенных в других сеансах (для таблиц, где время хранения строк ограничено сеансом, это неочевидно). Однако выполнение операций DDL с такими таблицами СУБД по понятным причинам увязывает с наличием в них строк (собственных) в других сеансах. Так, построить индекс (CREATE INDEX) удастся только, если в данный момент другой сеанс не завел в таблице собственные строки. Таким образом, косвенная связь содержимого таких таблиц в разных сеансах все-таки имеется.

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

    Таблицы с внешним хранением данных

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

  • ORACLE_LOADER: способен отображать текстовые данные из файла ОС в таблицу;
  • ORACLE_DATAPUMP[10-): допускает выгрузку данных БД в двоичный файл ОС и последующее многократное прочитывание их в виде таблицы.
    •

    [10-) начиная с версии 10

    Далее приводится пример одностороннего отображения. Подразумевается, что действия выполняются на сервере (это используется ниже только во время правки содержимого файла с помощью команд HOST в SQL*Plus и echo командной оболочки ОС).

    Предположим наличие каталога EXTFILES_DIR в БД, доступного для чтения (READ). Создадим файл employee.txt с исходными данными:

    Bush,13/03/2001,5000

    Powell,14/03/2001,,400.50

    Обратите внимание, что во второй строке применено английское форматирование для записи числа. Оно предполагает языковую ориентировку СУБД игнорируемых ошибок формата, могущих возникать в процессе работы на местности, где это принято, например, AMERICAN/AMERICA.

    Заведем в схеме SCOTT таблицу с внешним хранением:

    CREATE TABLE emp_load
  ( ename    VARCHAR2 ( 10 )
  , hiredate DATE
  , sal      NUMBER ( 7, 2 )
  , comm     NUMBER ( 7, 2 )
  )
ORGANIZATION EXTERNAL
 ( TYPE ORACLE_LOADER
   DEFAULT DIRECTORY extfiles_dir
   ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE 
      NOBADFILE
      NOLOGFILE
      FIELDS TERMINATED BY ','
      MISSING FIELD VALUES ARE NULL
       ( ename
       , hiredate CHAR DATE_FORMAT DATE MASK "dd/mm/yyyy"
       , sal
       , comm 
       )
    )
   LOCATION ( 'employee.txt' )
 )
;

    Проверка в SQL*Plus:

    SQL> SELECT * FROM emp_load;
ENAME      HIREDATE         SAL       COMM
---------- --------- ---------- ----------
Bush       13-MAR-01       5000
Powell     14-MAR-01                 400.5
SQL> HOST echo Hussein,,1000.44,20000 >> employee.txt
SQL> /
ENAME      HIREDATE         SAL       COMM
---------- --------- ---------- ----------
Bush       13-MAR-01       5000
Powell     14-MAR-01                 400.5
Hussein                 1000.44      20000
SQL> SELECT SUM ( sal ) FROM emp_load;
  SUM(SAL)
----------
   6000.44

    Во втором запросе результат вычисляется по ходу прочтения файла employee.txt, то есть не требуется полной загрузки данных этого файла в БД для вычисления результата.

    Конструкция LOCATION в определении таблицы допускает задание списка файлов или же переопределение файлов-источников, например:

    SQL> HOST echo Laden,13/03/2005,10000 >> centralasia.txt
SQL> ALTER TABLE emp_load LOCATION ('employee.txt', 'centralasia.txt');
Table altered.
SQL> SELECT * FROM emp_load;
ENAME      HIREDATE         SAL       COMM
---------- --------- ---------- ----------
Bush       13-MAR-01       5000           
Powell     14-MAR-01                 500.5
Hussein                 1000.44      20000
Laden      13-MAR-05      10000

    Это позволяет переориентировать таблицу EMP_LOAD на другие файлы по мере подготовки их внешними программами приложения. Переопределять разрешено и DEFAULT DIRECTORY.

    Еще пример свойств. Указания NOBADFILE и NOLOGFILE можно заменить на противоположные (и подразумеваемые молчаливо), в результате чего в каталоге начнут появляться протокольные файлы доступа к данным из БД. Это, однако, потребует дополнительно привилегии WRITE для SCOTT на использование каталога EXTFILES_DIR (для предыдущих действий хватало привилегии READ). Указание REJECT LIMIT сообщит предельное количество игнорируемых нарушений формата в записях из файлов-источников, обнаруживаемых в процессе выполнения SELECT:

    ALTER TABLE emp_load ACCESS PARAMETERS ( 
   RECORDS DELIMITED BY NEWLINE
   BADFILE 
   LOGGING 
);
ALTER TABLE emp_load REJECT LIMIT 20;

    Пока нарушений формата менее 21, ошибку доступа СУБД порождать не будет, а только будет пополнять записями о нарушениях протокольный файл.

    Таблицу с внешним хранением можно использовать для обновления данных наряду с обычными:

    MERGE INTO bonus b USING emp_load e 
ON ( b.ename = e.ename )
WHEN MATCHED THEN UPDATE SET sal = sal * 10
;

    Некоторые общие свойства объектов хранения разных видов

    Формально в Oracle имеется несколько десятков разных видов хранимых в БД объектов. Некоторое представление о многообразии дает запрос:

    SELECT DISTINCT object_type FROM all_objects;

    (Не все из них управляются командами SQL CREATE/ALTER/DROP, значительная часть — процедурно.)

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

    Пространства имен для объектов в Oracle

    Для именования объектов хранения Oracle разных видов используются различные пространства имен. Распределение по пространствам имен для наиболее популярных типов поясняется таблицей.

    Отдельное общее пространство именОтдельные собственные пространства имен
    Таблицы

    Представления данных

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

    Частные синонимы

    Хранимые процедуры

    Хранимые функции

    Пакеты

    Материализованные представления данных

    Собственные типы пользователей

    …

    		Индексы

    Заявляемые ограничения целостности

    Кластеры

    Триггерные процедуры

    Частные связи с иной БД

    Каталог в ОС

    Публичные синонимы

    Публичные связи с иной БД

    …

    Например, разрешено назвать в одной схеме одним и тем же именем таблицу, индекс, ограничение целостности и каталог. При работе со схемой SCOTT следующие команды не вызовут ошибок:

    CREATE UNIQUE INDEX emp ON emp ( ename )
;
-- Ошибки нет.
ALTER TABLE emp ADD CONSTRAINT emp UNIQUE ( ename ) USING INDEX emp
;
-- Ошибки нет.

    Не разрешено назвать в одной схеме одним и тем же именем таблицу и представление данных, таблицу и функцию и так далее. При работе со схемой SCOTT следующие команды вернут в программу ошибку:

    VIEW emp AS SELECT * FROM emp
;
-- Ошибка !.
CREATE SEQUENCE emp
;
-- Ошибка !

    Редакции объектов БД в Oracle

    С версии 11.2 для некоторых видов хранимых объектов можно заводить разные "редакции" (editions) и переключаться между ними в работе, моделируя тем самым несколько версий прикладного программного обеспечения на этапе его разработки или переделки. Речь не идет о редакциях данных, и на таблицы эта техника не распространяется. Она применима к объектам следующих видов:

  • VIEW
  • SYNONYM
  • PROCEDURE
  • FUNCTION
  • TRIGGER
  • PACKAGE/PACKAGE BODY
  • TYPE/TYPE BODY
  • LIBRARY
    •

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

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

    В версии Oracle 11.2 техника редакций объектов воплощена в своем начальном варианте, вероятно, не окончательном.

    Создание редакций для объектов и управление ими

    Управление редакциями регулируется привилегиями CREATE/ALTER/DROP ANY EDITION. Слово ANY в названиях напоминает о внесхемном характере редакций, распространяющемся на уровень всей БД целиком (формально они все приписаны пользователю SYS).

    Если правом создавать редакции объектов и управлять ими требуется доверить пользователю YARD, администратору БД следует выдать:

    CONNECT / AS SYSDBA
GRANT CREATE ANY EDITION, DROP ANY EDITION TO yard;
Узнать действующую в данный момент редакцию можно из контекста сеанса USERENV (встроенного в СУБД):
SQL> CONNECT yard/pass
Connected.
SQL> SELECT SYS_CONTEXT ( 'USERENV', 'CURRENT_EDITION_NAME' ) FROM dual;
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------------------------------
ORA$BASE

    ORA$BASE — это встроенная в БД умолчательно действующая редакция, на основе которой администратор может создавать последовательность редакций (а в будущих версиях Oracle, возможно, дерево) на свое усмотрение. Имя умолчательной для БД редакции можно выяснить запросом

    SELECT property_value 
FROM   database_properties 
WHERE  property_name = 'DEFAULT_EDITION'
;

    Примеры создания редакций:

    CREATE EDITION app_release_1;
CREATE EDITION app_release_2 AS CHILD OF app_release_1;

    В первом случае редакция APP_RELEASE_1 была создана на основе умолчательно действующей редакции ORA$BASE, во втором — как следует из текста команды.

    Откомментировать редакцию в словаре-справочнике БД можно командой COMMENT:

    COMMENT ON EDITION app_release_1
  IS 'The first release of application'
;

    Снять комментарий можно, указав пустую строку ''. Наблюдаются комментарии через таблицу ALL_EDITION_COMMENTS.

    Узнать существующие редакции в их взаимосвязи можно запросом к особой таблице:

    SQL> SELECT * FROM all_editions;
EDITION_NAME                   PARENT_EDITION_NAME            USA
------------------------------ ------------------------------ ---
ORA$BASE                                                      YES
APP_RELEASE_1                  ORA$BASE                       YES
APP_RELEASE_2                  APP_RELEASE_1                  YES

    Удалить можно только лист из дерева (пока — ветки), свободный от подчиненных редакций:

    DROP EDITION app_release_2;

    Для того чтобы пользователь Oracle мог не просто обращаться с редакциями объектов, но и формировать их, ему следует сообщить особое качество:

    CONNECT / AS SYSDBA
ALTER USER yard ENABLE EDITIONS;

    Качество ENABLE EDITIONS — не изначальное и неотъемлемое; если оно раз выдано, отменить его нельзя. В результате все пользователи Oracle оказываются разделены на две категории: те, кому разрешено формировать редакции, и те, кому не разрешено. При том возможен перевод пользователя из второй категории в первую, но никак не обратно. Удостовериться в наличие свойства ENABLE EDITIONS у пользователя можно по значению поля EDITIONS_ENABLED (нового в версии 11.2) в таблице DBA_USERS (владелец ее SYS, и обычным пользователям сама по себе она не видна).

    После выдачи последней команды каждый объект пользователя YARD, для которого разрешено редактирование, так или иначе будет привязан к какой-нибудь редакции.

    Настройка на работу с нужной редакцией

    Чтобы пользователь Oracle имел право в конкретном сеансе работать с конкретной редакцией:

  • он должен иметь привилегию на работу с редакцией, выданную лично ему или, вместо этого, псевдопользователю PUBLIC (то есть всем вообще);
  • сеанс должен быть переключен на работу с этой редакцией.
    •

    Выдать пользователю личное общее разрешение на работу с объектами требуемой редакции можно примерно так:

    GRANT USE ON EDITION app_release_1 TO scott;

    USE — это привилегия на объекты вида EDITION, передаваемая к тому же через PUBLIC и через роли. Если редакцию объявить в БД умолчательной, она автоматически полагается выданной для PUBLIC, то есть общедоступной, и не требует личных (или же ролевых) разрешений. По этой причине изначально частных разрешений на работу с ORA$BASE не требуется — оно есть у всех. То же самое произойдет с редакцией APP_RELEASE_1, если в какой-то момент выдать:

    ALTER DATABASE DEFAULT EDITION = app_release_1;

    На последнюю команду способен обладатель привилегии ALTER DATABASE (а ею обладают SYS и SYSTCODE, но пока что не YARD). Как только такая команда будет выдана, команды GRANT USE, как выше, для придания нужных полномочий пользователю SCOTT не потребуется. Выдачей подобной команды может венчаться отладка новых редакций объектов ("перевод приложения на новую редакцию").

    Когда пользователь Oracle получил разрешение (то есть привилегию) на работу с объектами конкретной редакции, он получает право в рамках отдельных сеансов настраиваться на нее:

    SQL> CONNECT scott/tiger
Connected.
SQL> SELECT SYS_CONTEXT ( 'USERENV', 'CURRENT_EDITION_NAME' ) FROM dual;
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------------------------------
ORA$BASE
SQL> ALTER SESSION SET EDITION = app_release_1;
Session altered.
SQL> SELECT SYS_CONTEXT ( 'USERENV', 'CURRENT_EDITION_NAME' ) FROM dual;
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------------------------------
APP_RELEASE_1

    Код выше подтверждает то, что по умолчанию при открытии сеанса действует редакция, объявленая ранее умолчательной в БД.

    Пример создания и использования разных редакций представления данных (view)

    К настоящему моменту в БД имеется две редакции. Будем формировать их содержание редакциями объектов в схеме YARD. Создадим в ней две несложные редакции одного и того же представления данных — с выдачей сведений об отделе сотрудника и без:

    CONNECT yard/pass
ALTER SESSION SET EDITION = ora$base;
CREATE OR REPLACE EDITIONING VIEW codepl
  AS
  SELECT codepno, ename, deptno FROM codep
;
ALTER SESSION SET EDITION = app_release_1;
CREATE OR REPLACE EDITIONING VIEW codepl
  AS
  SELECT codepno, ename FROM codep
;

    Настройку на редакцию ORA$BASE можно было выше не выполнять, потому что эта редакция умолчательная (это проверялось ранее) и автоматически действует в начале каждого сеанса.

    В результате появились две редакции представления данных CODEPL:

    SQL> SELECT view_name, edition_name FROM user_editioning_views_ae;
VIEW_NAME                      EDITION_NAME
------------------------------ ------------------------------
CODEPL                           ORA$BASE
CODEPL                           APP_RELEASE_1

    Редактируемые представления данных (editioning views) отличаются от обычных не только формальным словом EDITIONING при создании, но и некоторыми техническими свойствами. Они могут строиться на основе единственной таблицы, без фильтрации строк фразой WHERE и с отсутствием преобразований столбцов (в то же время воспроизведение всех столбцов не обязательно). Есть и другие отличия, не востребованные тематикой этого текста.

    Чтобы пользователь SCOTT имел доступ к данным, для каждой редакции требуется выдать отдельное разрешение:

    ALTER SESSION SET EDITION = ora$base;
GRANT SELECT ON codepl TO scott;
ALTER SESSION SET EDITION = app_release_1;
GRANT SELECT ON codepl TO scott;
Вот как этими разрешениями может воспользоваться SCOTT:
SQL> CONNECT scott/tiger
Connected.
SQL> ALTER SESSION SET EDITION = ora$base;
Session altered.
SQL> SELECT * FROM yard.codepl WHERE ROWNUM = 1;
     CODEPNO ENAME          DEPTNO
---------- ---------- ----------
      7369 SMITH              20
SQL> ALTER SESSION SET EDITION = app_release_1;
Session altered.
SQL> SELECT * FROM yard.codepl WHERE ROWNUM = 1;
     CODEPNO ENAME
---------- ----------
      7369 SMITH

    Теперь без отмены прежнего представления данных (которым может пользоваться текущее приложение) открылась возможность отлаживать приложение применительно к новому.

    Упражнение. Отберите у пользователя SCOTT привилегию на выборку данных из YARD.CODEPL в редакции APP_RELEASE_1 и наблюдайте результат попытки обращения.

    Методология использования в связи с изменением структуры таблиц

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

    Пусть требуется изменить структуру таблицы CODEP, например, добавить новый столбец. Это может быть вызвано желанием заменить столбец ENAME на два столбца: отдельно для имени сотрудника и отдельно для фамилии. Загодя отладить имеющееся приложение для новой структуры можно следующим образом.

    Во-первых, создать на основе таблицы представление, воспроизводящее полностью данные таблицы. Командами RENAME подменить имя таблицы на искусственное, а старое CODEP передать представлению. Приложение от такой подмены не пострадает.

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

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

    Создать новую редакцию представления, учитывающую новый столбец в таблице.

    С этого момента приложение может продолжать работать с данными через первую редакцию представления и одновременно отлаживаться применительно ко второй редакции. Когда решено, что приложение отлажено, командами RENAME возвращаем таблице прежнее имя CODEP и отказываемся от технологических представлений.

    Страницы:

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

    Основными инструментами моделирования предметной области в БД является в 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;

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

  • Во-первых, можно удалить генератор и воссоздать его заново с требуемым текущим значением. При этом есть шанс ошибиться с правильным воспроизведением прочих свойств.
  • Во-вторых, в качестве искусственной меры можно временно изменить шаг приращения на подходящую величину, обратиться к NEXTVAL, добившись нужного текущего значения, и вернуть приращение назад.
  • В-третьих, пользователь 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;

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

    Замечания

  • Чтобы команда CREATE [PUBLIC] DATABASE LINK выше проработала, пользователь, выдающий ее, должен иметь полномочие (привилегию) CREATE DATABASE LINK или же CREATE PUBLIC DATABASE LINK.
  • Чтобы предложение SELECT выше проработало, нужно средствами Oracle Net обеспечить сетевое имя ORCL для удаленной БД (в общем случае оно не обязано совпадать с именем с базы).
  • 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.

    Индексы

    Индексы в БД в Oracle

    Индексы (от латинского "указатель", обращающий внимание на запрещенные католической церковью книги или их фрагменты) являются хранимыми вспомогательными объектами, используемыми при отработке запросов на SQL. В базах данных смысл индексов — воплотить функцию, позволяющую по значению (или набору значений) получить адреса строк таблицы с этими значениями. В промышленных БД всех типов подобные функции обеспечиваются с помощью индекса как специальной хранимой структуры, хотя умозрительно требования в такой структуре нет, а нужна лишь функциональность.

    Наиболее употребимы в Oracle B*-древовидные индексы. Они могут создаваться:

  • автоматически, СУБД — как средство проверки ограничений целостности "первичный ключ" и "уникальность" в таблицах,
  • либо вручную, разработчиком — ради ускорения доступа к строкам таблицы.

    • Во втором случае ("вручную") для создания индексов используется специальная команда SQL. Примеры:

    CREATE INDEX emp_idx ON emp ( ename );
CREATE UNIQUE INDEX name_loc_idx ON dept_copy ( dname, loc );

    На выбор столбцов для древовидного индекса есть ограничения.

  • Разрешено создавать индекс не более чем на 32 столбца.
  • Нельзя индексировать столбцы некоторых типов (например, семейства LOB или же LONG/LONG RAW).
    •

    Влияние индексов на эффективность работы с БД противоречиво. Индексы:

  • способны сокращать время обращения к строке таблицы;
  • способны увеличивать время обращения к строке таблицы (при "неудачном" распределении индексированных строк по блокам);
  • требуют места в БД;
  • способны замедлять обновления таблиц.
    •

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

    Некоторые общие и простые соображения по поводу использования древовидных индексов:

  • Индекс неэффективен при малом количестве различных индексированых значений (например, пол: "М" и "Ж"), когда они представлены примерно равными количествами.
  • При отсутствии значений (NULL) сразу во всех индексируемых столбцах (если индекс построен по нескольким столбцам) строка не индексируется. Поиск "по отсутствующим значениям" будет игнорировать индекс и выполняться полным просмотром таблицы.
    •

    Второй по важности тип индекса появился в версии Oracle 8.1 и существует для Enterprise Edition. Это поразрядный (bitmap) индекс. Он используется исключительно для ускорения доступа к данным таблицы и дает отдачу во вполне определенных обстоятельствах.

    Доменный индекс (иначе — прикладной, предметный) программируется разработчиком приложения для конкретного типа объектов, однако несколько видов доменных индексов приходит в готовом виде с ПО Oracle, будучи уже запрограммированными разработчиками СУБД.

    Для всех видов индексов допускаются частные случаи конфигурации.

    Индексы для проверки заявляемых ограничений целостности

    Употребление индексов ради ускорения доступа составляет предмет оптимизации запросов и не является темой настоящего материала. Здесь приводятся некоторые сведения об индексах, используемых СУБД для технической поддержки ограничений целостности.

    При обычном объявлении в таблице первичного ключа или свойства уникальности столбцов СУБД автоматически создаст служебный уникальный древовидный индекс. В случае многостолбцовой уникальности допускается задать несколько ограничений на одних и тех же столбцах, но обязательно перечисляемых в разном порядке. Для всех таких ограничений будет использоваться один и тот же индекс — соответствующий первому по порядку создания ограничению. В результате следующих действий два "разных" ограничения AB и BA будут внутренне проверяться одним и тем же индексом AB:

    CREATE TABLE t ( a NUMBER, b NUMBER, c NUMBER );
ALTER TABLE t ADD CONSTRAINT ab UNIQUE ( a, b );
ALTER TABLE t ADD CONSTRAINT ba UNIQUE ( b, a );

    В автоматику создания служебного индекса можно вмешаться. Так, желаемые свойства автоматически создаваемому индексу можно сообщить, вложив в предложение CREATE TABLE или ALTER TABLE … ADD ограничение (где формулируется ограничение целостности) конструкцию CREATE INDEX, например:

    CREATE TABLE t ( 
  c NUMBER PRIMARY KEY USING INDEX ( CREATE INDEX pk_t ON t ( c ) )
, d VARCHAR2 ( 100 ) 
);

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

    Если на необходимые столбцы индекс был заведен ранее, в качестве служебного можно взять уже имеющийся:

    CREATE TABLE t ( c NUMBER );
CREATE INDEX pk_t ON t ( c );
ALTER TABLE t ADD PRIMARY KEY ( c ) USING INDEX pk_t;

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

    В последнем предложении конструкцию USING INDEX можно было бы не употреблять. Однако же если бы индекса PK_T заранее не существовало, эту же конструкцию можно было использовать для заведения индекса с желаемыми характеристиками, применив следующую формулировку:

    ... USING INDEX [имя_индекса] [свойства_индекса] ...

    или даже:

    ... USING INDEX ( CREATE INDEX имя_индекса [свойства_индекса] ) ...

    Обратите внимание, что индекс в этом случае не обязан быть уникальным. (Упражнение. Проверьте свойство уникальности у индекса PK_T). Более того, если ограничение создается как DEFERRABLE, индекс обязан быть неуникальным, и именно таковым он при том создается СУБД автоматически.

    Если при заведения ограничения используется существующий индекс, явно создаваемый или же создаваемый ради ограничения с возможностью отложенной проверки (DEFERRABLE), то в отличие от автоматического он не будет удаляться вместе с удалением ограничения. (Упражнение. Проверьте это.) В этом есть своя логика, но это же может приводить к недоразумениям, когда по удалению ограничения целостности в БД сохранится "остаточный" индекс, не всегда нужный по делу.

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

    CREATE TABLE tr ( d NUMBER REFERENCING t ( c ) );
CREATE INDEX fk_t ON tr ( d );
DROP INDEX fk_t;

    Упражнение. Проверьте, что создание индекса на внешний ключ не оказывает влияния на логику поведения последнего.

    Решение о создании индекса на столбцы внешнего ключа принимается исходя из конкретных обстоятельств.

    Таблицы с временным хранением строк

    Отличаются от обычных таблиц БД тем, что время хранения строк в них ограничено концом либо транзакции, либо сеанса связи с СУБД — по выбору разработчика БД. Описания же таких таблиц (метаданные) хранятся в словаре-справочнике БД на общих основаниях с описаниями обычных таблиц, то есть вплоть до выдачи команды DROP TABLE. Эти свойства объясняют выбор фирмой Oracle названия: GLOBAL TEMPORARY в отличие от таблиц LOCAL TEMPORARY, имеющихся со времен SQL-92 (но не в Oracle), полный жизненный цикл которых ограничен программным блоком.

    Пример создания таблицы с временем хранения строк, ограниченным транзакцией:

    CREATE GLOBAL TEMPORARY TABLE temp AS SELECT * FROM emp WHERE 1 = 2;
-- строк нет
INSERT INTO temp SELECT * FROM emp;
-- строки появились
SELECT * FROM temp;
-- проверка
COMMIT;
-- строки пропали
SELECT * FROM temp;
-- проверка

    Упражнение. Как изменится результат команды CREATE выше, если в формулировке SELECT опустить фразу WHERE?

    Примеры создания таблиц с явно указанными временами хранения строк — до конца текущего сеанса или же до конца текущей транзакции:

    CREATE GLOBAL TEMPORARY TABLE tx ( c NUMBER ) ON COMMIT PRESERVE ROWS;
CREATE GLOBAL TEMPORARY TABLE ts ( c NUMBER ) ON COMMIT DELETE ROWS;

    ON COMMIT DELETE ROWS не требует явного указания, так как подразумевается по умолчанию.

    Если не считать "короткого" времени жизни строк, по своим потребительским свойствам таблицы с временным хранением строк почти не отличаются от обычных. Например, для них можно строить индекс (напомним: ведь их описание хранится постоянно).

    Таблицы обоих видов предоставляют каждому сеансу собственное множество строк, независимое от строк, заведенных в других сеансах (для таблиц, где время хранения строк ограничено сеансом, это неочевидно). Однако выполнение операций DDL с такими таблицами СУБД по понятным причинам увязывает с наличием в них строк (собственных) в других сеансах. Так, построить индекс (CREATE INDEX) удастся только, если в данный момент другой сеанс не завел в таблице собственные строки. Таким образом, косвенная связь содержимого таких таблиц в разных сеансах все-таки имеется.

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

    Таблицы с внешним хранением данных

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

  • ORACLE_LOADER: способен отображать текстовые данные из файла ОС в таблицу;
  • ORACLE_DATAPUMP[10-): допускает выгрузку данных БД в двоичный файл ОС и последующее многократное прочитывание их в виде таблицы.
    •

    [10-) начиная с версии 10

    Далее приводится пример одностороннего отображения. Подразумевается, что действия выполняются на сервере (это используется ниже только во время правки содержимого файла с помощью команд HOST в SQL*Plus и echo командной оболочки ОС).

    Предположим наличие каталога EXTFILES_DIR в БД, доступного для чтения (READ). Создадим файл employee.txt с исходными данными:

    Bush,13/03/2001,5000

    Powell,14/03/2001,,400.50

    Обратите внимание, что во второй строке применено английское форматирование для записи числа. Оно предполагает языковую ориентировку СУБД игнорируемых ошибок формата, могущих возникать в процессе работы на местности, где это принято, например, AMERICAN/AMERICA.

    Заведем в схеме SCOTT таблицу с внешним хранением:

    CREATE TABLE emp_load
  ( ename    VARCHAR2 ( 10 )
  , hiredate DATE
  , sal      NUMBER ( 7, 2 )
  , comm     NUMBER ( 7, 2 )
  )
ORGANIZATION EXTERNAL
 ( TYPE ORACLE_LOADER
   DEFAULT DIRECTORY extfiles_dir
   ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE 
      NOBADFILE
      NOLOGFILE
      FIELDS TERMINATED BY ','
      MISSING FIELD VALUES ARE NULL
       ( ename
       , hiredate CHAR DATE_FORMAT DATE MASK "dd/mm/yyyy"
       , sal
       , comm 
       )
    )
   LOCATION ( 'employee.txt' )
 )
;

    Проверка в SQL*Plus:

    SQL> SELECT * FROM emp_load;
ENAME      HIREDATE         SAL       COMM
---------- --------- ---------- ----------
Bush       13-MAR-01       5000
Powell     14-MAR-01                 400.5
SQL> HOST echo Hussein,,1000.44,20000 >> employee.txt
SQL> /
ENAME      HIREDATE         SAL       COMM
---------- --------- ---------- ----------
Bush       13-MAR-01       5000
Powell     14-MAR-01                 400.5
Hussein                 1000.44      20000
SQL> SELECT SUM ( sal ) FROM emp_load;
  SUM(SAL)
----------
   6000.44

    Во втором запросе результат вычисляется по ходу прочтения файла employee.txt, то есть не требуется полной загрузки данных этого файла в БД для вычисления результата.

    Конструкция LOCATION в определении таблицы допускает задание списка файлов или же переопределение файлов-источников, например:

    SQL> HOST echo Laden,13/03/2005,10000 >> centralasia.txt
SQL> ALTER TABLE emp_load LOCATION ('employee.txt', 'centralasia.txt');
Table altered.
SQL> SELECT * FROM emp_load;
ENAME      HIREDATE         SAL       COMM
---------- --------- ---------- ----------
Bush       13-MAR-01       5000           
Powell     14-MAR-01                 500.5
Hussein                 1000.44      20000
Laden      13-MAR-05      10000

    Это позволяет переориентировать таблицу EMP_LOAD на другие файлы по мере подготовки их внешними программами приложения. Переопределять разрешено и DEFAULT DIRECTORY.

    Еще пример свойств. Указания NOBADFILE и NOLOGFILE можно заменить на противоположные (и подразумеваемые молчаливо), в результате чего в каталоге начнут появляться протокольные файлы доступа к данным из БД. Это, однако, потребует дополнительно привилегии WRITE для SCOTT на использование каталога EXTFILES_DIR (для предыдущих действий хватало привилегии READ). Указание REJECT LIMIT сообщит предельное количество игнорируемых нарушений формата в записях из файлов-источников, обнаруживаемых в процессе выполнения SELECT:

    ALTER TABLE emp_load ACCESS PARAMETERS ( 
   RECORDS DELIMITED BY NEWLINE
   BADFILE 
   LOGGING 
);
ALTER TABLE emp_load REJECT LIMIT 20;

    Пока нарушений формата менее 21, ошибку доступа СУБД порождать не будет, а только будет пополнять записями о нарушениях протокольный файл.

    Таблицу с внешним хранением можно использовать для обновления данных наряду с обычными:

    MERGE INTO bonus b USING emp_load e 
ON ( b.ename = e.ename )
WHEN MATCHED THEN UPDATE SET sal = sal * 10
;

    Некоторые общие свойства объектов хранения разных видов

    Формально в Oracle имеется несколько десятков разных видов хранимых в БД объектов. Некоторое представление о многообразии дает запрос:

    SELECT DISTINCT object_type FROM all_objects;

    (Не все из них управляются командами SQL CREATE/ALTER/DROP, значительная часть — процедурно.)

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

    Пространства имен для объектов в Oracle

    Для именования объектов хранения Oracle разных видов используются различные пространства имен. Распределение по пространствам имен для наиболее популярных типов поясняется таблицей.

    Отдельное общее пространство именОтдельные собственные пространства имен
    Таблицы

    Представления данных

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

    Частные синонимы

    Хранимые процедуры

    Хранимые функции

    Пакеты

    Материализованные представления данных

    Собственные типы пользователей

    …

    		Индексы

    Заявляемые ограничения целостности

    Кластеры

    Триггерные процедуры

    Частные связи с иной БД

    Каталог в ОС

    Публичные синонимы

    Публичные связи с иной БД

    …

    Например, разрешено назвать в одной схеме одним и тем же именем таблицу, индекс, ограничение целостности и каталог. При работе со схемой SCOTT следующие команды не вызовут ошибок:

    CREATE UNIQUE INDEX emp ON emp ( ename )
;
-- Ошибки нет.
ALTER TABLE emp ADD CONSTRAINT emp UNIQUE ( ename ) USING INDEX emp
;
-- Ошибки нет.

    Не разрешено назвать в одной схеме одним и тем же именем таблицу и представление данных, таблицу и функцию и так далее. При работе со схемой SCOTT следующие команды вернут в программу ошибку:

    VIEW emp AS SELECT * FROM emp
;
-- Ошибка !.
CREATE SEQUENCE emp
;
-- Ошибка !

    Редакции объектов БД в Oracle

    С версии 11.2 для некоторых видов хранимых объектов можно заводить разные "редакции" (editions) и переключаться между ними в работе, моделируя тем самым несколько версий прикладного программного обеспечения на этапе его разработки или переделки. Речь не идет о редакциях данных, и на таблицы эта техника не распространяется. Она применима к объектам следующих видов:

  • VIEW
  • SYNONYM
  • PROCEDURE
  • FUNCTION
  • TRIGGER
  • PACKAGE/PACKAGE BODY
  • TYPE/TYPE BODY
  • LIBRARY
    •

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

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

    В версии Oracle 11.2 техника редакций объектов воплощена в своем начальном варианте, вероятно, не окончательном.

    Создание редакций для объектов и управление ими

    Управление редакциями регулируется привилегиями CREATE/ALTER/DROP ANY EDITION. Слово ANY в названиях напоминает о внесхемном характере редакций, распространяющемся на уровень всей БД целиком (формально они все приписаны пользователю SYS).

    Если правом создавать редакции объектов и управлять ими требуется доверить пользователю YARD, администратору БД следует выдать:

    CONNECT / AS SYSDBA
GRANT CREATE ANY EDITION, DROP ANY EDITION TO yard;
Узнать действующую в данный момент редакцию можно из контекста сеанса USERENV (встроенного в СУБД):
SQL> CONNECT yard/pass
Connected.
SQL> SELECT SYS_CONTEXT ( 'USERENV', 'CURRENT_EDITION_NAME' ) FROM dual;
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------------------------------
ORA$BASE

    ORA$BASE — это встроенная в БД умолчательно действующая редакция, на основе которой администратор может создавать последовательность редакций (а в будущих версиях Oracle, возможно, дерево) на свое усмотрение. Имя умолчательной для БД редакции можно выяснить запросом

    SELECT property_value 
FROM   database_properties 
WHERE  property_name = 'DEFAULT_EDITION'
;

    Примеры создания редакций:

    CREATE EDITION app_release_1;
CREATE EDITION app_release_2 AS CHILD OF app_release_1;

    В первом случае редакция APP_RELEASE_1 была создана на основе умолчательно действующей редакции ORA$BASE, во втором — как следует из текста команды.

    Откомментировать редакцию в словаре-справочнике БД можно командой COMMENT:

    COMMENT ON EDITION app_release_1
  IS 'The first release of application'
;

    Снять комментарий можно, указав пустую строку ''. Наблюдаются комментарии через таблицу ALL_EDITION_COMMENTS.

    Узнать существующие редакции в их взаимосвязи можно запросом к особой таблице:

    SQL> SELECT * FROM all_editions;
EDITION_NAME                   PARENT_EDITION_NAME            USA
------------------------------ ------------------------------ ---
ORA$BASE                                                      YES
APP_RELEASE_1                  ORA$BASE                       YES
APP_RELEASE_2                  APP_RELEASE_1                  YES

    Удалить можно только лист из дерева (пока — ветки), свободный от подчиненных редакций:

    DROP EDITION app_release_2;

    Для того чтобы пользователь Oracle мог не просто обращаться с редакциями объектов, но и формировать их, ему следует сообщить особое качество:

    CONNECT / AS SYSDBA
ALTER USER yard ENABLE EDITIONS;

    Качество ENABLE EDITIONS — не изначальное и неотъемлемое; если оно раз выдано, отменить его нельзя. В результате все пользователи Oracle оказываются разделены на две категории: те, кому разрешено формировать редакции, и те, кому не разрешено. При том возможен перевод пользователя из второй категории в первую, но никак не обратно. Удостовериться в наличие свойства ENABLE EDITIONS у пользователя можно по значению поля EDITIONS_ENABLED (нового в версии 11.2) в таблице DBA_USERS (владелец ее SYS, и обычным пользователям сама по себе она не видна).

    После выдачи последней команды каждый объект пользователя YARD, для которого разрешено редактирование, так или иначе будет привязан к какой-нибудь редакции.

    Настройка на работу с нужной редакцией

    Чтобы пользователь Oracle имел право в конкретном сеансе работать с конкретной редакцией:

  • он должен иметь привилегию на работу с редакцией, выданную лично ему или, вместо этого, псевдопользователю PUBLIC (то есть всем вообще);
  • сеанс должен быть переключен на работу с этой редакцией.
    •

    Выдать пользователю личное общее разрешение на работу с объектами требуемой редакции можно примерно так:

    GRANT USE ON EDITION app_release_1 TO scott;

    USE — это привилегия на объекты вида EDITION, передаваемая к тому же через PUBLIC и через роли. Если редакцию объявить в БД умолчательной, она автоматически полагается выданной для PUBLIC, то есть общедоступной, и не требует личных (или же ролевых) разрешений. По этой причине изначально частных разрешений на работу с ORA$BASE не требуется — оно есть у всех. То же самое произойдет с редакцией APP_RELEASE_1, если в какой-то момент выдать:

    ALTER DATABASE DEFAULT EDITION = app_release_1;

    На последнюю команду способен обладатель привилегии ALTER DATABASE (а ею обладают SYS и SYSTCODE, но пока что не YARD). Как только такая команда будет выдана, команды GRANT USE, как выше, для придания нужных полномочий пользователю SCOTT не потребуется. Выдачей подобной команды может венчаться отладка новых редакций объектов ("перевод приложения на новую редакцию").

    Когда пользователь Oracle получил разрешение (то есть привилегию) на работу с объектами конкретной редакции, он получает право в рамках отдельных сеансов настраиваться на нее:

    SQL> CONNECT scott/tiger
Connected.
SQL> SELECT SYS_CONTEXT ( 'USERENV', 'CURRENT_EDITION_NAME' ) FROM dual;
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------------------------------
ORA$BASE
SQL> ALTER SESSION SET EDITION = app_release_1;
Session altered.
SQL> SELECT SYS_CONTEXT ( 'USERENV', 'CURRENT_EDITION_NAME' ) FROM dual;
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------------------------------
APP_RELEASE_1

    Код выше подтверждает то, что по умолчанию при открытии сеанса действует редакция, объявленая ранее умолчательной в БД.

    Пример создания и использования разных редакций представления данных (view)

    К настоящему моменту в БД имеется две редакции. Будем формировать их содержание редакциями объектов в схеме YARD. Создадим в ней две несложные редакции одного и того же представления данных — с выдачей сведений об отделе сотрудника и без:

    CONNECT yard/pass
ALTER SESSION SET EDITION = ora$base;
CREATE OR REPLACE EDITIONING VIEW codepl
  AS
  SELECT codepno, ename, deptno FROM codep
;
ALTER SESSION SET EDITION = app_release_1;
CREATE OR REPLACE EDITIONING VIEW codepl
  AS
  SELECT codepno, ename FROM codep
;

    Настройку на редакцию ORA$BASE можно было выше не выполнять, потому что эта редакция умолчательная (это проверялось ранее) и автоматически действует в начале каждого сеанса.

    В результате появились две редакции представления данных CODEPL:

    SQL> SELECT view_name, edition_name FROM user_editioning_views_ae;
VIEW_NAME                      EDITION_NAME
------------------------------ ------------------------------
CODEPL                           ORA$BASE
CODEPL                           APP_RELEASE_1

    Редактируемые представления данных (editioning views) отличаются от обычных не только формальным словом EDITIONING при создании, но и некоторыми техническими свойствами. Они могут строиться на основе единственной таблицы, без фильтрации строк фразой WHERE и с отсутствием преобразований столбцов (в то же время воспроизведение всех столбцов не обязательно). Есть и другие отличия, не востребованные тематикой этого текста.

    Чтобы пользователь SCOTT имел доступ к данным, для каждой редакции требуется выдать отдельное разрешение:

    ALTER SESSION SET EDITION = ora$base;
GRANT SELECT ON codepl TO scott;
ALTER SESSION SET EDITION = app_release_1;
GRANT SELECT ON codepl TO scott;
Вот как этими разрешениями может воспользоваться SCOTT:
SQL> CONNECT scott/tiger
Connected.
SQL> ALTER SESSION SET EDITION = ora$base;
Session altered.
SQL> SELECT * FROM yard.codepl WHERE ROWNUM = 1;
     CODEPNO ENAME          DEPTNO
---------- ---------- ----------
      7369 SMITH              20
SQL> ALTER SESSION SET EDITION = app_release_1;
Session altered.
SQL> SELECT * FROM yard.codepl WHERE ROWNUM = 1;
     CODEPNO ENAME
---------- ----------
      7369 SMITH

    Теперь без отмены прежнего представления данных (которым может пользоваться текущее приложение) открылась возможность отлаживать приложение применительно к новому.

    Упражнение. Отберите у пользователя SCOTT привилегию на выборку данных из YARD.CODEPL в редакции APP_RELEASE_1 и наблюдайте результат попытки обращения.

    Методология использования в связи с изменением структуры таблиц

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

    Пусть требуется изменить структуру таблицы CODEP, например, добавить новый столбец. Это может быть вызвано желанием заменить столбец ENAME на два столбца: отдельно для имени сотрудника и отдельно для фамилии. Загодя отладить имеющееся приложение для новой структуры можно следующим образом.

    Во-первых, создать на основе таблицы представление, воспроизводящее полностью данные таблицы. Командами RENAME подменить имя таблицы на искусственное, а старое CODEP передать представлению. Приложение от такой подмены не пострадает.

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

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

    Создать новую редакцию представления, учитывающую новый столбец в таблице.

    С этого момента приложение может продолжать работать с данными через первую редакцию представления и одновременно отлаживаться применительно ко второй редакции. Когда решено, что приложение отлажено, командами RENAME возвращаем таблице прежнее имя CODEP и отказываемся от технологических представлений.

    Предыдущее занятие Следующее занятие
    Вернуться к учебному плану
    ^