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_Inf
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=天數
