半岛外围网上直营

    插入或更新大的数据量时，使用Oracle数组绑定功能可以大大加快应用程序的执行。数组绑定的主要优点是在同一时间允许插入有不同参数的几个SQL语句。

    以下Oracle表定义将用于在VB和C ＃示例中：

CREATE TABLE dept
(
  deptno NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY,
  dname VARCHAR2(14),
  loc VARCHAR2(13)
);

    首先，需要创建一个OracleConnection类的对象，并打开一个连接到Oracle数据库：

[C#]

...
OracleConnection conn = new OracleConnection(
   "User Id=Scott;Password=tiger;Data Source=Ora");
conn.Open();
...

[Visual Basic]

...
Dim conn As OracleConnection = new OracleConnection( _
   "User Id=Scott;Password=tiger;Data Source=Ora")
conn.Open()
...

    请注意，这仅仅是一个段的示例代码。全文将在本文末尾。

    之后，你应该创建一个与OracleConnection对象相关的OracleCommand类的实例，并指定用于执行SQL语句：

[C#]

...
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO dept VALUES(:deptno_p, :dname_p, :loc_p)";
...

[Visual Basic]

...
Dim cmd As OracleCommand = conn.CreateCommand()
cmd.CommandText = "INSERT INTO dept VALUES(:deptno_p, :dname_p, :loc_p)"
...

    现在你应该为来自SQL的每个参数关联OracleParameter类对象，并添加这个对象到OracleCommand对象的参数集合，通过下面的方式实现用参数名称完成关联：

[C#]

...
cmd.Parameters.Add("deptno_p", OracleDbType.Integer);
cmd.Parameters.Add("dname_p", OracleDbType.VarChar, 200);
cmd.Parameters.Add("loc_p", OracleDbType.VarChar, 200);
...

[Visual Basic]

...
cmd.Parameters.Add("deptno_p", OracleDbType.Integer)
cmd.Parameters.Add("dname_p", OracleDbType.VarChar, 200)
cmd.Parameters.Add("loc_p", OracleDbType.VarChar, 200)
...

    你应该为每个参数指定名称和类型，建议对于VarChar / NVarChar参数指定参数显示的大小。

下面就是参数赋值了，参数值应该用相应的SQL语句执行数的数量来进行分组，每一项的数组必须对应于单个执行SQL语句。

[C#]

...
cmd.Parameters["deptno_p"].Value = new int[] { 10, 20, 30, 40 };
cmd.Parameters["dname_p"].Value = new string[] { 
   "ACCOUNTING", "RESEARCH", "SALES", "OPERATIONS" };
cmd.Parameters["loc_p"].Value = new string[] { 
   "NEW YORK", "DALLAS", "CHICAGO", "BOSTON" };
...

[Visual Basic]

...
Dim deptno(3) As Integer
deptno(0) = 10
deptno(1) = 20
deptno(2) = 30
deptno(3) = 40

Dim dname(3) As String
dname(0) = "ACCOUNTING"
dname(1) = "RESEARCH"
dname(2) = "SALES"
dname(3) = "OPERATIONS"

Dim loc(3) As String
loc(0) = "NEW YORK"
loc(1) = "DALLAS"
loc(2) = "CHICAGO"
loc(3) = "BOSTON"

cmd.Parameters("deptno_p").Value = deptno
cmd.Parameters("dname_p").Value = dname
cmd.Parameters("loc_p").Value = loc
...

    在完成前面的步骤后，就可以调用ExecuteArray方法，假定一个参数指定SQL语句将被执行的次数。值得注意的是，这种方法的值必须等于参数值的元素的数量。现在用任何的Oracle工具执行SELECT * FROM Dept，将会看见有四个新的记录添加。

[C#]

...
cmd.ExecuteArray(4);
...

[Visual Basic]

...
cmd.ExecuteArray(4)

    下面是一个示例代码，主要是用数组绑定执行几个插入操作：

[C#]

public void ArrayBindInsert()
{
  // Create connection object
  OracleConnection conn = new OracleConnection(
     "User Id=Scott;Password=tiger;Data Source=Ora");

  // Open connection
  conn.Open();

  // Create command object with previously opened connection
  OracleCommand cmd = conn.CreateCommand();

  // Set command text property
  cmd.CommandText = "INSERT INTO dept VALUES(:deptno_p, :dname_p, :loc_p)";

  // Add parameters to command parameters collection
  cmd.Parameters.Add("deptno_p", OracleDbType.Integer);
  cmd.Parameters.Add("dname_p", OracleDbType.VarChar);
  cmd.Parameters.Add("loc_p", OracleDbType.VarChar);

  // Set parameters values
  cmd.Parameters["deptno_p"].Value = new int[] { 10, 20, 30, 40 };
  cmd.Parameters["dname_p"].Value = new string[] { 
     "ACCOUNTING", "RESEARCH", "SALES", "OPERATIONS" };
  cmd.Parameters["loc_p"].Value = new string[] { 
     "NEW YORK", "DALLAS", "CHICAGO", "BOSTON" };

  // Insert four records at one time
  cmd.ExecuteArray(4);

  // Dispose command
  cmd.Dispose();

  // Close connection
  conn.Close();
}

[Visual Basic]

Public Sub ArrayBindInsert()
  ' Create connection object
  Dim conn As OracleConnection = new OracleConnection( _
     "User Id=Scott;Password=tiger;Data Source=Ora")

  ' Open connection
  conn.Open()

  ' Create command object with previously opened connection
  Dim cmd As OracleCommand = conn.CreateCommand()

  ' Set command text property
  cmd.CommandText = "INSERT INTO dept VALUES(:deptno_p, :dname_p, :loc_p)"

  ' Add parameters to command parameters collection
  cmd.Parameters.Add("deptno_p", OracleDbType.Integer)
  cmd.Parameters.Add("dname_p", OracleDbType.VarChar)
  cmd.Parameters.Add("loc_p", OracleDbType.VarChar)

  ' Set parameters values
  Dim deptno(3) As Integer
  deptno(0) = 10
  deptno(1) = 20
  deptno(2) = 30
  deptno(3) = 40

  Dim dname(3) As String
  dname(0) = "ACCOUNTING"
  dname(1) = "RESEARCH"
  dname(2) = "SALES"
  dname(3) = "OPERATIONS"

  Dim loc(3) As String
  loc(0) = "NEW YORK"
  loc(1) = "DALLAS"
  loc(2) = "CHICAGO"
  loc(3) = "BOSTON"

  cmd.Parameters("deptno_p").Value = deptno
  cmd.Parameters("dname_p").Value = dname
  cmd.Parameters("loc_p").Value = loc

  ' Insert four records at one time
  cmd.ExecuteArray(4)

  ' Dispose command
  cmd.Dispose()

  ' Close connection
  conn.Close()
End Sub