Article From:https://www.cnblogs.com/hnxxcxg/p/9968701.html

firedacCall ORACLE’s stored procedure

EMBOfficial original address: http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Use_Oracle_with_FireDAC

The following is the Chinese translation.

ORALCECompared with MSSQL, the storage process has one more PACKAGE (package).

So FIREDAC calls are slightly different.

ORACLEThe sample scripts for creating stored procedures are as follows:

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;
/

 firedacThe code for calling the ORACLE stored procedure is as follows:

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;

  Calling ORACLE Advanced Cursor to Return Data Set

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;

  Call with FDStoredProc:

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

  Call with FDQuery:

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;

  

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

  Call:

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.

Leave a Reply

Your email address will not be published. Required fields are marked *