Oracle存储过程实现返回多个结果集 在构造函数方法中使用 dataset

原文 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;