網頁

2017/8/30

Oracle 如何建立Stored Procedures

Stored Procedure(預存程序)為資料庫中的獨立程式單元,包括Function, Procedure及Package皆通稱為Stored Procedure。

Oracle SQL Developer中,
Function稱作「函數」,
Procedure稱作「程序」,
Package稱作「套裝程式」。



由於Stored Procedure是在資料庫中執行,因此可以在Stored Procedure中直接撰寫SQL。使用Stored Procedure的好處是可以減少應用程式對資料庫的連線,直接在資料庫將資料處理好後才傳回給應用程式。當然資料庫伺服器的效能負擔就會吃重一些。

Procedure結構包含以下要素...

  • 宣告區 - 開始於DECLARE關鍵字,用來宣告程序會用到的變數或常數,為選擇性的區塊。
  • 執行區 - 開始於BEGIN,結束於END,程式邏輯放在包夾的範圍,是必要的區塊。
  • 例外處理區 - 開始於EXCEPTION關鍵字,用來處理執行區塊中可能出現的錯誤,為選擇性的區塊
Header AS
[宣告區 ...]
BEGIN
[執行區 ...]
[例外處理 ...]
END;

Header部分用來命名程序,並說明此程序是函式Function,程序Procedure還是Package。

主邏輯程式放在BEGINEND之間,BEGINEND成對。

BEGIN ... END之間可以再包有BEGIN ... END

EXCEPTION要寫在在BEGINEND中。


建立Procedure範例如下,Procedure的名稱為MY_PROCEDURE,沒有傳入傳出參數,也沒有宣告區及例外區,BEGINEND的之間僅執行了DBMS_OUTPUT.put_line來印出Hello World字樣,效果同Java的System.out.println(記得開啟SERVEROUTPUT)。

CREATE OR REPLACE PROCEDURE MY_PROCEDURE IS 
BEGIN
    DBMS_OUTPUT.put_line ('Hello World!');
END MY_PROCEDURE;

記得END後面也要放Procedure的名稱,本例為MY_PROCEDURE

執行以上敘述便會在資料庫的程序目錄中建立Procedure,要重新整理才會出現。

在SQL中可使用EXEC指令來執行Procedure,

EXEC MY_PROCEDURE;

執行結果如下。

Hello World!

下一個範例建立有參數的Procedure。

CREATE OR REPLACE PROCEDURE MY_PROCEDURE2 (
    i_p_1 VARCHAR2,
    i_p_2 VARCHAR2,
    i_p_3 NUMBER
) 
IS
BEGIN
    DBMS_OUTPUT.put_line (i_p_1);
    DBMS_OUTPUT.put_line (i_p_2);
    DBMS_OUTPUT.put_line (i_p_3);
END MY_PROCEDURE2;

定義Procedure參數時必須給定參數名稱,資料型態,而Mode預設值則是選擇性的。

在此範例中參數僅定義名稱及資料型態,MODE及預設值皆空白。MODE若為空白的話則預設為IN。另外建立PROCEDURE時參數的資料型態不用指明長度。

由於這個Procedure有參數,所以執行時要帶入參數,例如

EXEC MY_PROCEDURE2('hello', 'world', 999);

執行結果如下

hello
world
999


下面的範例Procedure中在宣告區中宣告了兩個變數

CREATE OR REPLACE PROCEDURE MY_PROCEDURE3 (
    i_p_1 VARCHAR2
) IS
    v_1 VARCHAR2(12);
    v_2 NUMBER(10);
BEGIN
    v_1 := 'HELLO WORLD';
    v_2 := 9999999999;
    DBMS_OUTPUT.put_line (i_p_1);
    DBMS_OUTPUT.put_line (v_1);
    DBMS_OUTPUT.put_line (v_2);
END MY_PROCEDURE3;

執行Procedure

EXEC MY_PROCEDURE3('hello world');

執行結果輸出如下。

hello world
HELLO WORLD
9999999999

如要建立Package,請參考Oracle 如何建立Package

如果覺得文章有幫助的話還幫忙點個Google廣告,感恩。


沒有留言:

張貼留言