我们来自五湖四海,不为别的,只因有共同的爱好,为中国互联网发展出一分力!

C#中使用Oracle 存储过程笔记

2014年05月09日22:06 阅读: 33303 次

标签: C#中使用Oracle 存储过程笔记

?调用包含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=天数

分享到: 更多
?2019 安全焦点 版权所有.