Oracle 存储过程 使用示例 .

此文转自大虾DAVE 地址：http://blog.csdn.net/tianlesoftware/article/details/6147230 可以说我第一次数据库安装成功也是从他博客里学习的，在此表示下感谢

在这里说一条使用存储过程很重要的理由：存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

           

            Oracle存储过程定义和优点与函数区别

            http://blog.csdn.net/tianlesoftware/archive/2010/01/27/5261364.aspx

 

            Oracle 查看表存储过程触发器函数等对象定义语句的方法

            http://blog.csdn.net/tianlesoftware/archive/2010/06/19/5679293.aspx

 

 

1. 存储过程格式

/* Formatted on 2011/1/17 13:20:44 (QP5 v5.115.810.9015) */

CREATEORREPLACEprocedure proc_trade(

  v_tradeid innumber,                       --交易id

  v_third_ip invarchar2,                    --第三方ip

  v_third_time indate,                     --第三方完成时间

  v_thire_state innumber,                  --第三方状态

  o_result outnumber,                      --返回值

  o_detail outvarchar2                    --详细描述

)

as

-- 定义变量

  v_error varchar2(500);

begin

    --对变量赋值

     o_result:=0;

     o_detail:='验证失败';

 

    --业务逻辑处理

    if v_tradeid>100then

        insertinto table_name(...)values(...);

        commit;

    elsif v_tradeid<100and v_tradeid>50then

        insertinto table_name(...)values(...);

        commit;

    else

            gotolog;

    endif;

--跳转标志符，名称自己指定

<<log>>

        o_result:=1;

--捕获异常

exception

   whenno_data_found

   then

      result :=2;

   whendup_val_on_index

   then

      result :=3;

   whenothers

   then

      result :=-1;

end proc_trade;

 

            在上面这个存储过程中使用了输入参数，并返回输出参数，这里的参数类型是我们自己指定的。 这种写法可行，但是最好使用%type 来获取参数的类型(table_name.column_name%TYPE)。 这样就不会出现参数类型的错误。

 

如：

CREATEORREPLACEPROCEDUREspdispsms(

   aempid      IN       otherinfo.empid%TYPE,

   amsg        IN       otherinfo.msg%TYPE,

   abillno     IN       otherinfo.billno%TYPE,

   ainfotype   IN       otherinfo.infotype%TYPE,

   aopid       IN       otherinfo.OPERATOR%TYPE,

   ainfoid     OUT      otherinfo.infoid%TYPE,

   RESULT      OUT      INTEGER

)

 

2. 存储过程中的循环

            存储过程写的是业务逻辑，循环是常用的处理方法之一。

 

2.1  for ... in ... loop 循环

2.1.1：循环遍历游标

示例1：

CREATEORREPLACEPROCEDURE proc_test

AS

   CURSOR c1

   IS

      SELECT  * FROMdat_trade;

BEGIN

   FOR xIN c1

   LOOP

      DBMS_OUTPUT.put_line(x.id);

   ENDLOOP;

END proc_test;

 

示例2：

CREATEORREPLACEPROCEDURE proc_test

AS

BEGIN

   FOR xIN(SELECT  power_id FROMsys_power)

   LOOP

      DBMS_OUTPUT.put_line(x.power_id);

   ENDLOOP;

END proc_test;

 

2.1. 2：根据数值进行循环

示例1：

CREATEORREPLACEPROCEDURE proc_test(v_num INNUMBER)

AS

BEGIN

     for xin1..100loop

         dbms_output.put_line(x);

     endloop;

END proc_test;       

 

示例2：在过程里指定输入参数v_num.在调用过程时指定循环次数。

CREATEORREPLACEPROCEDURE proc_test(v_num INNUMBER)

AS

BEGIN

   FOR xIN1.. v_num

   LOOP

      DBMS_OUTPUT.put_line(x);

   ENDLOOP;

END proc_test;       

 

 

2.2  loop 循环

   LOOP

      DELETEFROMorders

            WHERE senddate<TO_CHAR(ADD_MONTHS(SYSDATE,-3),

                                     'yyyy-mm-dd')

              ANDROWNUM< 1000;

 

      EXITWHENSQL%ROWCOUNT< 1;

      COMMIT;

   ENDLOOP;

 

这里的SQL%ROWCOUNT是隐士游标。除了这个，还有其他几个：%found，%notfound， %isopen。

 

2.3  while 循环

CREATEORREPLACEPROCEDURE proc_test(v_num INNUMBER)

AS

   i   NUMBER:=1;

BEGIN

   WHILE i< v_num

   LOOP

      BEGIN

         i := i +1;

         DBMS_OUTPUT.put_line(i);

      END;

   ENDLOOP;

END proc_test;

 

3. 存储过程中的判断

            判断也是存储过程中最常用的方法之一。

 

3.1  if ... elsif ... else ... 判断

CREATEORREPLACEPROCEDUREproc_test(v_numINNUMBER)

AS

BEGIN

   IF v_num<10

   THEN

      DBMS_OUTPUT.put_line(v_num);

   ELSIF v_num>10AND v_num<50

   THEN

      DBMS_OUTPUT.put_line(v_num -10);

   ELSE

      DBMS_OUTPUT.put_line(v_num -50);

   ENDIF;

END proc_test;

 

3.2  case ... when ... end case 判断

CREATEORREPLACEPROCEDURE proc_test(v_num INNUMBER)

AS

BEGIN

    case v_num

        when1then

             DBMS_OUTPUT.put_line(v_num);

        when2then

            DBMS_OUTPUT.put_line(v_num);

        when3then

            DBMS_OUTPUT.put_line(v_num);

        elsenull;

    endcase;  

END proc_test;

 

4. 游标

            存储过程中使用游标也是很常见的。 这里的游标分两种：

 

4.1  Cursor型游标(不能用于参数传递)

            这种方法具体参考 2.1.1：循环遍历游标中的示例。

 

4.2  SYS_REFCURSOR型游标

            该游标是Oracle以预先定义的游标，可作出参数进行传递。

            注意一点：SYS_REFCURSOR只能通过OPEN方法来打开和赋值

 

4.2.1  我们可以使用这种类似的游标来返回一个结果集：

 

CREATE OR REPLACE procedure  proc_test(

checknum in number,  --每次返回的数据量

ref_cursor out sys_refcursor  --返回的结果集，游标

)

as

begin

    open ref_cursor for select * from (select * from dat_trade where state=41 order by id) where rownum<checknum;

end proc_test;

/

 

SYS_REFCURSOR中可使用三个状态属性：

（1）.            %NOTFOUND(未找到记录信息)

（2）.            %FOUND(找到记录信息)

（3）.            %ROWCOUNT(然后当前游标所指向的行位置)

 

CREATEORREPLACEPROCEDURE proc_test(

checknum     IN    NUMBER,--每次返回的数据量

ref_cursor      OUT sys_refcursor--返回的结果集，游标

)

AS

t_tmp   table_name%ROWTYPE;

BEGIN

   OPEN ref_cursorFOR

      SELECT  *

        FROM  (  SELECT  *

                    FROM  table_name

                   WHERE  state =41

                ORDERBY  id)

       WHERE  ROWNUM< checknum;

--循环游标

   LOOP

      FETCH ref_cursorINTO   t_tmp;

      EXITWHEN ref_cursor%NOTFOUND;

--    DBMS_OUTPUT.put_line(t_tmp.id);

      UPDATE  table_name

         SET  state =53

       WHERE  id= t_tmp.id;

      COMMIT;

   ENDLOOP;

 

   CLOSE ref_cursor;

END proc_test;

 

 

 

 

五. 存储过程的调试

            如果使用PL/SQL Developer 或者TOAD 工具的话，调试还是很方便的。 如果是在Sqlplus里，我们可以使用：

            SQL>show errors

            来查看错误。不过在开发中估计也很少有人直接使用sqlplus来写存储过程。 效率低，调试又麻烦。 还是使用工具方便点。我一直使用的是Toad的。

 

            如果想在某处退出存储过程，直接使用Return;就可以了。 与存储过程编写相关的数组和游标， 这两块说起来还是有很多东西。 在上面的示例中， 也简单的举了几个有关游标与存储过程编写的例子。

 

            总之，写代码都是都是费脑子的事，相比之下还是做管理DBA舒服点，虽然压力大很多，至少不用这么费心思去整理业务逻辑。