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

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