Иптышев А.А. Курс лекций по дисциплине базы данных

Подождите немного. Документ загружается.

131

При использовании для ввода команды Script-файла необходимо пом-

нить, что в соответствии с синтаксисом операторы внутри процедуры отде-

ляются друг от друга символом ";". В то же время команды в Script-файле

также отделяются ";"• Чтобы устранить неоднозначность разделе команд, не-

обходимо установить какой-либо иной ограничитель текста команд. Для это-

го следует непосредственно перед заданием команды CREATE PROCEDURE

выполнить команду SET TERM, устанавливающую такой ограничитель, а в

конце работы той же командой SET TERM восстановить стандартный (";")•

Синтаксис команды CREATE PROCEDURE:

CREATE PROCEDURE name [(LIST_<params>)]

[RETURNS (LIST_<params>)]

<procedure__body> ;

LIST_<params> : : = param datatype

<procedure_body> :: = [ <variable_declaration_list>J

<block>

<variable_declaration_list> ;:=

DECLARE VARIABLE var datatype;

[<variable_declaration_list>]

< block> ::=

BEGIN

<L_statement>

END

<L_statement> ;;= < compound_statement>

[<L_statement>]

< compound_statement> = {< block> / statement;}

132

Таблица 18.

Синтаксические конструкции команды CREATE PROCEDURE

Конструкция Описание

name Имя процедуры. Должно быть уникальным

среди имен процедур , таблиц и обзоров в

базе данных

param <datatype> Параметр процедуры, передаваемый ей вы-

зывающими приложениями, param - имя

параметра, уникальное среди имен пара-

метров и переменных процедуры,

<datatype> - допустимый в InterBase тип

данных.

RETURNS param <datatype> Выходные значения (параметры)

процедуры, возвращаемые ею вызывающим

приложениям, param - имя выходного пара-

метра, уникальное среди имен параметров и

переменных процедуры, <datatype> - допус-

тимый в InterBase тип данных. Процедура

возвращает значения выходных пара-

метров вызывающим приложениям по дос-

тижении ее конца или по команде

SUSPEND в теле процедуры.

AS Ключевое слово, отделяющее заголовок

процедуры от ее тела.

DECLARE VARIABLE var <datatype> Объявляет локальные переменные, исполь-

зуемые в процедуре. Каждое объявление

должно начинаться с ключевых слов

DECLARE VARIABLE и заканчиваться ";".

Один оператор объявляет одну перемен-

ную, var - имя переменной, уникальное сре-

ди имен параметров и переменных проце-

дуры. <datatype> - допустимый в InterBase

тип данных.

statement Любой допустимый на языке процедур и

триггеров оператор. Оператор должен

оканчиваться символом ";" (кроме опера-

торных скобок BEGINEND)

Процедура, которая возвращает список книг, генерирует по таблице ав-

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

CREATE PROCEDURE PBUTHOR(CODE INTEGER)

133

RETURNS (AUTHORS VARCHAR(250))

declare variable auname varchar(60);

declare variable UNIKEY integer;

declare variable ws integer;

begin

ws=-l;

for select a.UNIKEY, b.auname

from tbook a, tauthor b, tbook_author с

where (a.unikey=:Code and a.unikey=c.bookkey and

с.author=b.author)

into :UNIKEY, :auname

begin

if(ws=-l) then authors=auname;

else authors=authors||', '||auname;

WS=UNIKEY;

end

if(ws!=-l) then suspend;

end;

16.2 Изменение хранимых процедур

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

PROCEDURE. Команда ALTER PROCEDURE выполняется в ISQL. Во вне-

дренном SQL команда недоступна.

ALTER PROCEDURE изменяет описание хранимой процедуры при ус-

ловии сохранения корректности ссылок на нее со стороны объектов базы

данных. Изменения, внесенные в хранимую процедуру, сразу становятся дос-

тупными для всех использующих ее приложений. Перекомпиляция соответ-

ствующих приложений не требуется.

Команда ALTER PROCEDURE может быть выполнена создателем

процедуры, пользователем SYSDBA или другим пользователем с аналогич-

ными правами.

134

При изменении хранимой процедуры новое описание замещает суще-

ствующее. Необходимо помнить, что изменение состава, последовательности

и типов входных и выходных параметров процедур, как и интерфейса при-

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

зывающих программах.

На практике можно рекомендовать предварительно выбрать сущест-

вующую хранимую процедуру, затем внести в нее изменения и загрузить ко-

мандой AFTER PROCEDURE. Конкретная последовательность действий за-

висит от того, какие средства используются для работы с базой.

Синтаксис команды ALTER PROCEDURE аналогичен синтаксису ко-

манды CREATE PROCEDURE:

ALTER PROCEDURE name [(LIST_<params>)]

[RETURNS (LIST_<params>)]

<procedure_body>;

name - имя существующей хранимой процедуры.

Состав параметров и опций команды полностью аналогичен парамет-

рам и опциям команды CREATE PROCEDURE (см. разд. Создание

хранимых процедур).

ALTER PROCEDURE PBUTHOR(CODE INTEGER)

RETURNS (AUTHORS VARCHAR(250))

declare variable auname varchar(60);

declare variable UNIKEY integer;

declare variable ws integer;

begin

ws=-l;

for select a.UNIKEY, b.auname

from tbook a, tauthor b, tbook_author с

where (a.unikey=:Code and a.unikey=c.bookkey and

с.author=b.author)

into :UNIKEY, :auname

begin

if(ws=-l) then authors=auname;

135

else authors=authors||' - '||auname;

/* в исходном тексте разделителем в списке

была запятая */

ws=UNIKEY;

end

if(ws!=-l) then suspend;

end;

16.3 Удаление хранимых процедур

Удаление хранимых процедур осуществляется командой DROP

PROCEDURE, которая выполняется в ISQL. Во внедренном SQL команда не-

доступна.

Команда DROP PROCEDURE удаляет существующую в базе данных

процедуру, и может быть выполнена создателем процедуры, пользователем

SYSDBA или другим пользователем с аналогичными правами. Для успешно-

го выполнения команды DROP PROCEDURE необходимо выполнение ряда

условий. Нельзя удалить процедуру, если она используется другими проце-

дурами, триггерами или обзорами базы данных.

Перед удалением процедуры необходимо внести соответствующие из-

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

процедуру, если она используется незавершенной транзакцией. Необ-

ходимо завершить транзакцию и только после этого удалить процедуру.

Команда DROP PROCEDURE недоступна во внедренном SQL, необхо-

димо использовать динамический SQL.

Синтаксис команды:

DROP PROCEDURE name;

name - имя существующей процедуры.

DROP PROCEDURE PBUTHOR;

16.4 Использование команд Alter Procedure и Drop Procedure

При внесении изменений в процедуры или их удалении необходимо

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

136

объектами базы. В этом случае их изменение должно быть запрещено во из-

бежание разрушения логической целостности базы.

Если процедура используется внешним приложением, то целостность

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

чем вносить изменения, необходимо убедиться в отсутствии нежелательных

последствий от таких изменений.

Изменения желательно проводить, когда с базой не работают приложе-

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

с какой именно версией процедуры работают приложения.

При выполнении запросов на работу процедуры последняя помещается

в кэш метаданных и остается там до завершения запроса. Изменения в про-

цедуре становятся видимыми на клиенте после его отсоединения и повторно-

го соединения с базой.

16.4 Обращение к выполнимой процедуре

При обращении к выполнимой процедуре из другой выполнимой про-

цедуры или триггера используется следующий синтаксис:

EXECUTE PROCEDURE name [LIST_: paramj

/•RETURNING_VALUES [LIST_: par am ];

name - имя процедуры,

param - входной параметр или возвращаемое значение.

Символ «:» здесь является синтаксическим элементом.

При обращении к выполнимой процедуре из ISQL используется сле-

дующий синтаксис.

EXECUTE PROCEDURE name [ ( ] [LIST_param] [ ) ];

name - имя процедуры,

param - входной параметр.

ОБРАЩЕНИЕ К ПРОЦЕДУРЕ ВЫБОРА

При обращении к процедуре выбора используется следующий синтак-

сис:

SELECT < col_list> from name (LIST param)

[WHERE < search_condition>]

[ORDER BY < order_list>];

name - имя процедуры,

137

param - входной параметр процедуры,

< col_list> - список выбираемых выражений (должен базироваться на

списке возвращаемых значений процедуры),

<search_condition>, < order_list> - условия выборки и способ упорядо-

чения результатов.

В целом структура команды SELECT не зависит от того, каким образом

выбираются данные (см. разд. 3.1). Синтаксически использование процедуры

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

могут указываться ее параметры.

138

3.РАЗДЕЛ 2. ОПТИМИЗАЦИЯ РАБОТЫ И СРЕДСТВА

РАСШИРЕНИЯ БД

Тема 17. Оптимизация работы и средства расширения БД

Средством расширения БД являются пользовательские функции позво-

ляющие расширит базовую функциональность БД. Пользовательские функ-

ции (User defined functions - UDF) - это программы на базовом языке для вы-

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

дач. UDF помогают обеспечить модульность приложений, выделяя их в от-

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

К UDF и фильтрам BLOB можно обращаться через isql или из про-

грамм на базовом языке. К UDF можно также обращаться из хранимых про-

цедур и триггеров.

Пользовательские функции не являются частью базы. Они помещаются

в отдельную библиотеку (DLL), находящуюся на той же машине, что и база

данных. Чтобы сделать функции доступными в базе, их надо в ней объявить.

После этого обращение к ним осуществляется точно так же, как к стандарт-

ным функциям InterBase. Другими словами, вызов этих функций может про-

изводиться везде, где синтаксисом SQL предусмотрено использование выра-

жений.

Чтобы можно было использовать внешнюю функцию, она должна быть

объявлена в базе как внешняя функция, а реализующий ее программный код

помещен в библиотеку, указанную в объявлении.

17.1 Объявление внешней функции

Как только UDF была написана и откомпилирована в библиотеку, она

должна быть объявлена в базе данных, где предполагается ее использовать.

Для этого предусмотрена команда DELCARE EXTERNAL FUNCTION.

Каждая функция в базе данных должна быть объявлена отдельно. Пока

точка входа и имя модуля (библиотеки) и путь к нему не изменяется, нет не-

обходимости в переобъявлении функции, даже если сама функция изменяет-

ся.

Синтаксис объявления внешней функции следующий:

139

DECLARE EXTERNAL FUNCTION name

[\LIST_datatypes]

[RETURNS (datatype [BY VALUE] / CSTRING ( int)] [FREE_IT]

ENTRY_POINT 'entryname'

MODULE_NAME 'moduIename';

datatypes ; : = {datatype / CSTRING ( i n t )}

datatype - любой разрешенный в InterBase тип данных кроме массивов и

BLOB.

CSTRING ( int) - специальный тип для представления строк, представ-

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

FREE_IT - специальная конструкция, обеспечивающая указывающая на

необходимость освобождения памяти выделенной пользовательской функци-

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

такой конструкции вызвана тем, что запрос памяти производится в UDF , а

освобождение должно выполнятся в InterBase, о чем, конечно, должно быть

известно заранее.

Пример объявления функции:

DECLARE EXTERNAL FUNCTION IIF

DOUBLE PRECISION, INTEGER, INTEGER

RETURNS INTEGER BY VALUE

ENTRY_POINT "if_i"

MODULE_NAME "func.dll";

Объявлена функция в именем IIF, в нее передается три параметра один

типа DOUBLE PRECISION и два типа INTEGER, функция возвращает пара-

метр по значению типа INTEGER. Точка входа в процедуре имеет имя «if_i»,

процедура расположена в библиотеке dll c именем func.dll.

140

Таблица 19

Синтаксические конструкции объявления внешних функций

Конструкция

Описание

name

Имя UDF для использования в командах

SQL; может отличаться от имени функции

(в библиотеке) указанной после ключевого

слова ENTRYPOINT

datatype

Тип данных входного параметра или воз-

вращаемого значения.

Все входные параметры передаются UDF

по ссылке.

Возвращаемое значения может передавать-

ся как по ссылке, так и по значению.

Использование элементов массива запре-

щено.

RETURNS

Определяет возвращаемое функцией значе-

ние

BY VALUE

Указывает, что возврат производится по

значению. Если конструкция опущена, то

возврат производится по ссылке

CSTRING (int)

Указывает, что UDF возвращает строку, за-

канчивающуюся ограничителем

FREE_IT Освобождает память, занятую возвращае-

мым значением после выполнения UDF.

Использовать только, если память в UDF

выделялась динамически.

'entryname'

Заключенное в кавычки имя UDF, как оно

записано в библиотеке

'modulename'

Спецификация файла, идентифицирующая

библиотеку, которая содержит UDF. Текст

должен заключаться в кавычках.