MySQL的儲存函數與儲存過程相關概念與具體範例詳解

2023-03-02 18:01:53

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_nameG;
SHOW  CREATE FUNCTION email_by_idG;

使用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的儲存函數與儲存過程內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！