c#中使用oracle 存儲(chǔ)過程筆記
1. 調(diào)用包含out/ in out類型參數(shù)的存儲(chǔ)過程
存儲(chǔ)過程:
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 ;
/
調(diào)用方法:
oracleparameter retpar = new oracleparameter(“channelid”, oracletype.number);
retpar.direction = parameterdirection.output;//此處和存儲(chǔ)過程中的類型匹配
//如果為in out 類型 此處應(yīng)聲//明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);
//有返回值時(shí)必須使用executereader方法
object val = param[ 3 ].value;
return int.parse( val.tostring() );
2. 存儲(chǔ)過程返回記錄集
存儲(chǔ)過程必須寫在包中,再調(diào)用.
包的寫法:
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;
/
調(diào)用:
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)//此處為包體中聲明的游標(biāo)類型
};
param[ 0 ].value = siteid;
return oraclehelper.executereader(oraclehelper.conn_string_base, commandtype.storedprocedure,"pkg_cms.site_getsitedata", param);
//調(diào)用時(shí)候先寫包名
3. oracle存儲(chǔ)過程中其它的方法
字符串操作
instr(str,maker)//取字符串中字符的位置
substr(str,beginnum,len)//取子串
to_char()//將數(shù)字轉(zhuǎn)到字符串
|| //拼串 相當(dāng)于+ 號(hào)
length(oldword) //取字符串長(zhǎng)度
時(shí)間類
to_date('datestr','yyyy-mi-dd')//字符串轉(zhuǎn)成date型”’yyyy-mm-dd’”
date1-date2=天數(shù)
本文來源于網(wǎng)頁設(shè)計(jì)愛好者web開發(fā)社區(qū)http://www.html.org.cn收集整理，歡迎訪問。
