MYSQL預存程序和儲存函數怎麼使用-mysql教程-PHP中文網

MYSQL預存程序和儲存函數怎麼使用

WBOY

發布： 2023-06-03 16:55:21

轉載

1076 人瀏覽過

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, &#39;高收入&#39;, &#39;&#39;) 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 ;

登入後複製