本文主要給大家介紹MySQL 存儲過程的三種類型和函數(shù)講義，希望可以給大家補充和更新些知識，如有其它問題需要了解的可以持續(xù)在創(chuàng)新互聯(lián)行業(yè)資訊里面關注我的更新文章的。

成都創(chuàng)新互聯(lián)主打移動網(wǎng)站、成都網(wǎng)站設計、做網(wǎng)站、網(wǎng)站改版、網(wǎng)絡推廣、網(wǎng)站維護、域名注冊、等互聯(lián)網(wǎng)信息服務，為各行業(yè)提供服務。在技術實力的保障下，我們?yōu)榭蛻舫兄Z穩(wěn)定，放心的服務，根據(jù)網(wǎng)站的內(nèi)容與功能再決定采用什么樣的設計。最后，要實現(xiàn)符合網(wǎng)站需求的內(nèi)容、功能與設計，我們還會規(guī)劃穩(wěn)定安全的技術方案做保障。

一提到存儲過程可能就會引出另一個話題就是存儲過程的優(yōu)缺點，這里也不做討論，一般別人問我我就這樣回答你覺得它好你就用它。因為mysql中存儲過程和函數(shù)的語法非常接近所以就放在一起，主要區(qū)別就是函數(shù)必須有返回值（return），并且函數(shù)的參數(shù)只有IN類型而存儲過程有IN、OUT、INOUT這三種類型。

語法

 創(chuàng)建存儲過程和函數(shù)語法

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語法塊是需要注意的地方，先用一個例子來介紹。

例子：

#創(chuàng)建數(shù)據(jù)庫DROP DATABASE IF EXISTS Dpro;CREATE  DATABASE DproCHARACTER 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');
#插入測試數(shù)據(jù)INSERT INTO Employee(id,name,depid) VALUES(1,'陳',100),(2,'王',101),(3,'張',101),(4,'李',102),(5,'郭',103);
#創(chuàng)建存儲過程DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKERBEGINSELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid;END$$
DELIMITER ;
#執(zhí)行存儲過程
CALL Pro_Employee(101,@pcount);SELECT @pcount;

 語法解釋：

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

主要解釋characteristic部分：

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

[NOT] DETERMINISTIC：如果程序或線程總是對同樣的輸入?yún)?shù)產(chǎn)生同樣的結果，則被認為它是“確定的”，否則就是“非確定”的。如果既沒有給定DETERMINISTIC也沒有給定NOT DETERMINISTIC，默認的就是NOT DETERMINISTIC（非確定的）CONTAINS SQL：表示子程序不包含讀或?qū)憯?shù)據(jù)的語句。

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

READS SQL DATA：表示子程序包含讀數(shù)據(jù)的語句，但不包含寫數(shù)據(jù)的語句。

MODIFIES SQL DATA：表示子程序包含寫數(shù)據(jù)的語句。

SQL SECURITY DEFINER：表示執(zhí)行存儲過程中的程序是由創(chuàng)建該存儲過程的用戶的權限來執(zhí)行。

SQL SECURITY INVOKER：表示執(zhí)行存儲過程中的程序是由調(diào)用該存儲過程的用戶的權限來執(zhí)行。（例如上面的存儲過程我寫的是由調(diào)用該存儲過程的用戶的權限來執(zhí)行，當前存儲過程是用來查詢Employee表，如果我當前執(zhí)行存儲過程的用戶沒有查詢Employee表的權限那么就會返回權限不足的錯誤，如果換成DEFINER如果存儲過程是由ROOT用戶創(chuàng)建那么任何一個用戶登入調(diào)用存儲過程都可以執(zhí)行，因為執(zhí)行存儲過程的權限變成了root）

COMMENT 'string':備注，和創(chuàng)建表的字段備注一樣。

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

報錯示例

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復制數(shù)據(jù)，而一些操作，比如function所得的結果在master和slave上可能不同，所以存在潛在的安全隱患。因此，在默認情況下回阻止function的創(chuàng)建。

解決辦法有兩種：

1.將log_bin_trust_function_creators參數(shù)設置為ON，這樣一來開啟了log-bin的MySQL Server便可以隨意創(chuàng)建function。這里存在潛在的數(shù)據(jù)安全問題，除非明確的知道創(chuàng)建的function在master和slave上的行為完全一致。
  設置該參數(shù)可以用動態(tài)的方式或者指定該參數(shù)來啟動數(shù)據(jù)庫云服務器或者修改配置文件后重啟云服務器。需注意的是，動態(tài)設置的方式會在服務器重啟后失效。
  mysql> show variables like 'log_bin_trust_function_creators';
  mysql> set global log_bin_trust_function_creators=1;
  另外如果是在master上創(chuàng)建函數(shù)，想通過主從復制的方式將函數(shù)復制到slave上則也需在開啟了log-bin的slave中設置上述變量的值為ON（變量的設置不會從master復制到slave上，這點需要注意），否則主從復制會報錯。2.明確指明函數(shù)的類型  1 DETERMINISTIC 不確定的  2 NO SQL 沒有SQl語句，當然也不會修改數(shù)據(jù)  3 READS SQL DATA 只是讀取數(shù)據(jù)，當然也不會修改數(shù)據(jù)
比如：CREATE DEFINER=`username`@`%` READS SQL DATA FUNCTION `fn_getitemclock`(i_itemid bigint,i_clock int,i_pos int) RETURNS int(11)...
這樣一來相當于明確的告知MySQL服務器這個函數(shù)不會修改數(shù)據(jù)，因此可以在開啟了log-bin的服務器上安全的創(chuàng)建并被復制到開啟了log-bin的slave上。

 修改存儲過程函數(shù)語法

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

刪除存儲過程函數(shù)語法

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

查看存儲過程和函數(shù)

1.查看存儲過程狀態(tài)

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

show procedure status like 'Pro_Employee' \G

2.查看存儲過程和函數(shù)的創(chuàng)建語法

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

SHOW CREATE PROCEDURE Pro_Employee \G;

3.查看存儲過程和函數(shù)詳細信息

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

看了以上MySQL 存儲過程的三種類型和函數(shù)講義關于，希望能給大家在實際運用中帶來一定的幫助。本文由于篇幅有限，難免會有不足和需要補充的地方，如有需要更加專業(yè)的解答，可在官網(wǎng)聯(lián)系我們的24小時售前售后，隨時幫您解答問題的。

另外有需要云服務器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務器、裸金屬服務器、高防服務器、香港服務器、美國服務器、虛擬主機、免備案服務器”等云主機租用服務以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務可用性高、性價比高”等特點與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應用場景需求。

當前名稱：MySQL存儲過程的三種類型和函數(shù)講義-創(chuàng)新互聯(lián)
文章鏈接：http://m.rwnh.cn/article46/ieheg.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供全網(wǎng)營銷推廣、面包屑導航、網(wǎng)站改版、品牌網(wǎng)站建設、網(wǎng)站營銷、網(wǎng)站維護

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)