在项目中,经常会遇到需要批量执行SQL语句的情况,下面将为您示例说明批量执行SQL语句的方法,希望对教您学习SQL语句有所帮助。
当然,我们更想在程序中去执行这些语句,而不是再去打开查询分析器。
当我们要在程序中批量执行SQL语句时,需要明白AdoConnection,或AdoQuery(假设我们使用ADO组件)可以执行的语句有什么要求。
1、在查询分析器里,一条SQL语句,我们可以直接写成
Select * from aTable where ID=123
或者
Select * from aTable where ID=123;
这两条语句结果相同。不同的地方只是第二条多了一个“;”,用来表示一条语句结束。
2、在查询分析器里,多条SQL语句的写法
insert into aTable(Field1,Field2) values( '值一','值二')
go
这里的 go 表示一个批处理
根据以上两点,我们可以看出,我们要在程序中批量执行SQL语句,需要判断一个完整的操作包含几条语句,这些语句是不是可以单独执行等。
下面是一个示例程序(程序中不涉及到Select操作)
- unit BatchSQL;
- {
- 批量执行SQL脚本
- E-main: goldli@163.com
- }
- interface
- uses
- SysUtils,ADODB,Classes;
- type
- TOnException = procedure(const E: Exception) of object;
- TOnSQLExecute = procedure(const strSQL: string;const RowsAffected:Integer) of object;
- type
- {完整的SQL语句}
- TSQLString = class
- private
- FBuffer:string;
- FSQL:TStrings;
- FChanged:Boolean;
- function GetSQL:string;
- public
- constructor Create;
- destructor Destroy;override;
- procedure Append(const StrSQL:string);
- property SQL:string read GetSQL;
- end;
- TBatchSQL = class
- private
- FConnection:TADOConnection;
- FSQLList:TList;
- FOnException:TOnException;
- FOnSQLExecute:TOnSQLExecute;
- public
- constructor Create(const AConnection:TADOConnection);
- destructor Destroy;override;
- property Connection:TADOConnection write FConnection;
- procedure LoadFromFile(const FileName:string);
- procedure Execute;
- property OnException:TOnException write FOnException;
- property OnSQLExecute:TOnSQLExecute write FOnSQLExecute;
- end;
- implementation
- { TSQLString }
- procedure TSQLString.Append(const StrSQL: string);
- begin
- FSQL.Append(StrSQL);
- FChanged:=True;
- end;
- constructor TSQLString.Create;
- begin
- FSQL:=TStringList.Create;
- end;
- destructor TSQLString.Destroy;
- begin
- FSQL.Free;
- inherited;
- end;
- function TSQLString.GetSQL: string;
- begin
- if FChanged then
- begin
- FBuffer:=FSQL.Text;
- FChanged:=False;
- end;
- Result:=FBuffer;
- end;
- { TBatchSQL }
- constructor TBatchSQL.Create(const AConnection: TADOConnection);
- begin
- if Assigned(AConnection) then
- FConnection:=AConnection;
- FSQLList:=TList.Create;
- end;
- destructor TBatchSQL.Destroy;
- var
- i:Integer;
- begin
- FConnection:=nil;
- for i:= FSQLList.Count -1 downto 0 do
- TSQLString(FSQLList.Items[i]).Free;
- FSQLList.Free;
- inherited;
- end;
- procedure TBatchSQL.Execute;
- var
- i:Integer;
- Qry:TADOQuery;
- SQLString:TSQLString;
- begin
- Assert(Assigned(FConnection),'数据库连接不能为nil.');
- Assert(FSQLList.count > 0,'请先加载SQL文件.');
- FConnection.LoginPrompt:=False;
- FConnection.Connected:=True;
- Qry:=TADOQuery.Create(nil);
- with Qry do
- begin
- Connection:=FConnection;
- Prepared:=True;
- for i:=0 to FSQLList.Count -1 do
- begin
- SQLString:=TSQLString(FSQLList.Items[i]);
- SQL.Clear;
- SQL.Add(SQLString.SQL);
- try
- ExecSQL;
- if Assigned(FOnSQLExecute) then
- FOnSQLExecute(SQLString.SQL,RowsAffected);
- except
- on E:Exception do
- if Assigned(FOnException) then
- FOnException(E)
- else
- raise Exception.Create('SQL语句出错:' + sLineBreak + SQLString.SQL);
- end;
- end;
- Free;
- end;
- end;
- procedure TBatchSQL.LoadFromFile(const FileName: string);
- var
- SqlStr,Tmp:string;
- F:TextFile;
- SQLString:TSQLString;
- begin
- Assert(FileExists(FileName),'SQL文件不存在,不能加载.');
- AssignFile(F,FileName);
- Reset(f);
- Repeat
- Readln(F,Tmp);
- if Tmp='GO' then
- begin
- SQLString:=TSQLString.Create;
- SQLString.Append(SqlStr);
- FSQLList.Add(SQLString);
- SqlStr:='';
- Tmp:='';
- end;
- SqlStrSqlStr:=SqlStr + Tmp;
- Until eof(F);
- Closefile(F);
- end;
- end.
【编辑推荐】