.NET调用Oracle存储过程，使用数组类型的参数(如ArrayList)

今天一个项目组的朋友问及：如何在.NET中调用Oracle的存储过程，并以数组作为参数输入。

Oracle的PL/SQL非常强大，支持定长数组和变长数组，支持任何自定义数据类型。通过阅读ODP的文档，发现Oracle是完全支持将数组作为存储过程参数的。下面给出文档信息。

Array Binding
The array bind feature enables applications to bind arrays of a type using the OracleParameter class. Using the array bind feature, an application can insert multiple rows into a table in a single database round-trip.

The following example inserts three rows into the Dept table with a single database round-trip. The OracleCommand ArrayBindCount property defines the number of elements of the array to use when executing the statement.

 
// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client;
 
class ArrayBindSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
    con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;";
    con.Open();
    Console.WriteLine("Connected successfully");
 
    int[] myArrayDeptNo = new int[3] { 10, 20, 30 };
    OracleCommand cmd = new OracleCommand();
 
    // Set the command text on an OracleCommand object
    cmd.CommandText = "insert into dept(deptno) values (:deptno)";
    cmd.Connection = con;
 
    // Set the ArrayBindCount to indicate the number of values
    cmd.ArrayBindCount = 3;
 
    // Create a parameter for the array operations
    OracleParameter prm = new OracleParameter("deptno", OracleDbType.Int32);
 
    prm.Direction = ParameterDirection.Input;
    prm.Value = myArrayDeptNo;
 
    // Add the parameter to the parameter collection
    cmd.Parameters.Add(prm);
 
    // Execute the command
    cmd.ExecuteNonQuery();
    Console.WriteLine("Insert Completed Successfully");
 
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
  }
}

See Also:

"Value" for more information

OracleParameter Array Bind Properties
The OracleParameter class provides two properties for granular control when using the array bind feature:

ArrayBindSize

The ArrayBindSize property is an array of integers specifying the maximum size for each corresponding value in an array. The ArrayBindSize property is similar to the Size property of an OracleParameter object, except the ArrayBindSize property specifies the size for each value in an array.

Before the execution, the application must populate the ArrayBindSize property; after the execution, ODP.NET populates it.

The ArrayBindSize property is used only for parameter types that have variable length such as Clob, Blob, and Varchar2. The size is represented in bytes for binary datatypes, and characters for the Unicode string types. The count for string types does not include the terminating character. The size is inferred from the actual size of the value, if it is not explicitly set. For an output parameter, the size of each value is set by ODP.NET. The ArrayBindSize property is ignored for fixed-length datatypes.

ArrayBindStatus

The ArrayBindStatus property is an array of OracleParameterStatus values that specify the status of each corresponding value in an array for a parameter. This property is similar to the Status property of the OracleParameter object, except that the ArrayBindStatus property specifies the status for each array value.

Before the execution, the application must populate the ArrayBindStatus property. After the execution, ODP.NET populates the property. Before the execution, an application using the ArrayBindStatus property can specify a NULL value for the corresponding element in the array for a parameter. After the execution, ODP.NET populates the ArrayBindStatus property, indicating whether the corresponding element in the array has a null value, or if data truncation occurred when the value was fetched.

Error Handling for Array Binding
If an error occurs during an array bind execution, it can be difficult to determine which element in the Value property caused the error. ODP.NET provides a way to determine the row where the error occurred, making it easier to find the element in the row that caused the error.

When an OracleException object is thrown during an array bind execution, the OracleErrorCollection object contains one or more OracleError objects. Each of these OracleError objects represents an individual error that occurred during the execution, and contains a provider-specific property, ArrayBindIndex, which indicates the row number at which the error occurred.

 如果您对本文有任何疑问或者建议，请到讨论区发表您的意见: >> 论坛入口 <<