Автор: Нариман Курбанов, Королевство 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)