Данная статья посвящена выполнению хранимых процедур в 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
|
1 2 3 |
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, используя следующий код
|
1 2 3 |
FDStoredProc1.StoredProcName := 'my_proc'; FDStoredProc1.FetchOptions.Items := FDStoredProc1.FetchOptions.Items - [fiMeta]; FDStoredProc1.Command.FillParams(FDStoredProc1.Params); |
Или другой пример
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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; |
“Запакованные процедуры” (Using Packaged Procedures)
Этот раздел пропущу так как в MySQL нет аналога Packaged Procedures
Выполнение хранимой процедуры
Если процедура не возвращает множество
Для хранимой процедуры, которая не возвращает множество, необходимо использовать метод ExecProc. Чтобы использовать хранимую функцию, используйте методы ExecProc или ExecFunc methods, где ExecFunc возвращает значение функции. Если хранимая процедура возвращает множество, мы получим ошибку “[FireDAC][Phys][Oracl]-310. Cannot execute command returning result sets”.
Примеры использования
|
1 2 3 4 5 |
FDStoredProc1.StoredProcName := 'MY_PROC'; FDStoredProc1.Prepare; FDStoredProc1.Params[0].Value := 100; FDStoredProc1.Params[1].Value := 'audi'; FDStoredProc1.ExecProc; |
или более компактно
|
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
Пример использования
|
1 2 3 |
FDStoredProc1.StoredProcName:='array_db.SELECT_ALL'; DataSource1.DataSet:=FDStoredProc1; FDStoredProc1.Open(); // <<< Например так |
Использование TFDQuery
Главное различие между TFDStoredProc и TFDQuery в том, что TFDStoredProc автоматически генерирует вызов хранимой процедуры, используя информацию из параметров. SQL код, вызывающий хранимую процедуру может быть исполнен прямо, используя любой метод FireDAC для исполнения команд SQL.
Например, таким образом
|
1 2 3 4 5 6 7 8 9 10 |
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.
ПРАКТИКА
Простейший пример с хранимой процедурой без параметров
Разберем несколько примеров. Начнем с простого, создадим на сервере простейшую хранимую процедуру.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DROP PROCEDURE IF EXISTS SELECT_ALL; DELIMITER // CREATE PROCEDURE SELECT_ALL() BEGIN SELECT*FROM FIRSTTABLE; END// DELIMITER ; |
Добавим её на сервер через консоль
Теперь проверим есть ли она там на самом деле через следующую инструкцию
|
1 |
select name from mysql.proc where db='array_db'; |
Да, действительно процедура добавлена и мы видим следующее
Теперь далее, попробуем вызвать нашу процедуру при помощи следующего кода
|
1 |
call select_all(); |
Всё Ок, на сервере процедура работает. Теперь попробуем её вызвать в клиенте Delphi таким образом.
Построим такого клиента (новое VCL приложение)
В свойстве FDStoredProc1.StoredProcName укажем следующее значение
|
1 |
FDStoredProc1.StoredProcName:='array_db.SELECT_ALL' |
На кнопке сделаем такой код
|
1 2 3 4 5 6 |
procedure TForm1.Call_ProcedureClick(Sender: TObject); begin FDStoredProc1.StoredProcName:='array_db.SELECT_ALL'; FDStoredProc1.Open(); //FDStoredProc1.Active:=true; // Также сработает end; |
Результат
Пример хранимой процедуры с входными параметрами
Теперь создадим на сервере простейшую хранимую процедуру с параметрами.
|
1 2 3 4 5 6 7 8 9 10 |
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
|
1 2 3 4 5 6 7 8 9 10 |
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 я решил поступить именно таким образом, разделив выполнение процедуры и отображение множества.
Пример хранимой процедуры с выходным параметром
|
1 2 3 4 5 6 7 8 9 |
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
|
1 2 3 4 5 6 7 8 |
procedure TForm1.call_sqrtClick(Sender: TObject); begin FDStoredProc3.ExecProc('array_db.my_sqrt',[15]); // << Отправляем только IN параметр (входной) showmessage( FDStoredProc3.Params.ParamByName('out_number').Value ); end; |
Результат
