017_.Net 调用 Oracle存储过程例子

protected void Button1_Click(object sender, EventArgs e)
    {

        //----例一 调返回值的过程
        string MyConnString = ConfigurationManager.ConnectionStrings["myDbConn"].ConnectionString;
        OracleConnection Oraclecon = new OracleConnection(MyConnString);
        OracleCommand myCMD = new OracleCommand();
        myCMD.Connection = Oraclecon;
        myCMD.CommandText = "CNDBA.CNPRJ_010";
        myCMD.CommandType = CommandType.StoredProcedure;

        OracleParameter para = new OracleParameter("p_koban", OracleType.VarChar, 7);
        para.Value = "AMA2001";
        para.Direction = ParameterDirection.Input;
        myCMD.Parameters.Add(para);

        para = new OracleParameter("p_status", OracleType.VarChar, 3);
        para.Direction = ParameterDirection.Output;
        myCMD.Parameters.Add(para);

        para = new OracleParameter("P_KOMEIE", OracleType.VarChar, 15);
        para.Direction = ParameterDirection.Output;
        myCMD.Parameters.Add(para);

        para = new OracleParameter("P_KOMEIC", OracleType.VarChar, 30);
        para.Direction = ParameterDirection.Output;
        myCMD.Parameters.Add(para);

        para = new OracleParameter("P_CONTRACT_OFFICE", OracleType.VarChar, 3);
        para.Direction = ParameterDirection.Output;
        myCMD.Parameters.Add(para);

        para = new OracleParameter("P_JOB_SITE_OFFICE", OracleType.VarChar, 3);
        para.Direction = ParameterDirection.Output;
        myCMD.Parameters.Add(para);

        Oraclecon.Open();
        myCMD.ExecuteNonQuery();

        for (int i = 0; i < myCMD.Parameters.Count; i++)
        {

            Response.Write(myCMD.Parameters[i].Value.ToString() + "<br>");

        }
        Oraclecon.Close();

        //-------------------------

       //----例二 调返回记录集的过程

        // create the connection
        string MyConnString = ConfigurationManager.ConnectionStrings["myDbConn"].ConnectionString;
        OracleConnection conn=new OracleConnection(MyConnString);
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = conn;
        //cmd.CommandText = "CMDBA.SELECT_CONTRACT_OFFICES.Get_CONTRACT_OFFICES";
        cmd.CommandText = "DCDBA.SELECT_CONTRACT_OFFICES1.Get_C1010";
        cmd.CommandText = "SYS.SELECT_CONTRACT_OFFICES2.Get_C1010";
        // add the parameters including the two REF CURSOR types to retrieve
        // the two result sets
        cmd.Parameters.Add("cur_CONTRACT_OFFICES", OracleType.Cursor).Direction =ParameterDirection.Output;
        cmd.CommandType = CommandType.StoredProcedure;
        // create the DataAdapter and map tables
        OracleDataAdapter da = new OracleDataAdapter(cmd);
        da.TableMappings.Add("Table", "EMPLOYEES");
        // create and fill the DataSet
        DataSet ds = new DataSet();
        da.Fill(ds);

        this.DataGrid1.DataSource = ds.Tables[0].DefaultView;
        this.DataGrid1.DataBind();

      

    }