FireDAC 下的 Sqlite [8] - 自定义函数
来源:互联网 发布:查贴吧ip软件手机 编辑:程序博客网 时间:2024/04/30 10:49
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
代码:
效果图:
本例先建了一个成绩表, 然后通过两个 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;interfaceuses 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.
效果图:
0 0
- FireDAC 下的 Sqlite [8] - 自定义函数
- FireDAC 下的 Sqlite [1] - 前言
- FireDAC 下的 Sqlite [4] - 创建数据库
- FireDAC 下的 Sqlite [6] - 加密
- FireDAC 下的 Sqlite [9] - 关于排序
- FireDAC 下的 Sqlite [9] - 关于排序
- FireDAC 下的 Sqlite 创建数据库
- FireDAC 下的 Sqlite [3] - 获取数据库的基本信息
- FireDAC 下的 Sqlite [5] - 数据的插入、更新、删除
- FireDAC 下的 Sqlite [2] - 第一个例子
- FireDAC 下的 Sqlite [7] - 备份、优化、事务(Transaction)
- FireDAC 下的 Sqlite [10] - 使用 R-Tree 搜索
- FireDAC 下的 Sqlite [12] - 备忘录(草草结束这个话题了)
- FireDAC 下的 Sqlite [11] - 关于批量提交 SQL 命令的测试
- FireDAC操作 SQLite
- 使用FireDAC打开SQLite数据库韩文、阿拉伯文出现乱码的处理方法
- sqlite 自定义函数
- SQLite 加入自定义函数
- 期权的理解
- 正则表达式中各种字符的含义
- 记AWS杭州线下活动 150613
- JQ怎么获取margin-left的值
- 《学习OpenCV》第三章课后题1
- FireDAC 下的 Sqlite [8] - 自定义函数
- weblogic 建域或启动慢的解决方法
- unity 发布Android包
- 仿淘宝购物车嵌套ListView
- Spring-Boot Not Finding JSP Pages in WAR File
- Application Cache---HTML5应用程序缓存Application Cache
- 通过地址获取经纬度(中文地址亦可)
- /etc/fstab 参数详解及如何设置开机自动挂载
- 高效幂算法