原文 Oracle存储过程实现返回多个结果集 在构造函数方法中使用 dataset
DataSet相当你用的数据库； DataTable相当于你的表。一个 DataSet 可以包含多个 DataTable。如果用datatable只适合返回一个结果集（游标）的信息。
例：
public DataSet Get_TRAFeeQinfenStatus(int type, string BargainOrderCode, string ParkUserId, string BerthCode) { OracleParameter[] parms = { new OracleParameter("VRETURN_LIST1", OracleType.Cursor), new OracleParameter("VRETURN_LIST2", OracleType.Cursor), new OracleParameter("VRETURN_LIST3", OracleType.Cursor), new OracleParameter("Vtype", OracleType.Number), new OracleParameter("VBargainOrderCode", OracleType.VarChar), new OracleParameter("VParkUserId", OracleType.VarChar), new OracleParameter("VBerthCode", OracleType.VarChar) }; parms[0].Direction = ParameterDirection.Output; parms[1].Direction = ParameterDirection.Output; parms[2].Direction = ParameterDirection.Output; parms[3].Value = type; parms[4].Value = BargainOrderCode; parms[5].Value = ParkUserId; parms[6].Value = BerthCode; DataSet ds = OracleHelper.ExecuteDataset(CommandType.StoredProcedure, string.Format("{0}.GET_TRAFEEQINFENSTATUS", CADRE_SZRPP_PK), parms); return ds; }
oracle存储过程实现：
PROCEDURE GET_TRAFEEQINFENSTATUS ( VRETURN_LIST1 OUT OUTPUTLIST,--查退费单 VRETURN_LIST2 OUT OUTPUTLIST,--查欠费单 VRETURN_LIST3 OUT OUTPUTLIST,--缴费记录 VTYPE IN INTEGER DEFAULT NULL, VBARGAINORDERCODE IN TRA_ORDERPAYMENTINFO.BARGAINORDERCODE%TYPE DEFAULT NULL, VPARKUSERID IN TRA_ORDERPAYMENTINFO.PARKUSERID%TYPE DEFAULT NULL, VBERTHCODE IN TRA_ORDERPAYMENTINFO.BERTHCODE%TYPE DEFAULT NULL ) IS VTYPENUMBER INTEGER; VPAYSTATUS NVARCHAR2(4000); VARREARSSTATUS NVARCHAR2(4000); VREFUNDSTATUS NVARCHAR2(4000); BEGIN SELECT VTYPE INTO VTYPENUMBER FROM DUAL; IF (VTYPENUMBER=2) THEN OPEN VRETURN_LIST1 FOR SELECT REFUNDSTATUS,PAYSTATUS FROM TRA_REFUNDORDER WHERE BARGAINORDERCODE=VBARGAINORDERCODE AND PARKUSERID=VPARKUSERID AND BERTHCODE=VBERTHCODE; --解决: ORA-24338: 未执行语句句柄 一定要进行对游标输出参数值赋值 ELSE OPEN VRETURN_LIST1 FOR SELECT VREFUNDSTATUS AS REFUNDSTATUS,VPAYSTATUS AS PAYSTATUS FROM DUAL; END IF; IF (VTYPENUMBER=3 ) THEN OPEN VRETURN_LIST2 FOR SELECT ARREARSSTATUS FROM TRA_ARREARSORDER WHERE BARGAINORDERCODE=VBARGAINORDERCODE AND PARKUSERID=VPARKUSERID AND BERTHCODE=VBERTHCODE; ELSE OPEN VRETURN_LIST2 FOR SELECT VARREARSSTATUS AS ARREARSSTATUS FROM DUAL; END IF; IF (VTYPENUMBER=5 ) THEN OPEN VRETURN_LIST3 FOR SELECT PAYSTATUS FROM TRA_ORDERPAYMENTINFO WHERE BARGAINORDERCODE=VBARGAINORDERCODE AND PARKUSERID=VPARKUSERID AND BERTHCODE=VBERTHCODE; ELSE OPEN VRETURN_LIST3 FOR SELECT VPAYSTATUS AS PAYSTATUS FROM DUAL; END IF; END;