FireDAC 下的 Sqlite [8] - 自定义函数

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;

interface

uses
  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.

---

效果图: