Oracle 存储过程 使用示例

因为工作的需要，最近一直在写存储过程。 工作了 3 年，一直都是做管理，也没有正儿八经的去写过存储过程， 这次正好可以好好练习一下。

 

            在这里说一条使用存储过程很重要的理由： 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 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) */

CREATE OR REPLACE procedure proc_trade (

  v_tradeid in number ,                         -- 交易 id

  v_third_ip in varchar2 ,                      -- 第三方 ip

  v_third_time in date ,                       -- 第三方完成时间

  v_thire_state in number ,                    -- 第三方状态

  o_result out number ,                        -- 返回值

  o_detail out varchar2                      -- 详细描述

)

as

-- 定义变量

  v_error varchar2 ( 500 );

begin

    -- 对变量赋值

     o_result := 0 ;

     o_detail := ' 验证失败 ' ;

 

    -- 业务逻辑处理

    if v_tradeid > 100 then

        insert into table_name (...) values(...);

        commit;

    elsif v_tradeid < 100 and v_tradeid > 50 then

        insert into table_name (...) values(...);

        commit;

    else

            goto log;

    end if;

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

<<log>>

        o_result := 1 ;

-- 捕获异常

exception

   when no_data_found

   then

      result := 2 ;

   when dup_val_on_index

   then

      result := 3 ;

   when others

   then

      result := - 1 ;

end proc_trade ;

 

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

 

如：

CREATE OR REPLACE PROCEDURE spdispsms (

   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 ：

CREATE OR REPLACE PROCEDURE proc_test

AS

   CURSOR c1

   IS

      SELECT    * FROM dat_trade ;

BEGIN

   FOR x IN c1

   LOOP

      DBMS_OUTPUT.put_line ( x .id);

   END LOOP;

END proc_test ;

 

示例 2 ：

CREATE OR REPLACE PROCEDURE proc_test

AS

BEGIN

   FOR x IN (SELECT    power_id FROM sys_power )

   LOOP

      DBMS_OUTPUT.put_line ( x . power_id );

   END LOOP;

END proc_test ;

 

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

示例 1 ：

CREATE OR REPLACE PROCEDURE proc_test ( v_num IN NUMBER )

AS

BEGIN

     for x in 1 .. 100 loop

         dbms_output.put_line(x);

     end loop;

END proc_test ;        

 

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

CREATE OR REPLACE PROCEDURE proc_test ( v_num IN NUMBER )

AS

BEGIN

   FOR x IN 1 .. v_num

   LOOP

      DBMS_OUTPUT.put_line ( x );

   END LOOP;

END proc_test ;        

 

 

2.2   loop 循环

   LOOP

      DELETE FROM orders

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

                                      'yyyy-mm-dd' )

              AND ROWNUM < 1000 ;

 

      EXIT WHEN SQL % ROWCOUNT < 1 ;

      COMMIT;

   END LOOP;

 

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

 

2.3  while 循环

CREATE OR REPLACE PROCEDURE proc_test ( v_num IN NUMBER )

AS

   i   NUMBER := 1 ;

BEGIN

   WHILE i < v_num

   LOOP

      BEGIN

         i := i + 1 ;

         DBMS_OUTPUT.put_line ( i );

      END;

   END LOOP;

END proc_test ;

 

3. 存储过程中的判断

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

 

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

CREATE OR REPLACE PROCEDURE proc_test ( v_num IN NUMBER )

AS

BEGIN

   IF v_num < 10

   THEN

      DBMS_OUTPUT.put_line ( v_num );

   ELSIF v_num > 10 AND v_num < 50

   THEN

      DBMS_OUTPUT.put_line ( v_num - 10 );

   ELSE

      DBMS_OUTPUT.put_line ( v_num - 50 );

   END IF;

END proc_test ;

 

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

CREATE OR REPLACE PROCEDURE proc_test ( v_num IN NUMBER )

AS

BEGIN

    case v_num

        when 1 then

             DBMS_OUTPUT.put_line ( v_num );

        when 2 then

            DBMS_OUTPUT.put_line ( v_num );

        when 3 then

            DBMS_OUTPUT.put_line ( v_num );

        else null;

    end case;   

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( 然后当前游标所指向的行位置 )

 

CREATE OR REPLACE PROCEDURE proc_test (

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

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

)

AS

t_tmp   table_name% ROWTYPE;

BEGIN

   OPEN ref_cursor FOR

      SELECT    *

        FROM    (   SELECT    *

                    FROM    table_name

                   WHERE    state = 41

                ORDER BY    id)

       WHERE    ROWNUM < checknum ;

-- 循环游标

   LOOP

      FETCH ref_cursor INTO    t_tmp ;

      EXIT WHEN ref_cursor% NOTFOUND;

--    DBMS_OUTPUT.put_line ( t_tmp .id);

      UPDATE    table_name

         SET    state = 53

       WHERE    id = t_tmp .id;

      COMMIT;

   END LOOP;

 

   CLOSE ref_cursor ;

END proc_test ;

 

 

 

 

 

 

 

五 . 存储过程的调试

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

            SQL>show errors

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

 

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

 

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