這篇文章主要介紹了MySQL存儲過程與存儲函數(shù)實例分析的相關(guān)知識，內(nèi)容詳細易懂，操作簡單快捷，具有一定借鑒價值，相信大家閱讀完這篇mysql存儲過程與存儲函數(shù)實例分析文章都會有所收獲，下面我們一起來看看吧。

創(chuàng)新互聯(lián)致力于互聯(lián)網(wǎng)品牌建設(shè)與網(wǎng)絡(luò)營銷，包括成都網(wǎng)站建設(shè)、網(wǎng)站制作、SEO優(yōu)化、網(wǎng)絡(luò)推廣、整站優(yōu)化營銷策劃推廣、電子商務(wù)、移動互聯(lián)網(wǎng)營銷等。創(chuàng)新互聯(lián)為不同類型的客戶提供良好的互聯(lián)網(wǎng)應(yīng)用定制及解決方案，創(chuàng)新互聯(lián)核心團隊十載專注互聯(lián)網(wǎng)開發(fā)，積累了豐富的網(wǎng)站經(jīng)驗，為廣大企業(yè)客戶提供一站式企業(yè)網(wǎng)站建設(shè)服務(wù)，在網(wǎng)站建設(shè)行業(yè)內(nèi)樹立了良好口碑。

存儲過程

• 簡介

是一組經(jīng)過 預(yù)先編譯 的 SQL 語句的封裝
存儲過程預(yù)先存儲在 MySQL 服務(wù)器上，需要執(zhí)行的時候，客戶端只需要向服務(wù)器端發(fā)出調(diào)用存儲過程的命令，服務(wù)器端就可以把預(yù)先存儲好的這一系列 SQL 語句全部執(zhí)行

• 分類

1、沒有參數(shù)（無參數(shù)無返回） 
2、僅僅帶 IN 類型（有參數(shù)無返回） 
3、僅僅帶 OUT 類型（無參數(shù)有返回） 
4、既帶 IN 又帶 OUT（有參數(shù)有返回） 
5、帶 INOUT（有參數(shù)有返回）

• 格式

DELIMITER $
CREATE PROCEDURE 存儲過程名(IN|OUT|INOUT 參數(shù)名 參數(shù)類型,...)
[characteristics ...]
BEGIN
    sql語句1;
    sql語句2;
END $

IN ：當前參數(shù)為輸入?yún)?shù)，也就是表示入?yún)?；存儲過程只是讀取這個參數(shù)的值。如果沒有定義參數(shù)種類， 默認就是 IN ，表示輸入?yún)?shù)
OUT ：當前參數(shù)為輸出參數(shù)，也就是表示出參；執(zhí)行完成之后，調(diào)用這個存儲過程的客戶端或者應(yīng)用程序就可以讀取這個參數(shù)返回的值了
INOUT ：當前參數(shù)既可以為輸入?yún)?shù)，也可以為輸出參數(shù)

形參類型可以是 MySQL數(shù)據(jù)庫中的任意類型

characteristics 表示創(chuàng)建存儲過程時指定的對存儲過程的約束條件

1. BEGIN…END：BEGIN…END 中間包含了多個語句，每個語句都以（;）號為結(jié)束符
2. DECLARE：DECLARE 用來聲明變量，使用的位置在于 BEGIN…END 語句中間，而且需要在其他語句使用之前進行變量的聲明
3. SET：賦值語句，用于對變量進行賦值
4. SELECT… INTO：把從數(shù)據(jù)表中查詢的結(jié)果存放到變量中，也就是為變量賦值

存儲過程體中可以有多條 SQL 語句，如果僅僅一條SQL 語句，則可以省略 BEGIN 和 END

DELIMITER 新的結(jié)束標記
DELIMITER //”語句的作用是將MySQL的結(jié)束符設(shè)置為//，并以“END //”結(jié)束存儲過程。存儲過程定義完畢之后再使用“DELIMITER ;”恢復默認結(jié)束符
當使用DELIMITER命令時，應(yīng)該避免使用反斜杠（‘\’）字符，因為反斜線是MySQL的轉(zhuǎn)義字符

• 代碼案例

# 類型1：無參數(shù)無返回值
# 舉例1：創(chuàng)建存儲過程select_all_data()，查看 employees 表的所有數(shù)據(jù)
DELIMITER $     # 開始
CREATE PROCEDURE select_all_data()
BEGIN
  SELECT * FROM employees;
END $
DELIMITER;    # 結(jié)束
# 存儲過程的調(diào)用
CALL select_all_data();

# 舉例2：創(chuàng)建存儲過程avg_employee_salary()，返回所有員工的平均工資
DELIMITER //
CREATE PROCEDURE avg_employee_salary()
BEGIN 
  SELECT AVG(salary) FROM employees;
END //
DELIMITER ;
# 調(diào)用
CALL avg_employee_salary();

# 舉例3：創(chuàng)建存儲過程show_max_salary()，用來查看“emps”表的最高薪資值
DELIMITER //
CREATE PROCEDURE show_max_salary()
BEGIN
  SELECT MAX(salary)
  FROM employees;
END //
DELIMITER ;
# 調(diào)用
CALL show_max_salary();

# 類型2：帶 OUT
# 舉例4：創(chuàng)建存儲過程show_min_salary()，查看“emps”表的最低薪資值。并將最低薪資通過OUT參數(shù)“ms”輸出
DELIMITER //
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)   # 輸出ms，類型是DOUBLE
BEGIN
  SELECT MIN(salary) INTO ms    # 將min賦值給ms
  FROM employees;
END //
DELIMITER 
# 調(diào)用
CALL show_min_salary(@ms);
# 查看變量值
SELECT @ms;

# 類型3：帶 IN
# 舉例5：創(chuàng)建存儲過程show_someone_salary()，查看“emps”表的某個員工的薪資，并用IN參數(shù)empname輸入員工姓名
DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))   # 傳入empname，類型為VARCHAR
BEGIN
  SELECT salary FROM employees
  WHERE last_name = empname;   # 使用empname
END //
DELIMITER;
# 調(diào)用方式1
CALL show_someone_salary('Abel');
# 調(diào)用方式2
SET @empname := 'Abel';
CALL show_someone_salary(@empname);

# 類型4：帶 IN 和 OUT
# 舉例6：創(chuàng)建存儲過程show_someone_salary2()，查看“emps”表的某個員工的薪資，并用IN參數(shù)empname輸入員工姓名，用OUT參數(shù)empsalary輸出員工薪資
DELIMITER //
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20), OUT empsalary DECIMAL(10,2))
BEGIN
  SELECT salary INTO empsalary   # 賦值
  FROM employees
  WHERE last_name = empname;   # 使用參數(shù)
END //
DELIMITER;
# 調(diào)用
SET @empname = 'Abel';
CALL show_someone_salary2(@empname, @empsalary);
# 查看
SELECT @empname
SELECT @empsalary;

# 類型5：帶 INOUT
# 舉例7：創(chuàng)建存儲過程show_mgr_name()，查詢某個員工領(lǐng)導的姓名，并用INOUT參數(shù)“empname”輸入員工姓名，輸出領(lǐng)導的姓名
DELIMITER $
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
BEGIN
  SELECT last_name INTO empname
  FROM employees
  WHERE employee_id = (
        SELECT manager_id
        FROM employees
        WHERE last_name = empname
        );
END $
DELIMITER ;
#調(diào)用
SET @empname := 'Abel';
CALL show_mgr_name(@empname);
# 查看
SELECT @empname;

存儲函數(shù)

• 格式

CREATE FUNCTION 函數(shù)名(參數(shù)名 參數(shù)類型,...)
RETURNS 返回值類型
[characteristics ...]
BEGIN
函數(shù)體 #函數(shù)體中肯定有 RETURN 語句
END

1、RETURNS type 語句表示函數(shù)返回數(shù)據(jù)的類型
2、RETURNS子句只能對FUNCTION做指定，對函數(shù)而言這是 強制 的。它用來指定函數(shù)的返回類型，而且函數(shù)體必須包含一個 RETURN value 語句
3、characteristic 創(chuàng)建函數(shù)時指定的對函數(shù)的約束。取值與創(chuàng)建存儲過程時相同
4、函數(shù)體也可以用BEGIN…END來表示SQL代碼的開始和結(jié)束。如果函數(shù)體只有一條語句，也可以省略BEGIN…END
5、調(diào)用存儲函數(shù)
      SELECT 函數(shù)名(實參列表)

• characteristics

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

1、LANGUAGE SQL ：說明存儲過程執(zhí)行體是由SQL語句組成的，當前系統(tǒng)支持的語言為SQL
2、[NOT] DETERMINISTIC ：指明存儲過程執(zhí)行的結(jié)果是否確定。DETERMINISTIC表示結(jié)果是確定的。每次執(zhí)行存儲過程時，相同的輸入會得到相同的輸出。NOT DETERMINISTIC表示結(jié)果是不確定
的，相同的輸入可能得到不同的輸出。如果沒有指定任意一個值，默認為NOT DETERMINISTIC
3、{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使用SQL語句的限制
      CONTAINS SQL表示當前存儲過程的子程序包含SQL語句，但是并不包含讀寫數(shù)據(jù)的SQL語句
      NO SQL表示當前存儲過程的子程序中不包含任何SQL語句
      READS SQL DATA表示當前存儲過程的子程序中包含讀數(shù)據(jù)的SQL語句
      MODIFIES SQL DATA表示當前存儲過程的子程序中包含寫數(shù)據(jù)的SQL語句
      默認情況下，系統(tǒng)會指定為CONTAINS SQL
4、SQL SECURITY { DEFINER | INVOKER } ：執(zhí)行當前存儲過程的權(quán)限，即指明哪些用戶能夠執(zhí)行當前存儲過程
      DEFINER 表示只有當前存儲過程的創(chuàng)建者或者定義者才能執(zhí)行當前存儲過程；
      INVOKER 表示擁有當前存儲過程的訪問權(quán)限的用戶能夠執(zhí)行當前存儲過程。
      如果沒有設(shè)置相關(guān)的值，則MySQL默認指定值為DEFINER
5、COMMENT 'string' ：注釋信息，可以用來描述存儲過程

• 代碼案例

# 舉例1：創(chuàng)建存儲函數(shù)，名稱為email_by_name()，參數(shù)定義為空，該函數(shù)查詢Abel的email，并返回，數(shù)據(jù)類型為字符串型
DELIMITER //    # 開始
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)   # 返回值
  DETERMINISTIC   # 表示結(jié)果確定
  CONTAINS SQL    # 表示包含sql語句
  READS SQL DATA    # 表示包含讀數(shù)據(jù)的sql
BEGIN
  RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //
DELIMITER;    # 結(jié)束
# 調(diào)用
SELECT email_by_name();

# 舉例2：創(chuàng)建存儲函數(shù)，名稱為email_by_id()，參數(shù)傳入emp_id，該函數(shù)查詢emp_id的email，并返回，數(shù)據(jù)類型為字符串型
# 創(chuàng)建函數(shù)前執(zhí)行此語句，保證函數(shù)的創(chuàng)建會成功；則不需要寫characteristics
SET GLOBAL log_bin_trust_function_creators = 1;
# 聲明函數(shù)
DELIMITER //    # 開始
CREATE FUNCTION email_by_id(emp_id INT)  # 傳入?yún)?shù)
RETURNS VARCHAR(25)   # 返回值
BEGIN
  RETURN (SELECT email FROM employees WHERE employee_id = emp_id);  # 使用emp_id
END //
DELIMITER;
# 調(diào)用
SELECT email_by_id(101);
# 調(diào)用方式2
SET @emp_id := 102;
SELECT email_by_id(@emp_id);

# 舉例3：創(chuàng)建存儲函數(shù)count_by_id()，參數(shù)傳入dept_id，該函數(shù)查詢dept_id部門的員工人數(shù)，并返回，數(shù)據(jù)類型為整型。
DELIMITER //
CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT
BEGIN
  RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
END //
DELIMITER ;
# 調(diào)用
SET @dept_id := 50;
SELECT count_by_id(@dept_id);

• 存儲過程、存儲函數(shù)的查看

# 方式1：使用SHOW CREATE語句查看存儲過程和函數(shù)的創(chuàng)建信息
# 查看存儲過程
SHOW CREATE PROCEDURE show_mgr_name;
# 查看存儲函數(shù)
SHOW CREATE FUNCTION count_by_id;

# 方式2：使用SHOW STATUS語句查看存儲過程和函數(shù)的狀態(tài)信息
# 查看存儲過程
SHOW PROCEDURE STATUS;
# 查看指定存儲過程
SHOW PROCEDURE STATUS LIKE 'show_max_salary';
# 查看指定存儲函數(shù)
SHOW FUNCTION STATUS LIKE 'email_by_id';

# 方式3：從information_schema.Routines表中查看存儲過程和函數(shù)的信息

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='email_by_id' AND ROUTINE_TYPE = 'FUNCTION';

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='show_min_salary' AND ROUTINE_TYPE = 'PROCEDURE';

• 存儲過程、函數(shù)的修改

ALTER PROCEDURE show_max_salary
SQL SECURITY INVOKER
COMMENT '查詢最高工資';

• 存儲過程、函數(shù)的刪除

DROP FUNCTION IF EXISTS count_by_id;

DROP PROCEDURE IF EXISTS show_min_salary;

關(guān)于“mysql存儲過程與存儲函數(shù)實例分析”這篇文章的內(nèi)容就介紹到這里，感謝各位的閱讀！相信大家對“mysql存儲過程與存儲函數(shù)實例分析”知識都有一定的了解，大家如果還想學習更多知識，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。