Компонент для вызова ХП
Для вызова ХП в технологии ADO используется компонент TADOStoredProc. Компонент позволяет передать входные параметры в вызываемую ХП, запустить на выполнение ХП на сервере БД, а также получить результаты выполнения ХП в виде выходных параметров, кода ошибки или результирующего набора данных.
В некоторых случаях можно использовать для вызова ХП и компоненты TADOQuery или TADOCommand, вводя в поля SQL или CommandText соответствующий оператор запуска процедуры (для MS SQL Server – это оператор EXEC). Однако такой подход не позволяет получить значения выходных параметров процедур и кода ошибки. Кроме того, при таком способе приходится учитывать особенности вызова ХП для каждой конкретной СУБД, в то время как компонент TADOStoredProc автоматически учитывает эти особенности.
Также как и компоненты TADOTable и TADOQuery, компонент TADOStoredProc является наследником базового класса TDataSet, т.е. является разновидностью набора данных. В связи с этим, ряд свойств и методов, присутствующих в компонентах TADOTable и TADOQuery, имеется и у компонента TADOStoredProc. К таковым, в частности, относятся:
• Connection – ссылка на компонент TADOConnection для подключения к БД, в которой хранится ХП;
• Active – при задании свойству значения true выполняет ХП и открывает набор данных, возвращаемый ХП. Не возвращает значения выходных параметров.
Однако есть и некоторые новые свойства и методы. К таким свойствам относятся:
• StoredProcName – имя ХП;
• Parameters – массив входных и выходных параметров ХП;
К основным методам относятся:
• ExecProc – выполняет ХП. Позволяет вернуть значения выходных параметров, но не возвращает результирующий набор данных.
Рассмотрим простейший пример использования компонента TADOStoredProc. Создадим в контексте БД Supply хранимую процедуру sp1, которая уменьшает цену всех товаров на 20%. Исходный текст ХП:
CREATE PROCEDURE sp1
AS
UPDATE Товары
SET Цена = Цена * 0.8
GO
Создадим приложение, позволяющее выполнить хранимую процедуру. Для этого необходимо:
• поместить на форму компоненты TADOConnection, TADOTable, TADOStoredProc, TDataSource, TDBGrid и TButton, задать им соответствующие имена, например, ADOConnection, ADOTableGoods, ADOStoredProc1, DataSourceGoods, DBGridGoods, ButtonExecSP1;
• настроить соединение с БД с помощью компонента ADOConnection (см. предыдущую работу);
• связать компоненты между собой. Для этого задать с помощью Object Inspector следующие свойства компонент:
DBGridGoods.DataSource = DataSourceGoods (связать сетку с источником);
DataSourceGoods.DataSet = ADOTableGoods (связать источник с таблицей);
ADOTableGoods.Connection = ADOConnection (связать таблицу с компонентом соединения с БД);
• задать свойство TableName компонента ADOTableGoods, выбрав имя таблицы, которая обновляется хранимой процедурой, т.е. Товары;
• разрешить «живые данные» для компонента ADOTableGoods, установив его свойство Active = true;
• связать компонент ADOStoredProc1 компонентом ADOConnection;
• задать свойство ProcedureName компонента StoredProc1, выбрав имя хранимой процедуры sp1;
• для кнопки ButtonExecSP1 написать обработчик, который при ее нажатии будет выполнять хранимую процедуру, и обновлять данные таблицы Товары в окне DBGridGoods.
Пример кода для Borland Delphi:
...
ADOStoredProc1.ExecProc;
ADOTableGoods.Refresh;
...
Метод Refresh позволяет обновить набор данных. Он может применяться к любому компоненту, наследующему свойства от TDataSet. В нашем случае он позволяет увидеть изменения, внесенные в таблицу Товары процедурой sp1.
• запустить приложение и убедиться в правильном выполнении хранимой процедуры (рисунок 1).
При нажатии на кнопку ButtonExecSP1 ХП sp1 выполняется, уменьшая цену всех товаров на 20%. Результаты обновления записей таблицы товаров можно наблюдать в сетке DBGridGoods.
Как уже отмечалось, компонент TADOStoredProc может вернуть в качестве результатов выполнения ХП набор данных. Для этого требуется вставить в тело ХП оператор SELECT. Результат выполнения оператора SELECT и будет результирующим набором данных.
Таким образом, можно рассмотренную выше задачу решить и другим способом. Вместо использования отдельного компонента TADOTable для просмотра результатов выполнения ХП будем использовать сам компонент TADOStoredProc. Для этого внесем в клиентское приложение следующие изменения:
• изменим процедуру sp1, добавив в нее оператор SELECT, возвращающий все записи таблицы Товары:
ALTER PROCEDURE sp1
AS
UPDATE Товары
SET Цена = Цена * 0.8
SELECT * FROM Товары
GO
• удалим компонент ADOTableGoods;
• компонент DataSourceGoods привяжем к компоненту ADOStoredProcSP1:
DataSourceGoods–>DataSet = ADOStoredProc1;
• обработчик для кнопки ButtonExecSP1 изменим следующим образом:
// Закрыть набор данных
ADOStoredProc1.Active := false;
// Выполнить ХП и открыть набор данных
ADOStoredProc1.Active := true;
Обратите внимание, что использовать метод ExecProc в данном случае нельзя, т.к. он не позволяет открыть результирующий набор данных. Если обработчик будет написан так, как приведено ниже, содержимое таблицы Товары не удастся увидеть в сетке:
...
ADOStoredProc1.ExecProc;
...
• откомпилируем и запустим проект. После нажатия на кнопку ButtonExecSP1 в окне приложения появятся результаты изменения записей таблицы Товары.
Особенности передачи параметров
Достаточно часто в ХП используются параметры. Они бывают следующих видов:
• входные – используются для задания исходных данных, необходимых для работы процедуры;
• выходные – возвращают результаты выполнения процедуры;
• входные-выходные – могут использоваться одновременно и для задания исходных данных, и для возврата результатов выполнения процедуры.
• код ошибки – возвращает целое число в диапазоне 0..255, позволяющее узнать, насколько успешно выполнена процедура. Если ошибок в выполнении ХП нет, обычно возвращается код 0. При наличии ошибок возвращаются те или иные коды отличные от нуля.
Назначение каждого кода приводится в описании каждой конкретной ХП.
Для работы с параметрами компонент TADOStoredProc имеет специальное свойство Parameters типа T Parameters, представляющее собой массив параметров. При этом свойство Parameters.Count содержит количество параметров процедуры.
На этапе разработки приложения при выборе требуемой процедуры с помощью свойства ProcedureName происходит автоматическое определение количества, названий, типов и видов параметров ХП. Соответственно, массив параметров Parameters автоматически заполняется.
Тип TParameters среди прочих содержит полезное свойство ParamValues, позволяющее обращаться к требуемому параметру по имени. Например:
ADOStoredProc2.Parameters.ParamValues['@Name'] := EditName.Text;
Здесь параметру с именем @Name присваивается значение, из поля ввода с именем EditName. Свойство ParamValues относится к вариантному типу, который позволяет автоматически преобразовывать к нужному типу строковые и числовые типы данных.
Рассмотрим пример, показывающий, каким образом могут передаваться входные параметры. Создадим процедуру sp2, позволяющую добавить запись о новом товаре в таблицу Товар. Исходный текст процедуры приведен ниже.
…
CREATE PROCEDURE sp2 (
@Code int, -- Код товара
@Name varchar(20), -- Наименование
@Firm varchar(20), -- Производитель
@Price int -- Цена
)
AS
-- Добавить новый товар
INSERT INTO Товары (КодТовара, Наименование, Производитель, Цена)
VALUES (@Code, @Name, @Firm, @Price)
GO
В процедуре используются четыре входных параметра. Обратите внимание, что для поля типа varchar необходимо явно указать длину поля. Например, varchar(20). Если длину поля не указать, то по умолчанию длина поля будет считаться равной 1, т.е. все равно, что написать varchar(1).
Создадим клиентское приложение, вызывающее ХП. Для этого необходимо:
• поместить на форму компоненты TADOConnection, TADOTable, TADOStoredProc, TDataSource, TDBGrid, TButton, задать им соответствующие имена, например, ADOConnection, ADOTableGoods, ADOStoredProc2, DataSourceGoods, DBGridGoods, ButtonExecSP2. Для ввода значений параметров также разместить четыре компонента TEdit (с именами TEditCode, TEditName, TEditFirm, TEditPrice). Изображение формы приведено на рисунке 2;
• настроить соединение с БД с помощью компонента ADOConnection (см. предыдущую работу);
• связать компоненты между собой по аналогии с предыдущим примером;
• для кнопки ButtonExecSP2 написать обработчик, который при ее нажатии будет передавать введенные исходные данные в хранимую процедуру, выполнять ее и обновлять данные таблицы Товары в сетке DBGridGoods.
Пример кода для Borland Delphi:
...
// Задать параметры процедуры
// Код товара
ADOStoredProc2. Parameters.ParamValues['@Code'] := EditCode.Text;
// Название товара
ADOStoredProc2. Parameters.ParamValues['@Name'] := EditName.Text;
// Производитель
ADOStoredProc2. Parameters.ParamValues['@Firm'] := EditFirm.Text;
// Цена
ADOStoredProc2.Parameters.ParamValues['@Price']:= EditPrice.Text;
// Выполнить процедуру
ADOStoredProc2.ExecProc;
// Обновить набор данных
ADOTableGoods.Refresh;
...
В приведенном примере сначала считываются значения из полей ввода и присваиваются соответствующим параметрам, затем процедура запускается на выполнение с помощью метода ExecProc.
• запустить приложение и убедиться в правильном выполнении хранимой процедуры. При нажатии на кнопку Выполнить в таблицу Товары будет добавляться новая запись со значениями атрибутов, указанными в соответствующих полях ввода. Новая запись должна сразу же появиться в сетке.
Теперь рассмотрим пример процедуры, использующей выходные параметры. Создадим процедуру, позволяющую узнать количество записей в таблице Поставки. Исходный текст процедуры приведен ниже.
CREATE PROCEDURE sp3 (@s int output) AS
SELECT @s = COUNT(*)
FROM Поставки
GO
В процедуре используется один выходной параметр @s, предназначенный для возврата найденного количества записей. Обратите внимание, что выходной параметр должен выть описан с помощью ключевого слова OUTPUT. С помощью операторов SELECT или SET выходному параметру должно быть присвоено результирующее значение.
Напомним, что программе на языке T-SQL хранимая процедура с выходными параметрами вызывается следующим образом:
-- Описать переменную,
-- в которую будет помещено значение выходного параметра
DECLARE @TotalCount INT
-- Выполнить процедуру
EXEC sp3 @TotalCount OUTPUT
-- Вывести результат на экран
PRINT @TotalCount
Создадим клиентское приложение, вызывающее ХП. Для этого необходимо:
• поместить на форму компоненты TADOConnection, TADOTable, TADOStoredProc, TDataSource, TDBGrid, TButton, TEdit и задать им соответствующие имена, например, ADOConnection, ADOTableSupply, ADOStoredProc3, DataSourceSupply, DBGridSupply, ButtonExecSP3, EditTotalCount.;
• настроить соединение с БД с помощью компонента ADOConnection (см. предыдущую работу);
• связать компоненты между собой по аналогии с предыдущими примерами;
• для кнопки ButtonExecSP3 написать обработчик, который при ее нажатии будет выполнять хранимую процедуру и передавать значение выходного параметра в поле EditTotalCount.
Пример кода для Borland Delphi:
...
// Выполнить процедуру
ADOStoredProc3.ExecProc;
// Считать значение выходного параметра
EditTotalCount.Text := ADOStoredProc3.Parameters.ParamValues['@s'];
...
• запустить приложение и убедиться в правильном выполнении хранимой процедуры. При нажатии на кнопку Выполнить в поле ввода будет отображаться количество записей в таблице Поставки (рисунок 3).
Рисунок 3
Результат работы ХП может быть также возвращен в виде кода ошибки. В языке T-SQL код ошибки задается с помощью оператора RETURN, завершающего выполнение ХП, например:
...
// Завершить процедуру, вернув код ошибки 7
RETURN 7
...
Или
...
// Завершить процедуру, вернув код ошибки из переменной @err
RETURN @err
...
В клиентском приложении код ошибки доступен в компоненте TADOStoredProc в виде параметра с именем @RETURN_VALUE. В следующем примере вызывается некоторая ХП, код ошибки которой считывается в поле EditError:
...
// Выполнить процедуру
ADOStoredProc4.ExecProc;
// Считать значение кода ошибки
EditError.Text :=
ADOStoredProc4.Parameters.ParamValues['@RETURN_VALUE'];
...
Таким образом, код ошибки можно рассматривать как особый вид выходного параметра. В массиве Parameters коду ошибки всегда соответствует элемент с индексом 0.
|
Главная
Каталог файлов
