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

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

• 存儲函數(shù)一定會有返回值的
• 存儲過程不一定有返回值

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

存儲過程

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

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

• 簡化操作，提高了SQL語句的重用性，減少了開發(fā)程序員的壓力
• 減少操作過程中的失誤，提高效率
• 減少網(wǎng)路傳輸量，客戶端不需要將所有的SQL語句通過網(wǎng)絡(luò)發(fā)給服務(wù)器
• 減少SQL語句暴露在網(wǎng)上的風險，提高數(shù)據(jù)查詢的安全性

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

• 視圖：是虛擬表，通常不對底層數(shù)據(jù)表直接操作
• 存儲過程：程序化的SQL，可以直接操作底層數(shù)據(jù)表，相比于面向集合的操作方式，能夠?qū)崿F(xiàn)一些更復雜的數(shù)據(jù)處理
• 相較于函數(shù)，存儲過程沒有返回值

分類

• 沒有參數(shù)（無參數(shù)無返回）
• 僅僅帶有IN 類型 （有參數(shù)無返回）
• 僅僅帶OUT類型（無參數(shù)有返回）
• 即帶IN又帶OUT(有參數(shù)有返回)
• 帶INOUT(有參數(shù)有返回)

創(chuàng)建存儲過程

DELIMITER $

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

DELIMITER ;

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

調(diào)用存儲過程

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語句，把程序執(zhí)行的中間結(jié)果查詢出來，從而調(diào)試一個SQL語句的正確性。調(diào)試成功之后，把SELECT語句后移到下一個SQL語句，逐步推進查詢下一個 SQL語句

存儲函數(shù)

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

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

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

• 參數(shù)類型，F(xiàn)UNCTION 中總是默認為IN參數(shù)
• RETURNS type 表示函數(shù)返回數(shù)據(jù)的類型，對于函數(shù)而言是強制的
• characteristics 表示創(chuàng)建函數(shù)時指定的對函數(shù)的約束
• 函數(shù)題可以用BEGIN … END表示SQL代碼的開始和結(jié)束。如果函數(shù)體只有一條語句，則可以省略BEGIN … END

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

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

練習一

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

存儲函數(shù)與存儲過程的對比

• 存儲過程 PEOCEDURE 存儲函數(shù) FUNCTION
• 調(diào)用語法 CALL 存儲過程 SELECT 存儲函數(shù)
• 存儲過程返回值可以有0個或?qū)Ω?存儲函數(shù)返回值只有一個
• 存儲過程一般用于更新操作 存儲函數(shù)一般用于查詢結(jié)果為一個值并返回
• 存儲函數(shù)可以放在查詢語句中使用，存儲過程則不行
• 存儲過程功能更為強大，包括能夠執(zhí)行對表的操作（創(chuàng)建表，刪除表）和事務(wù)操作，這些功能是存儲函數(shù)并不具備的

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

查看

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

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

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

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

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

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

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

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

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

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

到此這篇關(guān)于MySQL的存儲函數(shù)與存儲過程相關(guān)概念與具體實例詳解的文章就介紹到這了,更多相關(guān)MySQL的存儲函數(shù)與存儲過程內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論