存储过程编写及C#调用

1.首先，我需要一张基础表

-- 创建学生表，用于测试，不讲究
create table XUESHENG
(
  id        INTEGER,
  xing_ming VARCHAR2(25),
  yu_wen    NUMBER,
  shu_xue   NUMBER
);

2.给里边插入插入数据若干条

insert into XUESHENG (ID, XING_MING, YU_WEN, SHU_XUE)values (1, 'zhangsan', 70, 70);
insert into XUESHENG (ID, XING_MING, YU_WEN, SHU_XUE)values (2, 'lisi', 80, 80);
insert into XUESHENG (ID, XING_MING, YU_WEN, SHU_XUE)values (3, 'wangwu', 90, 90);

3.1创建一个最简单的，没有传入参数，没有传出参数，没有返回值的存储过程

create or replace procedure proc_xuesheng_insert is
begin
  insert into xuesheng values (4, 'maliu', 90, 90);
  commit;
end proc_xuesheng_insert;

exec proc_xuesheng_insert

  

//将马六的成绩插入表
int i = OracleHelper.ExcuteProcedureWithNoParameter(iniConnString, "proc_xuesheng_insert");

/// <summary>
        /// 执行 无参数 的存储过程
        /// </summary>
        /// <param name="connString">连接字符串</param>
        /// <param name="proceducreName">存储过程名</param>
        /// <returns>影响的条数</returns>
        public static int ExcuteProcedureWithNoParameter(string connString, string proceducreName)
        {
            int i = -1;
            using (OracleConnection oc = new OracleConnection(connString))
            {
                try
                {
                    oc.Open();
                    OracleCommand cmd = oc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;//指明是执行存储过程
                    cmd.CommandText = proceducreName;
                    i = cmd.ExecuteNonQuery();
                    oc.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "存储过程调用错误", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
                }
                finally
                {
                    oc.Close();
                }
            }
            return i;
        }

 

 3.2创建一个有传入值的存储过程

create or replace procedure proc_xuesheng_yuwen_add(
temp_name in varchar2,--要改分数的学生名
temp_num in number--要加多少分
) 
is
begin
  update xuesheng set xuesheng.yu_wen=xuesheng.yu_wen+temp_num where temp_name=xuesheng.xing_ming;
  commit;
end;

exec proc_xuesheng_yuwen_add('zhangsan',3)

 

//将xx学生的语文分数加i分
int j = OracleHelper.ExcuteProcedureWithOnlyParameterIn(iniConnString, "proc_xuesheng_score_add","lisi",1);

/// <summary>
        /// 执行只有输入字段内容的
        /// </summary>
        /// <param name="connString">连接字符串</param>
        /// <param name="proceducreName">存储过程名</param>
        /// <param name="studentName">学生名称</param>
        /// <param name="scoreNeed2Add">加分数</param>
        /// <returns></returns>
        public static int ExcuteProcedureWithOnlyParameterIn(string connString, string proceducreName, string studentName, int scoreNeed2Add)
        {
            int i = -1;
            using (OracleConnection oc = new OracleConnection(connString))
            {
                try
                {
                    oc.Open();
                    OracleCommand cmd = oc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;//指明是执行存储过程
                    cmd.CommandText = proceducreName;

                    cmd.Parameters.Add(new OracleParameter() { ParameterName = "TEMP_NAME", Value = studentName, OracleDbType = OracleDbType.Varchar2, Size = 20, Direction = ParameterDirection.Input });
                    cmd.Parameters.Add(new OracleParameter() { ParameterName = "TEMP_NUM", Value = scoreNeed2Add, OracleDbType = OracleDbType.Int32, Size = 8, Direction = ParameterDirection.Input });

                    i = cmd.ExecuteNonQuery();
                    oc.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "存储过程调用错误", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
                }
                finally
                {
                    oc.Close();
                }
            }
            return i;
        }

 

 

程序写到这里发现了一个问题：

如果调用的过程涉及到参数的话，就需要从外部按照顺序构造参数OracleParameter[]，然后传进来即可。

 

 //用参数话的方法，把只有输入值的内容传出来
            OracleParameter[] ops = {
                new OracleParameter() { ParameterName = "TEMP_NAME", Value = "lisi", OracleDbType = OracleDbType.Varchar2, Size = 20, Direction = ParameterDirection.Input },
                new OracleParameter() { ParameterName = "TEMP_NUM", Value = 1, OracleDbType = OracleDbType.Int32, Size = 8, Direction = ParameterDirection.Input }
            };
            int jj = OracleHelper.ExcuteProcedureWithOnlyParameterIn2(iniConnString, "proc_xuesheng_score_add", ops);


/// <summary>
        /// 执行只有输入字段内容的
        /// </summary>
        /// <param name="connString">连接字符串</param>
        /// <param name="proceducreName">存储过程名</param>
        /// <param name="ops">参数列表</param>
        /// <returns></returns>
        public static int ExcuteProcedureWithOnlyParameterIn2(string connString, string proceducreName,OracleParameter[] ops)
        {
            int i = -1;
            using (OracleConnection oc = new OracleConnection(connString))
            {
                try
                {
                    oc.Open();
                    OracleCommand cmd = oc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;//指明是执行存储过程
                    cmd.CommandText = proceducreName;

                    foreach (var op in ops)
                    {
                        cmd.Parameters.Add(op);
                    }

                    i = cmd.ExecuteNonQuery();
                    oc.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "存储过程调用错误", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
                }
                finally
                {
                    oc.Close();
                }
            }
            return i;
        }

 

 

 3.3创建一个有传入值传出值的存储过程

create or replace procedure proc_xuesheng_totalScore(
temp_name in varchar2,
temp_num  out number) is
  num_1 number;
  num_2 number;
begin
  select yu_wen, shu_xue
    into num_1, num_2
    from xuesheng
   where xing_ming = temp_name;
  --dbms_output.put_line(num_1 + num_2);
  temp_num := num_1 + num_2;
end;

var temp_num number
exec proc_xuesheng_totalScore('wangwu',:temp_num)

PL/SQL procedure successfully completed

temp_num
---------
180

int sumScore = -1;
OracleHelper.ExcuteProcedureWithParameterInOut(iniConnString, "proc_xuesheng_totalScore","lisi", out sumScore);

MessageBox.Show(sumScore.ToString());

 
       /// <summary>
        /// 执行有输入输出的
        /// </summary>
        /// <param name="connString">连接字符串</param>
        /// <param name="proceducreName">存储过程名</param>
        /// <param name="studentName">学生名称</param>
        /// <param name="sumScore">语文数学分数合计</param>
        /// <returns></returns>
        public static int ExcuteProcedureWithParameterInOut(string connString, string proceducreName, string studentName, out int sumScore)
        {
            int i = -1;
            sumScore = -1;
            using (OracleConnection oc = new OracleConnection(connString))
            {
                try
                {
                    oc.Open();
                    OracleCommand cmd = oc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;//指明是执行存储过程
                    cmd.CommandText = proceducreName;
                    
                    OracleParameter[] parameters = {
                        new OracleParameter() { ParameterName = "TEMP_NAME", Value = studentName, OracleDbType = OracleDbType.Varchar2, Size = 20, Direction = ParameterDirection.Input },
                        new OracleParameter() { ParameterName = "TEMP_NUM", Value = null, OracleDbType = OracleDbType.Int32, Size = 8, Direction = ParameterDirection.Output }
                    };
                    foreach (var x in parameters)
                    {
                        cmd.Parameters.Add(x);
                    }

                    i = cmd.ExecuteNonQuery();
                    sumScore = int.Parse(cmd.Parameters[1].Value.ToString());
                    oc.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "存储过程调用错误", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
                }
                finally
                {
                    oc.Close();
                }
            }
            return i;
        }

 

这个方法写出来，不太爽，因为需要在方法内构造这个存储过程，需要将他外置到具体这个存储的需求端

   int temp_num=-1;
            OracleParameter[] ops = {
                new OracleParameter() { ParameterName = "TEMP_NAME", Value = "lisi", OracleDbType = OracleDbType.Varchar2, Size = 20, Direction = ParameterDirection.Input },
                new OracleParameter() { ParameterName = "TEMP_NUM", Value = null, OracleDbType = OracleDbType.Int32, Size = 8, Direction = ParameterDirection.Output }
            };
            ops = OracleHelper.ExcuteProcedureWithParameterInOut2(iniConnString, "proc_xuesheng_totalScore", ops);
            foreach (OracleParameter op in ops)
            {
                if (op.Direction == ParameterDirection.Output)
                {
                    if (op.ParameterName == "TEMP_NUM")
                        temp_num = int.Parse(op.Value.ToString());
                }
            }
            MessageBox.Show(temp_num.ToString());




        /// <summary>
        /// 执行有输入输出的
        /// </summary>
        /// <param name="connString">连接字符串</param>
        /// <param name="proceducreName">存储过程名</param>
        /// <param name="ops">Oracle参数列表</param>
        /// <returns>赋值过output内容后的Oracle参数列表</returns>
        public static OracleParameter[] ExcuteProcedureWithParameterInOut2(string connString, string proceducreName, OracleParameter[] ops)
        {
            using (OracleConnection oc = new OracleConnection(connString))
            {
                try
                {
                    oc.Open();
                    OracleCommand cmd = oc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;//指明是执行存储过程
                    cmd.CommandText = proceducreName;
                    foreach (var op in ops)
                        cmd.Parameters.Add(op); 
                    cmd.ExecuteNonQuery();

                    for (int i = 0; i < ops.Length; i++)
                    {
                        ops[i] = cmd.Parameters[i];
                    }
                    oc.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "存储过程调用错误", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
                }
                finally
                {
                    oc.Close();
                }
            }
            return ops;
        }

 

 大功告成，今天开始工作时，自己只是个存储过程，没超过10行的小白，今天能完成这么多工作还是很欣喜的。