.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);