[mysql] MySQL 5.0 的 Stored Procedure

分類: 技術分享 作者: daniel

14 5 月 2009

O'reilly 的 MySQL Stored Procedure ProgrammingMySQL 在 5.0 版後,增加了 Stored Procedure 的功能, 讓 MySQL 能夠真正的具備大型商用資料庫的架勢。根據維基百科的說明,Stored Procedure 預存程式是一種在資料庫中儲存的程式,同時也是可供外部程式呼叫的一種資料庫物件,可以視為資料庫中的函數或子程式。透過它的幫助,首先我們能夠減少使用外部程式(如 PHP)時,因多次 Query 資料庫而花費的往返時間;而且 Stored Procedure 儲存在資料庫時,可以視為已經編譯完成的程式,所以在執行效能上比一般 SQL Query 更為迅速。其次由於它存在資料庫中,因此如果有多個外部程式必須同時應用到這個程式時,只要呼叫同一支 Stored Procedure 即可;不需要因為更換外部程式而重新撰寫程式,大大的提升程式的可維護性。不過受限於它是於資料庫中使用的程式,當然在可用的變數型態,以及支援的函數上都遠不如真正的程式語言;所以只適合用在簡單不複雜的一般邏輯判斷上。左圖是 O'Reilly 於 2006 年出版的《MySQL Stored Procedure Programming》;雖然並沒有中文版,不過 O'Reilly 願意專門為 MySQL 的 Stored Procedure 出書,這個功能對於開發者來說的重要性可見一斑。

稍微簡單介紹一下 Stored Procedure 大概的使用方式,雖然說是資料庫中的程式,但是使用的語法仍是非常的 SQL Like。在開始之前,首先我們必須重新定義 MySQL 的結束符號;因為內建的結束符號是「;」,而 Stored Procedure 的語法會頻繁的使用到「;」。所以必須變更它:

-- 網路上比較常看到的方式是變更成「//」,當然也可以用任何其他喜歡的符號代替
DELIMITER //

接下來當然是 CREATE 一個 PROCEDURE 囉:

-- 建立一個名為 test 的 PROCEDURE,可以帶入數字型態的 var1 與 var2 二個參數
CREATE PROCEDURE test(var1 INT(10), var2 INT(10))
BEGIN
 
-- 處理的邏輯寫在這裡,BEGIN 與 END 中間的區塊
 
-- 特別注意,在 END 之後必須加上我們先前重新定義的結束符號,表示 PROCEDURE 的結尾
END//

在處理的邏輯中定義變數:

-- 定義變數及其型態,同時也可以選擇性的給予預設值;供 PROCEDURE 處理時暫存使用
DECLARE value INT(10) DEFAULT 0;

為變數賦值:

-- 設定變數的值
SET value = var1 * var2;

PROCEDURE 提供簡單的邏輯判斷,如:IF 判斷、WHILE 迴圈等:

-- 使用 IF 進行數值大小的判斷
IF value < 10 THEN
    SELECT 'value < 10';
ELSEIF value >= 10 AND value <= 20 THEN
    SELECT 'value between 10 and 20';
ELSE
    SELECT 'value > 20';
END IF;

所以一個完整的 Stored Procedure 寫法會是:

-- 如果存在時先刪除 PROCEDURE
DROP PROCEDURE IF EXISTS test;
-- 重新定義結束符號
DELIMITER //
 
-- 建立一個名為 test 的 PROCEDURE,可以帶入數字型態的 var1 與 var2 二個參數
CREATE PROCEDURE test(var1 INT(10), var2 INT(10))
BEGIN
 
    -- 處理的邏輯寫在這裡,BEGIN 與 END 中間的區塊
    -- 定義變數
    DECLARE value INT(10);
 
    -- 變數賦值
    SET value = var1 * var2;
 
    -- 邏輯判斷
    IF value < 10 THEN
        SELECT 'value < 10';
    ELSEIF value >= 10 AND value <= 20 THEN
        SELECT 'value between 10 and 20';
    ELSE
        SELECT 'value > 20';
    END IF;
 
-- 特別注意,在 END 隻後必須加上我們先前重新定義的結束符號,表示 PROCEDURE 的結尾
END//

建立完成之後,只要在 MySQL 中使用:

CALL test(3,5);

就可以看到結果

當然除了簡單的顯示外,處理邏輯中也可以使用新增、修改,甚至是建立資料表等等 SQL 語法;在資料統計彙整的使用上,相當的方便呢!雖然在邏輯能力以及函數的使用上遠遠不及真正的程式語言,但是在統計簡單重複性的資料時,執行的效能比其他外部程式要優秀的多!同時因為 Stored Procedure 是儲存於資料庫中,當統計的方式變動時只要修改它,而不需要對聯結同一個資料庫而使用不同的外部程式做個別的修改;大大的提升可維護性。

感謝 Neo 與 Jace 的說明,以及網路上前輩們的文章;以後在處理大量統計資料時,又多了一種可以選擇的解決方案囉!^^

參考資料:

我要留言

關於這裡

這個部落格分享了哇寶在電子商務領域的技術及資訊,希望能讓更多人一起為台灣的網路產業加油。