半岛外围网上直营

    dotConnect为Oracle允许用户使用PL / SQL阵列，PL/SQL表作为匿名PL / SQL块的参数或作为存储进程的参数。作为一般的阵列，PL/SQL阵列可用于常见数据类型，并可通过指数访问。

    在示例中将会使用标准的Dept表，以下的示例中就演示了如何使用PL / SQL表类型的参数，更新来自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名字的参数，这个SQL用来自NameArr数组的值，更新Dept表的DName域。

    首先，创建一个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()
...

    上面的代码只是一段示例，不要去编译。全部打代码将会在文章的结尾给出。

    接着就是创建一个有OracleConnection对象和指定了用于执行的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;"
...

    现在对于来自SQL的每个参数，应该关联OracleParameter类的对象，以及添加这个对象到OracleCommand对象参数的集合。关联主要是通过下面的参数名称实现的：

[C#]

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

[Visual Basic]

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

    然后是指定NameArr名称有PL/SQL表类型的参数。所以需要设置请求值的参数的ArrayLength属性。如果说Dept有4个记录，那么该数组的大小也应该是4种。

C#

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

[Visual Basic]

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

    接着需要对NameArr参数的数组项目设置值，数组项目的数量必须是和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
...

现在可以调用OracleCommand类的ExecuteNonQuery （）方法执行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