在一般的数据存取操作过程中如果要对一个主表和对应的子表进行插入操作那么我们最常见的写法就是写两个存储过程或者SQL语句一个负责主表数据插入一个负责子表数据插入然后在一个事务中实现主表和子表数据的插入
现在遇到一个问题是能否在一个存储过程中实现主表和子表数据的插入呢？那么就需要将一对多的数据作为存储过程的参数传入这种情况下就需要使用表类型下面以一个学生和班级的例子来说明
先建立一个班级表和一个学生表一个班级里面有多个学生
CREATE TABLE CLASS
(
CLASSID NUMBER () PRIMARY KEY
CLASSNAME VARCHAR ( BYTE) NOT NULL
)
CREATE TABLE STUDENT
(
STUID NUMBER() PRIMARY KEY
CLASSID NUMBER() NOT NULL
STUNAME NVARCHAR() NOT NULL
STUGENDER CHAR( BYTE)
STUBIRTHDAY DATE
DESCRIPTION NVARCHAR()
)
CREATE SEQUENCE CLASSID
CREATE SEQUENCE STUDENTID
首先我们需要在Oracle中创建一个学生的对象类型这个对象类型中就是学生的属性
CREATE OR REPLACE type StudentType as object
(
StuName nvarchar()
StuGender char()
StuBirthday date
StuDescription nvarchar()
);
接下来是将这个学生类型创建成表类型
CREATE OR REPLACE type StuList as table of StudentType;
接下来就是写我们的一个插入存储过程将班级和学生列表作为参数传入具体脚本为
CREATE OR REPLACE PROCEDURE ZYAddClassStudent(
ClassName in varchar
Students in StuList
) IS
BEGIN
insert into Class values(classidnextvalClassName);
insert into Student(StuIDClassIDStuNameStugenderStubirthdayDescription)
select studentidnextvalclassidcurrvalStuNameStuGenderStuBirthdaystudescription
from TABLE(Students);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
RAISE;
END AddClassStudent;
现在Oracle服务器上的各个对象已经创建完成接下来就是要编写C#代码连接到Oracle数据库插入数据了
在C#项目中添加OracleDataAccess的引用这是Oracle为Net开发的类库可以从官网下载添加引用后再添加命名空间
using OracleDataAccessTypes;
using OracleDataAccessClient;
然后再创建Student对应的类
public class Student : IOracleCustomType
{
#region IOracleCustomType Members
public void FromCustomObject(OracleDataAccessClientOracleConnection con IntPtr pUdt)
{
if (StudentName != null)
OracleUdtSetValue(con pUdt STUNAME StudentName);
else
throw new NullReferenceException(STUNAME is null);
OracleUdtSetValue(con pUdt STUGENDER Gender);
OracleUdtSetValue(con pUdt STUBIRTHDAY Birthday);
OracleUdtSetValue(con pUdt STUDESCRIPTION Description);
}
public void ToCustomObject(OracleDataAccessClientOracleConnection con IntPtr pUdt)
{
StudentName = (String)OracleUdtGetValue(con pUdt STUNAME);
Gender = (String)OracleUdtGetValue(con pUdt STUGENDER);
Birthday = (DateTime)OracleUdtGetValue(con pUdt STUBIRTHDAY);
Description = (String)OracleUdtGetValue(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 s = new Student() { StudentName = 张三 Birthday = ConvertToDateTime(//) Gender = M Description = HAHA };
Student s = new Student() { StudentName = 李四 Birthday = ConvertToDateTime(//) Gender = F Description = A };
Student s = new Student() { StudentName = 王五 Birthday = ConvertToDateTime(//) Gender = M Description = B };
Student s = new Student() { StudentName = 小月月 Birthday = ConvertToDateTime(//) Gender = F Description = C };
List<Student> ss = new List<Student>();
ssAdd(s);
ssAdd(s);
ssAdd(s);
ssAdd(s);
string conn = Data Source=BRDWDEV;User Id=zy;Password=;;
using (OracleConnection oc = new OracleConnection(conn))
{
ocOpen();
OracleCommand cmd = ocCreateCommand();
cmdCommandType = SystemDataCommandTypeStoredProcedure;
cmdCommandText = ZYADDCLASSSTUDENT;
OracleParameter p = new OracleParameter();
pOracleDbType = OracleDbTypeVarchar;
pUdtTypeName = CLASSNAME;
pValue = 测试班级名;
pDirection = ParameterDirectionInput;
cmdParametersAdd(p);
OracleParameter p = new OracleParameter();
pOracleDbType = OracleDbTypeArray;
pDirection = ParameterDirectionInput;
pUdtTypeName = STULIST;//注意这里是类型而不是参数名
pValue = ssToArray();//注意这里应该是数组
cmdParametersAdd(p);
int count = cmdExecuteNonQuery();
ConsoleWriteLine(count);
ocClose();
}
以此类推其实还可以把班级建立对象类型然后再建立班级列表类型这样就可在一个存储过程中插入多个班级每个班级多个学生的数据
