MySQL 儲存過程和函式

低吟不作語發表於2022-04-23
MySql儲存過程函式

概述  

一提到儲存過程可能就會引出另一個話題就是儲存過程的優缺點，這裡也不做討論。因為mysql中儲存過程和函式的語法非常接近所以就放在一起，主要區別就是函式必須有返回值（return），並且函式的引數只有IN型別而儲存過程有IN、OUT、INOUT這三種型別。

 

 

 1.建立儲存過程和函式語法

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
 
CREATE FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body
    
    proc_parameter:
    [ IN | OUT | INOUT ] param_name type
    
    func_parameter:
    param_name type
 
type:
    Any valid MySQL data type
 
characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'
 
routine_body:
    Valid SQL procedure statement or statements

語法來自官方自帶的參考手冊，characteristic語法塊是需要注意的地方，先用一個例子來介紹。

2.示例

#建立資料庫
DROP DATABASE IF EXISTS Dpro;
CREATE  DATABASE Dpro
CHARACTER SET utf8
;

USE Dpro;

#建立部門表
DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee
(id INT NOT NULL PRIMARY KEY COMMENT '主鍵',
 name VARCHAR(20) NOT NULL COMMENT '人名',
 depid INT NOT NULL COMMENT '部門id'
);

#插入測試資料
INSERT INTO Employee(id,name,depid) VALUES(1,'陳',100),(2,'王',101),(3,'張',101),(4,'李',102),(5,'郭',103);

#建立儲存過程
DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid;

END$$
DELIMITER ;

#執行儲存過程
CALL Pro_Employee(101,@pcount);

SELECT @pcount;

3. 語法解釋

在建立儲存過程的時候一般都會用DELIMITER$$.....END$$ DELIMITER ;放在開頭和結束，目的就是避免mysql把儲存過程內部的";"解釋成結束符號，最後通過“DELIMITER ;”來告知儲存過程結束。

主要解釋characteristic部分：

LANGUAGE SQL：用來說明語句部分是SQL語句，未來可能會支援其它型別的語句。

[NOT] DETERMINISTIC：如果程式或執行緒總是對同樣的輸入引數產生同樣的結果，則被認為它是“確定的”，否則就是“非確定”的。如果既沒有給定DETERMINISTIC也沒有給定NOT DETERMINISTIC，預設的就是NOT DETERMINISTIC（非確定的）CONTAINS SQL：表示子程式不包含讀或寫資料的語句。

NO SQL：表示子程式不包含SQL語句。

READS SQL DATA：表示子程式包含讀資料的語句，但不包含寫資料的語句。

MODIFIES SQL DATA：表示子程式包含寫資料的語句。

SQL SECURITY DEFINER：表示執行儲存過程中的程式是由建立該儲存過程的使用者的許可權來執行。

SQL SECURITY INVOKER：表示執行儲存過程中的程式是由呼叫該儲存過程的使用者的許可權來執行。（例如上面的儲存過程我寫的是由呼叫該儲存過程的使用者的許可權來執行，當前儲存過程是用來查詢Employee表，如果我當前執行儲存過程的使用者沒有查詢Employee表的許可權那麼就會返回許可權不足的錯誤，如果換成DEFINER如果儲存過程是由ROOT使用者建立那麼任何一個使用者登入呼叫儲存過程都可以執行，因為執行儲存過程的許可權變成了root）

COMMENT 'string':備註，和建立表的欄位備註一樣。

注意：在編寫儲存過程和函式時建議明確指定上面characteristic部分的狀態，特別是存在複製的環境中，如果建立函式不明確指定這些狀態會報錯，從一個非複製環境將帶函式的資料庫遷移到複製環境的機器上如果沒有明確指定DETERMINISTIC, NO SQL, or READS SQL DATA該三個狀態也會報錯。

4.報錯示例

Error Code: 1418. This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

這個報錯就是上面注意部分說的問題。原來是因為在主從複製的兩臺MySQL伺服器中開啟了二進位制日誌選項log-bin,slave會從master複製資料，而一些操作，比如function所得的結果在master和slave上可能不同，所以存在潛在的安全隱患。因此，在預設情況下回阻止function的建立。

解決辦法有兩種：

1.將log_bin_trust_function_creators引數設定為ON，這樣一來開啟了log-bin的MySQL Server便可以隨意建立function。這裡存在潛在的資料安全問題，除非明確的知道建立的function在master和slave上的行為完全一致。
  設定該引數可以用動態的方式或者指定該引數來啟動資料庫伺服器或者修改配置檔案後重啟伺服器。需注意的是，動態設定的方式會在伺服器重啟後失效。
  mysql> show variables like 'log_bin_trust_function_creators';
  mysql> set global log_bin_trust_function_creators=1;
  另外如果是在master上建立函式，想通過主從複製的方式將函式複製到slave上則也需在開啟了log-bin的slave中設定上述變數的值為ON（變數的設定不會從master複製到slave上，這點需要注意），否則主從複製會報錯。

2.明確指明函式的型別
  1 DETERMINISTIC 不確定的
  2 NO SQL 沒有SQl語句，當然也不會修改資料
  3 READS SQL DATA 只是讀取資料，當然也不會修改資料
比如：CREATE DEFINER=`username`@`%` READS SQL DATA FUNCTION `fn_getitemclock`(i_itemid bigint,i_clock int,i_pos int) RETURNS int(11)...
這樣一來相當於明確的告知MySQL伺服器這個函式不會修改資料，因此可以在開啟了log-bin的伺服器上安全的建立並被複制到開啟了log-bin的slave上。

 5.修改儲存過程函式語法

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
 
characteristic:
    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

6.刪除儲存過程函式語法

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

7.檢視儲存過程和函式

1.檢視儲存過程狀態



SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']


show procedure status like 'Pro_Employee' \G






2.檢視儲存過程和函式的建立語法




SHOW CREATE {PROCEDURE | FUNCTION} sp_name

SHOW CREATE PROCEDURE Pro_Employee \G;






3.檢視儲存過程和函式詳細資訊




SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME='Pro_Employee' \G;






總結  


 儲存過程和函式語法不難理解，但是往往儲存過程中不單單隻包含這種簡單的查詢語法，還會巢狀迴圈語句、變數、報錯處理、事務等，下一篇文章會單獨講變數，將變數的知識加入到儲存過程，包括變數的宣告和報錯處理，歡迎關注。


 


 


 


 








備註：


    作者：pursuer.chen


    部落格：http://www.cnblogs.com/chenmh


本站點所有隨筆都是原創，歡迎大家轉載；但轉載時必須註明文章來源，且在文章開頭明顯處給明連結。


《歡迎交流討論》










相關文章