原文 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[].Direction = ParameterDirection.Output;
parms[].Direction = ParameterDirection.Output;
parms[].Direction = ParameterDirection.Output;
parms[].Value = type;
parms[].Value = BargainOrderCode;
parms[].Value = ParkUserId;
parms[].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;