MySQL 預存程序

標籤：style   blog   io   color   ar   os   使用   for   sp   

一、引言

預存程序（Stored Procedure）是一組為了完成特定功能的SQL語句集，經編譯後儲存在資料庫中，使用者通過指定預存程序的名字並給定參數（如果該預存程序帶有參數）來調用執行它。

1. 預存程序增強了SQL語言的功能和靈活性。預存程序可以用流量控制語句編寫，有很強的靈活性，可以完成複雜的判斷和較複雜的運算。
2. 預存程序允許標準組件是編程。預存程序被建立後，可以在程式中被多次調用，而不必重新編寫該預存程序的SQL語句。而且資料庫專業人員可以隨時對預存程序進行修改，對應用程式原始碼毫無影響。
3. 預存程序能實現較快的執行速度。如果某一操作包含大量的Transaction-SQL代碼或分別被多次執行，那麼預存程序要比批處理的執行速度快很多。因為預存程序是先行編譯的。在首次運行一個預存程序時查詢，最佳化器對其進行分析最佳化，並且給出最終被儲存在系統資料表中的執行計畫。而批處理的Transaction-SQL語句在每次運行時都要進行編譯和最佳化，速度相對要慢一些。
4. 預存程序能過減少網路流量。針對同一個資料庫物件的操作（如查詢、修改），如果這一操作所涉及的Transaction-SQL語句被組織程預存程序，那麼當在客戶電腦上調用該預存程序時，網路中傳送的只是該調用語句，從而大大增加了網路流量並降低了網路負載。
5. 預存程序可被作為一種安全機制來充分利用。系統管理員通過執行某一預存程序的許可權進行限制，能夠實現對相應的資料的存取權限的限制，避免了非授權使用者對資料的訪問，保證了資料的安全。

二、關於 MySQL 的預存程序

MySQL在5.0以前並不支援預存程序，這使得MySQL在應用上大打折扣。好在MySQL 5.0終於開始已經支援預存程序，這樣即可以大大提高資料庫的處理速度，同時也可以提高資料庫編程的靈活性。

三、建立 MySQL 預存程序

CREATE    [DEFINER = { user | CURRENT_USER }]    PROCEDURE sp_name ([proc_parameter[,...]])    [characteristic ...] routine_body

• DEFINER: 預設為目前使用者，如果指定 user 格式為 ‘user_name‘@‘host_name‘，MySQL 5.0.3後在 SQL SECURITY INVOKER 模式（預設為 DEFINER ）下運行該預存程序需要有相應EXECUTE許可權（預存程序有關的許可權有三種：ALTER ROUTINE 編輯或刪除預存程序；CREATE ROUTINE 建立預存程序；EXECUTE 運行預存程序 ）
• sp_name：預存程序的名稱，可以使用修改
• proc_parameter：參數列表，格式為 ( [ IN | OUT | INOUT ] param type) ，例如（IN param1 int）
• characteristic：儲存特性，包含有：
  

  characteristic:    COMMENT ‘string‘  | LANGUAGE SQL  | [NOT] DETERMINISTIC  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }  | SQL SECURITY { DEFINER | INVOKER }

• routine_body：SQL代碼的內容，可以用BEGIN…END來標誌SQL代碼的開始和結束。

建立預存程序執行個體：

mysql> delimiter //mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)    -> BEGIN    ->   SELECT COUNT(*) INTO param1 FROM t;    -> END//Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;

 此預存程序名稱為 simpleproc，有一個輸出參數 param1 類型為int。Select 語句查詢表 into 的記錄並存入變數 param1 並輸入。

• DELIMITER：需要注意的是 delimiter // 和 delimiter ;。 DELIMITER 聲明分隔字元的意思，MySQL 預設的分隔字元是; ，上例如果沒有聲明分隔字元為 / /編譯器就會將預存程序的一部分當作 SQL 陳述式執行，導致錯誤。在預存程序定義前，將 MySQL 的分隔字元申明為// (或其他字元，如 && )，在建立完成後，重新恢複 MySQL 的預設分隔符號。
• IN, OUT, INOUT：IN 表示該參數的值必須在調用預存程序時指定，在預存程序中修改該參數的值不能被返回；OUT：該值可在預存程序內部被改變，並可返回；INOUT：調用時指定，並且可被改變和返回
  
  • IN 參數執行個體
    

    mysql > DELIMITER //mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int)       -> BEGIN        -> SELECT p_in;        -> SET p_in=2;        -> SELECT p_in;        -> END;        -> //Query OK, 0 rows affected (0.00 sec)  mysql > DELIMITER ;

    mysql > SET @p_in=1;  mysql > CALL demo_in_parameter(@p_in);  +------+  | p_in |  +------+  |   1  |   +------+   +------+  | p_in |  +------+  |   2  |   +------+   mysql> SELECT @p_in;  +-------+  | @p_in |  +-------+  |  1    |  +-------+  

    以上可以看出，p_in 雖然在預存程序中被修改，但並不影響 @p_id 的值

  • OUT 參數執行個體
    

    mysql > DELIMITER //  mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int)       -> BEGIN      -> SELECT p_out;       -> SET p_out=2;       -> SELECT p_out;       -> END;       -> //  Query OK, 0 rows affected (0.00 sec)mysql > DELIMITER ;

    mysql > SET @p_out=1;  mysql > CALL sp_demo_out_parameter(@p_out);  +-------+  | p_out |   +-------+  | NULL  |   +-------+   +-------+  | p_out |  +-------+  |   2   |   +-------+   mysql> SELECT @p_out;  +-------+  | p_out |  +-------+  |   2   |  +-------+  

    以上可以看出，p_out 無法傳入預存程序內部

  • INOUT 執行個體
    

    mysql > DELIMITER //mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)        -> BEGIN      -> SELECT p_inout;       -> SET p_inout=2;       -> SELECT p_inout;        -> END;       -> //  Query OK, 0 rows affected (0.00 sec)mysql > DELIMITER ;

    mysql > SET @p_inout=1;  mysql > CALL demo_inout_parameter(@p_inout) ;  +---------+  | p_inout |  +---------+  |    1    |  +---------+   +---------+  | p_inout |   +---------+  |    2    |  +---------+   mysql > SELECT @p_inout;  +----------+  | @p_inout |   +----------+  |    2     |  +----------+ 

    以上可以看出，p_inout 能被出入預存程序，且 p_inout 值的改變也影響 @p_inout 的值。

• DECLARE：變數定義，格式為 variable_name [,variable_name...] datatype [DEFAULT value];。例如：
  
  • DECLARE l_int int unsigned default 4000000;
  • DECLARE l_numeric number(8,2) DEFAULT 9.95;  
  • DECLARE l_date date DEFAULT ‘1999-12-31‘;  
  • DECLARE l_datetime datetime DEFAULT ‘1999-12-31 23:59:59‘;  
• 變數賦值：
  
  • SET variable_name = expression ...。例如 SET p_inout=2;  
  • 使用 SELECT…INTO 語句，SELECT 查詢列或複合函數 INTO 變數名 FROM TABLE;。例如 SELECT COUNT(*) INTO param1 FROM t;

四、修改 MySQL 預存程序

ALTER PROCEDURE proc_name [characteristic ...]characteristic:    COMMENT ‘string‘  | LANGUAGE SQL  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }  | SQL SECURITY { DEFINER | INVOKER }

MySQL 預存程序的只允許修改 名稱（proc_name）和特性（characteristic），如果需要修改其他的內容必須刪除重建。
修改預存程序執行個體：（預存程序 simpleproc 已在上文中建立）

mysql> ALTER PROCEDURE simpleproc     -> MODIFIES SQL DATA     -> SQL SECURITY INVOKER ; Query OK, 0 rows affected (0.00 sec) 

三、調用 MySQL 預存程序

call sp_name();  

• sp_name：預存程序的名稱，括弧內包含預存程序的參數

四、查看 MySQL 預存程序

1. 查看預存程序
   

   select name from mysql.proc where db=’db_name’;

   or

   select routine_name from information_schema.routines where routine_schema=‘db_name‘;

   or

   show procedure status where db=‘db_name‘;

2. 查看預存程序詳細
   

   SHOW CREATE PROCEDURE

五、刪除 MySQL預存程序

DROP PROCEDURE proc_name

刪除預存程序一般使用 drop procedure，也可以類似上文中查看，從表中刪除預存程序。

 

MySQL 預存程序