實作 : 練習使用Stored Procedure以及Stored Function

Stored Procedure(預儲程序)：把一連串的SQL程序步驟儲存起來，最後透過 『call 預儲程序名稱;』來呼叫。 Stored Function(預儲函數)：或稱為使用者定義函數，跟預儲程序很像，不過最後會傳回值。呼叫方式不是 call，而是Select，例如：『select 預儲函數名稱(引數值);』或『select 預儲函數名稱();』。

本文就來練習使用Stored Procedure以及Stored Function。

Stored Procedure(預儲程序)和Stored Function(預儲函數)的好處包括：

重用性：可以多次重用，減少了反覆編寫相同的SQL代碼的需要。

性能：是預編譯的，這提高了操作的性能。

安全性：可以幫助保護資料庫免受SQL注入攻擊和未經授權的訪問。

維護：比嵌入在應用程序代碼中的SQL代碼更容易維護。

建立Stored Procedure/Function語法 :
https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

Stored Function 範例

如下的stored function，是用來換算磅到公斤。

DELIMITER //
CREATE FUNCTION lbs_to_kg(lbs MEDIUMINT UNSIGNED)
RETURNS FLOAT(10,5)
DETERMINISTIC
BEGIN
RETURN (lbs * 0.45359237);
END//
DELIMITER ;

當你使用 SELECT lbs_to_kg(10); 就會得到 4.53592，也就是10磅等於4.53592公斤。

DETERMINISTIC的意思就是指給同樣的輸入，就會得到同樣的輸出。nonDETERMINISTIC就是指給同樣的輸入，未必會得到同樣的輸出。

如下圖 :

Stored Procedure 範例#1

如下的stored procedure，是用來列出學生以姓名小到大排序。

DELIMITER //
CREATE PROCEDURE get_all_students()
BEGIN
SELECT * FROM students ORDER BY stud_name;
END //
DELIMITER ;

當你使用 CALL get_all_students(); 就會列出資料。

如下圖 :

Stored Procedure 範例#2

DELIMITER //
CREATE PROCEDURE get_students(IN dept_id varchar(2))
BEGIN
select * from students where stud_dept=dept_id;
END //
DELIMITER ;

然後就可以使用 CALL get_students('1');

如下圖 :

Stored Procedure 範例#3

DELIMITER //
CREATE PROCEDURE get_students_count(IN dept_id varchar(2), OUT Total_students int)
BEGIN
select count(*) INTO Total_students from students where stud_dept=dept_id;
END //
DELIMITER ;

然後就可以使用 CALL get_students_count('1', @tot);

SELECT @tot;

如下圖 :