好的，我们开始今天的讲座：MySQL存储过程之：存储过程的创建与调用：从DELIMITER到CALL的完整语法。
今天，我们将深入探讨MySQL存储过程的创建与调用，从DELIMITER的使用到CALL语句的详细语法，提供全面且实用的指南。存储过程是预编译的SQL语句集合，存储在数据库服务器中，可以被重复调用。它们提高了性能、安全性，并简化了复杂的数据库操作。
1. 存储过程的优势
在深入语法之前，我们先简单了解存储过程的优点：
- 提高性能： 存储过程预先编译并存储在服务器端，减少了客户端和服务器之间的网络流量，提高了执行速度。
- 增强安全性： 存储过程可以限制对底层数据的直接访问，只允许通过预定义的接口进行操作，从而提高安全性。
- 代码重用： 存储过程可以被多次调用，避免了重复编写相同的SQL代码，提高了开发效率。
- 简化复杂操作： 存储过程可以将复杂的业务逻辑封装起来，简化客户端程序的开发。
- 数据一致性： 通过在存储过程中执行一系列操作，可以保证数据的一致性。
2. DELIMITER语句：改变语句结束符
MySQL默认使用分号（;）作为SQL语句的结束符。但是，在创建存储过程时，存储过程体内部可能包含多个SQL语句，这些语句也需要用分号分隔。如果继续使用默认的分号作为结束符，MySQL会误认为存储过程定义在第一个分号处结束，导致语法错误。
DELIMITER语句允许我们临时改变SQL语句的结束符，通常将其改为//或$$等不常用的符号，以便MySQL能够正确解析整个存储过程的定义。
语法：
DELIMITER 新的结束符示例：
DELIMITER //使用DELIMITER更改结束符后，所有的SQL语句都将以新的结束符结尾，直到再次使用DELIMITER语句恢复默认的分号结束符。
3. CREATE PROCEDURE语句：创建存储过程
CREATE PROCEDURE语句用于创建存储过程。
语法：
CREATE PROCEDURE 存储过程名 ( [参数列表] )
BEGIN
SQL语句;
SQL语句;
...
END;参数列表是可选的，可以包含零个或多个参数。 每个参数的定义格式如下：
[IN | OUT | INOUT] 参数名 数据类型- IN： 输入参数，只能将值传递给存储过程，存储过程内部对该参数的修改不会影响外部变量。
- OUT： 输出参数，只能从存储过程内部将值传递给外部变量。
- INOUT： 输入/输出参数，可以将值传递给存储过程，并且存储过程内部对该参数的修改会影响外部变量。
示例：
DELIMITER //
CREATE PROCEDURE get_customer_count(IN city VARCHAR(50), OUT total INT)
BEGIN
SELECT COUNT(*) INTO total FROM customers WHERE customerCity = city;
END //
DELIMITER ;在这个例子中，我们创建了一个名为get_customer_count的存储过程，它接受一个输入参数city（VARCHAR类型）和一个输出参数total（INT类型）。 存储过程的功能是查询指定城市中的客户数量，并将结果存储到输出参数total中。
4. 存储过程体：BEGIN和END之间的SQL语句
存储过程体是BEGIN和END之间的SQL语句集合。 存储过程体可以包含各种SQL语句，例如SELECT、INSERT、UPDATE、DELETE等，以及控制流程语句，例如IF、CASE、WHILE、LOOP等。
示例：
DELIMITER //
CREATE PROCEDURE update_customer_address(IN customer_id INT, IN new_address VARCHAR(100))
BEGIN
UPDATE customers SET customerAddress = new_address WHERE customerID = customer_id;
SELECT * FROM customers WHERE customerID = customer_id; -- 返回更新后的客户信息
END //
DELIMITER ;这个例子中，存储过程体包含一个UPDATE语句和一个SELECT语句。 UPDATE语句用于更新指定客户的地址，SELECT语句用于返回更新后的客户信息。
5. 变量声明和使用
在存储过程中，可以使用DECLARE语句声明变量。
语法：
DECLARE 变量名 数据类型 [DEFAULT 默认值];示例：
DELIMITER //
CREATE PROCEDURE calculate_discount(IN product_price DECIMAL(10, 2), OUT discount_price DECIMAL(10, 2))
BEGIN
DECLARE discount_rate DECIMAL(5, 2) DEFAULT 0.1; -- 默认折扣率为10%
SET discount_price = product_price * (1 - discount_rate);
END //
DELIMITER ;在这个例子中，我们声明了一个名为discount_rate的变量，数据类型为DECIMAL(5, 2)，默认值为0.1。 SET语句用于给变量赋值。
6. 控制流程语句
存储过程支持多种控制流程语句，例如IF、CASE、WHILE、LOOP等，可以实现复杂的业务逻辑。
- IF语句：
语法：
IF 条件 THEN
SQL语句;
[ELSEIF 条件 THEN
SQL语句;]
[ELSE
SQL语句;]
END IF;示例：
DELIMITER //
CREATE PROCEDURE check_customer_credit(IN customer_id INT, OUT credit_status VARCHAR(20))
BEGIN
DECLARE credit_limit DECIMAL(10, 2);
SELECT customerCreditLimit INTO credit_limit FROM customers WHERE customerID = customer_id;
IF credit_limit > 10000 THEN
SET credit_status = 'High Credit';
ELSEIF credit_limit > 5000 THEN
SET credit_status = 'Medium Credit';
ELSE
SET credit_status = 'Low Credit';
END IF;
END //
DELIMITER ;- CASE语句：
语法：
CASE
WHEN 条件 THEN SQL语句;
WHEN 条件 THEN SQL语句;
...
[ELSE SQL语句;]
END CASE;示例：
DELIMITER //
CREATE PROCEDURE get_product_category(IN product_id INT, OUT category_name VARCHAR(50))
BEGIN
DECLARE category_id INT;
SELECT productCategoryID INTO category_id FROM products WHERE productID = product_id;
CASE
WHEN category_id = 1 THEN SET category_name = 'Electronics';
WHEN category_id = 2 THEN SET category_name = 'Clothing';
WHEN category_id = 3 THEN SET category_name = 'Books';
ELSE SET category_name = 'Other';
END CASE;
END //
DELIMITER ;- WHILE循环：
语法：
WHILE 条件 DO
SQL语句;
END WHILE;示例：
DELIMITER //
CREATE PROCEDURE calculate_sum(IN num INT, OUT sum INT)
BEGIN
DECLARE i INT DEFAULT 1;
SET sum = 0;
WHILE i <= num DO
SET sum = sum + i;
SET i = i + 1;
END WHILE;
END //
DELIMITER ;- LOOP循环：
语法：
[循环标签:] LOOP
SQL语句;
IF 条件 THEN
LEAVE 循环标签;
END IF;
END LOOP [循环标签];示例：
DELIMITER //
CREATE PROCEDURE find_first_even(IN start_num INT, IN end_num INT, OUT first_even INT)
BEGIN
DECLARE i INT DEFAULT start_num;
my_loop: LOOP
IF i > end_num THEN
SET first_even = NULL;
LEAVE my_loop;
END IF;
IF i % 2 = 0 THEN
SET first_even = i;
LEAVE my_loop;
END IF;
SET i = i + 1;
END LOOP my_loop;
END //
DELIMITER ;7. CALL语句：调用存储过程
CALL语句用于调用存储过程。
语法：
CALL 存储过程名 ( [参数列表] );示例：
-- 调用 get_customer_count 存储过程
CALL get_customer_count('New York', @total_customers);
SELECT @total_customers; -- 查看结果
-- 调用 update_customer_address 存储过程
CALL update_customer_address(1, '123 Main St');
-- 调用 calculate_discount 存储过程
CALL calculate_discount(100.00, @discounted_price);
SELECT @discounted_price;
-- 调用 check_customer_credit 存储过程
CALL check_customer_credit(1, @credit_status);
SELECT @credit_status;
-- 调用 calculate_sum 存储过程
CALL calculate_sum(10, @sum_result);
SELECT @sum_result;
-- 调用 find_first_even 存储过程
CALL find_first_even(1, 10, @first_even_num);
SELECT @first_even_num;注意：
- 对于OUT和INOUT参数，需要使用用户变量（以
@开头）来接收存储过程返回的值。 - 调用存储过程时，必须按照存储过程定义时的参数顺序传递参数。
8. 错误处理
在存储过程中，可以使用DECLARE CONTINUE HANDLER或DECLARE EXIT HANDLER来处理错误。
DECLARE CONTINUE HANDLER： 遇到错误后，继续执行存储过程。DECLARE EXIT HANDLER： 遇到错误后，立即退出存储过程。
语法：
DECLARE {CONTINUE | EXIT} HANDLER FOR {SQLSTATE value | condition_name | sqlwarning | not found | sqlexception}
SQL语句;示例：
DELIMITER //
CREATE PROCEDURE safe_update_customer(IN customer_id INT, IN new_email VARCHAR(100))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK; -- 回滚事务
SELECT 'Error: Update failed' AS message;
END;
START TRANSACTION; -- 开启事务
UPDATE customers SET customerEmail = new_email WHERE customerID = customer_id;
COMMIT; -- 提交事务
SELECT 'Update successful' AS message;
END //
DELIMITER ;在这个例子中，我们定义了一个EXIT HANDLER来处理SQLEXCEPTION错误。 如果在UPDATE语句执行过程中发生错误，存储过程会回滚事务并返回错误消息。
9. DROP PROCEDURE语句：删除存储过程
DROP PROCEDURE语句用于删除存储过程。
语法：
DROP PROCEDURE [IF EXISTS] 存储过程名;示例：
DROP PROCEDURE IF EXISTS get_customer_count;10. SHOW CREATE PROCEDURE语句：查看存储过程定义
SHOW CREATE PROCEDURE语句用于查看存储过程的定义。
语法：
SHOW CREATE PROCEDURE 存储过程名;示例：
SHOW CREATE PROCEDURE get_customer_count;11. 示例：更复杂的存储过程
DELIMITER //
CREATE PROCEDURE process_order (
IN order_id INT
)
BEGIN
-- 声明变量
DECLARE total_amount DECIMAL(10, 2);
DECLARE item_price DECIMAL(10, 2);
DECLARE quantity INT;
DECLARE item_id INT;
DECLARE done INT DEFAULT FALSE;
-- 声明游标，用于遍历订单项
DECLARE order_items_cursor CURSOR FOR
SELECT orderItemID, productPrice, quantity
FROM order_items
WHERE orderID = order_id;
-- 声明 CONTINUE HANDLER, 用于处理游标到达末尾的情况
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 初始化总金额
SET total_amount = 0;
-- 打开游标
OPEN order_items_cursor;
-- 循环遍历订单项
read_loop: LOOP
FETCH order_items_cursor INTO item_id, item_price, quantity;
IF done THEN
LEAVE read_loop;
END IF;
-- 计算当前订单项的总金额，并累加到总金额中
SET total_amount = total_amount + (item_price * quantity);
END LOOP;
-- 关闭游标
CLOSE order_items_cursor;
-- 更新订单的总金额
UPDATE orders SET orderTotalAmount = total_amount WHERE orderID = order_id;
-- 返回总金额
SELECT total_amount;
END //
DELIMITER ;
-- 调用示例：
-- CALL process_order(1);这个存储过程演示了游标的使用，游标允许逐行处理查询结果。它计算指定订单的总金额，并更新订单表。
总结一下：
我们学习了存储过程的基本语法，包括DELIMITER、CREATE PROCEDURE、CALL、变量声明、控制流程语句和错误处理。 掌握这些知识点，可以编写更复杂、更高效的存储过程。通过存储过程，我们可以将业务逻辑封装在数据库服务器端，提高性能、安全性，并简化客户端程序的开发。