mysql存储过程(if、case、begin...end、while、repeat、loop、cursor游标)的使用

mysql存储过程使用

一、if的使用

1、语法

IF condition THEN-- 条件成立时执行的代码
ELSEIF another_condition THEN-- 其他条件成立时执行的代码
ELSE-- 所有条件不成立时执行的代码
END IF;

2、示例(int类型)

-- 删除存储过程
DROP PROCEDURE IF EXISTS check_value;
-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE check_value(IN val INT)
BEGINIF val > 10 THENSELECT CONCAT(val, ' Value is greater than 10') as log;ELSEIF val = 10 THENSELECT CONCAT(val, ' Value is exactly 10') as log;ELSESELECT CONCAT(val, ' Value is less than 10') as log;END IF;
END $$
DELIMITER ;
-- 调用存储过程
CALL check_value(5);

3、示例(varchar类型)

-- 删除存储过程
DROP PROCEDURE IF EXISTS check_value;
-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE check_string(IN str VARCHAR(255))
BEGINIF str = 'apple' THENSELECT 'The string is "apple"';ELSEIF str = 'banana' THENSELECT 'The string is "banana"';ELSESELECT 'The string is neither "apple" nor "banana"';END IF;
END $$
DELIMITER ;
-- 调用存储过程
CALL check_string('orange');

二、CASE

1、简单case语句

1、语法

CASE case_valueWHEN when_value THEN statement_list[WHEN when_value THEN statement_list] ...[ELSE statement_list]
END CASE;

2、示例

-- 删除存储过程
DROP PROCEDURE IF EXISTS adjust_salary;
-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE adjust_salary(IN emp_id INT, IN rating CHAR(1))
BEGINCASE ratingWHEN 'A' THENUPDATE employees SET salary = salary + 1 WHERE id = emp_id;WHEN 'B' THENUPDATE employees SET salary = salary * 1.05 WHERE id = emp_id;ELSEUPDATE employees SET salary = salary * 1.02 WHERE id = emp_id;END CASE;
END $$
DELIMITER ;
-- 调用存储过程
call adjust_salary(1,'A');

2、多语句分支与BEGIN…END

1、语法

CASEWHEN condition1 THENBEGINstatement1;statement2;END;WHEN condition2 THEN...
END CASE;

2、示例(记录日志并更新状态）

-- 删除存储过程
DROP PROCEDURE IF EXISTS process_order;
-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE process_order(IN order_id INT)
BEGIN-- 定义订单状态DECLARE order_status VARCHAR(20);--  将查询到的orders表的数据的status赋值给定义的order_statusSELECT status INTO order_status FROM orders WHERE id = order_id;-- case 处理订单状态业务 1、待支付 2、已支付 3、发货CASE order_statusWHEN '1' THEN--  修改订单表的状态为已支付，并且添加日志记录BEGINUPDATE orders SET status = '2' WHERE id = order_id;INSERT INTO logs (message) VALUES ('Order processing started.');END;WHEN '2' THEN-- 订单状态状态为发货INSERT INTO logs (message) VALUES ('已发货');ELSEINSERT INTO logs (message) VALUES ('发货完成');END CASE;
END $$
DELIMITER ;
-- 调用存储过程
call process_order(2);

三、循环遍历 WHILE、REPEAT、LOOP

1、WHILE(循环遍历)

1、语法

WHILE condition DO-- 循环体代码
END WHILE;

2、示例 (求和1-10)

-- 删除存储过程
DROP PROCEDURE IF EXISTS sum_numbers;
-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE sum_numbers()
BEGIN-- 定义变量DECLARE total INT DEFAULT 0;-- 定义计数器变量，初始值为1DECLARE counter INT DEFAULT 1;-- 循环条件，当计数器小于等于10时，循环继续执行WHILE counter <= 10 DOSET total = total + counter;SET counter = counter + 1;
END WHILE;
-- 输出结果
SELECT total AS result;
END $$
DELIMITER ;
-- 调用存储过程
CALL sum_numbers();

2、REPEAT(循环遍历)

1、语法

REPEAT
-- 循环体
UNTIL condition
END REPEAT; 

2、示例 (求和1-10)

-- 删除存储过程
DROP PROCEDURE IF EXISTS sum_numbers_repeat;
-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE sum_numbers_repeat()
BEGIN-- 定义变量DECLARE total INT DEFAULT 0;-- 定义计数器变量，初始值为1DECLARE counter INT DEFAULT 1;-- 定义REPEAT循环REPEATSET total = total + counter;SET counter = counter + 1;-- 循环条件，当计数器小于等于10时，循环继续执行UNTIL counter > 10END REPEAT;-- 输出结果SELECT total AS result;
END $$
DELIMITER ;
-- 调用存储过程
call sum_numbers_repeat();

3、LOOP + LEAVE(循环遍历)

1、语法

[begin_label:] LOOP-- 循环体代码IF condition THENLEAVE [loop_label];END IF;
END LOOP [end_label];

2、示例 (求和1-10)

-- 删除存储过程
DROP PROCEDURE IF EXISTS sum_numbers_loop;
-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE sum_numbers_loop()
BEGIN-- 定义变量DECLARE total INT DEFAULT 0;-- 定义计数器变量，初始值为1DECLARE counter INT DEFAULT 1;-- my_loop: LOOP 定义循环标签my_loop: LOOPSET total = total + counter;SET counter = counter + 1;-- 循环条件，当计数器小于等于10时，循环继续执行，否则结束循环IF counter > 10 THENLEAVE my_loop;END IF;-- 结束循环END LOOP my_loop;-- 输出结果SELECT total AS result;
END $$
DELIMITER ;
-- 执行存储过程
call sum_numbers_loop();

四、游标CURSOR的使用

1、语法

DECLARE cursor_employesssor_name CURSOR FOR select_statement;
OPEN cursor_employesssor_name;
FETCH NEXT FROM cursor_employesssor_name INTO variable;
CLOSE cursor_employesssor_name;

2、示例 (遍历表中的数据)

-- 创建表
CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(100),salary DECIMAL(10, 2)
);
-- 插入准备的数据
insert into employees values(1,'张三',5400);
insert into employees values(2,'李四',5600);
insert into employees values(3,'王五',3400);
insert into employees values(4,'李明',1200);
insert into employees values(5,'王凯',6300);
-- 删除存储过程
DROP PROCEDURE IF EXISTS salary_employees;
-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE salary_employees(IN input_salary int)
BEGIN-- 声明变量DECLARE v_id INT;DECLARE v_salary DECIMAL(10, 2);DECLARE done INT DEFAULT FALSE;-- 声明游标，查询所有员工DECLARE cursor_employess CURSOR FOR SELECT id, salary FROM employees;-- 声明处理程序，当游标无数据时设置done为TRUEDECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;-- 打开游标OPEN cursor_employess;-- 开始循环read_loop: LOOP-- 获取下一行数据FETCH cursor_employess INTO v_id, v_salary;-- 检测循环结束条件IF done THENLEAVE read_loop;END IF;-- 处理数据：薪资输入的金额,则涨工资100IF v_salary > input_salary THENUPDATE employees SET salary = v_salary+100 WHERE id = v_id;ELSEIF v_salary < input_salary THEN--  不错任何操作END IF;END LOOP;-- 关闭游标CLOSE cursor_employess;
END $$
DELIMITER ;
-- 调用存储过程,当员工工资大于输入的数值的时候，涨工资100
CALL salary_employees(10000);

五、使用 SELECT 输出结果

SELECT 'Debug: This is a log message' AS log;