在.Net中使用Oracle的表类型和对象类型

在一般的数据存取操作过程中，如果要对一个主表和对应的子表进行插入操作，那么我们最常见的写法就是写两个存储过程或者SQL语句，一个负责主表数据插入，一个负责子表数据插入，然后在一个事务中实现主表和子表数据的插入。

现在遇到一个问题是，能否在一个存储过程中实现主表和子表数据的插入呢？那么就需要将一对多的数据作为存储过程的参数传入。这种情况下就需要使用表类型。下面以一个学生和班级的例子来说明：

先建立一个班级表和一个学生表，一个班级里面有多个学生。

代码

CREATE   TABLE  CLASS
(
   CLASSID      NUMBER  ( 38 )  PRIMARY   KEY ,
   CLASSNAME    VARCHAR2  ( 50  BYTE)  NOT   NULL
)；

CREATE   TABLE  STUDENT
(
  STUID         NUMBER ( 38 )  PRIMARY   KEY ,
  CLASSID       NUMBER ( 38 )                        NOT   NULL ,
  STUNAME      NVARCHAR2( 50 )                     NOT   NULL ,
  STUGENDER     CHAR ( 1  BYTE),
  STUBIRTHDAY  DATE,
  DESCRIPTION  NVARCHAR2( 2000 )
)；

CREATE  SEQUENCE CLASSID；

CREATE  SEQUENCE STUDENTID；

首先我们需要在Oracle中创建一个学生的对象类型，这个对象类型中就是学生的属性：

CREATE   OR   REPLACE  type StudentType  as  object
(
       StuName nvarchar2( 50 ),
       StuGender  char ( 1 ),
       StuBirthday date,
       StuDescription nvarchar2( 2000 )
);

 

接下来是将这个学生类型创建成表类型：

CREATE   OR   REPLACE  type StuList  as   table   of  StudentType;

接下来就是写我们的一个插入存储过程，将班级和学生列表作为参数传入，具体脚本为：

代码

CREATE   OR   REPLACE   PROCEDURE  ZY.AddClassStudent(

ClassName  in   varchar2 ,
Students  in  StuList
)  IS

BEGIN
    insert   into  Class  values (classid.nextval,ClassName);
    insert   into  Student(StuID,ClassID,StuName,Stugender,Stubirthday,Description)
    select  studentid.nextval,classid.currval,StuName,StuGender,StuBirthday,studescription
    from   TABLE (Students);
   EXCEPTION
      WHEN  NO_DATA_FOUND  THEN
        NULL ;
      WHEN  OTHERS  THEN
       RAISE;
END  AddClassStudent;

现在Oracle服务器上的各个对象已经创建完成，接下来就是要编写C#代码，连接到Oracle数据库，插入数据了。

在C#项目中添加Oracle.DataAccess的引用，这是Oracle为.Net开发的类库，可以从官网下载。添加引用后，再添加命名空间：

using  Oracle.DataAccess.Types;
using  Oracle.DataAccess.Client;

 

然后再创建Student对应的类：

代码

public   class  Student : IOracleCustomType
    {
         #region  IOracleCustomType Members

         public   void  FromCustomObject(Oracle.DataAccess.Client.OracleConnection con, IntPtr pUdt)
        {
             if  (StudentName  !=   null )
                OracleUdt.SetValue(con, pUdt,  " STUNAME " , StudentName);
             else
                 throw   new  NullReferenceException( " STUNAME is null " );
            OracleUdt.SetValue(con, pUdt,  " STUGENDER " , Gender);
            OracleUdt.SetValue(con, pUdt,  " STUBIRTHDAY " , Birthday);
            OracleUdt.SetValue(con, pUdt,  " STUDESCRIPTION " , Description);
        }

         public   void  ToCustomObject(Oracle.DataAccess.Client.OracleConnection con, IntPtr pUdt)
        {
            StudentName  =  (String)OracleUdt.GetValue(con, pUdt,  " STUNAME " );
            Gender  =  (String)OracleUdt.GetValue(con, pUdt,  " STUGENDER " );
            Birthday  =  (DateTime)OracleUdt.GetValue(con, pUdt,  " STUBIRTHDAY " );
            Description  =  (String)OracleUdt.GetValue(con, pUdt,  " STUDESCRIPTION " );
        }

         #endregion

        [OracleObjectMappingAttribute( " STUNAME " )]
         public  String StudentName {  get ;  set ; }

        [OracleObjectMapping( " STUGENDER " )]
         public   string  Gender {  get ;  set ; }

        [OracleObjectMapping( " STUBIRTHDAY " )]
         public  DateTime Birthday {  get ;  set ; }

        [OracleObjectMapping( " STUDESCRIPTION " )]
         public   string  Description {  get ;  set ; }

    }

并添加Student类对应Oracle对象类型的映射，通过Attribute来指定：

代码

[OracleCustomTypeMappingAttribute( " STUDENTTYPE " )]
public   class  StudentFactory : IOracleCustomTypeFactory
{
     #region  IOracleCustomTypeFactory Members

     public  IOracleCustomType CreateObject()
    {
         return   new  Student();
    }

     #endregion
}

现在StudentType类型已经创建完成，接下来就是创建StuList类型对应的类：

代码

[OracleCustomTypeMappingAttribute( " STULIST " )]
public   class  StudentList_TabFactory : IOracleArrayTypeFactory
{
     #region  IOracleArrayTypeFactory Members

     public  Array CreateArray( int  numElems)
    {
         return   new  Student[numElems];
    }

     public  Array CreateStatusArray( int  numElems)
    {
         return   null ;
    }

     #endregion
}

这里可以看到，返回的是Student的数组。现在准备工作都已经完成，接下来就是初始化一点数据，然后调用存储过程了，代码如下：

代码

Student s1  =   new  Student() { StudentName  =   " 张三 " , Birthday  =  Convert.ToDateTime( " 1984/12/29 " ), Gender  =   " M " , Description  =   " HAHA。 "  };
           Student s2  =   new  Student() { StudentName  =   " 李四 " , Birthday  =  Convert.ToDateTime( " 1982/12/29 " ), Gender  =   " F " , Description  =   " A。 "  };
           Student s3  =   new  Student() { StudentName  =   " 王五 " , Birthday  =  Convert.ToDateTime( " 1982/1/29 " ), Gender  =   " M " , Description  =   " B。 "  };
           Student s4  =   new  Student() { StudentName  =   " 小月月 " , Birthday  =  Convert.ToDateTime( " 1985/10/11 " ), Gender  =   " F " , Description  =   " C。 "  };
           List < Student >  ss1  =   new  List < Student > ();
           ss1.Add(s1);
           ss1.Add(s2);
           ss1.Add(s3);
           ss1.Add(s4);

            string  conn  =   " Data Source=BRDWDEV;User Id=zy;Password=123; " ;
            using  (OracleConnection oc  =   new  OracleConnection(conn))
           {
               oc.Open();

               OracleCommand cmd  =  oc.CreateCommand();
               cmd.CommandType  =  System.Data.CommandType.StoredProcedure;

               cmd.CommandText  =   " ZY.ADDCLASSSTUDENT " ;

               OracleParameter p0  =   new  OracleParameter();
               p0.OracleDbType  =  OracleDbType.Varchar2;
               p0.UdtTypeName  =   " CLASSNAME " ;
               p0.Value  =   " 测试班级名 " ;
               p0.Direction  =  ParameterDirection.Input;
               cmd.Parameters.Add(p0);

               OracleParameter p1  =   new  OracleParameter();
               p1.OracleDbType  =  OracleDbType.Array;
               p1.Direction  =  ParameterDirection.Input;
               p1.UdtTypeName  =   " STULIST " ; // 注意这里是类型，而不是参数名
               p1.Value  =  ss1.ToArray(); // 注意这里应该是数组
               cmd.Parameters.Add(p1);

                int  count  =  cmd.ExecuteNonQuery();
               Console.WriteLine(count);
               oc.Close();
           }

以此类推，其实还可以把班级建立对象类型，然后再建立班级列表类型，这样就可在一个存储过程中插入多个班级，每个班级多个学生的数据。