Delphi.FireDAC. Выполнение хранимых процедур и функций

Данная статья посвящена выполнению хранимых процедур в FireDAC. Основой для данной статьи стала официальная документация.

-старая версия

-новая версия

Класс TFDStoredProc может быть использован как designtime так и в runtime. TFDStoredProc генерирует SQL команду для вызова хранимой процедуры, базирующейся на свойствах TFDStoredProc. Перед исполнением, FireDAC отправляет значения параметров к DBMS, и после этого исполняет хранимую процедуру и получает output параметры.

Замечание. FireDAC не поддерживает параметры со значениями по умолчанию.

Настройка хранимых процедур в Design Time

Чтобы выполнить хранимую процедуру, необходимо бросить компонент TFDStoredProc на форму. Свойство TFDStoredProc.Connection будет автоматически настроено, если на форме есть FDConnection.

Опционально можно установить CatalogName, SchemaName, и PackageName или выбрать их значения из выпадающего списка. После установки StoredProcName и когда fiMeta включено в FetchOptions.Items, коллекция параметров заполняется автоматически.

Чтобы унифицировать имена параметров, можно установить ResourceOptions.UnifyParams в True. Например, это исключит префикс ‘@’ из имен параметров SQL сервера.

Настройка хранимых процедур в RunTime

Аналогично design-time можно использовать следующий код для run-time

FDStoredProc1.StoredProcName := 'my_proc';
FDStoredProc1.Prepare;
// now the Params collection is filled in

FDStoredProc1.StoredProcName := 'my_proc';

FDStoredProc1.Prepare;

// now the Params collection is filled in

Вызов метода Prepare заполняет коллекцию параметров, используя mkProcArgs мета данные, когда fiMeta включено в FetchOptions.Items

При этом

1) Запрос с использованием mkProcArgs может быть затратным по времени.

2) Приложение может не может менять определения параметров после того как они заполнены. Например, выражение TFDParam.AsXxxx неявно устанавливает тип данных параметра.

Чтобы избежать вышеприведенных случаев, исключите fiMeta из FetchOptions.Items, таким образом коллекция параметров не будет перестраиваться автоматически при вызове Prepare. Также, можно заполнять коллекции параметров, перед вызовом Prepare или ExexProc, используя следующий код

FDStoredProc1.StoredProcName := 'my_proc';
FDStoredProc1.FetchOptions.Items := FDStoredProc1.FetchOptions.Items - [fiMeta];
FDStoredProc1.Command.FillParams(FDStoredProc1.Params);

FDStoredProc1.StoredProcName := 'my_proc';

FDStoredProc1.FetchOptions.Items := FDStoredProc1.FetchOptions.Items - [fiMeta];

FDStoredProc1.Command.FillParams(FDStoredProc1.Params);

Или другой пример

FDStoredProc1.StoredProcName := 'my_proc';
FDStoredProc1.FetchOptions.Items := FDStoredProc1.FetchOptions.Items - [fiMeta];
with FDStorecProc1.Params do begin
  Clear;
  with Add do begin
    Name := 'Par1';
    ParamType := ptInput;
    DataType := ftString;
    Size := 50;
  end;
  with Add do begin
    Name := 'Par2';
    ParamType := ptOutput;
    DataType := ftInteger;
  end;
end;

FDStoredProc1.StoredProcName := 'my_proc';

FDStoredProc1.FetchOptions.Items := FDStoredProc1.FetchOptions.Items - [fiMeta];

with FDStorecProc1.Params do begin

Clear;

with Add do begin

Name := 'Par1';

ParamType := ptInput;

DataType := ftString;

Size := 50;

end;

with Add do begin

Name := 'Par2';

ParamType := ptOutput;

DataType := ftInteger;

end;

“Запакованные процедуры” (Using Packaged Procedures)

Этот раздел пропущу так как в MySQL нет аналога Packaged Procedures

Выполнение хранимой процедуры

Если процедура не возвращает множество

Для хранимой процедуры, которая не возвращает множество, необходимо использовать метод ExecProc. Чтобы использовать хранимую функцию, используйте методы ExecProc или ExecFunc methods, где ExecFunc возвращает значение функции. Если хранимая процедура возвращает множество, мы получим ошибку “[FireDAC][Phys][Oracl]-310. Cannot execute command returning result sets”.

Примеры использования

FDStoredProc1.StoredProcName := 'MY_PROC';
FDStoredProc1.Prepare;
FDStoredProc1.Params[0].Value := 100;
FDStoredProc1.Params[1].Value := 'audi';
FDStoredProc1.ExecProc;

FDStoredProc1.StoredProcName := 'MY_PROC';

FDStoredProc1.Prepare;

FDStoredProc1.Params[0].Value := 100;

FDStoredProc1.Params[1].Value := 'audi';

FDStoredProc1.ExecProc;

или более компактно

FDStoredProc1.ExecProc('MY_PROC', [100, 'audi']);

1	FDStoredProc1.ExecProc('MY_PROC', [100, 'audi']);

Если процедура возвращает множество

Чтобы использовать хранимую процедуру, возвращающую множество, используйте методы Open. Если хранимая процедура не возвращает множество при использовании метода Open, то мы получим ошибку [FireDAC][Phys][Oracl]-308. Cannot open / define command, which does not return result sets. Если хранимая процедура возвращает несколько множеств, то можно посмотреть раздел Command Batches

Пример использования

FDStoredProc1.StoredProcName:='array_db.SELECT_ALL';
DataSource1.DataSet:=FDStoredProc1;
FDStoredProc1.Open(); // <<< Например так

FDStoredProc1.StoredProcName:='array_db.SELECT_ALL';

DataSource1.DataSet:=FDStoredProc1;

FDStoredProc1.Open(); // <<< Например так

Использование TFDQuery

Главное различие между TFDStoredProc и TFDQuery в том, что TFDStoredProc автоматически генерирует вызов хранимой процедуры, используя информацию из параметров. SQL код, вызывающий хранимую процедуру может быть исполнен прямо, используя любой метод FireDAC для исполнения команд SQL.

Например, таким образом

with FDQuery1.SQL do begin
  Clear;
  Add('begin');
  Add('  sys.dbms_sql.bind_variable(:c, :name, :value');
  Add('end;');
end;
FDQuery1.Params[0].AsInteger := 1;
FDQuery1.Params[1].AsString := 'p1';
FDQuery1.Params[2].AsInteger := 100;
FDQuery1.ExecSQL;

with FDQuery1.SQL do begin

Clear;

Add('begin');

Add(' sys.dbms_sql.bind_variable(:c, :name, :value');

Add('end;');

end;

FDQuery1.Params[0].AsInteger := 1;

FDQuery1.Params[1].AsString := 'p1';

FDQuery1.Params[2].AsInteger := 100;

FDQuery1.ExecSQL;

Использование TFDCommand

Также можно использовать TFDCommand для того, чтобы выполнить хранимую процедуру. Все спецификации выше могут быть применены к TFDCommand.

ПРАКТИКА

Простейший пример с хранимой процедурой без параметров

Разберем несколько примеров. Начнем с простого, создадим на сервере простейшую хранимую процедуру.

DROP PROCEDURE IF EXISTS SELECT_ALL;

DELIMITER //

CREATE PROCEDURE SELECT_ALL()

BEGIN

SELECT*FROM FIRSTTABLE;

END//

DELIMITER ;

DROP PROCEDURE IF EXISTS SELECT_ALL;

DELIMITER //

CREATE PROCEDURE SELECT_ALL()

BEGIN

SELECT*FROM FIRSTTABLE;

END//

DELIMITER ;

Добавим её на сервер через консоль

Теперь проверим есть ли она там на самом деле через следующую инструкцию

select name from mysql.proc where db='array_db';

1	select name from mysql.proc where db='array_db';

Да, действительно процедура добавлена и мы видим следующее

Теперь далее, попробуем вызвать нашу процедуру при помощи следующего кода

call select_all();

1	call select_all();

Всё Ок, на сервере процедура работает. Теперь попробуем её вызвать в клиенте Delphi таким образом.

Построим такого клиента (новое VCL приложение)

В свойстве FDStoredProc1.StoredProcName укажем следующее значение

FDStoredProc1.StoredProcName:='array_db.SELECT_ALL'

1	FDStoredProc1.StoredProcName:='array_db.SELECT_ALL'

На кнопке сделаем такой код

procedure TForm1.Call_ProcedureClick(Sender: TObject);
begin
FDStoredProc1.StoredProcName:='array_db.SELECT_ALL';
FDStoredProc1.Open();
//FDStoredProc1.Active:=true; // Также сработает
end;

procedure TForm1.Call_ProcedureClick(Sender: TObject);

begin

FDStoredProc1.StoredProcName:='array_db.SELECT_ALL';

FDStoredProc1.Open();

//FDStoredProc1.Active:=true; // Также сработает

end;

Результат

Пример хранимой процедуры с входными параметрами

Теперь создадим на сервере простейшую хранимую процедуру с параметрами.

delimiter //

create procedure MyProcWithPar(p1 varchar(10),p2 varchar(10))
begin
insert into firsttable (field1,field2,field3,field4,field5)
values (p1,p1,p2,p2,p2);

end//

delimiter ;

delimiter //

create procedure MyProcWithPar(p1 varchar(10),p2 varchar(10))

begin

insert into firsttable (field1,field2,field3,field4,field5)

values (p1,p1,p2,p2,p2);

end//

delimiter ;

Добавим и проверим её…

Вроде все на месте, теперь пробуем в клиенте Delphi с помощью FireDAC

Немного преобразим форму, добавив пару компонент и кнопок…

После того как в объектном инспекторе мы выбираем свойство FDStoredProc2.StoredProcName у нас автоматически заполняются параметры процедуры в свойстве FDStoredProc2.Params

procedure TForm1.CallProcedure2Click(Sender: TObject);
begin
FDStoredProc2.StoredProcName:='array_db.MyProcWithPar';
FDStoredProc2.ExecProc('MyProcWithPar',['Hello','Wow']);

DataSource1.DataSet:=FDQuery1;
FDQuery1.Open('select*from firsttable');


end;

procedure TForm1.CallProcedure2Click(Sender: TObject);

begin

FDStoredProc2.StoredProcName:='array_db.MyProcWithPar';

FDStoredProc2.ExecProc('MyProcWithPar',['Hello','Wow']);

DataSource1.DataSet:=FDQuery1;

FDQuery1.Open('select*from firsttable');

end;

Результат

В принципе множество можно было отображать и внутри хранимой процедуры. Но для демонстрации метода ExecProc я решил поступить именно таким образом, разделив выполнение процедуры и отображение множества.

Пример хранимой процедуры с выходным параметром

DELIMITER $$

DROP PROCEDURE IF EXISTS my_sqrt$$
CREATE PROCEDURE my_sqrt(IN input_number INT, OUT out_number FLOAT)
BEGIN
    SET out_number=SQRT(input_number);
END$$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS my_sqrt$$

CREATE PROCEDURE my_sqrt(IN input_number INT, OUT out_number FLOAT)

BEGIN

SET out_number=SQRT(input_number);

END$$

DELIMITER ;

Тестируем в консоли…

Тестируем в Delphi, добавим пару компонентов на форму

Теперь код на кнопке call_sqrt

procedure TForm1.call_sqrtClick(Sender: TObject);
begin
FDStoredProc3.ExecProc('array_db.my_sqrt',[15]); // << Отправляем только IN параметр (входной)
showmessage(

FDStoredProc3.Params.ParamByName('out_number').Value
);
end;

procedure TForm1.call_sqrtClick(Sender: TObject);

begin

FDStoredProc3.ExecProc('array_db.my_sqrt',[15]); // << Отправляем только IN параметр (входной)

showmessage(

FDStoredProc3.Params.ParamByName('out_number').Value

);

end;

Результат

Delphi.FireDAC. Выполнение хранимых процедур и функций

Настройка хранимых процедур в Design Time

Настройка хранимых процедур в RunTime

“Запакованные процедуры” (Using Packaged Procedures)

Выполнение хранимой процедуры

Если процедура не возвращает множество

Если процедура возвращает множество

Использование TFDQuery

Использование TFDCommand

ПРАКТИКА

Простейший пример с хранимой процедурой без параметров

Результат

Пример хранимой процедуры с входными параметрами

Пример хранимой процедуры с выходным параметром

MY PROJECTS

Recent Posts

Categories