Oracle创建包、存储过程，及C#中调用

create or replace package body PK_DataTransfer is

--  积分卡合并                            2009-7-14
    procedure p_MemberMergence(s_SMemberID in nvarchar2,s_TMemberID in nvarchar2,n_Usersn in number) is
    begin
         update plus.tsell s
            set s.memberid=s_TMemberID,s.lastupdatedby=n_Usersn,s.lastupdatedon=sysdate
          where s.memberid=s_SMemberID;

         update plus.tsellrgoods r
            set r.memberid=s_TMemberID,r.lastupdatedby=n_Usersn,r.lastupdatedon=sysdate
          where r.memberid=s_SMemberID;

         update plus.tpointmanage p
            set p.ghmembersn=(select ghmembersn
                                from tmember
                               where memberid=s_TMemberID)
               ,p.lastupdatedby=n_Usersn,p.lastupdatedon=sysdate
         where p.ghmembersn=(select ghmembersn
                               from tmember
                              where memberid=s_SMemberID);

         update plus.tmember m
            set m.mpointsum=nvl(m.mpointsum,0)+(select mpointsum
                                                  from plus.tmember a
                                                 where a.memberid=s_SMemberID)
               ,m.lastupdatedby=n_Usersn,m.lastupdatedon=sysdate
          where m.memberid=s_TMemberID;

         update plus.tmember m
            set m.flag='*',m.pointsum=0,m.amountsum=0,m.lastupdatedby=n_Usersn,m.lastupdatedon=sysdate
          where m.memberid=s_SMemberID;

         commit;
    end;

 

C#中执行Oracle中的存储过程
 OracleParameter[] pms={new OracleParameter("s_SMemberID",OracleType.NVarChar,50),
            new OracleParameter("s_TMemberID",OracleType.NVarChar,50),
            new OracleParameter("n_Usersn",OracleType.Number)
           };
    pms[0].Value=ds.Tables[0].Rows[0][0].ToString();
    pms[0].Direction=ParameterDirection.Input;
    pms[1].Value=ds.Tables[0].Rows[0][1].ToString();
    pms[1].Direction=ParameterDirection.Input;
    pms[2].Value=Convert.ToInt64(this.Session["UserId"].ToString());
    pms[2].Direction=ParameterDirection.Input;
    dboperReq.RunProcedure("PK_DataTransfer.p_MemberMergence",pms);

public void RunProcedure(string storedProcName,OracleParameter[] parameters)
  {
   OracleConnection oracleconn = new OracleConnection() ;
   OracleCommand oraclecmd = new OracleCommand() ;
   oracleconn.ConnectionString =ConfigurationSettings.AppSettings["DBConnfortrans"] ;
   oraclecmd.Connection = oracleconn ;
   oracleconn.Open() ;   
   OracleTransaction otrans=oracleconn.BeginTransaction();         
   
   try
   {
    oraclecmd.Transaction=otrans;
    oraclecmd.CommandText=storedProcName;//声明存储过程名
    oraclecmd.CommandType=CommandType.StoredProcedure;
    foreach(OracleParameter parameter in parameters)
    {
     oraclecmd.Parameters.Add(parameter);
    }
    oraclecmd.ExecuteNonQuery();//执行存储过程
    otrans.Commit();
    oraclecmd.Dispose();
    oracleconn.Dispose();
   }
   catch(Exception err)
   {
    otrans.Rollback();
    oraclecmd.Dispose();
    oracleconn.Dispose();
    throw err ;
   }
  }