procedure TForm1.FormCreate(Sender: TObject);
const
strTable =
‘CREATE TABLE MyTable(Id integer PRIMARY KEY AUTOINCREMENT, Name string(10), Age integer)‘;
//Id(自增), Name, Age
begin
FDConnection1.DriverName :=
‘SQLite‘;
FDQuery1.ExecSQL(strTable);
FDQuery1.Open(
‘SELECT * FROM MyTable‘);
end;
{逐条插入}
procedure TForm1.Button1Click(Sender: TObject);
const
strInsert =
‘INSERT INTO MyTable(Name, Age) VALUES(:name, :age)‘;
begin
// FDQuery1.FetchOptions.AutoClose := True; //默认值
FDQuery1.ExecSQL(strInsert, [
‘A‘,
1]);
FDQuery1.ExecSQL(strInsert, [
‘B‘,
2]);
FDQuery1.ExecSQL(strInsert, [
‘C‘,
3]);
FDQuery1.ExecSQL(strInsert, [
‘D‘,
4]);
FDQuery1.Open(
‘SELECT * FROM MyTable‘);
end;
{用 ; 分割, 一次行插入}
procedure TForm1.Button2Click(Sender: TObject);
const
strInsert =
‘INSERT INTO MyTable(Name, Age) VALUES("%s", %d)‘;
var
LStr:
string;
begin
LStr :=
‘‘;
LStr := LStr + Format(strInsert, [
‘AA‘,
11]) +
‘;‘;
LStr := LStr + Format(strInsert, [
‘BB‘,
22]) +
‘;‘;
LStr := LStr + Format(strInsert, [
‘CC‘,
33]) +
‘;‘;
LStr := LStr + Format(strInsert, [
‘DD‘,
44]) +
‘;‘;
LStr := LStr +
‘SELECT * FROM MyTable‘;
FDQuery1.ExecSQL(LStr);
FDQuery1.Open();
end;
{使用 NextRecordSet 方法提取并执行所有命令}
procedure TForm1.Button3Click(Sender: TObject);
const
strInsert =
‘INSERT INTO MyTable(Name, Age) VALUES("%s", %d);‘;
begin
FDQuery1.FetchOptions.AutoClose := False;
//按说这个是必须要设置的, 但测试时不设置也可以
FDQuery1.SQL.Clear;
FDQuery1.SQL.Add(Format(strInsert, [
‘AAA‘,
111]));
FDQuery1.SQL.Add(Format(strInsert, [
‘BBB‘,
222]));
FDQuery1.SQL.Add(Format(strInsert, [
‘CCC‘,
333]));
FDQuery1.SQL.Add(Format(strInsert, [
‘DDD‘,
444]));
FDQuery1.SQL.Add(
‘SELECT * FROM MyTable‘);
FDQuery1.Execute();
FDQuery1.NextRecordSet;
end;
{使用 DML 数组参数}
procedure TForm1.Button4Click(Sender: TObject);
const
strInsert =
‘INSERT INTO MyTable(Name, Age) VALUES(:name, :age);‘;
begin
FDQuery1.FetchOptions.AutoClose := False;
//
FDQuery1.SQL.Text := strInsert;
FDQuery1.Params.ArraySize :=
4;
//准备把上面的语句执行 4 次
{分别设置 4 次的参数}
FDQuery1.Params[
0].AsStrings[
0] :=
‘AAAA‘;
FDQuery1.Params[
1].AsIntegers[
0] :=
1111;
FDQuery1.Params[
0].AsStrings[
1] :=
‘BBBB‘;
FDQuery1.Params[
1].AsIntegers[
1] :=
2222;
FDQuery1.Params[
0].AsStrings[
2] :=
‘CCCC‘;
FDQuery1.Params[
1].AsIntegers[
2] :=
3333;
FDQuery1.Params[
0].AsStrings[
3] :=
‘DDDD‘;
FDQuery1.Params[
1].AsIntegers[
3] :=
4444;
FDQuery1.Execute(
4,
0);
//从 1 条开始执行 4 次
FDQuery1.SQL.Add(
‘SELECT * FROM MyTable‘);
FDQuery1.NextRecordSet;
end;
测试效果图:
另外, 还可以使用 FireDAC 扩展的 SQL Script(TFDScript), 它还能直接调用文件中的 SQL 指令.
FireDAC 下的 Sqlite [11] - 关于批量提交 SQL 命令的测试,
标签：als src ble for 自增 size value rip row
