如何在 Oracle 11g 存储过程的 Where 子句中使用 Oracle ODP.NET UDT

引言

这段代码展示了如何使用 Oracle 的用户定义类型，例如 VARRAY，通过 ODP.NET 在 Oracle 存储过程查询的 WHERE 子句中使用。我将三个参数传递到我的 Oracle 存储过程：第一个参数是 UDT VARRAY，类型为 number (ParameterDirection.IN)；第二个参数是 UDT VARRAY，类型为 number (ParameterDirection.IN)；第三个参数是一个 refcursor，包含结果 (ParameterDirection.Output)。

前提条件：ODP.NET 11g、Visual Studio 2005 和 2008。

背景

在 ODP.NET 的早期版本中，Oracle 并没有为 .NET 提供用户定义类型支持。因此，为了完成相同的任务，我们必须首先将数组值插入到临时表(s)中，然后从该临时表中使用值在 WHERE 子句中。

Using the Code

创建一个类型 odp_varray_sample_type 作为 number 的 varray(3000)。

/
CREATE OR REPLACE
procedure odp_varray_sample_proc(PARAM1 IN OUT ODP_VARRAY_SAMPLE_TYPE,
    PARAM2 IN OUT ODP_VARRAY_SAMPLE_TYPE, param3 OUT TYPES.cursor_type) as
local_param TYPES.cursor_type;
 begin 
 OPEN local_param FOR
 select * from sched_gen_report_detail where reporter_sid in (select * 
    from table(cast(param1 as ODP_VARRAY_SAMPLE_TYPE)))
 union
 select * from sched_gen_report_detail where item_sid in (select * from table(
    cast(param2 as ODP_VARRAY_SAMPLE_TYPE)));
 param3 := local_param;
END ODP_VARRAY_SAMPLE_PROC;
/
*/

这是 C# 代码

using System;
using System.Data;
using System.Collections;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

class VArraySample
{
    static void Main(string[] args)
    {
        DataSet ds ;
        OracleDataAdapter myAdapter =null;
        Oracle.DataAccess.Types.OracleRefCursor refcur = null;
        string constr = "user id=PPI_UDB_FORMS;password=;" + 
           "data source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=" + 
           "(PROTOCOL=TCP)(HOST=cosmo.psb.bls.gov)(PORT=1521)))(" + 
           "CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=" + 
           "udbdev.psb.bls.gov)));User Id=PPI_UDB_FORMS;Password=aaaaaa;";
        string sql1 = "odp_varray_sample_proc";

        // create a new simple varray with values 1, 2, 3, and 4.
        SimpleVarray pa = new SimpleVarray();
 
        pa.Array = new Int32[] { 100018035, 100024174, 100022751, 100024637, 
                                 100027800, 100022749, 100023094, 100027800, 
                                 100011261, 100019536, 100007392, 100016106 };
 
        SimpleVarray pa2 = new SimpleVarray();
        pa2.Array = new Int32[] { 100000480, 100000481 };
        // create status array indicate element 2 is Null
        //pa.StatusArray = new OracleUdtStatus[] { OracleUdtStatus.NotNull, 
        // OracleUdtStatus.Null, OracleUdtStatus.NotNull, OracleUdtStatus.NotNull };
 
        // Establish a connection to Oracle
        OracleConnection con = new OracleConnection(constr);
        con.Open();
 
        OracleCommand cmd = new OracleCommand(sql1, con);
        cmd.CommandType = CommandType.StoredProcedure;
 
        OracleParameter param = new OracleParameter();
        param.OracleDbType = OracleDbType.Array;
        param.Direction = ParameterDirection.InputOutput;
 
        // Note: The UdtTypeName is case-senstive
        param.UdtTypeName = "PPI_UDB_FORMS.ODP_VARRAY_SAMPLE_TYPE";
        param.Value = pa;
        cmd.Parameters.Add(param);

        OracleParameter param2 = new OracleParameter();
        param2.OracleDbType = OracleDbType.Array;
        param2.Direction = ParameterDirection.InputOutput ;
 
        // Note: The UdtTypeName is case-senstive
        param2.UdtTypeName = "PPI_UDB_FORMS.ODP_VARRAY_SAMPLE_TYPE";
        param2.Value = pa2;
        cmd.Parameters.Add(param2);
        OracleParameter p2 = cmd.Parameters.Add("param2", 
                             OracleDbType.RefCursor, ParameterDirection.Output);
        // Insert SimpleVarray(1,NULL,3,4,9) into the table
        cmd.ExecuteNonQuery();
        refcur = (Oracle.DataAccess.Types.OracleRefCursor)p2.Value;
        myAdapter = new OracleDataAdapter ("",con);
        ds = new DataSet("testDS");
        myAdapter.Fill (ds, refcur);
 
        // Clean up
        cmd.Dispose();
        con.Close();
        con.Dispose();
    }
}

/* SimpleVarray Class
**  An instance of a SimpleVarray class represents an
**  ODP_VARRAY_SAMPLE_TYPE object
**  A custom type must implement INullable and IOracleCustomType interfaces
*/
public class SimpleVarray : IOracleCustomType, INullable
{
    [OracleArrayMapping()]
    public Int32[] Array;
 
    private OracleUdtStatus[] m_statusArray;
    public OracleUdtStatus[] StatusArray
    {
        get
        {
            return this.m_statusArray;
        }
        set
        {
            this.m_statusArray = value;
        }
    }

    private bool m_bIsNull;

    public bool IsNull
    {
        get
        {
          return m_bIsNull;
        }
    }

    public static SimpleVarray Null
    {
        get
        {
            SimpleVarray obj = new SimpleVarray();
            obj.m_bIsNull = true;
            return obj;
        }
    }

    public void ToCustomObject(OracleConnection con, IntPtr pUdt)
    {
        object objectStatusArray = null;
        Array = (Int32[])OracleUdt.GetValue(con, pUdt, 0, out objectStatusArray);
        m_statusArray = (OracleUdtStatus[])objectStatusArray;
    }

    public void FromCustomObject(OracleConnection con, IntPtr pUdt)
    {
        OracleUdt.SetValue(con, pUdt, 0, Array, m_statusArray);
    }

    public override string ToString()
    {
        if (m_bIsNull)
            return "SimpleVarray.Null";
        else
        {
            string rtnstr = String.Empty;
            if (m_statusArray[0] == OracleUdtStatus.Null)
                rtnstr = "NULL";
            else
                rtnstr = Array.GetValue(0).ToString();
            for (int i = 1; i < m_statusArray.Length; i++)
            {
                if (m_statusArray[i] == OracleUdtStatus.Null)
                    rtnstr += "," + "NULL";
                else
                    rtnstr += "," + Array.GetValue(i).ToString();
            }
            return "SimpleVarray(" + rtnstr + ")";
        }
    }
}

/* SimpleVarrayFactory Class
**   An instance of the SimpleVarrayFactory class is used to create 
**   SimpleVarray objects
*/
[OracleCustomTypeMapping("PPI_UDB_FORMS.ODP_VARRAY_SAMPLE_TYPE")]
public class SimpleVarrayFactory : IOracleCustomTypeFactory,   IOracleArrayTypeFactory
{
    // IOracleCustomTypeFactory
    public IOracleCustomType CreateObject()
    {
        return new SimpleVarray();
    }

    // IOracleArrayTypeFactory Inteface
    public Array CreateArray(int numElems)
    {
        return new Int32[numElems];
    }

    public Array CreateStatusArray(int numElems)
    {
        // CreateStatusArray may return null if null status information 
        // is not required.
        return new OracleUdtStatus[numElems];
    }
}