oracle存储过程中使用Ref Cursor强类型游标返回结果集
创建包头
create or replace package Pkg_Web_Client is --定义一个类型 TYPE Record_VL_InOutShortInfo is record ( vlioh_id vehicle_line_in_out_history.vlioh_id%type, li_no vehicle_line_in_out_history.li_no%type, vlioh_enter_time vehicle_line_in_out_history.vlioh_enter_time%type, vlioh_leave_time vehicle_line_in_out_history.vlioh_leave_time%type, vlioh_record_time vehicle_line_in_out_history.vlioh_record_time%type, --use_days number, vlioh_dir vehicle_line_in_out_history.vlioh_dir%type ); --定义一个游标变量 REF游标,用于返回结果集到C#客户端中 type Ret_Cursor_VL_InOutShortInfo is ref cursor return Record_VL_InOutShortInfo; -- Author : -- Created : 2014-10-30 17:16 -- Purpose : 从vehicle_line_in_out_history中查询指定时间段的数据,并用REF CURSOR游标返回结果集 procedure proc_GetVLineIOShortList( --listLineNo in Type_Varchar2_Short_Array,--待查询的线路 queryStartTime in date,--查询的起始时刻 queryEndTime in date,--查询的结束时刻 retCursor out Ret_Cursor_VL_InOutShortInfo--用于返回结果集的REF CURSOR ); end Pkg_Web_Client;
创建包体
create or replace package body Pkg_Web_Client is -- Author : -- Created : 2014-10-30 17:16 -- Purpose : 从vehicle_line_in_out_history中查询指定时间段的数据,并用REF CURSOR游标返回结果集 procedure proc_GetVLineIOShortList( --listLineNo in Type_Varchar2_Short_Array,--待查询的线路 queryStartTime in date,--查询的起始时刻 queryEndTime in date,--查询的结束时刻 retCursor out Ret_Cursor_VL_InOutShortInfo--用于返回结果集的REF CURSOR ) is begin open retCursor for select vlioh_id,li_no,vlioh_enter_time,vlioh_leave_time,vlioh_record_time --,(sysdate-vlioh_enter_time) use_days ,vlioh_dir from vehicle_line_in_out_history where vlioh_record_time>=queryStartTime and vlioh_record_time<=queryEndTime --and li_no in (select * from listLineNo) ; end; end Pkg_Web_Client;
C#端调用代码返回结果集
这里使用了ODP.NET中的Oracle.DataAccess.dll
OracleParameter parmStartTime = new OracleParameter("queryStartTime", OracleDbType.Date, ParameterDirection.Input); parmStartTime.Value = beginTime; OracleParameter parmEndTime = new OracleParameter("queryEndTime", OracleDbType.Date, ParameterDirection.Input); parmEndTime.Value = endTime; OracleParameter parmRetCursor = new OracleParameter("retCursor", OracleDbType.RefCursor, ParameterDirection.Output); OracleParameter[] paramList = new OracleParameter[] { parmStartTime, parmEndTime, parmRetCursor }; using (OracleConnection con = new OracleConnection(DbHelperOra.DefaultConnectionString)) { OracleCommand oracleCmd = new OracleCommand(); oracleCmd.CommandText = "Pkg_Web_Client.proc_GetVLineIOShortList"; oracleCmd.CommandType = CommandType.StoredProcedure; oracleCmd.Connection = con; foreach (OracleParameter oracleParameter in paramList) { oracleCmd.Parameters.Add(oracleParameter); } DataSet ds = new DataSet(); OracleDataAdapter adapter = new OracleDataAdapter(); try { con.Open(); adapter.SelectCommand = oracleCmd; adapter.Fill(ds); } catch { throw; } finally { adapter.Dispose(); con.Close(); } }
这里需要说明一下,在Record_VL_InOutShortInfo中有个字段为number类型,在ref cursor返回结果集时,由于oracle的number类型精度和.net的number类型进度导致返回结果集时抛出异常“算术运算导致溢出”, Arithmetic overflow,后来查到一个外国网站(http://www.stefan-taube.de/b0025.htm)提到精度问题,注释掉此字段后运行正常,DataSet可获取正常数据。