在一般的数据存取操作过程中,如果要对一个主表和对应的子表进行插入操作,那么我们最常见的写法就是写两个存储过程或者sql语句,一个负责主表数据插入,一个负责子表数据插入,然后在一个事务中实现主表和子表数据的插入。
现在遇到一个问题是,能否在一个存储过程中实现主表和子表数据的插入呢?那么就需要将一对多的数据作为存储过程的参数传入。这种情况下就需要使用表类型。下面以一个学生和班级的例子来说明:
先建立一个班级表和一个学生表,一个班级里面有多个学生。
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
代码
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;
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
首先我们需要在oracle中创建一个学生的对象类型,这个对象类型中就是学生的属性:
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
create or replace type studenttype as object
stuname nvarchar2(50),
stugender char(1),
stubirthday date,
studescription nvarchar2(2000)
);
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
接下来是将这个学生类型创建成表类型:
create or replace type stulist as table of studenttype;
接下来就是写我们的一个插入存储过程,将班级和学生列表作为参数传入,具体脚本为:
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
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;
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
现在oracle服务器上的各个对象已经创建完成,接下来就是要编写c#代码,连接到oracle数据库,插入数据了。
在c#项目中添加oracle.dataaccess的引用,这是oracle为.net开发的类库,可以从官网下载。添加引用后,再添加命名空间:
using oracle.dataaccess.types;
using oracle.dataaccess.client;
然后再创建student对应的类:
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
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; }
}
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
并添加student类对应oracle对象类型的映射,通过attribute来指定:
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
[oraclecustomtypemappingattribute("studenttype")]
public class studentfactory : ioraclecustomtypefactory
{
#region ioraclecustomtypefactory members
public ioraclecustomtype createobject()
return new student();
#endregion
}
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
现在studenttype类型已经创建完成,接下来就是创建stulist类型对应的类:
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
[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;
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
这里可以看到,返回的是student的数组。现在准备工作都已经完成,接下来就是初始化一点数据,然后调用存储过程了,代码如下:
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
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();
}
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLlR2bjlHcvN2LcNXZnFWbp9CXt92YuM3ZvxmYuNmLu9Wbt92Yvw1LcpDc0RHaiojIsJye.gif)
以此类推,其实还可以把班级建立对象类型,然后再建立班级列表类型,这样就可在一个存储过程中插入多个班级,每个班级多个学生的数据。