mysql存儲過程與存儲函數實例分析

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

存儲過程

• 簡介

是一組經過 預先編譯 的 SQL 語句的封裝
存儲過程預先存儲在 MySQL 服務器上，需要執行的時候，客戶端只需要向服務器端發出調用存儲過程的命令，服務器端就可以把預先存儲好的這一系列 SQL 語句全部執行

• 分類

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

• 格式

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

IN ：當前參數為輸入參數，也就是表示入參；存儲過程只是讀取這個參數的值。如果沒有定義參數種類， 默認就是 IN ，表示輸入參數
OUT ：當前參數為輸出參數，也就是表示出參；執行完成之后，調用這個存儲過程的客戶端或者應用程序就可以讀取這個參數返回的值了
INOUT ：當前參數既可以為輸入參數，也可以為輸出參數

形參類型可以是 MySQL數據庫中的任意類型

characteristics 表示創建存儲過程時指定的對存儲過程的約束條件

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

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

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

• 代碼案例

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

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

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

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

# 類型3：帶 IN
# 舉例5：創建存儲過程show_someone_salary()，查看“emps”表的某個員工的薪資，并用IN參數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;
# 調用方式1
CALL show_someone_salary('Abel');
# 調用方式2
SET @empname := 'Abel';
CALL show_someone_salary(@empname);

# 類型4：帶 IN 和 OUT
# 舉例6：創建存儲過程show_someone_salary2()，查看“emps”表的某個員工的薪資，并用IN參數empname輸入員工姓名，用OUT參數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;   # 使用參數
END //
DELIMITER;
# 調用
SET @empname = 'Abel';
CALL show_someone_salary2(@empname, @empsalary);
# 查看
SELECT @empname
SELECT @empsalary;

# 類型5：帶 INOUT
# 舉例7：創建存儲過程show_mgr_name()，查詢某個員工領導的姓名，并用INOUT參數“empname”輸入員工姓名，輸出領導的姓名
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 ;
#調用
SET @empname := 'Abel';
CALL show_mgr_name(@empname);
# 查看
SELECT @empname;

存儲函數

• 格式

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

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

• characteristics

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

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

• 代碼案例

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

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

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

• 存儲過程、存儲函數的查看

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

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

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

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';

• 存儲過程、函數的修改

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

• 存儲過程、函數的刪除

DROP FUNCTION IF EXISTS count_by_id;

DROP PROCEDURE IF EXISTS show_min_salary;

關于“mysql存儲過程與存儲函數實例分析”這篇文章的內容就介紹到這里，感謝各位的閱讀！相信大家對“mysql存儲過程與存儲函數實例分析”知識都有一定的了解，大家如果還想學習更多知識，歡迎關注億速云行業資訊頻道。