如何一次性执行3条命令(SQL语句)到oracle表? [英] How to execute 3 Commands (SQL statements) to oracle tables at one shot?

问题描述

问候，

我正在使用vb.net 2005和oracle 10g来实现应用程序，并且遇到了应该从oracle中三个不同表中选择/插入/更新/删除数据的功能.

因此，我想知道是否有一种方法可以分别准备所有3个命令(SQL语句)，然后一次将它们执行到oracle，因此，我可以保证所有命令都成功执行或全部失败执行，而且我可以获得更多性能，这对我来说很关键.

因此，我正在vb.net中寻找一种语法，该语法可以帮助我一次向oracle执行一个以上的OracleCommand.

所以请提出建议...

Greetings,

I''m implementing an application using vb.net 2005 with oracle 10g and I came across a functionality that should Select/Insert/Update/Delete data from three different tables in oracle.

So I`m wondering if there is a way to prepare all the 3 commands (SQL Statements) separately and then execute them in one shot to the oracle, so in this way I can guarantee that all of them are successfully executed or all are failed to execute, and also I could gain more performance which is critical in my case.

Thus I`m looking for a syntax in vb.net that helps me execute more than one OracleCommand in one shot to the oracle.

So please advise...

推荐答案

您实际上要执行的操作是将它们包装在事务中.幸运的是，OracleConnection类提供了BeginTransaction method(这将返回OracleTransaction).只需在此连接内调用所有命令，然后调用Commit()保存更改(如果更改成功执行)，或调用Rollback(如果其中一条语句失败).示例如下:

What you are actually looking to do is wrap them in a transaction. Fortunately for you, the OracleConnection class provides a BeginTransaction method (this returns an OracleTransaction). Simply call all your commands inside this connection, then call Commit() to save the changes if they execute successfully, or Rollback if one of the statement fails. A sample would look like this:

Public Sub RunCommands(connection As OracleConnectioncommands As List(Of OracleCommand))
    Dim transaction As OracleTransaction
    connection.Open()
    ' Start a local transaction
    transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)
    Try
      For Each command As OracleCommand In commands
        command.Connection = connection
        command.ExecuteNonQuery
      Next
      transaction.Commit()
    Catch e As Exception
      transaction.Rollback()
      Throw
    End Try
End Sub

不是可以在一个OleDbCommand中合并查询.
如果可能的话，创建一个存储过程.

It''s not possible to combine queries within one OleDbCommand.
If possible, make a stored procedure.

这篇关于如何一次性执行3条命令(SQL语句)到oracle表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！