《Mysql應(yīng)用理解MySQL存儲(chǔ)過(guò)程和函數(shù)》要點(diǎn)：
本文介紹了Mysql應(yīng)用理解MySQL存儲(chǔ)過(guò)程和函數(shù)，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。

一、概述? MYSQL數(shù)據(jù)庫(kù)

一提到存儲(chǔ)過(guò)程可能就會(huì)引出另一個(gè)話題就是存儲(chǔ)過(guò)程的優(yōu)缺點(diǎn),這里也不做討論,一般別人問(wèn)我我就這樣回答你覺得它好你就用它.因?yàn)閙ysql中存儲(chǔ)過(guò)程和函數(shù)的語(yǔ)法非常接近所以就放在一起,主要區(qū)別就是函數(shù)必須有返回值(return),并且函數(shù)的參數(shù)只有IN類型而存儲(chǔ)過(guò)程有IN、OUT、INOUT這三種類型.MYSQL數(shù)據(jù)庫(kù)

二、語(yǔ)法? MYSQL數(shù)據(jù)庫(kù)

?創(chuàng)建存儲(chǔ)過(guò)程和函數(shù)語(yǔ)法MYSQL數(shù)據(jù)庫(kù)

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

語(yǔ)法來(lái)自官方自帶的參考手冊(cè),characteristic語(yǔ)法塊是需要注意的地方,先用一個(gè)例子來(lái)介紹.MYSQL數(shù)據(jù)庫(kù)

例子：MYSQL數(shù)據(jù)庫(kù)

#創(chuàng)建數(shù)據(jù)庫(kù)
DROP DATABASE IF EXISTS Dpro;
CREATE DATABASE Dpro
CHARACTER SET utf8
;
USE Dpro;
#創(chuàng)建部門表
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'
);
#插入測(cè)試數(shù)據(jù)
INSERT INTO Employee(id,name,depid) VALUES(1,'陳',100),(2,'王',101),(3,'張',101),(4,'李',102),(5,'郭',103);
#創(chuàng)建存儲(chǔ)過(guò)程
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 ;
#執(zhí)行存儲(chǔ)過(guò)程
CALL Pro_Employee(101,@pcount);
SELECT @pcount;

MYSQL數(shù)據(jù)庫(kù)

語(yǔ)法解釋：MYSQL數(shù)據(jù)庫(kù)

在創(chuàng)建存儲(chǔ)過(guò)程的時(shí)候一般都會(huì)用DELIMITER$$.....END$$ DELIMITER ;放在開頭和結(jié)束,目的就是避免mysql把存儲(chǔ)過(guò)程內(nèi)部的";"解釋成結(jié)束符號(hào),最后通過(guò)“DELIMITER ;”來(lái)告知存儲(chǔ)過(guò)程結(jié)束.MYSQL數(shù)據(jù)庫(kù)

主要解釋characteristic部分：MYSQL數(shù)據(jù)庫(kù)

LANGUAGE SQL：用來(lái)說(shuō)明語(yǔ)句部分是SQL語(yǔ)句,未來(lái)可能會(huì)支持其它類型的語(yǔ)句.MYSQL數(shù)據(jù)庫(kù)

[NOT] DETERMINISTIC：如果程序或線程總是對(duì)同樣的輸入?yún)?shù)產(chǎn)生同樣的結(jié)果,則被認(rèn)為它是“確定的”,否則就是“非確定”的.如果既沒有給定DETERMINISTIC也沒有給定NOT DETERMINISTIC,默認(rèn)的就是NOT DETERMINISTIC(非確定的)CONTAINS SQL：表示子程序不包括讀或?qū)憯?shù)據(jù)的語(yǔ)句.MYSQL數(shù)據(jù)庫(kù)

NO SQL：表示子程序不包括SQL語(yǔ)句.MYSQL數(shù)據(jù)庫(kù)

READS SQL DATA：表示子程序包括讀數(shù)據(jù)的語(yǔ)句,但不包括寫數(shù)據(jù)的語(yǔ)句.MYSQL數(shù)據(jù)庫(kù)

MODIFIES SQL DATA：表示子程序包括寫數(shù)據(jù)的語(yǔ)句.MYSQL數(shù)據(jù)庫(kù)

SQL SECURITY DEFINER：表示執(zhí)行存儲(chǔ)過(guò)程中的程序是由創(chuàng)建該存儲(chǔ)過(guò)程的用戶的權(quán)限來(lái)執(zhí)行.MYSQL數(shù)據(jù)庫(kù)

SQL SECURITY INVOKER：表示執(zhí)行存儲(chǔ)過(guò)程中的程序是由調(diào)用該存儲(chǔ)過(guò)程的用戶的權(quán)限來(lái)執(zhí)行.(例如上面的存儲(chǔ)過(guò)程我寫的是由調(diào)用該存儲(chǔ)過(guò)程的用戶的權(quán)限來(lái)執(zhí)行,當(dāng)前存儲(chǔ)過(guò)程是用來(lái)查詢Employee表,如果我當(dāng)前執(zhí)行存儲(chǔ)過(guò)程的用戶沒有查詢Employee表的權(quán)限那么就會(huì)返回權(quán)限不足的錯(cuò)誤,如果換成DEFINER如果存儲(chǔ)過(guò)程是由ROOT用戶創(chuàng)建那么任何一個(gè)用戶登入調(diào)用存儲(chǔ)過(guò)程都可以執(zhí)行,因?yàn)閳?zhí)行存儲(chǔ)過(guò)程的權(quán)限變成了root)MYSQL數(shù)據(jù)庫(kù)

COMMENT 'string'：備注,和創(chuàng)建表的字段備注一樣.MYSQL數(shù)據(jù)庫(kù)

注意：在編寫存儲(chǔ)過(guò)程和函數(shù)時(shí)建議明確指定上面characteristic部分的狀態(tài),特別是存在復(fù)制的環(huán)境中,如果創(chuàng)建函數(shù)不明確指定這些狀態(tài)會(huì)報(bào)錯(cuò),從一個(gè)非復(fù)制環(huán)境將帶函數(shù)的數(shù)據(jù)庫(kù)遷移到復(fù)制環(huán)境的機(jī)器上如果沒有明確指定DETERMINISTIC, NO SQL, or READS SQL DATA該三個(gè)狀態(tài)也會(huì)報(bào)錯(cuò).MYSQL數(shù)據(jù)庫(kù)

報(bào)錯(cuò)示例MYSQL數(shù)據(jù)庫(kù)

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)
這個(gè)報(bào)錯(cuò)就是上面注意部分說(shuō)的問(wèn)題.原來(lái)是因?yàn)樵谥鲝膹?fù)制的兩臺(tái)MySQL服務(wù)器中開啟了二進(jìn)制日志選項(xiàng)log-bin,slave會(huì)從master復(fù)制數(shù)據(jù),而一些操作,比如function所得的結(jié)果在master和slave上可能不同,所以存在潛在的平安隱患.因此,在默認(rèn)情況下回阻止function的創(chuàng)建.MYSQL數(shù)據(jù)庫(kù)

解決方法有兩種：MYSQL數(shù)據(jù)庫(kù)

1.將log_bin_trust_function_creators參數(shù)設(shè)置為ON,這樣一來(lái)開啟了log-bin的MySQL Server便可以隨意創(chuàng)建function.這里存在潛在的數(shù)據(jù)平安問(wèn)題,除非明確的知道創(chuàng)建的function在master和slave上的行為完全一致.
? 設(shè)置該參數(shù)可以用動(dòng)態(tài)的方式或者指定該參數(shù)來(lái)啟動(dòng)數(shù)據(jù)庫(kù)服務(wù)器或者修改配置文件后重啟服務(wù)器.需注意的是,動(dòng)態(tài)設(shè)置的方式會(huì)在服務(wù)器重啟后失效.
MYSQL數(shù)據(jù)庫(kù)

mysql> show variables like 'log_bin_trust_function_creators';
 mysql> set global log_bin_trust_function_creators=1;

? 另外如果是在master上創(chuàng)建函數(shù),想通過(guò)主從復(fù)制的方式將函數(shù)復(fù)制到slave上則也需在開啟了log-bin的slave中設(shè)置上述變量的值為ON(變量的設(shè)置不會(huì)從master復(fù)制到slave上,這點(diǎn)需要注意),否則主從復(fù)制會(huì)報(bào)錯(cuò).MYSQL數(shù)據(jù)庫(kù)

2.明確指明函數(shù)的類型
? 1 )、DETERMINISTIC 不確定的
? 2 )、NO SQL 沒有SQl語(yǔ)句,當(dāng)然也不會(huì)修改數(shù)據(jù)
? 3 )、READS SQL DATA 只是讀取數(shù)據(jù),當(dāng)然也不會(huì)修改數(shù)據(jù)
比如：CREATE DEFINER=`username`@`%` READS SQL DATA FUNCTION `fn_getitemclock`(i_itemid bigint,i_clock int,i_pos int) RETURNS int(11)...
這樣一來(lái)相當(dāng)于明確的告知MySQL服務(wù)器這個(gè)函數(shù)不會(huì)修改數(shù)據(jù),因此可以在開啟了log-bin的服務(wù)器上平安的創(chuàng)建并被復(fù)制到開啟了log-bin的slave上.MYSQL數(shù)據(jù)庫(kù)

修改存儲(chǔ)過(guò)程函數(shù)語(yǔ)法MYSQL數(shù)據(jù)庫(kù)

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

刪除存儲(chǔ)過(guò)程函數(shù)語(yǔ)法MYSQL數(shù)據(jù)庫(kù)

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

查看存儲(chǔ)過(guò)程和函數(shù)MYSQL數(shù)據(jù)庫(kù)

1.查看存儲(chǔ)過(guò)程狀態(tài)MYSQL數(shù)據(jù)庫(kù)

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
show procedure status like 'Pro_Employee' \G

MYSQL數(shù)據(jù)庫(kù)

2.查看存儲(chǔ)過(guò)程和函數(shù)的創(chuàng)建語(yǔ)法MYSQL數(shù)據(jù)庫(kù)

SHOW CREATE {PROCEDURE | FUNCTION} sp_name
SHOW CREATE PROCEDURE Pro_Employee \G;

MYSQL數(shù)據(jù)庫(kù)

3.查看存儲(chǔ)過(guò)程和函數(shù)詳細(xì)信息MYSQL數(shù)據(jù)庫(kù)

MYSQL數(shù)據(jù)庫(kù)

總結(jié)? MYSQL數(shù)據(jù)庫(kù)

?存儲(chǔ)過(guò)程和函數(shù)語(yǔ)法不難理解,但是往往存儲(chǔ)過(guò)程中不單單只包括這種簡(jiǎn)單的查詢語(yǔ)法,還會(huì)嵌套循環(huán)語(yǔ)句、變量、報(bào)錯(cuò)處理、事務(wù)等,下一篇文章會(huì)單獨(dú)講變量,將變量的知識(shí)加入到存儲(chǔ)過(guò)程,包括變量的聲明和報(bào)錯(cuò)處理,歡迎關(guān)注.MYSQL數(shù)據(jù)庫(kù)

歡迎參與《Mysql應(yīng)用理解MySQL存儲(chǔ)過(guò)程和函數(shù)》討論，分享您的想法，維易PHP學(xué)院為您提供專業(yè)教程。

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.snjht.com/jiaocheng/12306.html