在.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;

這裡可以看到，傳回的是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();

           }

以此類推，其實還可以把班級建立對象類型，然後再建立班級清單類型，這樣就可在一個存儲過程中插入多個班級，每個班級多個學生的資料。

本文轉自深藍居部落格園部落格，原文連結：http://www.cnblogs.com/studyzy/archive/2010/10/13/1850161.html，如需轉載請自行聯系原作者