Использование хранимых процедур
Структурная схема терминов
Понятие и назначение хранимых процедур
Хранимая процедура представляет собой подпрограмму, расположенную на сервере и вызываемую из приложения клиента.
Использование хранимых процедур увеличивает скорость доступа к БД, т.к.:
Вместо текста запроса, который может быть длинным, по сети передается короткое обращение к хранимой процедуре.
Хранимая процедура, в отличие от запроса, не требует предварительной синтаксической проверки.
Еще одно преимущество состоит в том, что хранимые процедуры являются общими для всех приложений-клиентов и реализуют единые для них правила работы с БД.
Для выполнения хранимой на сервере процедуры используется компонент Delphi TStoredProc.
Хранимая процедура создается с помощью оператора:
CREATE PROCEDURE <Имя процедуры>
[(<Список входных параметров>)]
[RETURNS(<Список выходных параметров>)]
AS <Тело процедуры>
Необязательный список входных параметров обеспечивает передачу исходных данных в процедуру. Для каждого параметра указывается его имя и тип через пробел. Разделителем в списке параметров служит",".
При задании имен процедур и параметров, как и других объектов, целесообразно использовать мнемонические правила, например, имя процедуры можно начинать с префикса P, имя входного параметра можно начинать с IP, а имя выходного параметра – с букв OP. При использовании параметра в выражениях в теле процедуры перед его именем нужно ставить знак":".
Тело процедуры состоит из двух частей – описательной и исполнительной – и имеет следующий формат:
[<Объявление переменных>]
Begin <оператор>
[<оператор>]
End
В описательной части объявляются переменные, используемые внутри процедуры. Они являются локальными и по окончании работы процедуры теряют свои значения. В исполнительной части располагаются операторы, выполняющие необходимые действия.
Операторы располагаются между ключевыми словами BEGIN, END. В теле процедуры должен содержаться как минимум один оператор.
Созданную хранимую процедуру можно удалить или изменить.
Удаление хранимой процедуры выполняется с помощью оператора:
DROP PROCEDURE<имя процедуры>
Изменение процедуры производится оператором:
ALTER PROCEDURE <имя процедуры>
[(<Список входных параметров>)]
[RETURNS(<Список выходных параметров>)]
AS <Тело процедуры>
Последний оператор имеет тот же формат, что и оператор CREATE PROCEDURE. Он заменяет предыдущее описание хранимой процедуры на новое описание.
Язык хранимых процедур
Для написания хранимых процедур и триггеров используется специальный язык хранимых процедур.
Язык хранимых процедур сервера InterBase представляет собой процедурный алгоритмический язык, синтаксис которого похож на Pascal, но он обладает рядом возможностей SQL.
Язык хранимых процедур включает следующие операторы:
объявление переменных;
присваивание;
условные операторы;
составные;
цикла;
выбор записи;
выбор нескольких записей;
возврат значений;
выход из процедуры;
вызов процедуры;
посылка сообщения.
Операторы должны заканчиваться ";", кроме составного оператора.
Операторы объявления переменных имеют следующий формат
DECLARE VARIABLE <ИМЯ> <ТИП>;
Допустимыми типами являются типы столбцов InterBase.
Оператор присваивания имеет вид
<имя переменной> = <выражение>;
При выполнении этого оператора вычисляется значение выражения. Переменная и выражение должны иметь совместимые типы.
В качестве операндов в выражении можно использовать значение <имена переменных>, <имена параметров>, <встроенные и пользовательские функции>, а также <генераторы>.
Условный оператор имеет следующий формат
IF (<условие>) THEN <опер1>
[ELSE<опер2>];
Условный оператор может включать составной оператор, т.е. группу операторов, заключенных в операторные скобки BEGIN и END.
Оператор цикла имеет структуру
WHILE (<условие>) DO <оператор>
Оператор выбора записи представляет собой оператор SELECT, дополненный операндом INTO. При своем выполнении оператор выбирает несколько строк, но возвращает только одну, поэтому его называют также оператором выбора строки.
SELECT поля from <имена таблиц>
[WHERE <условие>] [Group By <условие>]
[ORDER By <поле>]
INTO:<имя>,…:[<имя>];
Имя после":" указывает переменную или выходной параметр, которым должны быть присвоены значения.
Этот оператор часто используется со статистическими функциями, например, AVG, SUM.
Пример
CREATE PROCEDURE pSalary
RETURNS (opSUM Float, opAVG Float)
AS
Begin
SELECT SUM (Salary), AVG (Salary) From Personal
INTO :opSUM, :opAVG;
End
Создается хранимая процедура, в которой для сотрудников организации подсчитывается сумма заработной платы и средняя заработная плата. Полученные значения присваиваются выходным параметрам. Входных параметров процедура не имеет.
Оператор выбора нескольких записей также представляет собой оператор SELECT, способный возвращать несколько записей (в данном случае – ни одной).
FOR <оператор выбора записи> DO <оператор>;
Может задавать не только отбор, но и обработку записей. После отбора записей согласно оператору SELECT для каждой из них выполняется оператор, указанный после слова DO.
Пример
CREATE PROCEDURE pCountCod
RETURN (opSUM INTEGER)
AS
DECLARE VARIABLE n Integer;
DECLARE VARIABLE x Integer;
BEGIN
X=0;
OR SELECT Code From List
NTO n
DO x=x+n;
opSUM = x;
END
Создается хранимая процедура pCountCod, которая вычисляет сумму значений целочисленного столбца Cod из таблицы List. Оператор SELECT обеспечивает отбор записей, а оператор присваивания увеличивает значение суммы X. После отбора всех записей результат возвращается через выходной параметр opSUM.
Часто после слова DO указывается оператор SUSPEND возврата значений, который передает в вызывающее приложение или хранимую процедуру значения выходных параметров и имеет вид:
SUSPEND;
Оператор POST_EVENT предназначен для посылки сообщений приложениям, связанным с сервером. Сообщение посылается при возникновении определенного события.
POST_EVENT "<ИМЯ СОБЫТИЯ>";
Виды хранимых процедур
По числу строк, возвращаемых в качестве результата, можно выделить следующие виды хранимых процедур:
возвращающие одну строку;
возвращающие несколько строк.
Процедуры, возвращающие одну строку, практически ничем не отличаются от процедур языка Pascal и обеспечивают возврат значений выходных параметров. Такие хранимые процедуры называют процедурами действия.
Процедуры, возвращающие несколько строк, передают набор данных, записями в котором являются строки результатов. Такие хранимые процедуры называют также процедурами выбора. В их теле размещаются совместно используемые операторы выбора нескольких записей и возврата значений – FOR SELECT … DO … SUSPEND, которые обеспечивают отбор требуемых записей и построчную передачу значений их столбцов в точку вызова.
Вызов хранимой процедуры представляет собой обращение к процедуре с передачей исходных данных для обработки и последующим получением результатов. В зависимости от вида хранимой процедуры различают способы ее вызова.
Вызов хранимой процедуры выбора
При вызове такой процедуры необходимо обеспечить возможность приема совокупности записей. Для этого вызов производится с помощью оператора select.
Пример
SELECT * FROM pSalary(2000, 3000);
В приложении оператор select, вызывающий хранимую процедуру, выполняется с помощью набора данных Query, для чего используется свойство SQL.
Пример
Procedure Tform1.Button1Click(Sender:Tobject);
Begin
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('SELECT * FROM pSalary(2000, 3000)');
Query1.Open;
End;
При нажатии на кнопку Button1 свойству SQL компонента Query1 присваивается код вызова хранимой процедуры, а затем выполняется запрос.
Вызов хранимой процедуры действия
Хранимая процедура действия вызывается оператором следующего формата:
EXECUTE PROCEDURE <Имя процедуры>
[(<Список входных параметров>)]
Для вызова хранимой процедуры действия из приложения предназначен компонент TStoredproc. Рассмотрим его свойства:
DatabaseName указывает компонент Database, используемый для соединения с БД, аналогично Query.
StoredProcName определяет вызываемую хранимую процедуру. Имя процедуры выбирается из списка в инспекторе объектов.
Params – параметры хранимой процедуры становятся доступными после выбора ее имени.
Выполнение выбранной хранимой процедуры осуществляется последовательным вызовом методов Prepare и Execproc.
Prepare подготавливает хранимую процедуру к выполнению путем связывания параметров компонента Storedproc и параметров процедуры.
Execproc выполняет процедуру.
Пример
Procedure.TForm1.Button1Click(Sender: Tobject);
Begin
Storedproc1. StoredprocName:=' pSalary';
Storedproc1.Prepare;
Storedproc1.ExecProc;
Edit1.text:= Storedproc1.ParamByName('opSum').Value;
Edit2.text:= Storedproc1.ParamByName('opAvg').Value;
End;