Нариман Курбанов
дата публикации 22-08-2006 03:56

---

Версия для печати

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

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]

• 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]

• ID - это уникальное поле-идентификатор с IDENTITY (автоувеличение значения на единицу).
• GoodsName - название проданного товара
• QuantitySold - количество проданного товара
• SoldDate - дата проданного товара
• SellerID - внешний ключ к таблице 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 ('Альберт Игнатов')

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)

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

    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;

Самая первая задача, это соединиться с сервером из нашего приложения. Для этого нам понадобиться файл с расширением ".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

//процедура для получения списка работников
procedure TForm1.SelectSellers();
begin
  with DataModule1.ADOStoredProc1 do
   begin
    // закрываем
    Close;
    // присваиваем Connection
    Connection := DataModule1.ADOConnection1;
    // указываем имя хранимой процедуры на сервере
    ProcedureName := 'pSelectSellers';
    // открываем датасет
    Open;
    // переводим Connection в Nil
    Connection := Nil;
   end;
end;

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

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;

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) обновление от 8/22/2006 3:59:00 AM