插入或更新大的數據量時，使用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