IDC资讯中心
c#中使用oracle 存储过程笔记
1. 调用包含out/ in out类型参数的存储过程
存储过程:
create or replace procedure "site_editsitedataexist"
(id_ number,
name_ varchar2,
httproot_ varchar2,
flag out integer )//out 只具备输出功能 in out 为输入/输出型
as
tempnum integer;
begin
flag:=0;
select count(id) into tempnum from website_info where name = name_ and id<>id_;
if tempnum > 0 then
flag:=3;
end if;
select count(id) into tempnum from website_info where httproot = httproot_ and id<>id_;
if tempnum > 0 then
flag:=4;
end if;
commit;
end ;
/
调用方法:
oracleparameter retpar = new oracleparameter(“channelid”, oracletype.number);
retpar.direction = parameterdirection.output;//此处和存储过程中的类型匹配
//如果为in out 类型 此处应声//明inputoutput
oracleparameter[] param = new oracleparameter[ 2 ]
{
new oracleparameter(“subjectid”, oracletype.varchar, 60)
};
param[ 0 ].value = 0;
oraclehelper.executereader( oraclehelper.conn_string_base, commandtype.storedprocedure,
"site_editsitedataexist" ,param);
//有返回值时必须使用executereader方法
object val = param[ 3 ].value;
return int.parse( val.tostring() );
2. 存储过程返回记录集
存储过程必须写在包中,再调用.
包的写法:
create or replace package pkg_cms
as
type myrctype is ref cursor;
procedure site_getsitedata(id_ number, p_rc out myrctype);
end pkg_cms;
/
create or replace package body pkg_cms
as
procedure site_getsitedata(id_ number,p_rc out myrctype)
is
begin
open p_rc for
select id, name, url, folder_name, desccms, char_name,
db_address, db_user, db_password, db_name, db_connstring, httproot
from website_info
where id=id_;
end site_getsitedata;
end pkg_cms;
/
调用:
oracleparameter[] param = new oracleparameter[ 2 ]
{
new oracleparameter(parm_id_, oracletype.number, 8),
new oracleparameter("p_rc", oracletype.cursor, 2000, parameterdirection.output, true, 0, 0, "",datarowversion.default, convert.dbnull)//此处为包体中声明的游标类型
};
param[ 0 ].value = siteid;
return oraclehelper.executereader(oraclehelper.conn_string_base, commandtype.storedprocedure,"pkg_cms.site_getsitedata", param);
//调用时候先写包名
3. oracle存储过程中其它的方法
字符串操作
instr(str,maker)//取字符串中字符的位置
substr(str,beginnum,len)//取子串
to_char()//将数字转到字符串
|| //拼串 相当于+ 号
length(oldword) //取字符串长度
时间类
to_date(datestr,yyyy-mi-dd)//字符串转成date型”’yyyy-mm-dd’”
date1-date2=天数
