MySQL的存儲函數與存儲過程實例代碼分析

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

MySQL存儲過程與存儲函數的相關概念

存儲函數和存儲過程的主要區別：

• 存儲函數一定會有返回值的

• 存儲過程不一定有返回值

存儲過程和函數能后將復雜的SQL邏輯封裝在一起，應用程序無需關注存儲過程和函數內部復雜的SQL邏輯，而只需要簡單地調用存儲過程和函數即可

存儲過程

一組預先編譯的SQL語句的封裝

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

• 簡化操作，提高了SQL語句的重用性，減少了開發程序員的壓力

• 減少操作過程中的失誤，提高效率

• 減少網路傳輸量，客戶端不需要將所有的SQL語句通過網絡發給服務器

• 減少SQL語句暴露在網上的風險，提高數據查詢的安全性

與視圖，函數的對比：

• 視圖：是虛擬表，通常不對底層數據表直接操作

• 存儲過程：程序化的SQL，可以直接操作底層數據表，相比于面向集合的操作方式，能夠實現一些更復雜的數據處理

• 相較于函數，存儲過程沒有返回值

分類

• 沒有參數（無參數無返回）

• 僅僅帶有IN 類型 （有參數無返回）

• 僅僅帶OUT類型（無參數有返回）

• 即帶IN又帶OUT(有參數有返回)

• 帶INOUT(有參數有返回)

創建存儲過程

DELIMITER $

CREATE PROCEDURE 存儲過程名 (IN|OUT|INOUT 參數名 參數類型,...)
[characteristics]
BEGIN
存儲過程體
END $

DELIMITER ;

DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
	SELECT *
	FROM employees;
END $
DELIMITER ;

調用存儲過程

CALL select_all_data();

無參數無返回值

DELIMITER //
CREATE PROCEDURE avg_employee_salary()
BEGIN 
	SELECT AVG(salary) FROM emp;
END //
DELIMITER ;
CALL avg_employee_salary();

無參數有返回值

DELIMITER //
CREATE PROCEDURE show_min_salart(OUT ms DOUBLE)
BEGIN 
	SELECT MIN(salary) INTO ms
	FROM emp;
END //
DELIMITER ;
CALL show_min_salart(@ms);
SELECT @ms;

有參數無返回值

DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN 
	SELECT salary
	FROM emp
	WHERE last_name=empname;
END //
DELIMITER ;
CALL show_someone_salary('Abel');
SET @empname='Abel';
CALL show_someone_salary(@empname)

有參數有返回值

DELIMITER //
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DECIMAL(10,2))
BEGIN 
	SELECT salary INTO empsalary
	FROM emp
	WHERE last_name=empname;
END //
DELIMITER ;
SET @empname='Abel';
CALL show_someone_salary2(@empname,@empsalary);
SELECT @empsalary;

帶INOUT

DELIMITER //
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
BEGIN 
SELECT last_name
FROM emp
Where employee_id=
(
	SELECT manager_id 
	FROM emp
	WHERE last_name=empname
);
END //
DELIMITER ;
SET @empname='Abel';
CALL show_mgr_name(@empname);
SELECT @empname;

如何調試

通過SELECT語句，把程序執行的中間結果查詢出來，從而調試一個SQL語句的正確性。調試成功之后，把SELECT語句后移到下一個SQL語句，逐步推進查詢下一個 SQL語句

存儲函數

MySQL允許用戶自定義函數，自定義好了之后，調用方式與調用MySQL預定義的系統函數一樣

創建存儲函數

CREATE FUNCTION 函數名（參數名 參數類型）
RETURUNS 返回值類型
[characteristics]
BEGIN 
    函數體 #函數體中肯定有RETURN語句
END

• 參數類型，FUNCTION 中總是默認為IN參數

• RETURNS type 表示函數返回數據的類型，對于函數而言是強制的

• characteristics 表示創建函數時指定的對函數的約束

• 函數題可以用BEGIN … END表示SQL代碼的開始和結束。如果函數體只有一條語句，則可以省略BEGIN … END

調用存儲函數

SELECT 函數名（實參列表）

練習一

DELIMITER //
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
BEGIN
RETURN
(
	SELECT email
	FROM emp
	WHERE last_name='Abel'
);
END //
DELIMITER ;
SELECT email_by_name();

練習2

DELIMITER //
CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
BEGIN
RETURN
(
	SELECT email
	FROM emp
	WHERE employee_id=emp_id
);
END //
DELIMITER ;
SELECT email_by_id(101);
SET @emp_id=102;
SELECT email_by_id(@emp_id);

存儲函數與存儲過程的對比

• 存儲過程 PEOCEDURE 存儲函數 FUNCTION

• 調用語法 CALL 存儲過程 SELECT 存儲函數

• 存儲過程返回值可以有0個或對各 存儲函數返回值只有一個

• 存儲過程一般用于更新操作 存儲函數一般用于查詢結果為一個值并返回

• 存儲函數可以放在查詢語句中使用，存儲過程則不行

• 存儲過程功能更為強大，包括能夠執行對表的操作（創建表，刪除表）和事務操作，這些功能是存儲函數并不具備的

存儲過程和函數的查看修改刪除

查看

使用SHOW CREATE 語句 查看創建信息

SHOW CREATE PROCEDURE show_mgr_name\G;
SHOW  CREATE FUNCTION email_by_id\G;

使用SHOW STATUS 語句查看存儲過程和函數的狀態信息

SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS LIKE 'show_mgr_name' ;
SHOW FUNCTION STATUS LIKE 'email_by_name' ;

從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_salart' AND ROUTINE_TYPE='PROCEDURE';

修改存儲過程與函數

修改存儲過程或函數，不影響存儲過程或函數功能，只是修改相關特征，使用ALTER語句實現

ALTER PROCEDURE|FUNCTION 存儲過程或函數名 [characteristic ...]

刪除存儲過程或函數

DROP PROCEDURE|FUNCTION [IF EXISTS] 存儲過程或函數名

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