ORACLE HANDBOOK系列之六：ODP.NET与复杂的PL/SQL数据类型(Using ODP.NET To Deal With Complex PLSQL Data Types)

在开始介绍之前，先给出文章里用到的所有PL/SQL代码：

（类型定义）

 

CREATE OR REPLACE TYPE T_Nested_Tab_Str IS TABLE OF VARCHAR2(25);
--
CREATE OR REPLACE TYPE T_Object IS OBJECT
(
 employee_id number(6),
 last_name varchar2(25)
);
--
CREATE OR REPLACE TYPE T_VARRAY_STR IS VARRAY(10) OF VARCHAR2(25);
--
CREATE OR REPLACE TYPE T_Nested_Tab_Obj IS TABLE OF T_Object;

（包的声明）

 

CREATE OR REPLACE PACKAGE pkg_odp_dotnet IS
 TYPE T_Ref_Cursor IS REF CURSOR;
 TYPE T_Asso_Array_Num IS TABLE OF employees_bk.employee_id%TYPE INDEX BY PLS_INTEGER;
 TYPE T_Asso_Array_Str IS TABLE OF employees_bk.last_name%TYPE INDEX BY PLS_INTEGER; 
 --
 PROCEDURE proc_ref_cursor(p_ref_cursor OUT T_Ref_Cursor);
 PROCEDURE proc_sys_refcursor(p_sys_refcursor OUT SYS_REFCURSOR);
 --
 PROCEDURE proc_asso_array_num_in(p_asso_array_num    IN T_Asso_Array_Num);
 PROCEDURE proc_asso_array_num_out(p_asso_array_num OUT T_Asso_Array_Num);
 PROCEDURE proc_asso_array_str_out(p_asso_array_str OUT T_Asso_Array_Str);
 --
 PROCEDURE proc_nested_tab_str_in(p_nested_tab_str IN T_Nested_Tab_Str);
 PROCEDURE proc_nested_tab_str_out(p_nested_tab_str OUT T_Nested_Tab_Str);
 --
 PROCEDURE proc_obj_in(p_obj IN T_Object);
 --
 PROCEDURE proc_nested_tab_obj_in(p_nested_tab_obj IN T_Nested_Tab_Obj);
 --
 PROCEDURE proc_varray_str_in(p_varray_str IN T_Varray_Str);
END;

（包体）

 

CREATE OR REPLACE PACKAGE BODY pkg_odp_dotnet IS
 /*This proc is invoked by .NET to test the ref cursor
    Retrieve the employees whose id less than 105*/
 PROCEDURE proc_ref_cursor(p_ref_cursor OUT T_Ref_Cursor)
 IS
 BEGIN
    OPEN p_ref_cursor FOR
    SELECT employee_id,first_name,last_name FROM employees_bk WHERE employee_id<105;
 END;
 
 ---------------------------------------------------------------------------------------------------------------------------------
 /*This proc is invoked by .NET to test the pre-defined sys_refcursor
    Retrieve the employees whose id less than 105*/
 PROCEDURE proc_sys_refcursor(p_sys_refcursor OUT SYS_REFCURSOR)
 IS
 BEGIN
    OPEN p_sys_refcursor FOR
    SELECT employee_id,first_name,last_name FROM employees_bk WHERE employee_id<105;
 END;
 
 ---------------------------------------------------------------------------------------------------------------------------------
 /*This proc is invoked by .NET to test the input parameter with the type of associative array, 
    and the type of the collection element is number
    Loop through each employee id in the given associative array, then update its salary*/
 PROCEDURE proc_asso_array_num_in(p_asso_array_num    IN T_Asso_Array_Num)
 IS
    idx PLS_INTEGER;
 BEGIN
    idx:=p_asso_array_num.FIRST;
    WHILE(idx IS NOT NULL) LOOP
      UPDATE employees_bk SET salary=salary+1 WHERE employee_id=p_asso_array_num(idx);
      idx:=p_asso_array_num.NEXT(idx);
    END LOOP;
    COMMIT;
 END;
 
 ---------------------------------------------------------------------------------------------------------------------------------
 /*This proc is invoked by .NET to test the output parameter with the type of associative array, 
    and the type of the collection element is varchar2
    Retrieve the employees whose id less than 105*/
 PROCEDURE proc_asso_array_str_out(p_asso_array_str OUT T_Asso_Array_Str)
 IS
 BEGIN
    SELECT last_name BULK COLLECT INTO p_asso_array_str FROM employees_bk WHERE employee_id<105;
 END;
 
 ---------------------------------------------------------------------------------------------------------------------------------
 /*This proc is invoked by .NET to test the output parameter with the type of associative array, 
    and the type of the collection element is number
    Retrieve the employees whose id less than 105*/
 PROCEDURE proc_asso_array_num_out(p_asso_array_num OUT T_Asso_Array_Num)
 IS
 BEGIN
    SELECT employee_id BULK COLLECT INTO p_asso_array_num FROM employees_bk WHERE employee_id<105;
 END;
 
 ---------------------------------------------------------------------------------------------------------------------------------
 /*This proc is invoked by .NET to test the input parameter with the type of nested table, 
    and the type of the collection element is number
    Loop through each last name in the given nested table, then update its salary*/
 PROCEDURE proc_nested_tab_str_in(p_nested_tab_str IN T_Nested_Tab_Str)
 IS
 BEGIN
    FORALL i IN p_nested_tab_str.FIRST..p_nested_tab_str.LAST
     UPDATE employees_bk SET salary=salary+1 WHERE last_name=p_nested_tab_str(i);
    COMMIT;
 END;
 
 ---------------------------------------------------------------------------------------------------------------------------------
 /*This proc is invoked by .NET to test the output parameter with the type of nested table, 
    and the type of the collection element is varchar2
    Retrieve the employees whose id less than 105*/
 PROCEDURE proc_nested_tab_str_out(p_nested_tab_str OUT T_Nested_Tab_Str)
 IS
 BEGIN
    SELECT last_name BULK COLLECT INTO p_nested_tab_str FROM employees_bk WHERE employee_id<105;
 END;
 
 ---------------------------------------------------------------------------------------------------------------------------------
 /*This proc is invoked by .NET to test the output parameter with the type of VARRAY, 
    and the type of the collection element is varchar2
    Loop through each last name in the given varray, then update its salary*/
 PROCEDURE proc_varray_str_in(p_varray_str IN T_Varray_Str)
 IS
 BEGIN
    FORALL i IN p_varray_str.FIRST..p_varray_str.LAST
      UPDATE employees_bk SET salary=salary+1 WHERE last_name=p_varray_str(i);
    COMMIT;
 END;
 
 ---------------------------------------------------------------------------------------------------------------------------------
 /*This proc is invoked by .NET to test the output parameter with the type of object
    Update the last_name according to two fields last_name, employee_id in the given object*/
 PROCEDURE proc_obj_in(p_obj IN T_Object)
 IS
 BEGIN
    UPDATE employees_bk SET last_name=p_obj.last_name WHERE employee_id=p_obj.employee_id;
    COMMIT;
 END;
 
 ---------------------------------------------------------------------------------------------------------------------------------
 /*This proc is invoked by .NET to test the output parameter with the type of nested table, 
    and the type of the collection element is object
    Loop through each object in the given nested table, then update the last name of employee*/
 PROCEDURE proc_nested_tab_obj_in(p_nested_tab_obj IN T_Nested_Tab_Obj)
 IS
 BEGIN
    FORALL idx IN p_nested_tab_obj.FIRST..p_nested_tab_obj.LAST
      UPDATE employees_bk SET last_name=p_nested_tab_obj(idx).last_name WHERE employee_id=p_nested_tab_obj(idx).employee_id;
    COMMIT;
 END;
END;

 

1.引用游标（REF Cursor）

引用游标作为输出参数的情况十分常见。相较于MS SQL Server，Oracle存储过程无法直接返回结果集，而需要借助REF Cursor。REF Cursor实际上是指向服务器内存的指针，也就是说客户端调用获取的是一个指针，它指向服务器内存中的结果集数据。

虽然可能令熟悉MS SQL Server平台的开发者困惑，但是使用REF Cursor带来两个明显的好处：1）它可以延迟数据的交付，客户端获取的只是指针，数据只有在被请求时才传递；2）使用REF Cursor在存储过程之间传递结果集，可以最小化对性能影响，毕竟传递的只是指针而不是真实数据。

（使用DataReader）

 

cmd.CommandText = "pkg_odp_dotnet.proc_ref_cursor";
cmd.CommandType = CommandType.StoredProcedure;
//
OracleParameter op = new OracleParameter();
op.ParameterName = "p_ref_cursor";
op.Direction = ParameterDirection.Output;
op.OracleDbType = OracleDbType.RefCursor;
cmd.Parameters.Add(op);
OracleDataReader odr = cmd.ExecuteReader();
//
if (odr.HasRows)
{
    while (odr.Read())
    {
        string s = odr.GetDecimal(odr.GetOrdinal("employee_id")) + " "
            + odr.GetString(odr.GetOrdinal("first_name")) + " "
            + odr.GetString(odr.GetOrdinal("last_name"));
        Console.WriteLine(s);
    }
}

（使用DataAdapter）

 

cmd.CommandText = "pkg_odp_dotnet.proc_ref_cursor";
cmd.CommandType = CommandType.StoredProcedure;
//
OracleParameter op = new OracleParameter();
op.ParameterName = "p_ref_cursor";
op.Direction = ParameterDirection.Output;
op.OracleDbType = OracleDbType.RefCursor;
cmd.Parameters.Add(op);
OracleDataAdapter oda = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
oda.Fill(ds);
//
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
    string s = ds.Tables[0].Rows[i]["employee_id"] + " "
            + ds.Tables[0].Rows[i]["first_name"] + " "
            + ds.Tables[0].Rows[i]["last_name"];
    Console.WriteLine(s);
}

注意上面的Tables[0]，如果有多个输出参数是REF Cursor，则可以使用Tables[0]、Tables[1]，以此类推。DataReader似乎无法在有多个输出的REF Cursor有情况下使用。

方便起见，可以使用Oracle预定义的SYS_REFCURSOR，这样可以省去TYPE定义。在用法上与自定义的REF Cursor相同，就不再举具体的实例了。

 

2.关联数组（Associative array）

关于PL/SQL中的集合类型，已经在上一篇文章《ORACLE HANDBOOK系统之三:PL/SQL中的集合类型(COLLECTIONS IN PL/SQL)》中介绍了（http://www.cnblogs.com/KissKnife/archive/2011/04/07/2008158.html），这里介绍C#与Oracle交互时如何使用关联数组。

（作为输入参数）

 

cmd.CommandText = "pkg_odp_dotnet.proc_asso_array_num_in";
cmd.CommandType = CommandType.StoredProcedure;
//
OracleParameter op = new OracleParameter();
op.ParameterName = "p_asso_array_num";
op.Direction = ParameterDirection.Input;
op.OracleDbType = OracleDbType.Decimal;
//
op.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
op.Value = new decimal[] { 100, 101, 102 };
//
cmd.Parameters.Add(op);
cmd.ExecuteNonQuery();

相应的，如果定义的Associative array的元素是Varchar2，则OracleParameter.OracleDbType就是OracleDbType.Varchar2，为OracleParameter.value赋值要使用string[]。

（作为输出参数）

 

using Oracle.DataAccess.Types;
…
cmd.CommandText = "pkg_odp_dotnet.proc_asso_array_str_out";
cmd.CommandType = CommandType.StoredProcedure;
//
OracleParameter op = new OracleParameter();
op.ParameterName = "p_asso_array_str";
op.Direction = ParameterDirection.Output;
op.OracleDbType = OracleDbType.Varchar2;
//
op.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
//
op.Size = 12;
int[] iArray = new int[op.Size];
for (int i = 0; i < iArray.Length; i++)
{
    iArray[i] = 25;
}
op.ArrayBindSize = iArray;
//
cmd.Parameters.Add(op);
cmd.ExecuteNonQuery();
//
OracleString[] osArray = (OracleString[])op.Value;
for (int i = 0; i <= osArray.GetUpperBound(0); i++)
{
    Console.WriteLine(osArray[i].Value + ",");
}

OracleParameter.Size，如果给定的Size比实际查询所得的行数小，则会报ORA-06513错误，所以如果无法确定实际返回的集合长度，需要往大了取。

OracleParameter.ArrayBindSize，这里集合元素是变长的VARCHAR2，因此需要为每个元素指定其长度，由于表中last_name的长度是25，所以这里定义成了25，比25小不会报错，但是可能导致字符串被截断。如果要返回的集合元素是NUMBER，则可以不指定ArrayBindSize，

OracleString，不要天真地以为可以使用string[]数组来接收返回的集合，NO！需要OracleString[]，使用string[]将导致转化出错。如果要返回的集合元素是NUMBER，通常用OracleDecimal[]而不能直接用decimal[]。

通常来说，以集合类型作为输出参数类型的情况相对较少，对于结果集，使用REF Cursor是更好的选择。

 

3.嵌套表（Nested table）

对于Nested table，有两种方案可以选择，1）直接映射成C#中的数组，2）映射成C#的自定义类型。

第一种方案，需要首先声明一个Factory类并实现IOracleArrayTypeFactory接口，并使用OracleCustomTypeMappingAttribute(schema.type_name)指定需要映射的Nested table的名称，schema.type_name需要大写。

可以看到我们使用了OracleDbType.Array，并且指定了参数的UdtTypeName。赋值时直接使用了string[]。

[OracleCustomTypeMappingAttribute("HR.T_NESTED_TAB_STR")]
public class Nested_Tab_Mapping_To_Array_Factory : IOracleArrayTypeFactory
{
    public Array CreateArray(int i)
    {
        return new string[i];
    }
    //
    public Array CreateStatusArray(int i)
    {
        return new OracleUdtStatus[i];
    }
}

（作为输入参数）

 

cmd.CommandText = "pkg_odp_dotnet.proc_nested_tab_str_in";
cmd.CommandType = CommandType.StoredProcedure;
//
OracleParameter op = new OracleParameter();
op.ParameterName = "p_nested_tab_str";
op.Direction = ParameterDirection.Input;
op.OracleDbType = OracleDbType.Array;
op.UdtTypeName = "HR.T_NESTED_TAB_STR";
op.Value = new string[] { "King", "Olsen" };
//
cmd.Parameters.Add(op);
cmd.ExecuteNonQuery();

（作为输出参数，同样需要上面的Factory类）

 

cmd.CommandText = "pkg_odp_dotnet.proc_nested_tab_str_out";
cmd.CommandType = CommandType.StoredProcedure;
//
OracleParameter op = new OracleParameter();
op.ParameterName = "p_nested_tab_str";
op.Direction = ParameterDirection.Output;
op.OracleDbType = OracleDbType.Array;
op.UdtTypeName = "HR.T_NESTED_TAB_STR";
//
cmd.Parameters.Add(op);
cmd.ExecuteNonQuery();
//
if (op.Value != DBNull.Value)
{
    string[] result = (string[])op.Value;
    foreach (string s in result)
    {
        Console.WriteLine(s);
    }
}

对比而言，第一种方案中将Nested table映射成C#数组的做法更简洁。下面我们介绍第二种方案，稍微繁杂一点，除了需要Factory类（需要同时实现IOracleArrayTypeFactory与IOracleCustomTypeFactory两个接口）外，它还需要一个自定义类型：

[OracleCustomTypeMappingAttribute("HR.T_NESTED_TAB_STR")]
public class Nested_Tab_Mapping_To_Object_Factory : IOracleCustomTypeFactory, IOracleArrayTypeFactory
{
    public IOracleCustomType CreateObject()
    {
        return new Nested_Tab_Mapping_To_Object();
    }
    //
    public Array CreateArray(int i)
    {
        return new String[i];
    }
    //
    public Array CreateStatusArray(int i)
    {
        return new OracleUdtStatus[i];
    }
}
 
public class Nested_Tab_Mapping_To_Object : IOracleCustomType, INullable
{
    [OracleArrayMapping()]
    public string[] container;
    //
    private OracleUdtStatus[] statusArray;
    public OracleUdtStatus[] StatusArray
    {
        get
        {
            return statusArray;
        }
        set
        {
            statusArray = value;
        }
    }
    //
    private bool isNull;
    public bool IsNull
    {
        get
        {
            return isNull;
        }
    }
    //
    public static Nested_Tab_Mapping_To_Object Null
    {
        get
        {
            Nested_Tab_Mapping_To_Object nt = new Nested_Tab_Mapping_To_Object();
            nt.isNull = true;
            return nt;
        }
    }
    //
    public void ToCustomObject(OracleConnection conn, IntPtr pUdt)
    {
        object outStatusArray;
        container = (string[])OracleUdt.GetValue(conn, pUdt, 0, out outStatusArray);
        statusArray = (OracleUdtStatus[])outStatusArray;
    }
    public void FromCustomObject(OracleConnection conn, IntPtr pUdt)
    {
        OracleUdt.SetValue(conn, pUdt, 0, container, statusArray);
    }
}

（作为输入参数）

 

cmd.CommandText = "pkg_odp_dotnet.proc_nested_tab_str_in";
cmd.CommandType = CommandType.StoredProcedure;
//
OracleParameter op = new OracleParameter();
op.ParameterName = "p_nested_tab_str";
op.Direction = ParameterDirection.Input;
op.OracleDbType = OracleDbType.Object;
op.UdtTypeName = "HR.T_NESTED_TAB_STR";
Nested_Tab_Mapping_To_Object nt = new Nested_Tab_Mapping_To_Object();
nt.container = new string[] { "King", "Olsen" };
op.Value = nt;
//
cmd.Parameters.Add(op);
cmd.ExecuteNonQuery();

（作为输出参数）

 

cmd.CommandText = "pkg_odp_dotnet.proc_nested_tab_str_out";
cmd.CommandType = CommandType.StoredProcedure;
//
OracleParameter op = new OracleParameter();
op.ParameterName = "p_nested_tab_str";
op.Direction = ParameterDirection.Output;
op.OracleDbType = OracleDbType.Object;
op.UdtTypeName = "HR.T_NESTED_TAB_STR";
//
cmd.Parameters.Add(op);
cmd.ExecuteNonQuery();
//
if (op.Value != DBNull.Value)
{
    Nested_Tab_Mapping_To_Object o = (Nested_Tab_Mapping_To_Object)op.Value;
    foreach (string s in o.container)
    {
        Console.WriteLine(s);
    }
}

 

4.动态数组（VARRAY）

与Nested table用法相同。

 

5.对象（Object）

Oracle中Object类型的定义可以参见文章开头的T_Object。遇到T_Object时，处理起来类似于第4节中将Nested table映射成自定义对象的情况，同样需要一个自定义类型以及一个Factory类。

[OracleCustomTypeMappingAttribute("HR.T_OBJECT")]
public class Ora_Object_Factory : IOracleCustomTypeFactory
{
    public virtual IOracleCustomType CreateObject()
    {
        Ora_Object o = new Ora_Object();
        return o;
    }
}
 
public class Ora_Object : IOracleCustomType, INullable
{
    private bool isNull;
    private int employeeId;
    private string lastName;
    //
    public bool IsNull
    {
        get
        {
            return this.isNull;
        }
    }
    //
    public static Ora_Object Null
    {
        get
        {
            Ora_Object mo = new Ora_Object();
            mo.isNull = true;
            return mo;
        }
    }
    //
    [OracleObjectMappingAttribute("EMPLOYEE_ID")]
    public int EmployeeId
    {
        get
        {
            return employeeId;
        }
        set
        {
            employeeId = value;
        }
    }
    //
    [OracleObjectMappingAttribute("LAST_NAME")]
    public string LastName
    {
        get
        {
            return lastName;
        }
        set
        {
            lastName = value;
        }
    }
    //
    public void FromCustomObject(Oracle.DataAccess.Client.OracleConnection conn, System.IntPtr pUdt)
    {
        OracleUdt.SetValue(conn, pUdt, "EMPLOYEE_ID", this.EmployeeId);
        OracleUdt.SetValue(conn, pUdt, "LAST_NAME", this.LastName);
    }
    public void ToCustomObject(Oracle.DataAccess.Client.OracleConnection conn, System.IntPtr pUdt)
    {
        this.EmployeeId = ((int)(OracleUdt.GetValue(conn, pUdt, "EMPLOYEE_ID")));
        this.LastName = ((string)(OracleUdt.GetValue(conn, pUdt, "LAST_NAME")));
    }
}

（作为输入参数）

 

cmd.CommandText = "pkg_odp_dotnet.proc_obj_in";
cmd.CommandType = CommandType.StoredProcedure;
//
OracleParameter op = new OracleParameter();
op.ParameterName = "p_obj";
op.Direction = ParameterDirection.Input;
op.OracleDbType = OracleDbType.Object;
op.UdtTypeName = "HR.T_OBJECT";
//
Ora_Object mo = new Ora_Object();
mo.EmployeeId = 100;
mo.LastName = "abcd";
op.Value = mo;
//
cmd.Parameters.Add(op);
cmd.ExecuteNonQuery();

作为输出参数的情况很简单，这里就不重复了。

 

6.对象集合（Object collection）

来到终极篇，所谓对象集合，这里指的是本身是个集合类型，并且集合元素是Oracle的Object，例如我们在最开始定义的T_Nested_Tab_Obj。

由于它是Nested table，则我们可以按照第3节中的作法将其转化成C#中的Array，于是，我们需要一个Factory类并实现IOracleArrayTypeFactory接口：

[OracleCustomTypeMappingAttribute("HR.T_NESTED_TAB_OBJ")]
public class Nested_Tab_Obj_Mapping_To_Array_Factory : IOracleArrayTypeFactory
{
    public Array CreateArray(int i)
    {
        return new Ora_Object[i];
    }
    //
    public Array CreateStatusArray(int i)
    {
        return new OracleUdtStatus[i];
    }
}

又由于集合的元素是Oracle的Object类型，仿照第5节中的做法，我们分别需要一个自定义类型以及一个Factory类，由于集合元素也是使用的T_Object，所以这两个类与第5节中相同，这里不再重复写了。下面我们看看具体的调用代码：

cmd.CommandText = "pkg_odp_dotnet.proc_nested_tab_obj_in";
cmd.CommandType = CommandType.StoredProcedure;
//
OracleParameter op = new OracleParameter();
op.ParameterName = "p_nested_tab_obj";
op.Direction = ParameterDirection.Input;
op.OracleDbType = OracleDbType.Array;
op.UdtTypeName = "HR.T_NESTED_TAB_OBJ";
//
Ora_Object mo = new Ora_Object();
mo.EmployeeId = 100;
mo.LastName = "00000";
Ora_Object mo2 = new Ora_Object();
mo2.EmployeeId = 102;
mo2.LastName = "00000";
op.Size = 2;
op.Value = new Ora_Object[] { mo, mo2 };
//
cmd.Parameters.Add(op);
cmd.ExecuteNonQuery();

作为输出参数的情况也很容易写，这里就不举例子了，VARRAY的情况与Nested table基本一样，也不举例了。到这里，关于在C#中如何处理几类主要的复杂参数类型，已经基本介绍完了，对于一些更为复杂的，比如Object中包含Object等等情况，不太常见，就不介绍了。

PS，虽然Associative array中的元素也可以是Object，但在与C#类型做映射时似乎不能成功，可能根Associative array是PL/SQL类型（即不能通过CREATE TYPE创建独立的类型）有关，还没有找到相关的文档。