MySQL的存儲(chǔ)函數(shù)與存儲(chǔ)過程實(shí)例代碼分析

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

MySQL存儲(chǔ)過程與存儲(chǔ)函數(shù)的相關(guān)概念

存儲(chǔ)函數(shù)和存儲(chǔ)過程的主要區(qū)別：

• 存儲(chǔ)函數(shù)一定會(huì)有返回值的

• 存儲(chǔ)過程不一定有返回值

存儲(chǔ)過程和函數(shù)能后將復(fù)雜的SQL邏輯封裝在一起，應(yīng)用程序無需關(guān)注存儲(chǔ)過程和函數(shù)內(nèi)部復(fù)雜的SQL邏輯，而只需要簡(jiǎn)單地調(diào)用存儲(chǔ)過程和函數(shù)即可

存儲(chǔ)過程

一組預(yù)先編譯的SQL語(yǔ)句的封裝

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

• 簡(jiǎn)化操作，提高了SQL語(yǔ)句的重用性，減少了開發(fā)程序員的壓力

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

• 減少網(wǎng)路傳輸量，客戶端不需要將所有的SQL語(yǔ)句通過網(wǎng)絡(luò)發(fā)給服務(wù)器

• 減少SQL語(yǔ)句暴露在網(wǎng)上的風(fēng)險(xiǎn)，提高數(shù)據(jù)查詢的安全性

與視圖，函數(shù)的對(duì)比：

• 視圖：是虛擬表，通常不對(duì)底層數(shù)據(jù)表直接操作

• 存儲(chǔ)過程：程序化的SQL，可以直接操作底層數(shù)據(jù)表，相比于面向集合的操作方式，能夠?qū)崿F(xiàn)一些更復(fù)雜的數(shù)據(jù)處理

• 相較于函數(shù)，存儲(chǔ)過程沒有返回值

分類

• 沒有參數(shù)（無參數(shù)無返回）

• 僅僅帶有IN 類型 （有參數(shù)無返回）

• 僅僅帶OUT類型（無參數(shù)有返回）

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

• 帶INOUT(有參數(shù)有返回)

創(chuàng)建存儲(chǔ)過程

DELIMITER $

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

DELIMITER ;

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

調(diào)用存儲(chǔ)過程

CALL select_all_data();

無參數(shù)無返回值

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

無參數(shù)有返回值

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;

有參數(shù)無返回值

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)

有參數(shù)有返回值

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;

如何調(diào)試

通過SELECT語(yǔ)句，把程序執(zhí)行的中間結(jié)果查詢出來，從而調(diào)試一個(gè)SQL語(yǔ)句的正確性。調(diào)試成功之后，把SELECT語(yǔ)句后移到下一個(gè)SQL語(yǔ)句，逐步推進(jìn)查詢下一個(gè) SQL語(yǔ)句

存儲(chǔ)函數(shù)

MySQL允許用戶自定義函數(shù)，自定義好了之后，調(diào)用方式與調(diào)用MySQL預(yù)定義的系統(tǒng)函數(shù)一樣

創(chuàng)建存儲(chǔ)函數(shù)

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

• 參數(shù)類型，F(xiàn)UNCTION 中總是默認(rèn)為IN參數(shù)

• RETURNS type 表示函數(shù)返回?cái)?shù)據(jù)的類型，對(duì)于函數(shù)而言是強(qiáng)制的

• characteristics 表示創(chuàng)建函數(shù)時(shí)指定的對(duì)函數(shù)的約束

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

調(diào)用存儲(chǔ)函數(shù)

SELECT 函數(shù)名（實(shí)參列表）

練習(xí)一

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();

練習(xí)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);

存儲(chǔ)函數(shù)與存儲(chǔ)過程的對(duì)比

• 存儲(chǔ)過程 PEOCEDURE 存儲(chǔ)函數(shù) FUNCTION

• 調(diào)用語(yǔ)法 CALL 存儲(chǔ)過程 SELECT 存儲(chǔ)函數(shù)

• 存儲(chǔ)過程返回值可以有0個(gè)或?qū)Ω?存儲(chǔ)函數(shù)返回值只有一個(gè)

• 存儲(chǔ)過程一般用于更新操作 存儲(chǔ)函數(shù)一般用于查詢結(jié)果為一個(gè)值并返回

• 存儲(chǔ)函數(shù)可以放在查詢語(yǔ)句中使用，存儲(chǔ)過程則不行

• 存儲(chǔ)過程功能更為強(qiáng)大，包括能夠執(zhí)行對(duì)表的操作（創(chuàng)建表，刪除表）和事務(wù)操作，這些功能是存儲(chǔ)函數(shù)并不具備的

存儲(chǔ)過程和函數(shù)的查看修改刪除

查看

使用SHOW CREATE 語(yǔ)句 查看創(chuàng)建信息

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

使用SHOW STATUS 語(yǔ)句查看存儲(chǔ)過程和函數(shù)的狀態(tài)信息

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

從information_schema.Routines表中查看存儲(chǔ)過程和函數(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_salart' AND ROUTINE_TYPE='PROCEDURE';

修改存儲(chǔ)過程與函數(shù)

修改存儲(chǔ)過程或函數(shù)，不影響存儲(chǔ)過程或函數(shù)功能，只是修改相關(guān)特征，使用ALTER語(yǔ)句實(shí)現(xiàn)

ALTER PROCEDURE|FUNCTION 存儲(chǔ)過程或函數(shù)名 [characteristic ...]

刪除存儲(chǔ)過程或函數(shù)

DROP PROCEDURE|FUNCTION [IF EXISTS] 存儲(chǔ)過程或函數(shù)名

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