Oracle存储过程例子

Dal.cs
OracleCommand myCmd = new OracleCommand();   --实例化
                myCmd.CommandType = CommandType.StoredProcedure; ---存储过程名称
                myCmd.CommandText = "PK_APAR.GET_ORG_TRADING_RECORD";  ---存储过程名称
                myCmd.Parameters.Add("V_ORG_ID", OracleDbType.Long).Value = orgId;  --参数
                myCmd.Parameters.Add("V_START_TIME", OracleDbType.Date).Value = StartTime;  --参数
                myCmd.Parameters.Add("V_END_TIME", OracleDbType.Date).Value = EndTime;   --参数
                myCmd.Parameters.Add("V_DIRECTION", OracleDbType.Varchar2).Value = direction;   --参数
                myCmd.Parameters.Add("L_RESULT", OracleDbType.RefCursor).Direction = ParameterDirection.Output;   --输出参数
                myCmd.Parameters.Add("RETURN_CODE", OracleDbType.Int64).Direction = ParameterDirection.Output;   --输出参数
                myCmd.Parameters.Add("RETURN_TEXT", OracleDbType.Varchar2, 2000).Direction = ParameterDirection.Output; --输出参数
                DataSet ds = Utilities.FillData(myCmd);
                return ds;

Utilities.cs
public static DataSet FillData(Oracle.DataAccess.Client.OracleCommand myCMD)
        {
            return FillData(myCMD,ConfigurationSettings.AppSettings["CenterDBConnStr"] --数据库连接符);
        }


public static DataSet FillData(Oracle.DataAccess.Client.OracleCommand myCMD,string connectString)
        {
            Oracle.DataAccess.Client.OracleConnection myConn = new Oracle.DataAccess.Client.OracleConnection(connectString);            

            myConn.Open();

            Oracle.DataAccess.Client.OracleTransaction trans = myConn.BeginTransaction();

            myCMD.Connection = myConn;
            

            Oracle.DataAccess.Client.OracleDataAdapter myAdapter = new Oracle.DataAccess.Client.OracleDataAdapter(myCMD);

            DataSet myData = new DataSet();

            try
            {

                myAdapter.Fill(myData);

                trans.Commit();
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);                     
            }
            finally
            {
                if( ConnectionState.Open == myConn.State )
                {
                    myConn.Close();
                }
            }

            return myData;
        }


WebCofing
<!--用户数据库连接--&gt
   

PROCEDURE GET_ORG_TRADING_RECORD(V_ORG_ID     IN TP_ORG.ORG_ID%TYPE,
                                   V_START_TIME IN TA_AABANK_TRADE.TRADE_TIME%TYPE,
                                   V_END_TIME   IN TA_AABANK_TRADE.TRADE_TIME%TYPE,
                                   V_DIRECTION  IN varchar2,
                                   L_RESULT     OUT REFCURSOR,
                                   RETURN_CODE  OUT NUMBER,
                                   RETURN_TEXT  OUT VARCHAR2) IS
  BEGIN
    OPEN L_RESULT FOR
      SELECT b.account_name,
             a.trade_time,
             d.base_code_name,
             a.trade_direction,
             a.trade_fee,
             a.account_balance,
             a.work_number,
             a.trade_remark
        from TA_AABANK_TRADE a, ta_aabank b, tp_org c, ta_base_code d
       where a.aabank_id = b.aabank_id
         and b.aabank_id = c.aabank_id
         and a.trade_type_id = d.base_code_id
         and c.org_id = V_ORG_ID
         and a.trade_direction like V_DIRECTION
         and a.trade_time between V_START_TIME and V_END_TIME;
    RETURN_CODE := 0;
    RETURN_TEXT := '成功！';
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      RETURN_CODE := 1;
      RETURN_TEXT := SQLERRM || '失败！';
  END GET_ORG_TRADING_RECORD;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29406868/viewspace-1064265/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29406868/viewspace-1064265/