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

C#中使用Oracle 存储过程笔记
C#中使用Oracle 存储过程笔记
C#中使用Oracle 存储过程笔记1. 调用包含out/ in out类型参数的存储过程存储过程:CREATE OR REPLACE PROCEDURE "SITE_EDITSITEDATAEXIST" (id_ number,name_ varchar2,httpRoot_ varchar2,flag out integer )//out 只具备输出功能 in out 为输入/输出型astempNum 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 类型 此处应声//明InputOutputOracleParameter[] 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=天数共同交流 欢迎指正 MSN:yutao728@hotmail.com

查看上一页  返回分类首页 返回96PC首页  查看下一页