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

发布时间：2020-12-12 19:43:09 所属栏目：百科来源：网络整理

导读：Sqlite 本身没有这个功能,FireDAC 通过 TFDSQLiteFunction 增加了该功能; 尽管通过某些 SQL 语句或通过视图也可以达到类似效果,但函数会更灵活些. 本例先建了一个成绩表,然后通过两个 TFDSQLiteFunction 实现了 "总分" 与 "平均分" 的计算. 你可以复制下面文

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(姓名,['李四',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.

效果图:

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!