Oracle 存储过程学习笔记(三)

三、

1. 今天我们首先写一个涨工资的存储过程，给每个低于5k工资的人涨点钱。

       CREATE OR REPLACE PROCEDURE p_test(forRaise in number)

       as

    begin

      for v_emp in (select * from emp) loop

          if(v_emp.salary<'5000') then

        update emp set salary =(v_emp.salary+forRaise) where emp_id=v_emp.emp_id;

        end if;

      end loop;

       end;

       调用：

       DECLARE

             FORRAISE NUMBER;

       BEGIN

             FORRAISE :=1;

              P_TEST(FORRAISE => FORRAISE);

       END;

       这里要注意两个地方：

（1）       循环中begin和end不是必须的

（2）       这里增加了if语句，其格式比较简单就不细说了。

（3）       这里没有定义游标，在游标的位置直接用select语句代替了。

       2． 这里顺便介绍下另外一种循环，while循环，实现同样的功能

              CREATE OR REPLACE PROCEDURE p_test(forRaise in number)

              as

                     cursor c is select * from emp;

                     v_row emp%rowtype;

              begin

             open c;

             fetch c into v_row;

             while  c%found Loop

                  if(v_row.salary<'5000') then

                        update emp set salary =(v_row.salary+forRaise) where emp_id=v_row.emp_id;

                  end if;

           fetch c into v_row;

             end loop;

             close c;

              end;

              说明：

（1）       这里需要定义一个游标，还要定义一个emp%rowtype类型的变量，%前面是表名，后面表示这个表的一行，

（2）       在使用游标前还要显示的打开游标，并将其赋值到row中，使用后关闭游标。

（3）       C%found表示只有row中有值的时候才会进行循环。

（4）       经过对比发现于while循环相比，for循环更像是C#中的foreach，使用起来方便很多。

（5）       另从9i开始提供的动态游标类型sys_refcursor，以前的版本必须要先创建一个ref cursor的类型，现在多个

3.        现在我们使用程序调用下涨工资的存储过程，这个存储过程是没有返回值的。

OracleConnection conn = new OracleConnection();   //创建一个新连接

conn.ConnectionString = "Data Source='ds';user id='id ';password='pwd';"; OracleCommand cmd = new OracleCommand("P_TEST", conn);

cmd.CommandType = CommandType.StoredProcedure;

OracleParameter p1 = new OracleParameter("forRaise", OracleType.UInt32);

p1.Value = 1;

p1.Direction = System.Data.ParameterDirection.Input;

cmd.Parameters.Add(p1);

conn.Open();

int r=cmd.ExecuteNonQuery();

    conn.Close();

    这样我们就可以给员工涨工资了，说明：

（1）         虽然给多个人涨了公司，但r的值是1，他只调用了1个存储过程，或者说受影响的只是1个。

（2）         参数P1的名字必须和存储过程中的一样否则会提示：调用 'P_TEST' 时参数个数或类型错误。

4.         现在我们试着从存储过程中得到点结果吧，我先看看我给几个人涨了工资，我每个月一共要多付多少钱了。

         改动存储过程：

         CREATE OR REPLACE PROCEDURE p_test(forRaise in number,res out number)

         is

    begin

      res:=0;

      for v_emp in (select * from emp) loop

             if(v_emp.salary<'4000') then

          update emp set salary =(v_emp.salary+forRaise) where emp_id=v_emp.emp_id;

          res:=res+1;

        end if;

      end loop;

         end;

         增加了一个out 的number型，记录改动的次数。然后相应的调整C#程序，获得这个改动的次数。

OracleCommand cmd = new OracleCommand("P_TEST", conn);

cmd.CommandType = CommandType.StoredProcedure;

OracleParameter p1 = new OracleParameter("forRaise", OracleType.UInt32);

p1.Value = 4;

p1.Direction = System.Data.ParameterDirection.Input;

OracleParameter p2 = new OracleParameter("res", OracleType.UInt32);

p2.Value = 10;

p2.Direction = System.Data.ParameterDirection.Output;

cmd.Parameters.Add(p1);

cmd.Parameters.Add(p2);

conn.Open();

int r=cmd.ExecuteNonQuery();

 conn.Close();

MessageBox.Show(“你已经给:”+p2.Value.ToString()+“人涨了工资”);

好了，今天就到这，下次返回数据集。