程式師世界 >> 編程語言 >> 更多編程語言 >> Delphi >> Delphi FireDAC 下的 Sqlite（八）自定義函數

Delphi FireDAC 下的 Sqlite（八）自定義函數

編輯：Delphi

Sqlite 本身沒有這個功能, FireDAC 通過 TFDSQLiteFunction 增加了該功能; 盡管通過某些 SQL 語句或通過視圖也可以達到類似效果, 但函數會更靈活些. 本例先建了一個成績表, 然後通過兩個 TFDSQLiteFunction 實現了 "總分" 與 "平均分" 的計算.

你可以復制下面文本框中的內容, 然後直接往窗體上貼, 以快速完成窗體設計: object DBGrid1: TDBGrid Left = 8 Top = 88 Width = 321 Height = 89 DataSource = DataSource1 TabOrder = 0 TitleFont.Charset = DEFAULT_CHARSET TitleFont.Color = clWindowText TitleFont.Height = -11 TitleFont.Name = 'Tahoma' TitleFont.Style = [] end object Button1: TButton Left = 382 Top = 88 Width = 75 Height = 25 Caption = 'Button1' TabOrder = 1 OnClick = Button1Click end object Button2: TButton Left = 382 Top = 129 Width = 75 Height = 25 Caption = 'Button2' TabOrder = 2 OnClick = Button2Click end object FDConnection1: TFDConnection Left = 34 Top = 24 end object FDPhysSQLiteDriverLink1: TFDPhysSQLiteDriverLink Left = 143 Top = 24 end object FDGUIxWaitCursor1: TFDGUIxWaitCursor Provider = 'Forms' Left = 260 Top = 24 end object FDQuery1: TFDQuery Connection = FDConnection1 Left = 344 Top = 24 end object DataSource1: TDataSource DataSet = FDQuery1 Left = 420 Top = 24 end object FDSQLiteFunction1: TFDSQLiteFunction DriverLink = FDPhysSQLiteDriverLink1 Active = True FunctionName = 'MyFun1' ArgumentsCount = 3 OnCalculate = FDSQLiteFunction1Calculate Left = 48 Top = 200 end object FDSQLiteFunction2: TFDSQLiteFunction DriverLink = FDPhysSQLiteDriverLink1 Active = True FunctionName = 'MyFun2' ArgumentsCount = 3 OnCalculate = FDSQLiteFunction2Calculate Left = 152 Top = 200 end

代碼:

unit Unit1;

interfaceuses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics, Vcl.Controls, Vcl.Forms,
  Vcl.Dialogs, 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.Stan.ExprFuncs, FireDAC.VCLUI.Wait, FireDAC.Stan.Param, FireDAC.DatS,
  FireDAC.DApt.Intf, FireDAC.DApt, Vcl.Grids, Vcl.DBGrids, Data.DB, FireDAC.Comp.DataSet, FireDAC.Comp.Client, FireDAC.Comp.UI,
  FireDAC.Phys.SQLite, Vcl.StdCtrls, FireDAC.Phys.SQLiteWrapper;

type
  TForm1 = class(TForm)
    FDConnection1: TFDConnection;
    FDPhysSQLiteDriverLink1: TFDPhysSQLiteDriverLink;
    FDGUIxWaitCursor1: TFDGUIxWaitCursor;
    FDQuery1: TFDQuery;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Button1: TButton;
    Button2: TButton;
    FDSQLiteFunction1: TFDSQLiteFunction;
    FDSQLiteFunction2: TFDSQLiteFunction;procedure FormCreate(Sender: TObject);procedure Button1Click(Sender: TObject);procedure Button2Click(Sender: TObject);procedure FDSQLiteFunction1Calculate(AFunc: TSQLiteFunctionInstance; AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject);procedure FDSQLiteFunction2Calculate(AFunc: TSQLiteFunctionInstance; AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject);
  private{ Private declarations }
  public{ Public declarations }
  end;

var
  Form1: TForm1;

implementation{$R *.dfm}procedure TForm1.FormCreate(Sender: TObject);
const
  strTable = 'CREATE TABLE MyTable(姓名 string(10), 語文 Integer, 數學 Integer, 英語 Integer)'; // 建一個學生成績表begin
  { 建立一個成績表, 並插入測試數據 }
  FDConnection1.Params.Add('DriverID=SQLite');
  FDConnection1.ExecSQL(strTable);
  FDQuery1.ExecSQL('INSERT INTO MyTable(姓名, 語文, 數學, 英語) VALUES(:1, :2, :3, :4)', ['張三', 66, 77, 88]);
  FDQuery1.ExecSQL('INSERT INTO MyTable(姓名, 語文, 數學, 英語) VALUES(:1, :2, :3, :4)', ['李四', 77, 88, 99]);
  FDQuery1.Open('SELECT * FROM MyTable');

  { 分別給兩個 TFDSQLiteFunction 設定參數 }
  FDSQLiteFunction1.DriverLink := FDPhysSQLiteDriverLink1;
  FDSQLiteFunction1.FunctionName := 'MyFun1'; // 函數名
  FDSQLiteFunction1.ArgumentsCount := 3; // 函數的參數個數
  // FDSQLiteFunction1.OnCalculate := FDSQLiteFunction1Calculate; //在設計時建立 OnCalculate 事件更方便
  FDSQLiteFunction1.Active := True;

  FDSQLiteFunction2.DriverLink := FDPhysSQLiteDriverLink1;
  FDSQLiteFunction2.FunctionName := 'MyFun2';
  FDSQLiteFunction2.ArgumentsCount := 3;
  // FDSQLiteFunction2.OnCalculate := FDSQLiteFunction2Calculate; //在設計時建立 OnCalculate 事件更方便
  FDSQLiteFunction2.Active := True;
end;

{ 調用 MyFun1 }procedure TForm1.Button1Click(Sender: TObject);
begin
  FDQuery1.Open('SELECT 姓名, MyFun1(語文, 數學, 英語) AS 總分 FROM MyTable');
end;

{ 調用 MyFun2 }procedure TForm1.Button2Click(Sender: TObject);
begin
  FDQuery1.Open('SELECT 姓名, MyFun2(語文, 數學, 英語) AS 平均分 FROM MyTable');
end;

{ 函數 MyFun1 的定義: 算總分 }procedure TForm1.FDSQLiteFunction1Calculate(AFunc: TSQLiteFunctionInstance; AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject);
begin
  AOutput.AsInteger := AInputs[0].AsInteger + AInputs[1].AsInteger + AInputs[2].AsInteger;
end;

{ 函數 MyFun2 的定義: 算平均分 }procedure TForm1.FDSQLiteFunction2Calculate(AFunc: TSQLiteFunctionInstance; AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject);
begin
  AOutput.AsFloat := (AInputs[0].AsInteger + AInputs[1].AsInteger + AInputs[2].AsInteger) / 3;
end;

end.