懂得MySQL存儲進程和函數

懂得MySQL存儲進程和函數。本站提示廣大學習愛好者：（懂得MySQL存儲進程和函數）文章只能為提供參考，不一定能成為您想要的結果。以下是懂得MySQL存儲進程和函數正文

1、概述 

一提到存儲進程能夠就會引出另外一個話題就是存儲進程的優缺陷，這裡也不做評論辯論，普通他人問我我就如許答復你認為它好你就用它。由於mysql中存儲進程和函數的語法異常接近所以就放在一路，重要差別就是函數必需有前往值（return），而且函數的參數只要IN類型而存儲進程有IN、OUT、INOUT這三品種型。

2、語法 

 創立存儲進程和函數語法

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語法塊是須要留意的處所，先用一個例子來引見。

例子：

#創立數據庫
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;

語法說明：

在創立存儲進程的時刻普通都邑用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該三個狀況也會報錯。

報錯示例

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上。

修正存儲進程函數語法

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

刪除存儲進程函數語法

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

檢查存儲進程和函數

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;

總結 

 存儲進程和函數語法不難懂得，然則常常存儲進程中不單單只包括這類簡略的查詢語法，還會嵌套輪回語句、變量、報錯處置、事務等，下一篇文章會零丁講變量，將變量的常識參加到存儲進程，包含變量的聲明和報錯處置，迎接存眷。