目錄

• 1. 什么是存儲過程和存儲函數？
• 2. 創建存儲過程
• 3. 創建存儲函數
• 4. 存儲過程和存儲函數的使用
• 5. 帶有if語句的存儲過程
• 6. 帶有循環語句的存儲過程
• 7. 帶有事務的存儲過程
• 8. 帶有游標的存儲函數
• 9. 存儲過程和存儲函數的優點
• 10.總結

1. 什么是存儲過程和存儲函數？

• 存儲過程（Stored Procedure）是指在一個數據庫中存儲的一組執行SQL語句的集合。存儲過程可以封裝業務邏輯，提高數據庫執行效率，同時也可以提高數據訪問的安全性。
• 存儲函數（Stored Function）是指在一個數據庫中存儲的一組執行SQL語句的集合，與存儲過程的區別在于，存儲函數有一個返回值。

2. 創建存儲過程

CREATE PROCEDURE procedure_name([IN/OUT] parameter_name data_type)
BEGIN
    SQL Statement;
END;

假設我們已經有一張名為employee的員工表，現在需要創建一個存儲過程，可以根據員工的工號查詢員工的姓名和工資：

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. 創建存儲函數

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;

假設我們已經有一張名為product的商品表，現在需要創建一個存儲函數，可以根據商品的編號查詢商品的單價：

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. 存儲過程和存儲函數的使用

• 調用存儲過程：

CALL procedure_name([parameter_name]);

• 調用存儲函數：

SELECT function_name([parameter_name]);

使用上面創建的get_employee_info_by_id存儲過程可以這樣調用：

CALL get_employee_info_by_id(1);

使用上面創建的get_product_price_by_id存儲函數可以這樣調用：

SELECT get_product_price_by_id(1001);

以下是一些常見的存儲過程和存儲函數的示例：

5. 帶有if語句的存儲過程

假設我們已經有一張名為employee的員工表，現在需要創建一個存儲過程，查詢員工的姓名和工資，如果工資大于5000，則在結果中添加一個備注：“高收入”。

DELIMITER //
CREATE PROCEDURE get_employee_info_with_note()
BEGIN
    SELECT name, salary, IF(salary > 5000, "高收入", "") AS note FROM employee;
END //
DELIMITER ;

6. 帶有循環語句的存儲過程

假設我們已經有一張名為product的商品表，現在需要創建一個存儲過程，把商品的單價全部乘以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. 帶有事務的存儲過程

假設我們已經有一張名為order的訂單表和一張名為order_item的訂單詳情表，現在需要創建一個存儲過程，向這兩張表中插入一條記錄。

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. 帶有游標的存儲函數

假設我們已經有一張名為product的商品表，現在需要創建一個存儲函數，查詢商品表中的最大單價。

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存儲過程和存儲函數的學習文章及示例，希望對您有幫助。

9. 存儲過程和存儲函數的優點

• 代碼可以重復使用，避免重復編寫SQL語句；
• 在存儲過程和存儲函數中可以使用流程控制語句，處理復雜邏輯；
• 通過存儲過程和存儲函數可以對數據庫操作進行封裝，提高效率和安全性。

10.總結

在MYSQL中，存儲過程和存儲函數可以幫助我們封裝業務邏輯，提高數據庫執行效率，同時也可以提高數據訪問的安全性。學會使用存儲過程和存儲函數可以幫助我們更好地管理和優化數據庫。

到此這篇關于淺談MYSQL存儲過程和存儲函數的文章就介紹到這了,更多相關MYSQL存儲過程和存儲函數內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！