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

发布时间：2020-12-12 23:51:57 所属栏目：百科来源：网络整理

导读：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.

效果图:

（编辑：李大同）

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