Delphi - сбориник статей
       

Вариант передачи параметра в хранимую процедуру, использующую оператор IN


Нариман Курбанов (_MaSteR_NN_),

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

Используется: СУБД MSSQL 2000, Delphi7, ADO.
Итак, начнём:
Поставим перед собой задачи:
  1. Создание тестовых табличек на сервере.
  2. Создание удобного (ИМХО) и понятного интерфейса для пользователя.
  3. Создание хранимых процедур на сервере.

Для примера, можно создать две таблицы на сервере. Первая таблица - Sellers(продавцы), вторая SoldGoods(проданные товары), которые будут связаны между собой по полю Seller.ID - SoldGoods.SellerID

--Создаём таблицу Sellers

CREATE TABLE [dbo].[Sellers] ( [ID] [bigint] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL, [SellerName] [Nvarchar] (300) COLLATE Cyrillic_General_CI_AS NULL ) ON [PRIMARY] Sellers - таблица, в которой будем хранить имена продавцов.
Поля:
  • ID - это уникальное поле-идентификатор с IDENTITY (автоувеличение значения на единицу).
  • SellerName - поле в котором будет храниться имя продавца.
--Создаём таблицу SoldGoods CREATE TABLE [dbo].[SoldGoods] ( [ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL, [GoodsName] [Nvarchar] (300) COLLATE Cyrillic_General_CI_AS NULL, [QuantitySold] [Float] NULL, [SoldDate] [datetime] NULL, [SellerID] [bigint] NOT NULL ) ON [PRIMARY] SoldGoods - таблица в которой будем хранить информацию о проданном товаре
Поля:
  • ID - это уникальное поле-идентификатор с IDENTITY (автоувеличение значения на единицу).
  • GoodsName - название проданного товара
  • QuantitySold - количество проданного товара
  • SoldDate - дата проданного товара
  • SellerID - внешний ключ к таблице Sellers. (в котором хранится уникальный номер продавца)
Теперь, для наглядности, заполним обе таблицы выборочными данными.
Сначала таблицу Sellers. INSERT INTO [Sellers] ([SellerName]) VALUES ('Дмитрий Олегович') INSERT INTO [Sellers] ([SellerName]) VALUES ('Антон Насыров') INSERT INTO [Sellers] ([SellerName]) VALUES ('Олег Арсеньев') INSERT INTO [Sellers] ([SellerName]) VALUES ('Алексей Логинов') INSERT INTO [Sellers] ([SellerName]) VALUES ('Альберт Игнатов') Затем таблицу SoldGoods. INSERT INTO [SoldGoods] ([GoodsName], [QuantitySold], [SoldDate], [SellerID]) VALUES ('Материнская плата', 5, '20060101', 1) INSERT INTO [SoldGoods] ([GoodsName], [QuantitySold], [SoldDate], [SellerID]) VALUES ('Видеокарта', 16, '20060108', 1) INSERT INTO [SoldGoods] ([GoodsName], [QuantitySold], [SoldDate], [SellerID]) VALUES ('Монитор', 4, '20060206', 1) INSERT INTO [SoldGoods] ([GoodsName], [QuantitySold], [SoldDate], [SellerID]) VALUES ('Сетевая плата', 8, '20060206', 1) INSERT INTO [SoldGoods] ([GoodsName], [QuantitySold], [SoldDate], [SellerID]) VALUES ('Материнская плата', 6, '20060103', 2) INSERT INTO [SoldGoods] ([GoodsName], [QuantitySold], [SoldDate], [SellerID]) VALUES ('Монитор', 9, '20060103', 2) INSERT INTO [SoldGoods] ([GoodsName], [QuantitySold], [SoldDate], [SellerID]) VALUES ('Сетевая плата', 14, '20060106', 2) INSERT INTO [SoldGoods] ([GoodsName], [QuantitySold], [SoldDate], [SellerID]) VALUES ('Видеокарта', 7, '20060102', 3) INSERT INTO [SoldGoods] ([GoodsName], [QuantitySold], [SoldDate], [SellerID]) VALUES ('Материнская плата', 6, '20060109', 3) INSERT INTO [SoldGoods] ([GoodsName], [QuantitySold], [SoldDate], [SellerID]) VALUES ('Монитор', 1, '20060115', 3) INSERT INTO [SoldGoods] ([GoodsName], [QuantitySold], [SoldDate], [SellerID]) VALUES ('Сетевая плата', 30, '20060120', 3) INSERT INTO [SoldGoods] ([GoodsName], [QuantitySold], [SoldDate], [SellerID]) VALUES ('Видеокарта', 14, '20060106', 4) INSERT INTO [SoldGoods] ([GoodsName], [QuantitySold], [SoldDate], [SellerID]) VALUES ('Материнская плата', 4, '20060106', 4) INSERT INTO [SoldGoods] ([GoodsName], [QuantitySold], [SoldDate], [SellerID]) VALUES ('Монитор', 5, '20060202', 5) INSERT INTO [SoldGoods] ([GoodsName], [QuantitySold], [SoldDate], [SellerID]) VALUES ('Сетевая плата', 19, '20060105', 5)

С созданием и заполнением таблиц на сервере закончили. Приступим к разработке клиентской части.

Клиентское приложение будет иметь две формы и один DataModule.

Положим на главную (первую) форму список (TCheckListBox), в котором будут выбираться продавцы.
Теперь добавим новую форму (форма для показа отчёта) в проект, и положим на неё сетку (TDBGrid), в которую будут выводиться результаты выборки.
Так же создадим DataModule1: TDataModule и положим на него следующие компоненты: ADOConnection1: TADOConnection; ADOStoredProc1: TADOStoredProc; DataSource1: TDataSource; ADOStoredProc2: TADOStoredProc; DataSource2: TDataSource; ADOQuery1: TADOQuery; DataSource3: TDataSource; Получим результат такого вида:

Настройка компонентов: ADOConnection1.LoginPrompt := False; DataSource1.DataSet := ADOStoredProc1; DataSource2.DataSet := ADOStoredProc2; DataSource3.DataSet := ADOQuery1; Form2.DBGrid1.DataSource := DataModule1.DataSource2; Первая (главная) форма должна использовать (uses) DataModule и Form2
Вторая (форма отчёта) форма должна использовать (uses) DataModule

"Каркас" нашего приложения готов!

Самая первая задача, это соединиться с сервером из нашего приложения. Для этого нам понадобиться файл с расширением ".udl", назовём его "Connect.udl". (Создайте файл в директории с исходным кодом). При запуске этого файла должно появиться окно:

Тут мы и настраиваем соединение с сервером. Затем в обработчике события создания формы напишем код для соединения с сервером:

procedure TForm1.FormCreate(Sender: TObject); begin //БУДЬТЕ ВНИМАТЕЛЬНЫ, СНАЧАЛА ДОЛЖЕН СОЗДАВАТЬСЯ DataModule1 // закрываем Коннект с базой DataModule1.ADOConnection1.Close; // указываем файл .udl для ADOConnection1 DataModule1.ADOConnection1.ConnectionString := 'FILE NAME='+GetCurrentDir+'\Connect.udl'; // Указываем провайдера, в данном случае возьмём его из файла .udl DataModule1.ADOConnection1.Provider := 'FILE NAME='+GetCurrentDir+'\Connect.udl'; // Открываем Коннект DataModule1.ADOConnection1.Open; end;

Как видим, в коде имеется предупреждение вида: "БУДЬТЕ ВНИМАТЕЛЬНЫ, СНАЧАЛА ДОЛЖЕН СОЗДАВАТЬСЯ DataModule1". Это означает, что в проекте перед созданием главной формы должен создаваться DataModule1. Для этого нужно нажать сочетание клавиш CTRL+SHIFT+F11 и в разделе Auto-Create Forms DataModule1 должен стоять первым. И уберите и списка Form2, эту форму будем создавать динамически.

На данный момент, мы уже имеем процедуру соединения с сервером. (Скомпилируйте и запустите проект, если нет ошибок, продолжаем далее).

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

Начнём с хранимой процедуры на сервере: CREATE PROCEDURE [dbo].[pSelectSellers] AS --выбираем все из таблицы продавцов SELECT * FROM SELLERS GO Затем процедура на клиенте (Все процедуры создаются в главном модуле Form1):
//процедура для получения списка работников procedure TForm1.SelectSellers(); begin with DataModule1.ADOStoredProc1 do begin // закрываем Close; // присваиваем Connection Connection := DataModule1.ADOConnection1; // указываем имя хранимой процедуры на сервере ProcedureName := 'pSelectSellers'; // открываем датасет Open; // переводим Connection в Nil Connection := Nil; end; end;

Таким образом, в дальнейшем мы получим список продавцов в датасет. Приступим к заполнению CheckListBox1.

На событие TForm1.FormShow "вешаем": procedure TForm1.FormShow(Sender: TObject); begin // запускаем процедуру получения списка работников (см. выше), // и получаем список продавцов в датасет SelectSellers(); // очищаем список CheckListBox1 CheckListBox1.Items.Clear; // ставим курсор датасета на первую запись DataModule1.ADOStoredProc1.First; try // начало изменений в списке CheckListBox1 CheckListBox1.Items.BeginUpdate; // цикл - пока не достигли конца записей ADOStoredProc1 while not DataModule1.ADOStoredProc1.Eof do begin { заполняем CheckListBox1 именами из таблицы Sellers В параметре AObject : TObject будем хранить значение ID-поля таблицы Sellers} CheckListBox1.Items.AddObject( DataModule1.ADOStoredProc1.fieldbyname('SellerName').AsString, pointer(DataModule1.ADOStoredProc1.fieldbyname('ID').AsInteger)); // переводим курсор датасета на следующую запись DataModule1.ADOStoredProc1.Next; Application.ProcessMessages; end; finally // конец изменений в списке CheckListBox1 CheckListBox1.Items.EndUpdate; end; end;

Мы в цикле заполнили Items.AddObject нашего CheckListBox1, где в первом параметре Const S мы храним имена продавцов, а в AObject храним поле ID, которое будем получать так: Integer(CheckListBox1.Items.Objects[i]);

На данный момент результат должен быть таков, запускаем проект и получаем


Главная форма: список продавцов.

Наш вариант передачи параметра будет работать по следующему принципу.

  1. Мы получим список продавцов с сервера из таблицы Sellers(имена и идентификаторы).
  2. Затем в клиентском приложении выберем (отметим), нужные имена продавцов,
  3. Создадим временную таблицу на сервере.
  4. В цикле заполним её идентификаторами выбранных продавцов, и будем использовать эту таблицу в запросе на выборку отчёта.
Решения:

Список продавцов получен. (См. выше) Для создания, удаления временной таблицы и выбора отчёта в приложении создадим три отдельные процедуры. Первая процедура из вышеописанных будет "для создания временной таблицы".

{процедура для создания временной таблицы на сервере} procedure TForm1.CreateTempTable(); begin with DataModule1.ADOQuery1 do begin // закрываем Close; // присваиваем Connection Connection := DataModule1.ADOConnection1; // создаём запрос на создание временной таблицы на сервере SQL.Text := 'CREATE TABLE #TEMP(NUM INT)'; // открываем датасет ExecSQL; // переводим Connection в Nil Connection := Nil; end; end;

Тем самым, вызвав эту процедуру, сервер будет создавать временную табличку под названием #TEMP.

Следующая процедура будет "для удаления временной таблицы". {процедура для удаления временной таблицы на сервере} procedure TForm1.DeleteTempTable(); begin with DataModule1.ADOQuery1 do begin // закрываем Close; // присваиваем Connection Connection := DataModule1.ADOConnection1; // создаём запрос на удаление временной таблицы на сервере SQL.Text := 'DROP TABLE #TEMP'; // открываем датасет ExecSQL; // переводим Connection в Nil Connection := Nil; end; end; И последняя третья процедура на запуск хранимой процедуры на сервере для выбора отчёта. {процедура для получения отчёта} procedure TForm1.SelectReport(); begin with DataModule1.ADOStoredProc2 do begin // закрываем Close; // присваиваем Connection Connection := DataModule1.ADOConnection1; // указываем имя хранимой процедуры на сервере ProcedureName := 'pSelectReport'; // обновляем параметры процедуры Parameters.Refresh; // открываем датасет Open; // переводим Connection в Nil Connection := Nil; end; end; Данная процедура будет запускать на сервере хранимую процедуру под названием pSelectReport, которой у нас пока нет. Создадим её: CREATE PROCEDURE [dbo].[pSelectReport] AS --выбираем данные из таблиц "Продавцы(SELLERS)" и "Проданные товары(SOLDGOODS)" --при помощи оператора IN в котором будем указывать(выбирать) идентификаторы из таблицы #TEMP SELECT S.ID AS SELLERID, S.SELLERNAME, SG.GOODSNAME, SG.QUANTITYSOLD, SOLDDATE FROM SELLERS S LEFT JOIN SOLDGOODS SG ON SG.SELLERID = S.ID WHERE S.ID IN (SELECT NUM FROM #TEMP) GO

Примечание: как мы видим, данная процедура использует параметр IN, в котором мы задаём выборку идентификаторов из таблицы #TEMP.

Совет: Так же можно использовать оператор JOIN. Например:

SELECT S.ID AS SELLERID, S.SELLERNAME, SG.GOODSNAME, SG.QUANTITYSOLD, SOLDDATE FROM SELLERS S LEFT JOIN SOLDGOODS SG ON SG.SELLERID = S.ID INNER JOIN #TEMP T ON S.ID = T.NUM При более сложных запросах, данная конструкция будет более оптимальна.

Итак, мы имеем процедуры на создание и удаление временной таблицы, выбора отчёта, выбора продавцов. Порядок их запуска должен быть примерно таков:

  1. Запускаем процедуру на создание временной таблицы.
  2. Заполняем её в цикле идентификаторами.
  3. Запускаем процедуру на выборку отчёта.
  4. Запускаем процедуру на удаление временной таблицы.

Приступим к написанию основной процедуры в обработчике события нажатия кнопки Button1, которая расположена на Form1.

procedure TForm1.Button1Click(Sender: TObject); var i : integer; begin TRY //начинаем транзакцию DataModule1.ADOConnection1.BeginTrans; //Запускаем процедуру создающую временную таблицу на сервере (см.выше) CreateTempTable(); // создаём цикл - до конца записей в списке CheckListBox1 for i := 0 to CheckListBox1.Items.Count-1 do begin // если текущий Item в CheckListBox1 находится в состоянии Checked if CheckListBox1.State[i] = cbChecked then begin with DataModule1.ADOQuery1 do begin // закрываем Close; // присваиваем Connection Connection := DataModule1.ADOConnection1; // создаём запрос на заполнение временной таблицы на сервере SQL.Text := 'INSERT INTO #TEMP VALUES (:NUM)'; Parameters.ParamByName('NUM').Value := IntToStr(Integer(CheckListBox1.Items.Objects[i])); // открываем датасет ExecSQL; // переводим Connection в Nil Connection := Nil; end; CheckListBox1.Selected[i]; end; end; // запускаем процедуру формирования отчёта (см. выше) SelectReport(); //Удаляем временную таблицу на сервере (см.выше) DeleteTempTable(); //завершаем транзакцию DataModule1.ADOConnection1.CommitTrans; //создаём форму отчёта Application.CreateForm(TForm2, Form2); // показываем её в модальном режиме Form2.ShowModal; EXCEPT //при ошибке, откатываем транзакцию DataModule1.ADOConnection1.RollbackTrans; // показываем диалог ошибки MessageDlg('Ошибка при формировании отчёта.', mtError, [mbRetry], 0); END; end; Попробуем "разобрать" данную процедуру.

Сначала мы начали транзакцию и создали временную таблицу на сервере. Затем создали цикл, который "проходит" по всем записям, хранящимся в CheckListBox1, выбирая из параметра AObject уникальный идентификатор каждого отмеченного продавца, и помещаем его в таблицу #TEMP. Далее запускаем процедуру формирования отчёта, в которой, используя, оператор IN, мы задействуем уже существующую и заполненную временную таблицу #TEMP. Выбрав отчёт, мы удаляем временную таблицу, запустив процедуру на её удаление. И последнее. Завершаем транзакцию и показываем форму отчёта.

Примечание: Наша транзакция расположена в блоке TRY : EXCEPT : END; это означает что при возникновении ошибки, она автоматически произведёт откат изменений методом RollbackTrans, и покажет MessageDlg. Иначе транзакция будет успешно завершена методом CommitTrans.

Результат формирования отчёта должен быть таков:


Полученный отчёт.

На этом мы и закончим рассмотрение нашего тестового примера.

К статье прилагается пример с описанием. Для работы примера, необходимо подключить базу (в папке DB) или создать свою, и настроить параметры соединения в файле Connect.udl(в папке Sources)

Проект, используемый в качестве примера (119 K) allGray">



Содержание раздела