本文转自:http://www.cnblogs.com/studyzy/archive/2010/10/13/1850161.html
在一般的数据存取操作过程中,如果要对一个主表和对应的子表进行插入操作,那么我们最常见的写法就是写两个存储过程或者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(); } 以此类推,其实还可以把班级建立对象类型,然后再建立班级列表类型,这样就可在一个存储过程中插入多个班级,每个班级多个学生的数据。 【出自博客园深蓝居,转载请注明作者出处】