MySQL 存储过程 —— procedure

MySQL 存储过程 —— procedure

1、简单存储过程

-- DELIMITER 边界符，代替';'
DELIMITER $$
DROP PROCEDURE IF EXISTS procedure_name $$
CREATE PROCEDURE procedure_name()
BEGIN
	-- 存储逻辑
END $$
DELIMITER ;

2、变量

-- 会话变量，变量名前加@
@variable_name
-- 普通变量定义，一般在BEGIN END中定义使用
DECLARE variable_name dataType(size) DEFAULT default_value;
-- 变量赋值，有两种方式
SET variable_name = variable_value;
SELECT column_name INTO variable_name FROM table_name ;

-- 示例
declare num int default 0;
set num = 1;
select id into num from test;

3、参数

-- 存储参数有三种，IN、OUT、INOUT
-- IN:输入参数，OUT:输出参数，INOUT:输入输出参数
IN/OUT/INOUT param_name dateType(size)

-- 示例
create procedure procedure_name(in id int, out name varchar(20), inout result varchar(50))
begin
end;

4、条件语句 if

-- if
IF expression THEN
	statements;
END IF;

-- if...else
IF expression THEN
	statements;
ELSE 
	else-statements;
END IF;

-- if...ifelse...else
IF expression THEN
	statements;
ELSEIF elseif-expression THEN
	elseif-statements;
...
ELSE
	else-statements;
END IF;

5、条件语句 case

CASE case-expression
WHEN expression1 THEN commands;
WHEN expression2 THEN commands;
...
ELSE commands;
END CASE;

CASE 
WHEN condition1 THEN commands;
WHEN condition2 THEN commands;
...
ELSE commands;
END CASE;

6、循环语句 while/repeat/loop

-- WHILE 先判断
WHILE expression DO
	statements;
END WHILE;

-- REPEAT 后判断
REPEAT
	statements;
UNTIL expression;
END REPEAT;

-- LOOP(leave、iterate)
-- leave 相当于 break
-- iterate 相当于 continue
loop_name: LOOP
	IF condition1 THEN
		LEAVE loop_name;
	END IF;
	IF condition2 THEN
		ITERATE loop_name;
	END IF;
	statements;
END LOOP loop_name;

7、异常处理

-- 规则
-- 异常
DECLARE CONTINUE/EXIT HANDLER FOR condition_value statement;
-- 条件 condition_value
DECLARE condition_name CONDITION FOR condition_value;

-- 示例
DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_finished = 1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET has_error = 1;

DECLARE not_found_table CONDITION FOR 1051;
DECLARE EXIT HANDLER FOR not_found_table select 'please create table first'; 

8、signal/resignal

参考：https://www.begtut.com/mysql/mysql-signal-resignal.html

-- item_name取值：MESSAGE_TEXT、MYSQL_ERRORNO、CURSOR_NAME等
SIGNAL SQLSTATE | condition_name
SET item_name_1 = value_1,
    item_name_2 = value_2, etc; 

-- 必须在错误或警告处理程序中使用RESIGNAL语句
RESIGNAL [SQLSTATE | condition_name]
SET item_name_1 = value_1,
    item_name_2 = value_2, etc;
    
-- 示例
IF(C != 1) THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'Order No not found in orders table';
END IF;

DELIMITER $$
CREATE PROCEDURE Divide(IN numerator INT, IN denominator INT, OUT result double)
BEGIN
	DECLARE division_by_zero CONDITION FOR SQLSTATE '22012';
	DECLARE CONTINUE HANDLER FOR division_by_zero
	RESIGNAL SET MESSAGE_TEXT = 'Division by zero / Denominator cannot be zero';
	IF denominator = 0 THEN
		SIGNAL division_by_zero;
	ELSE
		SET result := numerator / denominator;
	END IF;
END $$
DELIMITER ;

CALL Divide(10,0,@result); 
-- ERROR 1644 (22012): Division by zero / Denominator cannot be zero

9、游标

-- 定义
DECLARE cursor_name CURSOR FOR select_statement; 
-- 打开游标
OPEN cursor_name;
-- 移动光标
FETCH cursor_name INTO variables_list;
-- 关闭游标
CLOSE cursor_name;

10、存储过程示例

CREATE TABLE people_source ( 
  person_id  INTEGER NOT NULL PRIMARY KEY, 
  first_name VARCHAR2(20) NOT NULL, 
  last_name  VARCHAR2(20) NOT NULL, 
  title      VARCHAR2(10) NOT NULL 
);

CREATE TABLE people_target ( 
  person_id  INTEGER NOT NULL PRIMARY KEY, 
  first_name VARCHAR2(20) NOT NULL, 
  last_name  VARCHAR2(20) NOT NULL, 
  title      VARCHAR2(10) NOT NULL 
);

INSERT INTO people_target VALUES (1, 'John', 'Smith', 'Mr');
INSERT INTO people_target VALUES (2, 'alice', 'jones', 'Mrs');
INSERT INTO people_source VALUES (2, 'Alice', 'Jones', 'Mrs.');
INSERT INTO people_source VALUES (3, 'Jane', 'Doe', 'Miss');
INSERT INTO people_source VALUES (4, 'Dave', 'Brown', 'Mr');

-- delimiter 定义边界符，若不定义边界符则存储过程中不能出现分号;
delimiter $$ 
-- 删除已有存储过程
drop procedure if exists people_procedure $$
-- 创建存储过程
create procedure people_procedure()
begin
	-- 原表是否已有此条数据
	declare num int;
	-- 定义接受游标值的变量
	declare id int(11);
	declare fname varchar(20);
	declare lname varchar(20);
	declare tit   varchar(10);
	-- 定义循环终止标志
	declare loop_finished int default 0;
	-- 定义游标
	declare people_cursor cursor for (select person_id, first_name, last_name, title from people_source);
	-- 循环达到最后位置时，设置loop_finished=1
	declare continue handler for not found set loop_finished = 1;
	-- 开启游标
	open people_cursor;
	-- 循环遍历游标
	people_loop: loop
		-- 获取游标中的值
		fetch people_cursor into id, fname, lname, tit;
		-- 若loop_finished = 1时，跳出循环
		if loop_finished = 1 then
			leave people_loop;
		end if;
		-- 查询目标表是否已有此条数据
		select count(*) into num from people_target where person_id = id;
		
		if num > 0 then 
			-- 已存在则更新
			update people_target set first_name = fname, last_name = lname, title = tit where person_id = id;
		else 
			-- 不存在则新增
			insert into people_target(person_id, first_name, last_name, title) values(id, fname, lname, tit);
		end if; 
		
	-- 结束循环
	end loop people_loop;
	-- 关闭游标
	close people_cursor;
end $$
-- 取消边界符
delimiter ;


-- 查看现有的存储过程信息
show procedure status;
-- 查看存储过程 people_procedure的详细信息
show create procedure people_procedure;
-- 调用存储过程
call people_procedure();