Oracle 預存程序使用樣本

因為工作的需要，最近一直在寫預存程序。 工作了 3 年，一直都是做管理，也沒有正兒八經的去寫過預存程序， 這次正好可以好好練習一下。

在這裡說一條使用預存程序很重要的理由： 預存程序只在創造時進行編譯，以後每次執行預存程序都不需再重新編譯，而一般 SQL 陳述式每執行一次就編譯一次 , 所以使用預存程序可提高資料庫執行速度。

Oracle 預存程序定義和優點與函數區別

-----------------------

Oracle 查看錶預存程序觸發器函數等對象定義語句的方法

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

)

1 2 3 下一頁