Хранимые процедуры
Хранимые процедуры (stored procedures) представляют собой приложения, которые хранятся в базе
данных. Обычно, в среде клиент-сервер конечные приложения располагаются на клиентской
машине и там же выполняются. Любой доступ к базе данных из конечного приложения использует
контакт с сервером по компьютерной сети. Когда же приложение располагается внутри базы
данных, оно называется хранимой процедурой. Хранимые процедуры выполняются
непосредственно на компьютере сервера базы данных.
SQLBase использует хранимые процедуры, написанные на языке инструмента разработки
конечных приложений SQLWindows Application Language (SAL).
Использование хранимых процедур преследует следующие цели:
- Повышение производительности - Так как прикладная программа
перенесена на сервер, она не требует затрат на передачу информации по сети
при обращении к базе данных. Кроме того, сервер обычно бывает более
мощным компьютером, чем клиентские машины. Следовательно, те же
операции выполняются на сервере быстрее.
- Простота использования - Обычно, технология клиент-сервер
подразумевает большое количество клиентов, подключенным к одному
серверу. Использование хранимых процедур позволяет хранить приложение на
одном компьютере, а не на каждом клиенте в отдельности. Если приложение в
базе данных модифицируется, оно становится сразу доступно всем клиентам в
наиболее обновленном виде.
- Усиление защиты данных - Пользователь может получить возможность
доступа к данным только через использование заранее запрограммированных
процедур и не иметь прямой возможности изменять данные. Следовательно,
набор операций, которые могут быть осуществлены пользователем, легко
контролируются с помощью управления доступом к небольшому числу
хранимых процедур.
- Ускорение процесса освоения - Хранимые процедуры SQLBase пишутся на
языке SAL. Этот язык хорошо знаком разработчикам приложений на продуктах
Gupta. Кроме того, разработчики могут создавать и отлаживать прототипы
хранимых процедур в среде SQLWindows и затем переносить их в базу данных
с помощью программы SQLConsole.
В SQLBase имеется возможность хранения двух типов процедур.
- Хранимые команды - это SQL запрос, который хранится на сервере в
скомпилированном виде (то есть вместе со своим планом выполнения).
Выполнение хранимых команд производиться гораздо быстрее, чем обычных
SQL запросов, поскольку целый ряд шагов, необходимых для выполнения
запроса, производится в момент записи хранимой команды в базу данных. В то
же время хранимые команды имеют ограниченную функциональность в рамках
синтаксиса языка SQL.
- Хранимые процедуры - это приложения, объединяющие SQL запросы и
процедурную логику (операторы присваивания, логического ветвления и т.д.).
Хранимые процедуры позволяют хранить на сервере сложные приложения,
выполняющие большой объем работы без передачи данных по сети и
взаимодействия с клиентом.
Создание хранимых процедур
Хранимые процедуры и хранимые команды SQLBase создаются с помощью команды STORE языка SQL или при помощи функции sqlsto() SQL/API.
Тело хранимой процедуры состоит из следующих элементов или разделов:
- Имя процедуры. Является идентификатором процедуры и
используется при извлечении (retrieve) процедуры из базы данных перед ее
выполнением.
- Переменные. SQLBase поддерживает 5 типов данных в качестве
переменных хранимых процедур. Все они идентичны типам данных
SQLWindows с теми же именами.
а - Number - Числовой тип данных
а - Boolean - Логический тип данных с допустимыми значениями
TRUE/FALSE
а - String - Строковый тип данных
а - Date/Time - Этот тип может быть использован для данных типа
Дата, Время или Дата/Время
а - Sql Handle - используется в качестве курсоров для доступа к
базе данных
Секция описания переменных хранимой процедуры состоит из двух блоков: блока параметров
(Parameters) и блока локальных переменных (Local Variables).
Блок параметров содержит переменные, которые передают информацию в хранимую процедуру
или возвращают информацию из нее в вызывающее процедуру приложение. Блок параметров
эквивалентен списку bind-переменных в SQL запросе.
Хранимые процедуры SQLBase могут
содержать параметры двух типов: для ввода и для вывода. Параметры вывода обозначаются
ключевым словом RECEIVE перед описанием типа переменной. Эти параметры эквивалентны
списку into-переменных в запросах SELECT.
Блок локальных переменных содержит описания переменных, которые используются только
внутри данной хранимой процедуры и не могут быть определены извне.
- Процедурная логика. Этот раздел начинается с ключевого слова
ACTIONS. Он содержит всю логику и выполняемые инструкции процедуры.
Весь код этого раздела пишется на языке SAL. Хранимые процедуры SQLBase
поддерживают некоторое подмножество полного языка SAL. Например, в
текущей версии SQLBase реализована поддержка только тех функций SAL,
которые начинаются с префикса Sql ( SqlPrepare()). Поддержка
остальных функций (начинающихся с префикса Sal) будет реализована в
последующих версиях продукта.
Раздел процедурной логики может также состоять из следующих необязательных секций:
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 )
