Варіант передачі параметра в збережену процедуру

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

У цій статті ми розглянемо можливість передачі параметрів в збережену процедуру, яка використовує оператор IN. Виклад матеріалу буде вестися на базі тестового прикладу, який ми будемо обговорювати по ходу опису даної статті.


Використовується: СУБД MSSQL 2000, Delphi7, ADO.
Отже, почнемо …
Поставимо перед собою завдання:



  1. Створення тестових табличок на сервері.
  2. Створення зручного (ІМХО) і зрозумілого інтерфейсу для користувача.
  3. Створення збережених процедур на сервері.

Для прикладу, можна створити дві таблиці на сервері. Перша таблиця – Sellers(Продавці), друга SoldGoods(Продані товари), які будуть пов'язані між собою по полю Seller.IDSoldGoods.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 – Таблиця, в якій будемо зберігати імена продавців.
Поля:

– Створюємо таблицю 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 – Таблиця в якій будемо зберігати інформацію про проданий товар
Поля:


Тепер, для наочності, заповнимо обидві таблиці вибірковими даними.
Спочатку таблицю 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)

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*