firedac调用ORACLE的存储过程

firedac调用ORACLE的存储过程

EMB官方原文地址：http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Using_Oracle_with_FireDAC

笔者下面做的是中文翻译：

ORALCE的存储过程，相比MSSQL，多了一个PACKAGE（包）。

因此FIREDAC调用也稍有点不同。

ORACLE创建存储过程的样例脚本如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

CREATE OR REPLACE PACKAGE FDQA_TestPack AS   TYPE TVC2Tbl IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;   PROCEDURE TestPLSQLArray(ATable in out TVC2Tbl); END ADQA_testpack; /   CREATE OR REPLACE PACKAGE BODY FDQA_TestPack AS   PROCEDURE TestPLSQLArray(ATable IN OUT TVC2Tbl) IS   BEGIN     for i in ATable.First .. ATable.Last loop       ATable(i) := '*' || ATable(i) || '*';     end loop;   END; END FDQA_testpack; /

 firedac调用ORACLE存储过程的代码如下：

?

1 2 3 4 5 6 7

FDStoredProc1.PackageName := 'MYPACK'; FDStoredProc1.StoredProcName := 'CLNTPROC'; FDStoredProc1.Prepare; FDStoredProc1.ParamByName('AREC$CLIENT_ID').Value := 100; FDStoredProc1.ParamByName('AREC$NAME').Value := 'Client 1'; FDStoredProc1.ParamByName('AREC$ACT').Value := True; FDStoredProc1.ExecProc;

　　调用ORACLE高级游标返回数据集

?

1 2 3 4 5

CREATE PROCEDURE TestRefCrs (ACrs1 IN OUT SYS_REFCURSOR, ACrs2 IN OUT SYS_REFCURSOR) AS BEGIN   OPEN ACrs1 FOR SELECT * FROM "Orders";   OPEN ACrs2 FOR SELECT * FROM "Order Details"; END;

　　用FDStoredProc调用：

?

1 2 3 4 5 6 7

FDStoredProc1.FetchOptions.AutoClose := False; FDStoredProc1.StoredProcName := 'TESTREFCRS'; FDStoredProc1.Open; // work with "Orders" table data FDStoredProc1.NextRecordSet; // work with "Order Details" table data FDStoredProc1.Close;

　　用FDQuery调用：

?

1 2 3 4 5 6

FDQuery1.FetchOptions.AutoClose := False; FDQuery1.Open('BEGIN TestRefCrs(:p1, :p2); END;'); // work with "Orders" table data FDQuery1.NextRecordSet; // work with "Order Details" table data FDQuery1.Close;

　　

?

1 2 3 4

CREATE PROCEDURE TestDynCrs (ASQL IN VARCHAR2, ACrs OUT SYS_REFCURSOR) AS BEGIN   OPEN ACrs FOR ASQL; END;

　　调用：

?

1 2 3 4 5 6 7 8 9 10 11 12 13

FDQuery1.FetchOptions.AutoClose := False; FDQuery1.SQL.Text := 'BEGIN TestDynCrs(:p1, :p2); END;';   FDQuery1.Params[0].AsString := 'SELECT * FROM "Orders"'; FDQuery1.Open; // work with "Orders" table data FDQuery1.Close;   FDQuery1.Params[0].AsString := 'SELECT * FROM "Order Details"'; FDQuery1.Disconnect; FDQuery1.Open; // work with "Order Details" table data FDQuery1.Close;

　　

Working with Oracle Nested Cursors

FireDAC supports CURSOR type columns in SELECT lists. There may be multiple CURSORs in the list. But a CURSOR nested into a CURSOR is not supported. FireDAC sets such columns to dtRowSetRef and creates a TDataSetField for them. To process their row sets, the application should use the TFDMemTable, and set its DataSetField property to a TDataSetField reference.

While the application navigates through the main dataset, the nested datasets will be automatically open and refreshed to provide the nested cursor records for a current record of the main dataset.

For examples, see the FireDAC\Samples\DBMS Specific\Oracle\NestedCursors demo.