首页 / 软件开发 / Delphi / Delphi FireDAC 下的 Sqlite（八） 自定义函数

Delphi FireDAC 下的 Sqlite（八） 自定义函数2015-02-13Sqlite 本身没有这个功能, 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;interfaceusesWinapi.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;typeTForm1 = 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;varForm1: TForm1;implementation{$R *.dfm}procedure TForm1.FormCreate（Sender: TObject）;conststrTable = "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）;beginFDQuery1.Open（"SELECT 姓名, MyFun1（语文, 数学, 英语） AS 总分 FROM MyTable"）;end;{ 调用 MyFun2 }procedure TForm1.Button2Click（Sender: TObject）;beginFDQuery1.Open（"SELECT 姓名, MyFun2（语文, 数学, 英语） AS 平均分 FROM MyTable"）;end;{ 函数 MyFun1 的定义: 算总分 }procedure TForm1.FDSQLiteFunction1Calculate（AFunc: TSQLiteFunctionInstance; AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject）;beginAOutput.AsInteger := AInputs[0].AsInteger + AInputs[1].AsInteger + AInputs[2].AsInteger;end;{ 函数 MyFun2 的定义: 算平均分 }procedure TForm1.FDSQLiteFunction2Calculate（AFunc: TSQLiteFunctionInstance; AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject）;beginAOutput.AsFloat := （AInputs[0].AsInteger + AInputs[1].AsInteger + AInputs[2].AsInteger） / 3;end;end.