dotConnect為Oracle允許用戶使用PL / SQL陣列，PL/SQL表作為匿名PL / SQL塊的參數(shù)或作為存儲進程的參數(shù)。作為一般的陣列，PL/SQL陣列可用于常見數(shù)據類型，并可通過指數(shù)訪問。

    在示例中將會使用標準的Dept表，以下的示例中就演示了如何使用PL / SQL表類型的參數(shù)，更新來自Dept表的記錄。

DECLARE
  i INTEGER;
BEGIN
  i:= 1;
  FOR rec IN (SELECT DeptNo FROM Scott.Dept
    WHERE RowNum <= 10 ORDER BY DeptNo)
  LOOP
    UPDATE Scott.Dept
      SET DName = :NameArr(i)
      WHERE DeptNo = Rec.DeptNo;
    i:= i + 1;
  END LOOP;
END;

    上面給出的了SQL中有一個有PL / SQL表類型的NameArr名字的參數(shù)，這個SQL用來自NameArr數(shù)組的值，更新Dept表的DName域。

    首先，創(chuàng)建一個OracleConnection類的對象，同時打開一個會話：

[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()
...

    上面的代碼只是一段示例，不要去編譯。全部打代碼將會在文章的結尾給出。

    接著就是創(chuàng)建一個有OracleConnection對象和指定了用于執(zhí)行的SQL語句的OracleCommand類的實例：

[C#]

...
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "DECLARE\n" +
  "  i INTEGER;\n" +
  "BEGIN\n" +
  "  i:= 1;\n" +
  "  FOR rec IN (SELECT DeptNo FROM Scott.Dept\n" +
  "    WHERE RowNum <= 10 ORDER BY DeptNo)\n" +
  "  LOOP\n" +
  "    UPDATE Scott.Dept\n" +
  "      SET DName = :NameArr(i)\n" +
  "      WHERE DeptNo = Rec.DeptNo;\n" +
  "    i:= i + 1;\n" +
  "  END LOOP;\n" +
  "END;";
...

...
Dim cmd As OracleCommand = conn.CreateCommand()
cmd.CommandText = "DECLARE" & VbCrlf & _
        "  i INTEGER;" & VbCrlf & _
        "BEGIN" & VbCrlf & _
        "  i:= 1;" & VbCrlf & _
        "  FOR rec IN (SELECT DeptNo FROM Scott.Dept" & VbCrlf & _
        "    WHERE RowNum <= 10 ORDER BY DeptNo)" & VbCrlf & _
        "  LOOP" & VbCrlf & "    UPDATE Scott.Dept" & VbCrlf & _
        "      SET DName = :NameArr(i)" & VbCrlf & _
        "      WHERE DeptNo = Rec.DeptNo;" & VbCrlf & _
        "    i:= i + 1;" & VbCrlf & _
        "  END LOOP;" & VbCrlf & _
        "END;"
...

    現(xiàn)在對于來自SQL的每個參數(shù)，應該關聯(lián)OracleParameter類的對象，以及添加這個對象到OracleCommand對象參數(shù)的集合。關聯(lián)主要是通過下面的參數(shù)名稱實現(xiàn)的：

[C#]

...
cmd.Parameters.Add("NameArr", OracleDbType.VarChar);
...

[Visual Basic]

...
cmd.Parameters.Add("NameArr", OracleDbType.VarChar)
...
 

    然后是指定NameArr名稱有PL/SQL表類型的參數(shù)。所以需要設置請求值的參數(shù)的ArrayLength屬性。如果說Dept有4個記錄，那么該數(shù)組的大小也應該是4種。

C#

...
cmd.Parameters["NameArr"].ArrayLength = 4;

[Visual Basic]

...
cmd.Parameters("NameArr").ArrayLength = 4
...

    接著需要對NameArr參數(shù)的數(shù)組項目設置值，數(shù)組項目的數(shù)量必須是和ArrayLength屬性的值一樣。

[C#]

...
cmd.Parameters["NameArr"].Value = new srting[] { 
   "London", "Berlin", "Geneva", "Vienna" };
...

[Visual Basic]

...
Dim valArr(4) As string
valArr(0) = "London"
valArr(1) = "Berlin"
valArr(2) = "Geneva"
valArr(3) = "Vienna"
cmd.Parameters("NameArr").Value = valArr
...

現(xiàn)在可以調用OracleCommand類的ExecuteNonQuery （）方法執(zhí)行SQL：

[C#]

cmd.ExecuteNonQuery();

[Visual Basic]

cmd.ExecuteNonQuery()

示例完整的代碼如下：

[C#]

public void UpdateThroughPlSqlTable()
{
  // 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 = "DECLARE\n" +
                    "  i INTEGER;\n" +
                    "BEGIN\n" +
                    "  i:= 1;\n" +
                    "  FOR rec IN (SELECT DeptNo FROM Scott.Dept\n" +
                    "    WHERE RowNum <= 10 ORDER BY DeptNo)\n" +
                    "  LOOP\n" +
                    "    UPDATE Scott.Dept\n" +
                    "      SET DName = :NameArr(i)\n" +
                    "      WHERE DeptNo = Rec.DeptNo;\n" +
                    "    i:= i + 1;\n" +
                    "  END LOOP;\n" +
                    "END;";

  // Add parameters to command parameters collection
  cmd.Parameters.Add("NameArr", OracleDbType.VarChar);

  // Set Pl/SQL table length
  cmd.Parameters["NameArr"].ArrayLength = 4;

  // Set array parameter value
  cmd.Parameters["NameArr"].Value = new string[] { 
     "London", "Berlin", "Geneva", "Vienna" };

  // Update table
  cmd.ExecuteNonQuery();

  // Dispose command
  cmd.Dispose();

  // Close connection
  conn.Close();
}

[Visual Basic]

Public Sub UpdateThroughPlSqlTable()

  ' 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 = "DECLARE" & VbCrlf & _
        "  i INTEGER;" & VbCrlf & _
        "BEGIN" & VbCrlf & _
        "  i:= 1;" & VbCrlf & _
        "  FOR rec IN (SELECT DeptNo FROM Scott.Dept" & VbCrlf & _
        "    WHERE RowNum <= 10 ORDER BY DeptNo)" & VbCrlf & _
        "  LOOP" & VbCrlf & "    UPDATE Scott.Dept" & VbCrlf & _
        "      SET DName = :NameArr(i)" & VbCrlf & _
        "      WHERE DeptNo = Rec.DeptNo;" & VbCrlf & _
        "    i:= i + 1;" & VbCrlf & _
        "  END LOOP;" & VbCrlf & _
        "END;"

  ' Add parameters to command parameters collection
  cmd.Parameters.Add("NameArr", OracleDbType.VarChar)

  ' Set Pl/SQL table length
  cmd.Parameters("NameArr").ArrayLength = 4

  ' Set array parameter value
  Dim valArr(4) As string
  valArr(0) = "London"
  valArr(1) = "Berlin"
  valArr(2) = "Geneva"
  valArr(3) = "Vienna"
  cmd.Parameters("NameArr").Value = valArr

  ' Update table
  cmd.ExecuteNonQuery()

  ' Dispose command
  cmd.Dispose()

  ' Close connection
  conn.Close()
End Sub