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

Нариман Курбанов

Автор: Нариман Курбанов, Королевство Delphi

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

Используется: СУБД MSSQL 2000, Delphi7, ADO.
Итак, начнём…
Поставим перед собой задачи:

Создание тестовых табличек на сервере.
Создание удобного (ИМХО) и понятного интерфейса для пользователя.
Создание хранимых процедур на сервере.

Для примера, можно создать две таблицы на сервере. Первая таблица - 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]);

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

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

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

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

Решения:

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

{процедура для создания временной таблицы на сервере}
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

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

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

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

Приступим к написанию основной процедуры в обработчике события нажатия кнопки 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)

Ссылки по теме