SQL - 05. Stored Procedures and Functions

[Admin1]

Stored Procedures and Functions

使用Stored Routines的優點：http+www^slime/com*tw

• 更彈性的SQL語法
• 錯誤處理能力
• 合於SQL標準
• 程式碼封裝
• 可重覆利用
• 分離程式邏輯
• 易於維護
• 減少所需的網路頻寬
• Server端的upgrade也可以佳惠Client端
• 更好的安全性

Stored Procedures與Stored Functions的差異http+www^slime/com*tw

• Procedure的Parameter可以定義為IN, OUT, INOUT，而Function的Parameter則必定是IN(系統預設，不能自行指定)。
• Function必定有回傳值，因此必須包含一個RETURNS子句來定義傳回值的資料型態。
• Procedure可以直接產生單一或多個Result Set，但Function不行。

Stored Routins的名稱空間http+www^slime/com*tw

• 使用unqualified reference來呼叫stored procedure與stored function時，預設是對應到default database。
• 當routines執行時會將default database轉移到routines所屬的database，並在執行完畢後切換回原來的default database。因此要執行routines，你有可能需要有存取routines所屬的database的權限。

建立Stored Routineshttp+www^slime/com*tw

CREATE PROCEDURE proc_name ([parameters])
　　[characteristics]
　　routine_body

CREATE FUNCTION func_name ([parameters])
RETURNS data_type
　　[characteristics]
　　routine_body

其中characteristics可使用的參數為：http+www^slime/com*tw

• SQL Security {INVOKER|DEFINER}：指定routine要以建立者或執行者的權限執行。
• DETERMINISTIC or NOT DETERMINISTIC：對於相同的input data若routine每次執行時都能產出相同的結果，則該routine即為DETERMINISTIC。預設值為NOT DETERMINISTIC 。
• LANGUAGE SQL：routine所使用的語言，目前只支援SQL。
• COMMENT 'string'：用來指定routine的註解。

Compound Statementshttp+www^slime/com*tw

BEGIN
　　inner_block: BEGIN
　　　　IF DAYNAME(NOW()) = 'Wednesday' THEN
　　　　　　LEAVE inner_block;
　　　　END IF;
　　　　SELECT 'Today is not Wednesday';
　　END inner_block;
END;

• END後方的label可以省略。
• Block可以nested，也就是BEGIN/END區塊中可以包含其他的BEGIN/END區塊。
• 被outer block所使用的label name，inner block不可使用。

Declaring Parametershttp+www^slime/com*tw

Procedure的Parameter類型：

• IN：只能用來把參數值傳遞到procedure中。
• OUT：只能用來把參數值傳回caller。
• INOUT：可以把參數值傳遞到procedure中，也可以把參數值傳回caller。

注意事項：http+www^slime/com*tw

1. 在procedure的定義中若不指定parameter類型，則預設即為IN。
2. 在function的定義中不可以指定parameter類型，但預設是IN。
3. 在stored routine中，變數的前方不需要加『@』。

The DECLARE Statementhttp+www^slime/com*tw

定義的順序：

1. Variables
2. Conditions
3. Cursors
4. Handlers

注意事項：

1. 在同一個block中，不同的項目可以使用相同的名稱，但相同的項目必須使用不同的名稱。
2. 若inner block與outer block對於某項目皆使用相同的名稱，則在inner block中將無法存取outer block的同名項目。

SELECT......INTO語法本來後面只能接user variables，但在stored routine中也可以用來指定local variables的值。SELECT最多只可以傳回one row否則會產生error；若是傳回zero row則其後所接變數的值保持不變。

Conditions and Handlershttp+www^slime/com*tw

CREATE PROCEDURE add_name(name_param CHAR(20))
BEGIN
　　DECLARE dup_key CONDITION FOR SQLSTATE '02000';
　　DECLARE EXIT HANDLER FOR dup_key
　　BEGIN
　　　　INSERT INTO dup_names (name) VALUES (name_param);
　　　　SELECT 'duplicate key found, inserted into dup_names' AS result;
　　END
　　INSERT INTO unique_name (name) VALUES (name_param);
　　SELECT 'row inserted succesfully into unique_names' AS result;
END

注意事項；

1. 定義store routine時用大寫或小寫都可以。
2. 可以為某個condition宣告一個name，但並非必須。

CONDITION的宣告語法：http+www^slime/com*tw
DECLARE condition_name CONDITION FOR condition_type

HANDLER的宣告語法：http+www^slime/com*tw
DECLARE handler_type HANDLER FOR
condition_type [, condition_type] ...
statement

以下兩種指令的寫法意思是一樣的：http+www^slime/com*tw
　DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET exit_loop = 1;
或
　　DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
　　BEGIN
　　SET exit_loop = 1;
　　END;

要與HANDLER產生連結的CONDITION必須為：

1. SQLSTATE value或MySQL error code
2. 使用DECLARE CONDITION所宣告的condition_name
3. SQLWARNING：代表所有01開頭的SQLSTATE
4. NOT FOUND：代表所有02開頭的SQLSTATE
5. SQLEXCEPTION：所有除了SQLWARNING與NOT FOUND以外的值。

若要忽略某個CONDITION，只要指定給它一個空的CONTINUE HANDLER即可。

Cursorshttp+www^slime/com*tw

特性：

1. Read Only
2. 循序讀取

DECLARE cursor_name CURSOR FOR select_statement
OPEN cursor_name
FETCH cursor_name INTO var_name [, var_name] ...

Flow Controlhttp+www^slime/com*tw

• IF與CASE用來做條件測試。
• LOOP、REPEAT與WHILE用來執行迴圈，其中LOOP是無條件無限循環，而REPEAT與WHILE則包含條件字句來判斷是否繼續執行。

條件測試

IF val IS NULL THEN
　　SELECT 'val is NULL';
ELSE
　　SELECT 'val is not NULL';
END IF;
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

CASE val
　　WHEN 0 THEN SELECT 'val is 0';
　　WHEN 1 THEN SELECT 'val is 1';
　　ELSE SELECT 'val is not 0 or 1';
END CASE;

CASE val
　　WHEN val IS NULL THEN SELECT 'val is NULL'
　　WHEN val < 0 THEN SELECT 'val < 0';
　　WHEN val > 0 THEN SELECT 'val > 0';
　　ELSE SELECT 'val is 0';
END CASE;

CASE條件式是以case_expr = when_expr的形式進行比對，因此若有任一方的值為NULL則比對結果一定不會為true。

迴圈http+www^slime/com*tw

• LOOP指令本身不包含條件子句，因此必須要在迴圈的內容中另行處理執行結束條件。
• REPEAT與WHILE本身即包含條件子句，可判斷何時結束執行。
• Standard SQL有支援FOR迴圈指令，但MySQL目前尚未支援。
• 所有的迴圈區塊都可以被label。
• 可以使用LEAVE指令來跳離迴圈，或是使用RETURN(在Function中才可使用)。
• LEAVE和ITERATE只能夠使用在labeled construct中。

DECLARE i INT DEFAULT 0;
my_loop: LOOP
　　SET i = i + 1;
　　IF i >= 10 THEN
　　　　LEAVE my_loop;
　　END IF;
END LOOP my_loop;

DECLARE i INT DEFAULT 0;
REPEAT
　　SET i = i + 1;
UNTIL i >= 10
END REPEAT;

DECLARE i INT DEFAULT 0;
WHILE i < 10 DO
　　SET i = i + 1;
END WHILE;

修改Stored Routineshttp+www^slime/com*tw

只可以用來修改routine的characteristics，而且只可以用來修改SQL SECURITY與COMMENT。

ALTER PROCEDURE proc_name [characteristics]
ALTER FUNCTION func_name [characteristics]

ALTER FUNCTION f
　　SQL SECURITY INVOKER
　　COMMENT 'this is a comment';

註：characteristics的撰寫順序不重要。

刪除Stored Routinehttp+www^slime/com*tw

DROP PROCEDURE [IF EXISTS] proc_name
DROP FUNCTION [IF EXISTS] func_name

若是沒有加上IF EXISTS，則當要刪除的routine不存在時會產生error；加上IF EXISTS後，當要刪除的routine不存在時只會產生warning。

取得Stored Routine的Metadatahttp+www^slime/com*tw

• SELECT * FROM INFORMATION_SCHEMA.ROUTINES
  WHERE ROUTINE_NAME = 'routine_name'
  AND ROUTINE_SCHEMA='db_name';
• SHOW PROCEDURE STATUS LIKE 'w%';
• SHOW FUNCTION STATUS;
• SHOW CREATE PROCEDURE proc_name;
• SHOW CREATE FUNCTION func_name;

Stored Routine Privileges and Execution Security

1. CREATE ROUTINE
2. EXECUTE
3. ALTER ROUTINE
4. GRANT OPTION

當你建立routine時，MySQL會自動付予你EXECUTE與ALTER ROUTINE權限。

EXECUTE、ALTER ROUTINE與GRANT OPTION權限可以針對個別的routine進行設定，但前提是該routine必須已經存在，例如：
GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE world.world_record_count TO 'slime'@'localhost' WITH GRANT OPTION;





參考資料：www^slime^com^tw
Paul Dubois, Stefan Hinz, Carsten Pedersen(2005), “MySQL 5.0 Certification Study Guide.”