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

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；

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;

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 ; }

    }

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

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

     #endregion
}

[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 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();
           }