Главная arrow В помощь студентам arrow Работа с базами данных arrow Вариант передачи параметра в хранимую процедуру, использующую оператор IN  
01.12.2021 г.
Вариант передачи параметра в хранимую процедуру, использующую оператор IN Печать E-mail
Автор Administrator   
13.02.2009 г.

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

В этой статье мы рассмотрим возможность передачи параметров в хранимую процедуру, использующую оператор 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)

 
« Пред.   След. »

Ivanovo State University of Chemical Technology has entered into an academic partnership with Visual Paradigm to better facilitate the teaching of software design & modeling through the use of Visual Paradigm.
Enterprise Architect
Sparx Systems Enterprise Arctitect provides Ivanovo State University of Chemical Technology with Enterprise Architect, Eclipse Integration, Visual Studio Integration, SysML Technology, Zachman Framework and much more for use in educational purposes, offered by the Enterprise Architect Academic Site License.