Встроенный динамический SQL

Пакет NDS привносит в программы PL/SQL три новые функциональности:

- динамические DDL и DML без переменных связи;

- динамические DML без известного списка переменных связи;

- динамические DDL.

Основные преимущества NDS перед DBMS_SQL:

- выполняется быстрее, чем DBMS_SQL;

- синтаксис пакета является зеркальным повторением стандартных статических операторов SQL;

- можно вести выборку непосредственно в типы записей PL/SQL;

- поддерживает все типы данных PL/SQL, в том числе определенные пользователем типы и объекты, а также указатели на наборы строк.

Осуществляет синтаксический анализ команды SQL и её выполнение за один шаг команда EXECUTE IMMEDIATE

EXECUTE IMMEDIATE команда_sql

[INTO { переменная [, переменная …] | запись | объектная_переменная }]

[USING [IN | OUT | IN OUT] связанный_аргумент

[, [IN | OUT | IN OUT] связанный_аргумент …] ]

[{RETURNING | RETURN} INTO связанный_аргумент [, связанный_аргумент ]…];

Может применяться для любой команды SQL, кроме многострочных запросов. В Oracle9i посредством команды EXECUTE IMMEDIATE можно выполнять массовое извлечение, а также массовую вставку или обновление, даже если при массовой обработке были обнаружены ошибки.

Для применения динамического SQL в запросах, возвращающих несколько строк, применяется переменная CURSOR и цикл OPEN FOR.

Команда EXECUTE IMMEDIATE должна завершаться точкой с запятой, тогда как команда_sql не должна содержать завершающей точки с запятой.

Массовые связывания или групповая обработка (FORALL, BULK COLLECT INTO, BULK FETCH, BULK EXECUTE IMMEDIATE, RETURNING INTO, %BULK_ROWCOUNT).

Коллекции можно применять для улучшения производительности многократно выполняемых операций SQL за счет массового связывания (bulk binds). Массовое связывание уменьшает количество переключений контекста от процессора PL/SQL к процессору БД и обратно. Кроме того, связывание может применяться для реализации динамического SQL. Массовое связывание реализуется двумя конструкциями PL/SQL: FORALL и BULK COLLECT INTO.

Рассмотрим синтаксис FORALL:

FORALL индекс IN нижняя_граница..верхняя_граница [SAVE EXECEPTONS]

{EXECUTE IMMEDIATE команды_sql };

где индекс – целая переменная, может применяться только для команды_sql и только как индекс коллекции; нижняя_граница..верхняя_граница – ограничение для элементов коллекции, которые будут обрабатываться процессором БД; SAVE EXEPTIONS – позволяет продолжить массовую обработку даже в случае ошибок в некоторых отдельных операциях. После завершения обработки цикла подробную информацию о возникших проблемах можно будет получить в атрибуте % BULK_EXEPTIONS.

Когда PL/SQL обрабатывает эту команду, на обработку серверу БД отправляется вся коллекция, а не каждый ее элемент по отдельности. Можно применять инструкцию FORALL, как BULK COLLECT INTO, обращаясь к синтаксису EXECUTE IMMEDIATE для динамического SQL.

BULK COLLECT INTO имеет следующий синтаксис:

команда_sql BULK COLLECT INTO список_имен_коллекции;

где команда_SQL – это команда SQL, такая как SELECT INTO, FETCH INTO, RETURNING INTO или EXECUTE IMMEDIATE;

список_имен_коллекций – раздельный запятыми список коллекций, по одной для каждого столбца списка SELECT. Коллекции записей не могут быть предметом назначения инструкций BULK COLECT INTO. Однако сервер Oracle поддерживает извлечение набора объектов, одного типа и массовую сборку их в коллекцию объектов.

Когда PL/SQL обрабатывает эту команда, сервер БД возвращает для обработки всю коллекцию целиком, а не каждый ее элемент в отдельности. Можно включить инструкцию BULK COLLECT INTO в команды SELECT INTO, FETCH INTO и RETURNING INTO, ее можно применять с динамическим SQL, выполняем при помощи команды EXECUTE IMMEDIATE.

Для операций массового связывания можно использовать атрибут курсора %BULK_ROWCOUNT. Этот атрибут подобен ассоциативному массиву, содержащему количество строк, изменяемых при выполнении команд массового связывания. Элемент %BULK_ROWCOUNT с номером n содержит количество строк, измененных n-м выполнением команды SQL.

Передать %BULK_ROWCOUNT как параметр в другую программу нельзя, как и нельзя и использовать агрегатное присваивание другой коллекции. %ROWCOUNT содержит сумму всех элементов %BULK_ROWCOUNT. Атрибуты % FOUND и %NOTFOUND отражают результаты самого последнего выполнения команды SQL.

Пакет DBMS_OUTPUT

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

В состав PL/SQL 2.0 был включен пакет DBMS_OUTPUT, обеспечивающий вывод информации, а средства ввода данных в языке PL/SQL до сих пор не существует. Однако вводить информацию можно при помощи переменных подстановки SQL*Plus. В PL/SQL2.3 и выше имеется также пакет UTL_FILE, который используется для чтения информации из файлов операционной системы и для записи информации в файлы.

Владельцем пакета DBMS_OUTPUT является пользователь ОRACLE с именем SYS. В файле-сценарии создания DBMS_OUTPUT роли PUBLIC предоставляется полномочие ЕХЕСUTE на этот модуль; кроме того, в данном файле для пакета создается общий синоним. Это означает, что любой пользователь ОRACLE может, вызывать подпрограммы пакета DBMS_OUTPUT не указывая перед его именем SYS.

В пакете реализованы две базовые операции – GET и PUT. Операция PUT берет свои аргументы и помещает во внутренний буфер для хранения. Операция GET считывает этот буфер и возвращает его содержимое процедуре в качестве аргумента. Размер буфера устанавливается с помощью процедуры ENABLE.

Выполнение операции PUT обеспечивается процедурами PUT, PUT_LINE и NEW_LINE, а выполнение операции GET – процедурами GET_LINE и GET_LINES. Управляют буфером процедуры ENABLE и DISABLE.

Процедуры PUT и PUT_LINE определены в пакете следующим образом:

procedure PUT (a varchar2);

procedure PUT (a number);

procedure PUT (a date);

procedure PUT_LINE (a varchar2);

procedure PUT_LINE (a number);

procedure PUT_LINE (a date).

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

Буфер организован в виде строк, каждая из которых может состоять не более чем из 255 байт. PUT_LINE добавляет к аргументу символ новой строки, сообщая о конце строки; PUT же этого не делает. Вызов PUT_LINE аналогичен вызову PUT с последующим вызовом NEW_LINE.

Процедура GET_LINE определена в пакете следующим образом:

procedure GET_LINE (line out varchar2, status out integer);

где line представляет собой последовательность символов, из которых состоит одна строка буфера, а status указывает на то, успешно или нет, была считана эта строка. Максимальная длина строки – 255 байт. Если строка считана, то в переменной status находится 0, если в буфере больше нет строк для считывания, то в status – 1.

Аргументом процедуры GET_LINES является индексная таблица. Тип таблицы и вызов данной процедуры выглядят следующим образом:

type CHARARR is table of varchar2(255) index by binary integer;

procedure GET_LINES (lines out chararr, numlines in out integer);

где numlines – число запрошенных строк, на входе (параметр IN) в GET_LINES указывается число запрошенных строк, на выходе (параметр OUT) – число фактически возвращаемых строк.

Тип CHARARR определен в пакете DBMS_OUTPUT, поэтому если GET_LINES вызывается явным образом, нужно объявлять переменную с типом DBMS_OUTPUT.CHARARR. Например:

declare

v _ Data DBMS_OUTPUT.CHARARR;

v _ NumLines number;

begin

DBMS_OUTPUT.ENABLE (1000000);

DBMS_OUTPUT.PUT_LINE (‘Line one’);

DBMS_OUTPUT.PUT_LINE (‘Line two’);

DBMS_OUTPUT.PUT_LINE (‘Line three’);

v_NumLines: = 3;

DBMS_OUTPUT.GET_LINES(v_Data, v_NumLines);

for v_Counter in 1..3 v_NumLines

loop

insert into temp_table (char_col)

values (v_Data (v_Counter));

end loop;

end;

Процедура ENABLE задает размер буфера в байтах, по умолчанию задается размер 20000 байт, а максимальный размер – 1000000 байт. Если объявлена процедура DISABLE, то содержимое буфера уничтожается и последующие вызовы PUT и PUT_LINE бесполезны.

По существу, пакет DBMS_OUTPUT реализует алгоритм “первым пришел – первым обслужен”. В утилите SQL*Plus имеется средство, называемое SERVEROUTPUT (серверный вывод), команда SQL*Plus SET SERVEROUTPUT ON неявно вызывает процедуру DBMS_OUTPUT.ENABLE, которая устанавливает внутренний буфер серверного вывода. Если нужно, можно указать размер буфера с помощью команды:

SET SERVEROUTPUT ON SIZE размер_буфера

где размер_буфера – первоначальный размер буфера (аргумент процедуры DBMS_OUTPUT.ENABLE, вызываемой по умолчанию). Процедура DBMS_OUTPUT.GET_LINES вызывается после окончания блока PL/SQL, т.е.

результаты будут выводиться на экран после завершения блока, а не вовремя его выполнения.

ФАЙЛОВЫЙ ВВОД/ВЫВОД ПАКЕТ UTL_FILE

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

Безопасность. В клиентском PL/SQL имеется пакет ТЕХТ_IO, подобный UTL_FILE. Эти пакеты различны в отношении безопасности информации. Файлы, создаваемые с помощью клиентского модуля ТЕХТ IO, можно размещать в любом месте на станции клиента при наличии необходимых привилегий для работы с операционной системой. При выполнении клиентских операций файлового ввода/вывода каких-либо привилегий для работы с PL/SQL и собственно БД не требуется.

На сервере необходимо поддерживать безопасность информации на более высоком уровне. Для этого выделяются специальные каталоги (accessible directories), в которые пакет UTL_FILE может записывать данные. Они определяются параметром UTL_FILE_DIR инициализационного файла БД. Каждый доступный каталог указывается в этом файле отдельной строкой:

UTL_FILE_ DIR= имя_каталога

Вид спецификации имени каталога зависит от применяемой операционной системы.

Для того чтобы обратиться к файлу с помощью пакета UTL_FILE, нужно передать функции FOPEN имя каталога и имя файла в виде отдельных параметров. Имя каталога сопоставляется со списком доступных каталогов, и если оно там найдено, операция разрешается (при соблюдении ограничений безопасности операционной системы). Если в FOPEN указано имя недоступного каталога, выдается сообщение об ошибке. Подкаталоги доступных каталогов можно использовать только тогда, когда они также указаны как доступные явным образом.

Операции файлового ввода/вывода выполняются пакетом UTL_FILE в режиме пользователя ORACLE. Следовательно, пользователь ORACLE должен иметь привилегии операционной системы на чтение из всех доступных каталогов и на запись в них, а также все файлы, создаваемые в результате работы пакета UTL_FILE, будут принадлежать ему. Кроме того, файлы будут создаваться с привилегиями, установленными операционной системой для пользователя ORACLE по умолчанию. Если с этими файлами должны работать другие пользователи, обращаясь к ним не из модуля UTL_FILE, системный администратор обязан изменить полномочия на эти файлы.

Если при выполнении некоторой процедуры или функции пакета UTL_FILE возникает ошибка, устанавливается исключительная ситуация. Возможные исключительные ситуации приведены в таблице. Обратите внимание на то, что в их состав входят восемь определенных в пакете UTL_FILE и две стандартные исключительные ситуации (NO_DАТА_FOUND и VALUE_ERROR). Исключительные ситуации UTL_FILЕ можно распознать по имени или с помощью обработчика исключительной ситуации OTHERS. Стандартные исключительные ситуации определяются еще и по значениям, возвращаемым для них функцией SQLCODE.

Таблица Исключительные ситуации, устанавливаемые в UTL_FILE

Исключительная ситуация	Причина	Источник
INVALID_РАТH	Имя каталога или имя файла неверно или недоступно	FOPEN
INVALID_MODE	Для режима файла указана неверная строка символов	FOPEN
INVALID_FILE_HANDLE	Описатель файла не указывает на открытый файл	FCLOSE, GET_LINE, PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH
INVALID_OPERATION	Файл нельзя открыть так, как это было запрошено. Возможная причина – полномочия операционной системы. Устанавливается также при попытке записи в файл, открытого для чтения, или при попытке чтения файла, открытого для записи	GET_LINE, PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH
INVALID_MAXLINESIZE	Указанный максимальный размер строки слишком велик или слишком мал.	FOPEN
READ_ERROR	Ошибка операционной системы во время операции чтения	GET_LINE
WRITE ERROR	Ошибка операционной системы во время операции записи	PUT, PUT_LINE, PUTF, NEW_LINE, FFLUSH, FCLOSE, FCLOSE_АLL
INTERNAL_ERROR	Неопределенная внутрисистемная ошибка	Все функции
NO_DATA_FOUND	Во время чтения достигнут конец файла	GET_LINE
VALUE_ERROR	Вводимая строка слишком велика для буфера, указанного в GET_LINE	GET_LINE

Открытие и закрытие файлов. Во всех операциях пакета UTL_FILE фигурирует понятие описателя файла. Описатель или логический номер файла (file handle) – это значение, которое используется в PL/SQL для идентификации файла, подобно идентификатору курсора в пакете DBMS_SQL. Все описатели файлов имеют тип UTL_FILE.FILE_TYPE, они возвращаются функцией FOPEN и передаются как параметры вида IN в другие подпрограммы UTL_FILE.

Функция FOPEN открывает файл на ввод или на вывод. В любой момент времени файл может быть открыт или только на ввод, или только на вывод. Синтаксис FOPEN:

function FOPEN (location in varchar2, filename in varchar2, open_mode in varchar2);

return FILE_TYPE;

Каталог, путь к которому указывается, должен существовать на момент выполнения функции FOPEN. Однако если установить режим OPEN_MODE как ‘w’, то существующий файл будет перезаписываться. Параметры и возвращаемое значение функции FOPEN описаны в следующей таблице:

Таблица Параметры функции FOPEN

Параметр	Тип	Описание
location	varchar2	Путь к каталогу, в котором расположен файл. Если этот каталог не входит в число доступных, то устанавливается UTL_FILE.INVALID_PATH.
filename	varchar2	Имя открываемого файла. Если задан режим 'w', то существующий файл перезаписывается.
open_mode	varchar2	Используемый режим. Возможные значения: ‘r’ – чтение текста; ‘w’ – запись текста; ‘а’ – добавление текста. Этот параметр не чувствителен к регистру символов. Если установлен режим ‘а’, а файл не существует, то он создается в режиме ‘w’.
возвращаемое значение	UTL_FILE.FILE_TYPE	Описатель файла, используемый впоследствии в функциях.

В Oracle8 и выше в пакет UTL_FILE введен дополнительный, переопределенный вариант функции FOPEN:

function FOPEN (location in varchar2, filename in varchar2, open _ mode in varchar2, maxlinesize in binary_integer);

return FILE_TYPE;

Параметры location, filename и open_mode ведут себя так же, как и в первом варианте FOPEN, а maxlinesize используется для указания максимального размера строки файла. Диапазон значений: от 1 до 32767. Если это значение не указано, максимальный размер строки устанавливается равным 1024. Если maxlinesize меньше 1 или больше 32767, устанавливается исключительная ситуация UTL_FILE.INVALID_MAXLINESIZE.

После окончания чтения из файла или записи в него этот файл должен быть закрыт с помощью процедуры FCLOSE. В результате освобождаются ресурсы, используемые модулем UTL_FILE для работы с файлом. Описание FCLOSE выглядит следующим образом:

procedure FCLOSE (file_handle in out FILE_TYPE);

Единственным параметром этой процедуры является описатель файла. Все изменения, которые должны быть записаны в файл, завершаются перед его закрытием. В случае ошибки записи устанавливается UTL_FILE.WRITE_ERROR. Если описатель не идентифицирует корректный открытый файл, то устанавливается UTL_FILE.INVALID_FILEHANDLE.

Перед тем как производить операции чтения из файла или записи/перезаписи в файл необходимо проверить, открыт ли этот файл. Для этих целей в пакете UTL_FILE предусмотрена функция IS_OPEN. Она возвращает TRUE, если указанный файл открыт, и FALSE, если не открыт. Описание функции IS_OPEN:

function IS OPEN (file_handle in FILE TYPE)

return boolean;

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

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

procedure FCLOSE_ALL;

Эта процедура не принимает никаких аргументов. Все ожидающие записи изменения сбрасываются на диск перед закрытием файлов. Поэтому в случае ошибки записи FCLOSE_ALL может устанавливать исключительную ситуацию UTL_FILE.WRITE_ERROR.

Файловый вывод. Для вывода данных в файлы используются пять процедур: PUT, PUT_LINE, NEW_LINE, PUTF и FFLUSH. Максимальный размер выходной записи равен 1023 байтам (если не указано иное значение в FOPEN), в том числе байт для символа новой строки.

Описание процедуры PUT в модуле UTL_FILE имеет следующий вид:

procedure PUT (file_handle in FILE TYPE, buffer in varchar2);

Символ новой строки в файл не вводится. Для того чтобы включить в файл признак конца строки, используются процедуры PUT_LINE или NEW_LINE. В случае ошибки записи устанавливается UTL_FILE.WRITE_ERROR. Параметры процедуры PUT описаны в следующей таблице.

Таблица Параметры процедуры PUT

Параметр	Тип	Описание
file_handle	UTL_FILE.FILE_TYPE	Описатель файла, возвращаемый функцией FOPEN. Если описатель некорректен, то устанавливается UTL_FILE.INVALID_FILEHANDLE.
buffer	varchar2	Текстовая строка символов, выводимая в файл. Если файл не был открыт в режиме ‘w’ или ‘а’, то устанавливается UTL_FILE.INVALID_OPERATION.

Процедура NEW_LINE записывает один или несколько признаков конца строки в указанный файл:

procedure NEW_LINE (file_handle in FILE_TYPE, lines in natural:= 1);

Признак конца строки зависит от используемой системы – в разных операционных системах признаки конца строки различны. В случае ошибки записи устанавливается UTL_FILE.WRITЕ_ERROR. Параметры процедуры описаны в следующей таблице:

Таблица Параметры процедуры NEW_LINE

Параметр	Тип	Описание
file_handle	UTL_FILE.FILE_TYPE	Описатель файла, возвращаемый функцией FOPEN. Если описатель некорректен, то устанавливается UTL_FILE.INVALID_FILEHANDLE.
lines	natural	Число выводимых признаков конца строки. Значение по умолчанию равно 1, что соответствует выводу одного символа новой строки. Если файл не был открыт в режиме ‘w’ или ‘а’, то устанавливается UTL_FILE.INVALID_OPERATION.

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

procedure PUT_LINE (file handle in FILE_TYPE, buffer in varchar2);

Процедура PUTF подобна PUT, но позволяет форматировать выходную строку символов. PUTF – это сокращенный вариант функции printf(), применяемой в языке С, и имеет аналогичный синтаксис:

procedure PUTF (file_handle in FILE TYPE, format in varchar2,

arg1 in varchar2 default null,

arg2 in varchar2 default null,

arg3 in varchar2 default null,

arg4 in varchar2 default null,

arg5 in varchar2 default null);

Заметим, что аргументы с arg1 по arg5 имеют значения по умолчанию, то есть они необязательны. В строке FORMAT содержится обычный текст, а также могут применяться два специальных символа %s и \n. Каждый экземпляр %s в этой строке замещается одним из необязательных аргументов, а каждый экземпляр \n – символом новой строки. В случае ошибки записи устанавливается UTL_FILE.WRITE_ERROR.

С помощью процедур PUT, PUT_LINE, PUTF и NEW_LINE данные выводятся через буфер. Когда буфер заполняется, данные физически сбрасываются (flush) в файл. Процедура FFLUSH записывает содержимое буфера в файл немедленно, причем выводятся только те строки буфера, которые заканчиваются символом перевода строки, все данные, записанные последними операциями PUT, остаются в буфере. Описание процедуры выглядит следующим образом:

procedure FFLUSH (file_handle in FILE_TYPE).

Файловый ввод. Процедура GET_LINE используется не для записи в файл, а для чтения из файла. Одна строка текста считывается в указанном файле и возвращается параметром BUFFER. Символ новой строки в возвращаемую последовательность не включается. Описание процедуры GET_LINE выглядит следующим образом:

procedure GET_LINE (file_handle in FILE TYPE, buffer out varchar2);

Когда из файла считывается последняя строка, устанавливается исключительная ситуация NO_DАТА_FOUND. Если строка не помещается в буфер, размер которого указан как фактический параметр, устанавливается VALUE_ERROR. При чтении пустой строки возвращается пустая последовательность символов (NULL). В случае ошибки записи устанавливается UTL_FILE.READ_ERROR. Максимальный размер входной строки составляет 1022 байта (если параметром maxlinesize функции FOPEN не задан другой размер). Параметры процедуры GET_LINE описаны в следующей таблице.

Таблица Параметры функции GET_LINE

Параметр	Тип	Описание
file_handle	UTL_FILE.FILE_TYPE	Описатель файла, возвращаемый функцией FOPEN. Если описатель некорректен, то устанавливается UTL_FILE.INVALID_FILEHANDLE.
buffer	varchar2	Буфер, в который записывается строка. Если файл не был открыт на чтение (‘r’) то устанавливается UTL_FILE.INVALID_OPERATION.

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

ПАКЕТ DBMS_PIPE

В дополнение к средствам чтения таблиц БД и записи в эти таблицы в PL/SQL предлагаются два встроенных пакета, предназначенных для взаимодействия между соединениями, или сеансами (sessions): DBMS_PIPE и DBMS_ALERT. Их можно использовать для обмена сообщениями между сеансами, соединенными с одним и тем же экземпляром БД.

Посредством пакета DBMS_PIPE реализуются программные каналы БД (database pipe). Канал БД не зависит от операционной системы, и будет работать в любой среде, в которой функционирует ORACLE. Различные сеансы, соединенные с одним и тем же экземпляром ORACLE, могут посылать и получать сообщения по такому каналу. Канал могут использовать несколько сеансов, получающих сообщения, или получателей (readers), и несколько сеансов, посылающих сообщения, или отправителей (writers). При этом сеанс может посылать и получать несколько сообщений. Все эти сеансы могут находиться на разных машинах и работать в различных средах выполнения программ PL/SQL. Все, что требуется от сеансов, – соединиться с одним и тем же экземпляром ORACLE, а также иметь возможность выполнять блоки РL/SQL.

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

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

Посылка сообщений. Как уже указывалось выше, сообщения посылаются в два этапа. Сначала данные упаковываются в локальном буфере сообщений, а затем содержимое буфера посылается по каналу. Упаковка данных осуществляется с помощью процедуры РАСК_MESSAGE, а посылка – с помощью функции SEND_MESSAGE.

Процедура РАСК_MESSAGE переопределяется различными типами элементов данных. На принимающей стороне канала процедура UNPACK_MESSAGE также переопределяется различными типами. Описание процедуры РАСК_MESSAGE выглядит следующим образом:

procedure PACK_MESSAGE (item in varchar2);

procedure РАСК_MESSAGE (item in number);

procedure PACK_MESSAGE (item in date);

Размер буфера равен 4096 байтам. Если общий размер упакованных данных превышает это значение, то генерируется ошибка “ORA-6558: Buffer in DBMS_PIPE package is full. No more items allowed” (Буфер модуля DBMS_PIPE заполнен. Ввод других элементов запрещен). Для каждого элемента данных в буфере отводится 1 байт для типа данных, 2 байта – для их длины, 1 дополнительный байт для завершения сообщения, а также пространство для самих данных. В ORACLE 8 и выше 2 байта необходимы для идентификатора набора символов и 1 дополнительный байт для формы набора символов.

Когда после одного или нескольких вызовов РАСК_MESSAGE локальный буфер сообщений заполняется данными, его содержимое с помощью SEND_MESSAGE посылается по каналу:

function SEND_MESSAGE (pipename in varchar2, timeout in integer

default MAXWAIT,

maxpipesize in integer default 8192)

return integer;

Если программный канал еще не создан, то функция SEND_MESSAGE создает его. Каналы можно создавать и с помощью процедуры СRЕАТЕ_РIРЕ, которая применяется в РL/SQL версии 2.2 и выше. Параметры функции SEND_MESSAGE описаны в следующей таблице:

Таблица Параметры функции SEND_MESSAGE

Параметр	Тип	Описание
pipename	varchar2	Имя канала; имена каналов ограничены 30 символами и не чувствительны к регистру символов.
timeout	integer	Задержка в секундах. Если в силу различных причин (указанных возвращаемым кодом) сообщение не может быть послано, то вызов возвращает число секунд после задержки. Значение по умолчанию DBMS_PIPE.MAXWAIT равно 86 400 000 секундам (1000 дней).
maxpipesize	integer	Общий размер канала в байтах. По умолчанию устанавливается равным 8 192 байтам (два сообщения максимального размера). Сумма размеров всех сообщений в канале не может превышать этого значения (когда сообщение получается с помощью RECEIVE_MESSAGE, оно удаляется из канала). После создания канала его максимальный размер становится элементом его описания и сохраняется на все время его существования. В разных вызовах SEND_MESSAGE можно указывать разные значения параметра maxpipesize. Если новое значение больше существующего размера, то размер канала увеличивается. Если же новое значение меньше, то сохраняется существующий размер.

Значение, возвращаемые функцией SEND_MESSAGE, описаны в таблице.

Таблица Значение, возвращаемые функцией SEND_MESSAGE

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

Получение сообщений. Функции RECEIVE_MESSAGE и NEXT_ITEM_ТУРЕ и процедура UNPACK_MESSAGE пакета DBMS_PIPE используются для приема сообщений, посланных по каналу, а также для распаковки сообщений и превращения их в исходные элементы данных.

Функция RECEIVE_MESSAGE является обратной по отношению к функции SEND_MESSAGE. Она считывает сообщение из канала и помещает его в локальный буфер сообщений. После этого вызывается процедура UNPACK_MESSAGE, которая считывает данные уже из буфера. Описание функции RECEIVE_MESSAGE выглядит следующим образом:

function RECEIVE_MESSAGE (pipename in varchar2,

timeout in integer default MAXWAIT)

return integer;

Обычно RECEIVE_MESSAGE вызывается из принимающей программы. Если сообщения не ожидаются, то RECEIVE MESSAGE блокируется до тех пор, пока не считает сообщение. Таким образом, сеанс-получатель будет находиться в режиме ожидания до посылки сообщения по каналу. Принимающие программы похожи на процессы-демоны операционной системы. Демон также “засыпает” и “просыпается” только тогда, когда будет получено сообщение по программному каналу. Параметры функции RECEIVE_MESSAGE описаны в следующей таблице:

Таблица Параметры функции RECEIVE_MESSAGE

Параметр	Тип	Описание
pipename	varchar2	Имя канала. Оно должно быть таким же, как и указанное в SEND_MESSAGE. На имя канала налагаются те же ограничения (длина менее 30 символов, нечувствительность к регистру символов).
timeout	integer	Максимальное время ожидания сообщения в секундах. Как и в SEND_MESSAGE, значение по умолчанию – MAXWAIT (1000 дней). Если параметр timeout равен 0, то значение RECEIVE_MESSAGE возвращается немедленно с состоянием 0 (сообщение считано) или 1 (задержка).

Коды, возвращаемые функцией RECEIVE_MESSAGE, описаны в таблице.

Таблица Значения, возвращаемые функцией RECEIVE_MESSAGE

Возвращаемое значение	Описание
	Сообщение успешно получено. Оно было считано в локальный буфер и может быть распаковано с помощью UNPACK_MESSAGE.
	Задержка. За время ожидания RECEIVE_MESSAGE по каналу не было передано ни одного сообщения.
	Сообщение в канале слишком велико для буфера. Это внутренняя ошибка, которая не происходит в обычных условиях.
	Вызов прерван из-за внутренней ошибки.

Функция NEXT_ITEM_TYPE возвращает тип данных следующего элемента в буфере. На основании этого значения можно решить, в какую переменную написать данные. Если тип известен заранее, то вызывать ее необязательно. Функция NEXT_IТЕМ_ТУРЕ описывается следующим образом:

function NEXT_ITEM_TYPE return integer;

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

Таблица Возвращаемые коды функции NEXT_ITEM_TYPE

Возвращаемое значение	Описание
	Элементов в буфере больше нет
	Следующий элемент в буфере типа number
	Следующий элемент в буфере типа varchar2
	Следующий элемент в буфере типа date

Для распаковки принятых сообщений в модуле DBMS_PIPE предназначена процедура UNPACK_MESSAGE. Данная процедура является обратной по отношению к процедуре РАСК_MESSAGE. Подобно РАСК_MESSAGE, она переопределяется типами считываемых элементов. Описание процедуры UNPACK_MESSAGE выглядит следующим образом:

procedure UNPACK_MESSAGE (item out varchar2);

procedure UNPACK_MESSAGE (item out number);

procedure UNPACK_MESSAGE (item out date);

В ITEM записывается элемент данных из буфера. Если в буфере больше нет данных или если следующий элемент в буфере имеет не тот тип, который был запрошен, генерируется ошибка ORACLE ORA-6556 или ORA-6559. Перед установлением ошибки PL/SQL пытается преобразовать тип следующего элемента в нужный тип, используя формат преобразования, заданный по умолчанию.

Создание программных каналов и управление ими. Если канал не существует, то при первом указании его имени в SEND_MESSAGE он создается неявным образом. В PL/SQL версии 2.2 и выше каналы можно создавать и удалять явно, с помощью процедур CREATE_PIPE и REMOVE_PIPE.

Программные каналы. Программный канал представляет собой структуру данных, находящуюся в разделяемом пуле системной глобальной области (SGA – system global area) памяти. Поэтому он не занимает память, в которую могут помещаться другие объекты базы данных при их считывании с диска. Когда необходимо освободить дополнительное пространство разделяемого пула, каналы автоматически уничтожаются. Однако это касается только тех каналов, в которых не находятся сообщения, ожидающие считывания (хотя канал можно удалить из SGA и явным образом, с помощью процедуры PURGE, описанной ниже). Алгоритмом уничтожения программных каналов является алгоритм LRU (least recently used – использовавшийся раньше всех): уничтожается тот канал, который не использовался дольше всех. Максимальный размер канала и, следовательно, размер структуры данных в разделяемом пуле задается параметром MAXPIPESIZE функций SEND_MESSAGE и CREATE_PIPE.

Общие и частные каналы. Каналы, создаваемые неявно с помощью SEND_MESSAGE, называются общими (public). Любой пользователь, имеющий полномочие EXECUTE на модуль DBMS_PIPE и знающий имя канала, может посылать и принимать по нему сообщения. Доступ же к частным (private) каналам ограничивается пользователем, создавшим этот канал, хранимыми процедурами, выполняющимися на основании набора привилегий владельца этого канала, и пользователями, присоединившимися как SYSDBA или INTERNAL.

В PL/SQL версий 2.0 и 2.1 применяются только общие каналы, создаваемые неявно. В PL/SQL версии 2.2 и выше можно уже создавать каналы явно, используя для этого функцию CREATE_PIPE. Эта функция является единственным способом создания частного канала, однако при желании ей можно воспользоваться и для создания общего канала. Каналы, создаваемые посредством CREATE_PIPE, остаются в разделяемом пуле до тех пор, пока не будут явно удалены с помощью функции REMOVE_PIPE или пока экземпляр базы данных не будет остановлен. Такие каналы автоматически не уничтожаются в SGA. Описание CREATE_PIPE выглядит следующим образом:

function CREATE_PIPE (pipename in varchar2,

maxpipesize in integer default 8192,

private in boolean default true)

return integer;

Если канал успешно создан, CREATE_PIPE возвращает ноль. Если канал уже существует, а текущий пользователь имеет привилегии, необходимые для доступа к нему, то возвращается ноль и данные, находящиеся в канале, остаются без изменений. Если существует общий канал с указанным именем или частный канал, имеющий то же имя и принадлежащий другому пользователю, то устанавливается ошибка “ORA-23322: insufficient privilege to access pipe” (привилегии, недостаточные для доступа к каналу) и функция завершается неуспешно. Параметры этой функции описаны в следующей таблице:

Таблица Параметры функции CREATE_PIPE

Параметр	Тип	Описание
pipename	varchar2	Имя создаваемого канала. Имена каналов ограничены 30 символами. Имена, начинающиеся с ORA$, зарезервированы для внутреннего использования.
maxpipesize	integer	Максимальный размер канала в байтах. Этот же параметр используется в SEND_MESSAGE для неявного создания канала. Значение по умолчанию – 8 192 байта. Если SEND_MESSAGE вызывается со значением, превышающим MAXPIPESIZE, размер канала увеличивается. Если же SEND_MESSAGE вызывается со значением меньшим, чем MAXPIPESIZE, то сохраняется существующий размер.
private	boolean	TRUE для частного канала, в противном случае FALSE. Значение по умолчанию – TRUE. Общие каналы создаются неявно функцией SEND_MESSAGE, поэтому, как правило, не имеет смысла устанавливать для данного параметра значение FALSE.

Каналы, создаваемые явно функцией СRЕАТЕ_РIРЕ, удаляются с помощью функции REMOVE_PIPE. Если при этом в канале находятся сообщения, то они также удаляются. Эта функция – единственный способ удалить те каналы, которые были созданы явно. Описание функции REMOVE_PIPE выглядит следующим образом:

function REMOVE_PIPE (pipename in varchar2)

return integer;

Единственным параметром этой функции является имя удаляемого канала. Если канал существует, а текущий пользователь имеет на него привилегии, то канал удаляется и функция возвращает ноль. Если канал не существует, также возвращается ноль. Если канал существует, но текущий пользователь не имеет привилегий на доступ к нему, то устанавливается ошибка ORA-23322 (как и в функции CREATE_РIРЕ).

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

procedure PURGE (pipename in varchar2);

Привилегии и безопасность. Для модуля DBMS_PIPE установлены три различных уровня безопасности. Первым уровнем является привилегия EXECUTE на сам модуль. По умолчанию при создании модуля эта привилегия не предоставляется всем пользователям. Поэтому обращаться к модулю DBMS_PIPE смогут только те пользователи, которые имеют системную привилегию EXECUTE ANY PROCEDURE. Для того чтобы разрешить доступ к модулю DBMS_PIPE другим пользователям базы данных, нужно воспользоваться оператором GRANT и предоставить им привилегии EXECUTE на этот модуль.

Для обеспечения первого уровня безопасности информации привилегию EXECUTE на DBMS_PIPE можно предоставить только определенным пользователям базы данных. После этого можно создать свой собственный модуль для управления доступом к базовым каналам. Затем привилегию EXECUTE на этот модуль можно предоставить и другим пользователям. Второй уровень безопасности – имя программного канала. Если пользователи не знают этого имени, они не смогут посылать и принимать сообщения по данному каналу. Имя программного канала можно выбрать случайным образом или создать имя, уникальное для двух сеансов, взаимодействующих при помощи канала. Последний способ реализуется с помощью функции UNIQUE_SESSION_NAME и описан ниже.

Самым надежным уровнем безопасности является использование частных каналов. Частный канал доступен только пользователю, создавшему его, и пользователям, присоединившимся как SYSDBA или INTERNAL, поэтому доступ к каналу существенно ограничен. Даже если какой-либо пользователь имеет привилегию EXECUTE на модуль DBMS_PIPE и знает имя программного канала, при попытке доступа он получит сообщение об ошибке ORACLE: “ОRА-23322: insufficient privilege to access pipe” (привилегии, недостаточные для доступа к каналу). Данная ошибка возникает только при создании или удалении канала либо при попытке послать или получить сообщение. Другие вызовы в DBMS_PIPE не обращаются непосредственно к программным каналам.

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

Установление протокола связи. Программные каналы используются так же, как и другие низкоуровневые пакеты связи, например TCP/IP. Пользователь имеет возможность самостоятельно определить способы форматирования данных и их пересылки. Кроме того, пользователь может решить, кто должен получить сообщение. Поэтому сеансы отправители дабы воспользоваться гибкостью межсеансовых каналов должны следовать определенным рекомендациям по организации обмена информацией с сеансами-получателями. Прежде всего, между сеансами должны быть строго согласованы правила форматирования сообщений.

Форматирование сообщений. Каждое сообщение, посылаемое по программному каналу, состоит из одного или нескольких элементов данных. Элементы данных заносятся в буфер сообщений с помощью процедуры РАСК_MESSAGE, и после этого все содержимое буфера посылается по каналу с помощью функции SEND_MESSAGE. На другой стороне канала буфер принимается функцией RECEIVE_MESSAGE, а элементы данных считываются с помощью функции NEXT_ITEM_TYPE и процедуры UNPACK_MESSAGE.

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

create or replace trigger LogRSInserts

bеfore insert on registered_staff

for еасн row

declare

v_Status integer;

begin

/* Сначала упакуем в буфере описание операции. */

DBMS_PIPE.РАСК_MESSAGE (‘I’);

/* Упакуем информацию о текущем пользователе и временную метку.*/

DBMS_PIPE.PACK_MESSAGE (USER);

DBMS_PIPE.РАСК_MESSAGE (SYSDATE);

/* Упакуем новые значения.*/

DBMS_PIPE.PACK_MESSAGE (: new.fname);

DBMS_PIPE.PACK_MESSAGE (: new.lname);

DBMS_PIPE.PACK_MESSAGE (: new.tel_no);

DBMS_PIPE.PACK_MESSAGE (: new.address);

/* Пошлем сообщение по каналу ‘RSInserts’ */

v_Status:= DBMS_PIPE.SEND_MESSAGE (‘RSInserts’);

/*Если посылка неудачна, установим ошибку так, чтобы изменение не

было внесено*/

if v_Status!=0 then

raise_application_error (-20010, ‘LogRSInserts trigger’ ||

‘couldnot send the message, status =’ || v _ Status);

end if;

end LogRSInserts;

В зависимости от вида информации, в сообщении могут содержаться данные различных типов или разное количество элементов данных. Рекомендуется также помимо других инструкций включать в сообщение инструкцию STOP. Ее можно использовать, к примеру, для сообщения ожидающей программе о том, что нужно отсоединиться от базы данных и корректно завершить работу. Без такого сообщения ожидающая программа будет уничтожена операционной системой и/или базой данных, что нежелательно.

Адресация данных. Один и тот же канал могут использовать несколько получателей и отправителей, однако принять сообщение может только один получатель. Более того, какой именно получатель действительно примет сообщение, неизвестно. Поэтому рекомендуется адресовать сообщения конкретной программе-получателю. Это можно сделать, сгенерировав для канала уникальное имя, которое будет использоваться только двумя сеансами – одним получателем и одним отправителем. Для этого применяется функция UNIQUE_SESSION_NAME:

function UNIQUE_SESSION_NAME return varchar2;

Каждый вызов UNIQUE_SESSION_NAME возвращает строку символов, максимальная длина которой – 30 символов. Если эту функцию вызывает один и тот же сеанс базы данных, то всякий раз будет выдаваться одна и та же строка, уникальная среди всех сеансов, соединенных с базой данных в конкретный момент времени. Однако если сеанс отсоединяется, то его имя может быть позже задействовано другим сеансом.

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

Дата добавления: 2014-01-04