|
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,
& [1] [2] 下一页
[ORACLE]C#中使用Oracle 存储过程笔记
| 
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
