unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, OracleCI, Oracle, Vcl.StdCtrls,
  FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.UI.Intf,
  FireDAC.Phys.Intf, FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Stan.Async,
  FireDAC.Phys, FireDAC.VCLUI.Wait, FireDAC.Stan.Param, FireDAC.DatS,
  FireDAC.DApt.Intf, FireDAC.DApt, Data.DB, FireDAC.Comp.DataSet,
  FireDAC.Comp.Client, FireDAC.Phys.OracleDef, FireDAC.Phys.Oracle;

type
  TobPack = class (Tobject)
    mSRC_ADDR: Variant;
    mDST_ADDR: Variant;
    mNAT_SRC: Variant;
    mSRC_PORT: Variant;
    mDST_PORT: Variant;
    mNAT_PORT:Variant;
    mDate: Variant;
    mDateEnd: Variant;
    cnt_pack: integer;
    constructor Create(const dbSize: integer); overload;
    destructor Destroy; override;
  end;

  type
  TobPack1 = class (Tobject)
    mSRC_ADDR: array of int64;
    mDST_ADDR: array of int64;
    mNAT_SRC: array of int64;
    mSRC_PORT: array of integer;
    mDST_PORT: array of integer;
    mNAT_PORT: array of integer;
    mDate: array of TDateTime;
    mDateEnd: array of TDateTime;
    cnt_pack: integer;
    constructor Create(const dbSize: integer); overload;
    destructor Destroy; override;
  end;

type
  TForm1 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Label1: TLabel;
    Label2: TLabel;
    OraSession: TOracleSession;
    ods: TOracleQuery;
    Button3: TButton;
    Button4: TButton;
    Label3: TLabel;
    Label4: TLabel;
    FDConnection: TFDConnection;
    FDQuery: TFDQuery;
    FDPhysOracleDriverLink1: TFDPhysOracleDriverLink;
    procedure FormShow(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;
  size_pack: integer = 500000;
  ob: TobPack;
  ob1: TobPack1;
implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var
  fStartNew: Cardinal;
begin
  fStartNew:= GetTickCount;
  ods.Close;
  ods.SetVariable(0, ob.mDate);
  ods.SetVariable(1, ob.mDateEnd);
  ods.SetVariable(2, ob.mSRC_ADDR);
  ods.SetVariable(3, ob.mNAT_SRC);
  ods.SetVariable(4, ob.mDST_ADDR);
  ods.SetVariable(5, ob.mSRC_PORT);
  ods.SetVariable(6, ob.mNAT_PORT);
  ods.SetVariable(7, ob.mDST_PORT);
  ods.SetVariable(8, ob.cnt_pack);
  label1.caption:= 'Заполнено за:' + inttostr(GetTickCount - fStartNew);
end;

procedure TForm1.Button2Click(Sender: TObject);
var
  fStartNew: Cardinal;
begin
   fStartNew:= GetTickCount;
                ods.Execute;
   label2.caption:= 'выполнено за:' + inttostr(GetTickCount - fStartNew);
end;

procedure TForm1.Button3Click(Sender: TObject);
var
  fStartNew: Cardinal;
  i: integer;
begin
  fStartNew:= GetTickCount;
  FDQuery.Close;

  FDQuery.ParamByName('v_cnt').Value:= size_pack;


  for i := 0 to size_pack-1 do
  begin
    FDQuery.Params[0].Values[i]:= ob.mDate[i];
    FDQuery.Params[1].Values[i]:= ob.mDateEnd[i];
    FDQuery.Params[2].Values[i]:= ob.mSRC_ADDR[i];
    FDQuery.Params[3].Values[i]:= ob.mNAT_SRC[i];
    FDQuery.Params[4].Values[i]:= ob.mDST_ADDR[i];
    FDQuery.Params[5].Values[i]:= ob.mSRC_PORT[i];
    FDQuery.Params[6].Values[i]:= ob.mNAT_PORT[i];
    FDQuery.Params[7].Values[i]:= ob.mDST_PORT[i];
  end;

  label3.caption:= 'Заполнено за:' + inttostr(GetTickCount - fStartNew);

end;

procedure TForm1.Button4Click(Sender: TObject);
var
  fStartNew: Cardinal;
begin
  fStartNew:= GetTickCount;
  FDQuery.ExecSQL;
  label4.caption:= 'выполнено за:' + inttostr(GetTickCount - fStartNew);
end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  ob.Free;
  ob1.Free;
end;

procedure TForm1.FormShow(Sender: TObject);
var
  i: integer;
  ora_path, STT: string;
  SRC_ADDR, NAT_SRC, DST_ADDR: string;
  SRC, NAT, DST: uint32;
begin

  oraSession.Preferences.ConvertCRLF:= false;
  oraSession.Preferences.TrimStringFields:= false;

  OracleCI.OCIDLL:='c:\ProgramData\_ORACLE\oci.dll';
  ora_path:= 'c:\ProgramData\_ORACLE\';
   SetEnvironmentVariable('TNS_ADMIN',pchar(ora_path));

      stt:=GetEnvironmentVariable('PATH');
      stt:=ora_path+';'+stt;
      SetEnvironmentVariable('PATH',pchar(stt));
      stt:=GetEnvironmentVariable('PATH');

    oraSession.LogOn;


      ods:= TOracleQuery.create(nil);

        with ods do
        begin
           Session:= oraSession;
           DeleteVariables;
           sql.Text:='хранимая процедура';

           DeclareVariable('mDate', otDate);
           DimPLSQLTable('mDate', size_pack, 0);
           DeclareVariable('mDateEnd', otDate);
           DimPLSQLTable('mDateEnd', size_pack, 0);
           DeclareVariable('mSRC_ADDR', otString);
           DimPLSQLTable('mSRC_ADDR', size_pack, 10);
           DeclareVariable('mNAT_SRC', otString);
           DimPLSQLTable('mNAT_SRC', size_pack, 10);
           DeclareVariable('mDST_ADDR', otString);
           DimPLSQLTable('mDST_ADDR', size_pack, 10);
           DeclareVariable('mSRC_PORT', otInteger);
           DimPLSQLTable('mSRC_PORT', size_pack, 0);
           DeclareVariable('mSRC_TRANS_PORT', otInteger);
           DimPLSQLTable('mSRC_TRANS_PORT', size_pack, 0);
           DeclareVariable('mDST_PORT', otInteger);
           DimPLSQLTable('mDST_PORT', size_pack, 0);
           DeclareVariable('v_cnt', otInteger);
           Optimize:= True; //не будет пересобирать заново запрос

        end;

  FDPhysOracleDriverLink1.NLSLang:= 'AMERICAN_AMERICA.CL8MSWIN1251';
  FDPhysOracleDriverLink1.TNSAdmin:= ora_path;
  FDPhysOracleDriverLink1.VendorHome:= ora_path;
  FDPhysOracleDriverLink1.VendorLib:= 'c:\ProgramData\_ORACLE\oci.dll';

  FDConnection.Params.UserName:= 'inter';
  FDConnection.Params.Password:= 'ghjtrn';
  FDConnection.Params.Database:= 'ORA_15';
  FDConnection.Params.DriverID:= 'Ora';

  FDConnection.Open;

   FDQuery.Connection := FDConnection;
     // FDQuery.ResourceOptions.CmdExecTimeout:= quTimeOut;
      FDQuery.ResourceOptions.CmdExecMode := amBlocking;
      FDQuery.SQL.Text:=  'хранимая процедура';

      FDQuery.ParamByName('mDate').ArrayType:= atTable;
      FDQuery.ParamByName('mDate').ArraySize:= size_pack;
      FDQuery.ParamByName('mDate').DataType:= ftDateTime;

      FDQuery.ParamByName('mDateEnd').ArrayType:= atTable;
      FDQuery.ParamByName('mDateEnd').ArraySize:= size_pack;
      FDQuery.ParamByName('mDateEnd').DataType:= ftDateTime;

      FDQuery.ParamByName('mSRC_ADDR').ArrayType:= atTable;
      FDQuery.ParamByName('mSRC_ADDR').ArraySize:= size_pack;
      FDQuery.ParamByName('mSRC_ADDR').DataType:= ftString;
      FDQuery.ParamByName('mSRC_ADDR').Size:= 10;

      FDQuery.ParamByName('mNAT_SRC').ArrayType:= atTable;
      FDQuery.ParamByName('mNAT_SRC').ArraySize:= size_pack;
      FDQuery.ParamByName('mNAT_SRC').DataType:= ftString;
      FDQuery.ParamByName('mNAT_SRC').Size:= 10;

      FDQuery.ParamByName('mDST_ADDR').ArrayType:= atTable;
      FDQuery.ParamByName('mDST_ADDR').ArraySize:= size_pack;
      FDQuery.ParamByName('mDST_ADDR').DataType:= ftString;
      FDQuery.ParamByName('mDST_ADDR').Size:= 10;

      FDQuery.ParamByName('mSRC_PORT').ArrayType:= atTable;
      FDQuery.ParamByName('mSRC_PORT').ArraySize:= size_pack;
      FDQuery.ParamByName('mSRC_PORT').DataType:= ftInteger;

      FDQuery.ParamByName('mSRC_TRANS_PORT').ArrayType:= atTable;
      FDQuery.ParamByName('mSRC_TRANS_PORT').ArraySize:= size_pack;
      FDQuery.ParamByName('mSRC_TRANS_PORT').DataType:= ftInteger;

      FDQuery.ParamByName('mDST_PORT').ArrayType:= atTable;
      FDQuery.ParamByName('mDST_PORT').ArraySize:= size_pack;
      FDQuery.ParamByName('mDST_PORT').DataType:= ftInteger;
      FDQuery.ParamByName('v_cnt').DataType:= ftInteger;

      FDQuery.Prepare; {подготовить}




   ob:= TobPack.Create(size_pack);
   SRC_ADDR:= '1111111111';
   NAT_SRC:= '3333333333';
   DST_ADDR:= '2222222222';
   for i := 0 to size_pack-1 do
   begin
    ob.mSRC_ADDR[i] := SRC_ADDR;
    ob.mSRC_PORT[i] := 1111;
    ob.mNAT_SRC[i] := NAT_SRC;
    ob.mNAT_PORT[i] := 3333;
    ob.mDST_ADDR[i] := DST_ADDR;
    ob.mDST_PORT[i] := 2222;
    ob.mDate[i] := now;
    ob.mDateEnd[i] := now+1;
   end;

   ob1:= TobPack1.Create(size_pack);
   SRC:= 1111111111;
   NAT:= 9999999999;
   DST:= 2222222222;
   for i := 0 to size_pack-1 do
   begin
    ob1.mSRC_ADDR[i] := SRC;
    ob1.mSRC_PORT[i] := 1111;
    ob1.mNAT_SRC[i] := NAT;
    ob1.mNAT_PORT[i] := 3333;
    ob1.mDST_ADDR[i] := DST;
    ob1.mDST_PORT[i] := 2222;
    ob1.mDate[i] := now;
    ob1.mDateEnd[i] := now+1;
   end;
end;

{ TobPack }

constructor TobPack.Create(const dbSize: integer);
begin
  inherited Create;

    mSRC_ADDR := VarArrayCreate([0, dbSize-1], varOleStr);
    mDST_ADDR := VarArrayCreate([0, dbSize-1], varOleStr);
    mNAT_SRC:= VarArrayCreate([0, dbSize-1], varOleStr);
    mSRC_PORT:= VarArrayCreate([0, dbSize-1], varInteger);
    mDST_PORT:= VarArrayCreate([0, dbSize-1], varInteger);
    mNAT_PORT:= VarArrayCreate([0, dbSize-1], varInteger);
    mDate:= VarArrayCreate([0, dbSize-1], varDate);
    mDateEnd:= VarArrayCreate([0, dbSize-1], varDate);

end;

destructor TobPack.Destroy;
begin
  mSRC_ADDR := null;
  mDST_ADDR := null;
  mNAT_SRC:= null;
  mSRC_PORT:= null;
  mDST_PORT:= null;
  mNAT_PORT:= null;
  mDate:= Null;
  mDateEnd:= Null;


  inherited;
end;

{ TobPack1 }

constructor TobPack1.Create(const dbSize: integer);
begin
   inherited create;
    SetLength(mSRC_ADDR, dbSize);
    SetLength(mDST_ADDR, dbSize);
    SetLength(mNAT_SRC, dbSize);
    SetLength(mSRC_PORT, dbSize);
    SetLength(mNAT_PORT, dbSize);
    SetLength(mDST_PORT, dbSize);
    SetLength(mDate, dbSize);
    SetLength(mDateEnd, dbSize);
end;

destructor TobPack1.Destroy;
begin
  SetLength(mSRC_ADDR, 0);
  SetLength(mDST_ADDR, 0);
  SetLength(mNAT_SRC, 0);
  SetLength(mSRC_PORT, 0);
  SetLength(mNAT_PORT, 0);
  SetLength(mDST_PORT, 0);
  SetLength(mDate, 0);
  SetLength(mDateEnd, 0);
  inherited;
  inherited;
end;

end.