从c#调用Oracle插入存储过程

本文关键字：插入 存储过程 Oracle 调用 | 更新日期: 2023-09-27 18:11:52

存储过程:

CREATE OR REPLACE PROCEDURE insertMovie(
   p_m_id IN MOVIE.M_ID%TYPE,
   p_movie_name IN MOVIE.MOVIE_NAME%TYPE,
   p_year IN MOVIE.YEAR%TYPE,
   p_category IN MOVIE.CATEGORY%TYPE)
IS
BEGIN
  INSERT INTO MOVIE ("M_ID", "MOVIE_NAME", "YEAR", "CATEGORY") 
  VALUES (p_m_id, p_movie_name,p_year, p_category);
END;
/

这是我创建的将数据插入movie表的过程。现在我试着用c#调用它。我已经尝试了下面的代码，但它不工作。

在这方面的一点帮助将是非常感激的。谢谢你
    private void button1_Click(object sender, EventArgs e)
    {
        dbConnection conObj = new dbConnection();
        OracleConnection theConn = conObj.connFunc();
        String p_m_id, p_movie_name, p_year, p_category;
        p_m_id = movie_id.Text;
        p_movie_name = movie_name.Text;
        p_year = year.Text;
        p_category = category.Text;
        OracleParameter parChoiceIn = new OracleParameter();
        OracleParameter parDataIn = new OracleParameter();
        OracleParameter parOut = new OracleParameter();
        parChoiceIn.ParameterName = "p_m_id";
        parChoiceIn.OracleType = OracleType.Number;
        parChoiceIn.Size = 32;
        parChoiceIn.Direction = System.Data.ParameterDirection.Input;
        parChoiceIn.Value = p_m_id;
        parDataIn.ParameterName = "p_movie_name";
        parDataIn.OracleType = OracleType.VarChar;
        parDataIn.Size = 32;
        parDataIn.Direction = System.Data.ParameterDirection.Input;
        parDataIn.Value = p_movie_name;
        parDataIn.ParameterName = "p_year";
        parDataIn.OracleType = OracleType.Number;
        parDataIn.Size = 32;
        parDataIn.Direction = System.Data.ParameterDirection.Input;
        parDataIn.Value = p_year;
        parDataIn.ParameterName = "p_category";
        parDataIn.OracleType = OracleType.VarChar;
        parDataIn.Size = 32;
        parDataIn.Direction = System.Data.ParameterDirection.Input;
        parDataIn.Value = p_category;
        OracleCommand cmd = theConn.CreateCommand();
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandText = "insertMovie";
        cmd.Parameters.Add(parChoiceIn);
        cmd.Parameters.Add(parDataIn);
        cmd.Parameters.Add(parOut);
        theConn.Open();
        OracleDataAdapter adapter = new OracleDataAdapter(cmd);
        DataSet myset = new DataSet("theResCur");
        adapter.Fill(myset);
        theGrid.DataSource = myset;
        theGrid.DataSource = myset.Tables[0];
        theConn.Close();
    }
}

从c#调用Oracle插入存储过程

您正在创建一个parOut参数，但没有初始化它。

同样，您重新定义parDataIn参数三次，这样做，您的OracleCommand只接收最后定义的参数p_category。

我不明白你代码最后一部分的意图是什么。如果您只想简单地调用存储过程，也许您应该尝试这样做:

private void button1_Click(object sender, EventArgs e)
{
    dbConnection conObj = new dbConnection();
    OracleConnection theConn = conObj.connFunc();
    String p_m_id, p_movie_name, p_year, p_category;
    p_m_id = movie_id.Text;
    p_movie_name = movie_name.Text;
    p_year = year.Text;
    p_category = category.Text;
    theConn.Open();
    OracleCommand cmd = theConn.CreateCommand();
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    cmd.CommandText = "insertMovie";
    cmd.Connection = theConn;
    OracleParameter parChoiceIn = new OracleParameter();
    OracleParameter parDataIn = new OracleParameter();
    parChoiceIn.ParameterName = "p_m_id";
    parChoiceIn.OracleType = OracleType.Number;
    parChoiceIn.Size = 32;
    parChoiceIn.Direction = System.Data.ParameterDirection.Input;
    parChoiceIn.Value = p_m_id;
    cmd.Parameters.Add(parChoiceIn);
    parDataIn.ParameterName = "p_movie_name";
    parDataIn.OracleType = OracleType.VarChar;
    parDataIn.Size = 32;
    parDataIn.Direction = System.Data.ParameterDirection.Input;
    parDataIn.Value = p_movie_name;
    cmd.Parameters.Add(parDataIn);
    parDataIn = new OracleParameter();
    parDataIn.ParameterName = "p_year";
    parDataIn.OracleType = OracleType.Number;
    parDataIn.Size = 32;
    parDataIn.Direction = System.Data.ParameterDirection.Input;
    parDataIn.Value = p_year;
    cmd.Parameters.Add(parDataIn);
    parDataIn = new OracleParameter();
    parDataIn.ParameterName = "p_category";
    parDataIn.OracleType = OracleType.VarChar;
    parDataIn.Size = 32;
    parDataIn.Direction = System.Data.ParameterDirection.Input;
    parDataIn.Value = p_category;
    cmd.Parameters.Add(parDataIn);
    cmd.ExecuteNonQuery();
    theConn.Close();
}
相关文章：