.net中使用oracle创建的存储过程

存储过程(Procedure)
create [or replace] procedure pro_name
[(parameter1 [mode] datatype,parameter2 [mode] datatype, ...)]
is/as
.....//声明部分
begin
....//函数主题
exception
....//异常处理
end;

Demo:

无参：

create or replace procedure pro_peoinfo
is
cursor ci is select t.name,t.age from tbpeoinfo t ;
begin
for cc in ci loop
dbms_output.put_line(cc.name||chr(32)||cc.age);
end loop;
end;

//隐式游标的使用

有参：

--创建获得性别比例的存储过程
create or replace procedure pro_gendergroup(p_cursor out sys_refcursor)
is
begin
open p_cursor for
select count(*) as gendercount ,trim(t.sex) as gender from tbpeoinfo t group by trim(t.sex) having trim(t.sex)<>' ';
end pro_gendergroup;

 

 

create or replace procedure pro_inandput(pra1 in varchar2,pra2 out varchar2)
as
cst int;
begin
select count(*) into cst from tbpeoinfo t where t.sex=pra1;
if(cst>0) then
pra2:='有匹配的值';
else
pra2:='无匹配的值';
end if;
exception
when others then
rollback;
end;

 

以上代码是pl/sql中的代码：

.net中使用存成过程，以pro_gendergroup 为例

List<FlowPeopleGroupbyGenderModel> fpgenderlist = new List<FlowPeopleGroupbyGenderModel>();
DataTable dt;
try
{
OracleParameter[] para= {
new OracleParameter("p_cursor",OracleType.Cursor)//输出参数
};
para[0].Direction = ParameterDirection.Output;
dt=OperateData.ExecuteDataTable(Connection.ConnectionString,CommandType.StoredProcedure,"pro_gendergroup",para);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
FlowPeopleGroupbyGenderModel fpgender = new FlowPeopleGroupbyGenderModel();
fpgender.Gender = dt.Rows[i]["gender"].ToString();
fpgender.GenderCount = Convert.ToInt32(dt.Rows[i]["gendercount"].ToString());
fpgenderlist.Add(fpgender);
}
}
else
{
fpgenderlist = null;
}
}
catch (Exception)
{
fpgenderlist = null;
throw;
}

 

存储过程的好处就不提了，做dotnet开发用oracle的不是很多，希望能给初次使用oracle的朋友一点帮助，毕竟oracle中存储过程和sqlserver有点不一样。