預存程序(Stored Procedure)是一組SQL語句的集合,這些語句被儲存在資料庫中。透過封裝業務邏輯,預存程序可以提高資料庫執行效率和資料存取的安全性。
儲存函數(Stored Function)是指在一個資料庫中儲存的一組執行SQL語句的集合,與預存程序的差異在於,儲存函數有一個傳回值。
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 ;
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 ;
呼叫預存程序:
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);
以下是一些常見的預存程序和儲存函數的範例:
假設我們已經有一張名為employee
的員工表,現在需要建立一個儲存過程,查詢員工的姓名和工資,如果工資大於5000,則在結果中加入一個備註:「高收入」。
DELIMITER // CREATE PROCEDURE get_employee_info_with_note() BEGIN SELECT name, salary, IF(salary > 5000, '高收入', '') AS note FROM employee; END // DELIMITER ;
假設我們已經有一張名為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 ;
假設我們已經有一張名為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 ;
假設我們已經有一個名為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 ;
#程式碼可以重複使用,避免重複寫SQL語句;
在預存程序和儲存函數中可以使用流程控制語句,處理複雜邏輯;
透過預存程序和儲存函數可以對資料庫操作進行封裝,提高效率和安全性。
以上是MYSQL預存程序和儲存函數怎麼使用的詳細內容。更多資訊請關注PHP中文網其他相關文章!