在一般的数据存取操作过程中,如果要对一个主表和对应的子表进行插入操作,那么我们最常见的写法就是写两个存储过程或者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();
}
以此类推,其实还可以把班级建立对象类型,然后再建立班级列表类型,这样就可在一个存储过程中插入多个班级,每个班级多个学生的数据。