淺談MYSQL存儲過程和存儲函數(shù)
1. 什么是存儲過程和存儲函數(shù)？
- 存儲過程（Stored Procedure）是指在一個數(shù)據(jù)庫中存儲的一組執(zhí)行SQL語句的集合。存儲過程可以封裝業(yè)務(wù)邏輯，提高數(shù)據(jù)庫執(zhí)行效率，同時也可以提高數(shù)據(jù)訪問的安全性。
- 存儲函數(shù)（Stored Function）是指在一個數(shù)據(jù)庫中存儲的一組執(zhí)行SQL語句的集合，與存儲過程的區(qū)別在于，存儲函數(shù)有一個返回值。
2. 創(chuàng)建存儲過程
CREATE PROCEDURE procedure_name([IN/OUT] parameter_name data_type)
BEGIN
SQL Statement;
END;假設(shè)我們已經(jīng)有一張名為employee的員工表，現(xiàn)在需要創(chuàng)建一個存儲過程，可以根據(jù)員工的工號查詢員工的姓名和工資：
DELIMITER //
CREATE PROCEDURE get_employee_info_by_id(IN emp_id INT)
BEGIN
SELECT name, salary FROM employee WHERE id = emp_id;
END //
DELIMITER ;3. 創(chuàng)建存儲函數(shù)
CREATE FUNCTION function_name([IN/OUT] parameter_name data_type) RETURNS data_type
BEGIN
DECLARE variable_name data_type;
SQL Statement;
RETURN variable_name;
END;假設(shè)我們已經(jīng)有一張名為product的商品表，現(xiàn)在需要創(chuàng)建一個存儲函數(shù)，可以根據(jù)商品的編號查詢商品的單價：
DELIMITER //
CREATE FUNCTION get_product_price_by_id(IN product_id INT) RETURNS DECIMAL(10,2)
BEGIN
DECLARE price DECIMAL(10,2);
SELECT unit_price INTO price FROM product WHERE id = product_id;
RETURN price;
END //
DELIMITER ;4. 存儲過程和存儲函數(shù)的使用
- 調(diào)用存儲過程：
CALL procedure_name([parameter_name]);
- 調(diào)用存儲函數(shù)：
SELECT function_name([parameter_name]);
使用上面創(chuàng)建的get_employee_info_by_id存儲過程可以這樣調(diào)用：
CALL get_employee_info_by_id(1);
使用上面創(chuàng)建的get_product_price_by_id存儲函數(shù)可以這樣調(diào)用：
SELECT get_product_price_by_id(1001);
以下是一些常見的存儲過程和存儲函數(shù)的示例：
5. 帶有if語句的存儲過程
假設(shè)我們已經(jīng)有一張名為employee的員工表，現(xiàn)在需要創(chuàng)建一個存儲過程，查詢員工的姓名和工資，如果工資大于5000，則在結(jié)果中添加一個備注：“高收入”。
DELIMITER //
CREATE PROCEDURE get_employee_info_with_note()
BEGIN
SELECT name, salary, IF(salary > 5000, '高收入', '') AS note FROM employee;
END //
DELIMITER ;6. 帶有循環(huán)語句的存儲過程
假設(shè)我們已經(jīng)有一張名為product的商品表，現(xiàn)在需要創(chuàng)建一個存儲過程，把商品的單價全部乘以1.1。
DELIMITER //
CREATE PROCEDURE update_all_product_price()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE pid INT;
DECLARE price DECIMAL(10,2);
DECLARE cur CURSOR FOR SELECT id, unit_price FROM product;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO pid, price;
IF done THEN
LEAVE read_loop;
END IF;
UPDATE product SET unit_price = price * 1.1 WHERE id = pid;
END LOOP;
CLOSE cur;
END //
DELIMITER ;7. 帶有事務(wù)的存儲過程
假設(shè)我們已經(jīng)有一張名為order的訂單表和一張名為order_item的訂單詳情表，現(xiàn)在需要創(chuàng)建一個存儲過程，向這兩張表中插入一條記錄。
DELIMITER //
CREATE PROCEDURE insert_order(IN order_id INT, IN item_name VARCHAR(50), IN item_price DECIMAL(10,2), IN item_quantity INT)
BEGIN
START TRANSACTION;
INSERT INTO `order`(id) VALUES(order_id);
SET @last_order_id = LAST_INSERT_ID();
INSERT INTO order_item(order_id, item_name, item_price, item_quantity) VALUES(@last_order_id, item_name, item_price, item_quantity);
COMMIT;
END //
DELIMITER ;8. 帶有游標(biāo)的存儲函數(shù)
假設(shè)我們已經(jīng)有一張名為product的商品表，現(xiàn)在需要創(chuàng)建一個存儲函數(shù)，查詢商品表中的最大單價。
DELIMITER //
CREATE FUNCTION get_max_product_price() RETURNS DECIMAL(10,2)
BEGIN
DECLARE max_price DECIMAL(10,2);
DECLARE cur CURSOR FOR SELECT unit_price FROM product;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET max_price = 0;
OPEN cur;
FETCH cur INTO max_price;
read_loop: LOOP
FETCH cur INTO max_price;
IF max_price IS NULL THEN
LEAVE read_loop;
END IF;
IF max_price > @max_price THEN
SET @max_price = max_price;
END IF;
END LOOP;
CLOSE cur;
RETURN max_price;
END //
DELIMITER ;以上就是MYSQL存儲過程和存儲函數(shù)的學(xué)習(xí)文章及示例，希望對您有幫助。
9. 存儲過程和存儲函數(shù)的優(yōu)點
- 代碼可以重復(fù)使用，避免重復(fù)編寫SQL語句；
- 在存儲過程和存儲函數(shù)中可以使用流程控制語句，處理復(fù)雜邏輯；
- 通過存儲過程和存儲函數(shù)可以對數(shù)據(jù)庫操作進(jìn)行封裝，提高效率和安全性。
10.總結(jié)
在MYSQL中，存儲過程和存儲函數(shù)可以幫助我們封裝業(yè)務(wù)邏輯，提高數(shù)據(jù)庫執(zhí)行效率，同時也可以提高數(shù)據(jù)訪問的安全性。學(xué)會使用存儲過程和存儲函數(shù)可以幫助我們更好地管理和優(yōu)化數(shù)據(jù)庫。
到此這篇關(guān)于淺談MYSQL存儲過程和存儲函數(shù)的文章就介紹到這了,更多相關(guān)MYSQL存儲過程和存儲函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
我們主要介紹的是正確通過rpm包安裝、對MySQL進(jìn)行配置與卸載的實際操作步驟，以下就是文章的具體內(nèi)容描述，望你會有所收獲。2010-08-08
這篇文章主要介紹了MySQL中的基本查詢語句學(xué)習(xí)筆記,包括使用limit限制查詢結(jié)果條數(shù)和合并查詢結(jié)果的方法,需要的朋友可以參考下2016-03-03
mysql 數(shù)據(jù)庫基礎(chǔ)筆記，剛開始接觸mysql的朋友可以參考下2012-07-07
SELECT語句是SQL中最常用的語句。它的用途是從一個或多個表中檢索信息。為了使用SELECT檢索表數(shù)據(jù)，必須至少給出兩條信息：想選擇什么、從什么地方選擇2023-02-02
這篇文章主要給大家介紹了關(guān)于MySQL中幾種插入和批量語句的相關(guān)資料,在mysql數(shù)據(jù)庫中,實現(xiàn)批量插入數(shù)據(jù)與批量更新數(shù)據(jù)的例子,即批量insert、update的方法,需要的朋友可以參考下2021-09-09
如果你的?Mysql?搭建了主從同步,或者數(shù)據(jù)庫開啟了log-bin日志,那么隨著時間的推移,你的數(shù)據(jù)庫data目錄下會產(chǎn)生大量的日志文件,下面這篇文章主要給大家介紹了關(guān)于MySQL安全刪除binlog日志的詳細(xì)步驟,需要的朋友可以參考下2024-02-02
MySQL服務(wù)無法啟動且服務(wù)沒有報告任何錯誤的解決辦法
在啟動項目時,發(fā)現(xiàn)昨天能夠跑的項目今天跑不了了,一看原來是mysql數(shù)據(jù)庫出現(xiàn)了問題,下面這篇文章主要給大家介紹了關(guān)于MySQL服務(wù)無法啟動且服務(wù)沒有報告任何錯誤的解決辦法,需要的朋友可以參考下2023-05-05
下面小編就為大家?guī)硪黄€上MYSQL同步報錯故障處理方法總結(jié)(必看篇)。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-03-03
在MySQL中,可以通過使用EXPLAIN語句來分析SQL查詢是否成功使用了索引,本文給大家介紹了使用EXPLAIN語句分析SQL語句是否成功使用索引的步驟,需要的朋友可以參考下2023-12-12
windows server 2008 64位MySQL5.6免安裝版本配置方法圖解
這篇文章主要介紹了windows server 2008 64位MySQL5.6免安裝版本配置方法圖解,需要的朋友可以參考下2017-08-08