程式師世界 >> 編程語言 >> 更多編程語言 >> Delphi >> FireDAC 下的 Sqlite [8]

FireDAC 下的 Sqlite [8]

編輯：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;

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.

效果圖: