2014-02-24
794
Хранимая процедура представляет собой подпрограмму, расположенную на сервере и вызываемую из приложения клиента. Использование этих объектов увеличивает скорость доступа к БД по следующим причинам:
- вместо текста запроса, который может быть достаточно длинным, серверу передается по сети относительно короткое обращение к хранимой процедуре;
- хранимая процедура, в отличие от запроса, не требует предварительной синтаксической проверки.
Хранимая процедура создается инструкцией:
CREATE PROCEDURE
<Имя процедуры>[(<Список входных параметров>)]
[RETURNS (<Список выходных параметров>)]
AS <Тело процедуры>
После имени процедуры идет необязательный список входных параметров, с помощью которых из приложения в процедуру передаются исходные данные. С помощью выходных параметров в приложения передаются результаты выполнения процедуры.
Тело процедуры состоит из двух частей – описательной и исполнительной. В описательной части объявляются переменные, используемые внутри процедуры. Эти переменные являются локальными и по окончании работы процедуры теряют свои значения. В исполнительной части (между BEGIN и END) располагаются инструкции, выполняющие необходимые действия.
|
|
|
[<Объявление переменных>]
BEGIN
<Инструкция>
…
[<Инструкция>]
END
Удаление процедуры выполняется командой:
DROP PROCEDURE <Имя процедуры>
Изменение процедуры выполняется командой:
ALTER PROCEDURE
Виды хранимых процедур
По числу строк, возвращаемых в качестве результата, можно выделить следующие виды хранимых процедур:
- возвращает одну строку – процедура действия,
- возвращает несколько строк – процедура выбора.
Примеры создания процедур:
Инструкция выбора записи отбирает несколько строк, но возвращает только одну:
CREATE PROCEDURE pSalary //(желательно называть процедуру с префикса р)
RETURNS (opSum FLOAT, opAvg FLOAT) // (для выходных параметров префикс ор)
AS
BEGIN
SELECT SUM (Salary), AVG (Salary)
FROM Personnel
INTO: opSum,: opAvg;
END
Создается хранимая процедура, в которой для сотрудников организации (таблица Personnel) подсчитывается общая сумма зарплаты и средняя зарплата. Полученные значения присваиваются выходным параметрам opSum и opAvg, которые возвращают полученные значения в вызывающую программу. Входных параметров у процедуры нет.
Инструкция, возвращающая набор данных:
CREATE PROCEDURE pSalary2 (ipSalaryMin FLOAT, ipSalaryMax FLOAT) //(префикс ip для
входных параметров)
RETURNS (opName VARCHAR(20), opSalary FLOAT)
AS
BEGIN
FOR SELECT Name, Salary
FROM Personnel
WHERE Salary >=: ipSalaryMin
AND Salary <=: ipSalaryMax
INTO: opName,: opSalary
DO SUSPEND; // (инструкция используется для возврата в приложение
набора данных)
END
Создается хранимая процедура pSalary2, которая возвращает набор данных. Совокупность записей этого набора образуют записи, соответствующие сотрудникам организации, у которых оклад принадлежит заданному диапазону. Границы диапазона определяют входные параметры ipSalaryMin и ipSalaryMax. Набор данных содержит столбцы Name и Salary.
|
|
|
Вызов хранимой процедуры выбора
В приложении хранимая процедура выбора вызывается с помощью набора данных Query, для чего используется его свойство SQL.
Пример
procedure TForm.Button1Click (Sender: TObject);
begin
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add ('SELECT * FROM pSalary2 (2000, 3000)');
Query1.Open;
end;
Пример динамического запроса
Query1.SQL.Add ('SELECT * FROM pSalary2 (' + Edit1.Text + ',' + Edit2.Text + ')');
Вызов хранимой процедуры действия
Процедуру можно выполнить в программе IBConsole.
Для вызова хранимой процедуры действия из приложения предназначен компонент StoredProc.
Свойства компонента:
StoredProcName – имя вызываемой хранимой процедуры;
Params – массив параметров компонента StoredProc;
ParamBindMode – определяет, каким образом устанавливается соответствие между параметрами компонента StoredProc и параметрами процедуры:
- pbByName – устанавливается соответствие по именам;
- pbByNumber – соответствие по номерам – первый параметр компонента соотносится с первым параметром процедуры.
Методы компонента:
Prepare – подготавливает хранимую процедуру к выполнению путем связывания параметров компонента и параметров процедуры;
ExecProc – выполняет процедуру.
Пример:
procedure TForm.Button1Click (Sender: TObject);
begin
StoredProc1. StoredProcName:= 'pSalary';
StoredProc1. Prepare;
StoredProc1. ExecProc;
Edit1.Text:= StoredProc1.ParamByName ('opSum').Value;
Edit2.Text:= StoredProc1.ParamByName ('opAvg').Value;
end;
Вызывается процедура pSalary. Ее результаты - значения выходных параметров opSum и opAvg – выводятся в компонентах Edit1 и Edit2.
