網頁

▼

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。

主邏輯程式放在BEGIN與END之間，BEGIN與END成對。

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

EXCEPTION要寫在在BEGIN及END中。


建立Procedure範例如下，Procedure的名稱為MY_PROCEDURE，沒有傳入傳出參數，也沒有宣告區及例外區，BEGIN和END的之間僅執行了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廣告，感恩。


沒有留言:

張貼留言