delphi中,怎样批量执行SQL语句?(30分)

  • 主题发起人 主题发起人 guofengdelphi
  • 开始时间 开始时间
G

guofengdelphi

Unregistered / Unconfirmed
GUEST, unregistred user!
delphi通过ADO方式连接SQL Server 2000,<br>现在有个问题,delphi中有没有那个组件可以实现批量执行语句的功能?<br>怎么实现?<br>譬如实现如下功能:<br><br>事务开始<br>语句1;<br>语句2;<br>。。。<br>语句n;<br>事务提交;<br><br>如果没有这样的组件,那么这个功能该怎么实现?
 
直接用就行
 
用一个EXECSQL执行就行。
 
ADOQuery 有个属性是 批量更新的
 
在oracle中你可以用begin end;语句块!
 
ADOQUERY里的sql.add(sql) 不就可以了吗?
 
譬如说:<br>在一个事务中,我要更新三个表?<br>那么是不是把对三个表操作的SQL语句用<br>AdoQuery.sql.add();方法加入进来,<br>然后执行ExecSql,<br>最后提交事务就行了?
 
就是这个意思,不管用不用事务,想类似的语句都可以在一起来更新。<br>也就是可以用下面的语句来执行<br>With Query1 DO<br>begin<br>&nbsp; &nbsp; close;<br>&nbsp; &nbsp; SQL.Clear;<br>&nbsp; &nbsp; SQL.Add('update table1 set filed1=''1''');<br>&nbsp; &nbsp; SQL.Add('update table2 set filed1=''1''');<br>&nbsp; &nbsp; SQL.Add('update table3 set filed1=''1''');<br>&nbsp; &nbsp; SQL.Add('update table4 set filed1=''1''');<br>&nbsp; &nbsp; Execsql;<br>End;<br>这样的好处就是节约写代码,但不好的地方就是上面的语句执行错误时下面的语句就不会执行了。
 
肯定要用事务啊,属性:locktyp改为ltBatchOptimistic<br>即可批量更新
 
我来一个函数:<br>procedure BatExecSQL(const SQL:Array of string;Con:TADOConnection);<br>var<br>&nbsp; I:integer;<br>&nbsp; s:string;<br>begin<br>&nbsp;Con.BeginTrans; <br>&nbsp;for I:=0 to length(SQL)-1 do &nbsp; Con.Execute(SQL);<br>&nbsp;Con.CommitTrans;<br>end;
 
当然是用存储过程了,delphi里面构造SQL语句多麻烦<br>最终还要交给数据库
 
dephi构造SQL语句也不麻烦阿,自己写个工具,把SQL语句放进去一执行就能生成了。
 
我写了个函数,大家评论下程序执行的效率等各方面怎样:<br>function ADOExecSQL(Const Adocon:TAdoConnection;Const SqlList:TStringList;var StrErr:String):boolean;<br>Var<br>&nbsp; &nbsp;ExecQuery:TADOQuery;<br>&nbsp; &nbsp;Successed:Boolean;//成功否<br>&nbsp; &nbsp;i:integer;<br>begin<br>&nbsp; &nbsp;Successed:=false;<br>&nbsp; &nbsp;ExecQuery:=TADOQuery.Create(nil);<br>&nbsp; &nbsp;try<br>&nbsp; &nbsp; &nbsp; try<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //判断是否有连接<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if Not Adocon.Connected then<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Adocon.Connected:=true;<br><br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Adocon.BeginTrans;//事务开始<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;with ExecQuery do<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //UniDirectional:=True;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Active:=False;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Sql.Clear;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ExecQuery.Connection:=Adocon;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for i:=0 to SqlList.Count-1 do<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sql.Add(SqlList.Strings);<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ExecQuery.ExecSQL;<br><br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end;<br><br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Adocon.CommitTrans;//事务提交<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Successed:=true;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; StrErr:='';//执行成功<br>&nbsp; &nbsp; &nbsp; Except<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; on Err:Exception do<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Adocon.RollbackTrans;//事务回滚。<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Successed:=false;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; StrErr:=Err.Message ;//执行失败<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end;<br>&nbsp; &nbsp; &nbsp; end;<br>&nbsp; &nbsp;Finally<br>&nbsp; &nbsp; &nbsp; &nbsp;ExecQuery.Free;//释放<br>&nbsp; &nbsp; &nbsp; &nbsp;if Successed then<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Result:=True//执行成功<br>&nbsp; &nbsp; &nbsp; else<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Result:=False;//执行失败<br>&nbsp; &nbsp;end;<br>end;
 
属性:locktype改为ltBatchOptimistic<br>即可批量更新<br>但是我还是建议用存储过程实现
 
如果用ADO的隐性事务,只能回滚编译错误,不能回滚逻辑错误.即 ADOConnnection.BeginTrans<br>....<br>adoconnection.commit<br>.....<br>要解决这个问题用显式事务:<br>&nbsp; &nbsp; &nbsp; SQL.Add('BEGIN TRANSACTION') ;<br>&nbsp; &nbsp; &nbsp; SQL.Add(.....) ; &nbsp;//加入多个执行语句<br>&nbsp; &nbsp; &nbsp; SQL.Add(.....) ;<br>&nbsp; &nbsp; &nbsp; SQL.Add(.....) ;<br>&nbsp; &nbsp; &nbsp; SQL.Add(.....) ;<br>&nbsp; &nbsp; &nbsp; SQL.Add('if @@ERROR &lt;&gt; 0'+ #13#10 +<br>&nbsp; &nbsp; &nbsp; &nbsp; 'begin'+ #13#10 +<br>&nbsp; &nbsp; &nbsp; &nbsp; ' &nbsp;ROLLBACK TRANSACTION'+ #13#10 +<br>&nbsp; &nbsp; &nbsp; &nbsp; ' &nbsp;RETURN'+ #13#10 +<br>&nbsp; &nbsp; &nbsp; &nbsp; 'end else'+#13#10+<br>&nbsp; &nbsp; &nbsp; &nbsp; 'COMMIT TRANSACTION' ) ;
 
大家看下我的函数有没有什么问题?
 
我支持使用存储过程,效率更高!
 
后退
顶部