18c0693f

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



Хранимые процедуры (stored procedures) представляют собой приложения, которые хранятся в базе
данных. Обычно, в среде клиент-сервер конечные приложения располагаются на клиентской
машине и там же выполняются. Любой доступ к базе данных из конечного приложения использует
контакт с сервером по компьютерной сети. Когда же приложение располагается внутри базы
данных, оно называется хранимой процедурой. Хранимые процедуры выполняются
непосредственно на компьютере сервера базы данных.

SQLBase использует хранимые процедуры, написанные на языке инструмента разработки
конечных приложений SQLWindows Application Language (SAL).

Использование хранимых процедур преследует следующие цели:


В SQLBase имеется возможность хранения двух типов процедур.


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

Хранимые процедуры и хранимые команды SQLBase создаются с помощью команды STORE языка SQL или при помощи функции sqlsto() SQL/API.

Тело хранимой процедуры состоит из следующих элементов или разделов:


Секция описания переменных хранимой процедуры состоит из двух блоков: блока параметров
(Parameters) и блока локальных переменных (Local Variables).

Блок параметров содержит переменные, которые передают информацию в хранимую процедуру
или возвращают информацию из нее в вызывающее процедуру приложение. Блок параметров
эквивалентен списку bind-переменных в SQL запросе.


Хранимые процедуры SQLBase могут
содержать параметры двух типов: для ввода и для вывода. Параметры вывода обозначаются
ключевым словом RECEIVE перед описанием типа переменной. Эти параметры эквивалентны
списку into-переменных в запросах SELECT.

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


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

ON PROCEDURE STARTUP - Эта секция используется для процедур инициализации,
таких как установление контакта с базой данных, компиляция запросов и т.д. Она выполняется
только во время первого выполнения хранимой процедуры.

ON PROCEDURE EXECUTE - Данная секция выполняется всякий раз, когда хранимая
процедура выполняется командой EXECUTE. Во время первого выполнения эта секция
выполняется после секции ON PROCEDURE STARTUP. Во время всех последующих команд
EXECUTE выполняется только эта секция.

ON PROCEDURE FETCH - Эта секция выполняется всякий раз, когда процедуре
передается команда FETCH в случае использования процедуры в качестве result set (не забудьте,
что хранимые процедуры ведут себя подобно запросам SELECT). При этом строки из множества
результатов возвращаются из процедуры посредством переменных типа RECEIVE.

ON PROCEDURE CLOSE - Данная секция выполняется в тот момент, когда для курсора,
связанного с хранимой процедурой, происходит компиляция нового запроса или этот курсор
отсоединяется от базы данных.


Она используется для выполнения операций завершения, таких как
выпуск транзакции COMMIT, отсоединение от базы данных и т.д.

Если процедура не содержит описанных выше секций, по умолчанию предполагается, что она
состоит из одной секции ON PROCEDURE EXECUTE.

Хранимая процедура может быть определена как статическая или динамическая с помощью
ключевых слов STATIC/DYNAMIC после имени процедуры. По умолчанию, хранимые процедуры
SQLBase являются динамическими. Если процедура определяется как статическая, все SQL запросы
в ней должны быть предварительно скомпилированы и записаны в базу данных в виде хранимых
команд. Поэтому в статических процедурах все SQL запросы должны быть представлены в виде
строковых констант и не могут содержать переменных. Кроме того, в статических процедурах не
допускается использование команд типа CREATE, ALTER, DROP. Статические процедуры
обладают гораздо более высокой производительностью по сравнению с динамическими, поскольку
не требуют компиляции своих запросов во время выполнения.

Доступ пользователей к хранимым процедурам

Для того, чтобы выполнить хранимую процедуру, пользователь должен обладать определенными
привилегиями. Он может иметь право выполнить процедуру либо с привилегиями автора (execute
with creator privilege), либо со своими привилегиями (execute with grantee privilege). Если
пользователь выполняет процедуру с правами автора, он получает все его права по отношению к
объектам базы данных на время выполнения процедуры. При втором способе выполнения
изменения прав пользователя не происходит. Использование возможности расширения (изменения)
прав пользователя на время выполнения хранимой процедуры позволяет повысить защищенность
данных и канализировать доступ к ним через отработанные и проверенные процедуры.

Получение кодов ошибок из хранимых процедур

Процедура указывает на нормальное завершение возвращая нулевое значение в вызвавшее ее
приложение.

Если процедура возвращает ненулевое значение, оно рассматривается как ошибка.


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

Пример хранимой процедуры

Ниже приведен пример хранимой процедуры SQLBase, иллюстрирующей некоторые моменты,
описанные в данной статье. Эта процедура обновляет баланс по счету AccountNum в соответствии
с выплаченной суммой nAmount и возвращает новое значение баланса в переменной nNewBalance.
Если при этой операции происходит овердрафт, процедура устанавливает флаг bOverDrawn.

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

Procedure Withdraw
Parameters
Number: nAccount
Number: nAmount
Receive Number: nNewBalance
Receive Boolean: nOverDrawn
Local Variables
Sql Handle: hSelect
Sql Handle: hUpdate
String: sSelect
String: sUpdate
Number: nStatus
Actions
On Procedure Startup
Set sSelect = 'Select Balance from Checking where
AccountNum=:nAccount' \
'into :nNewBalance'
Set sUpdate = 'Update Checking Set Balance = Balance -
:nAmount' \
'where AccountNum = :nAccount'
Call SqlConnect( hSelect )
Call SqlPrepare( hSelect, sSelect )
Call SqlConnect( hUpdate )
Call SqlPrepare( hUpdate, sUpdate )
On Procedure Execute
Call SqlExecute( hSelect )
Call SqlFetchNext (hSelect, nStatus )
Set nNewBalance = nNewBalance - nAmount
If ( nNewBalance < 0 )
Set bOverDrawn = TRUE
Else
Set bOverDrawn = FALSE
Call SqlExecute( hSelect )
On Procedure Close
Call SqlDisconnect( hSelect )
Call SqlDisconnect( hUpdate )