MySQL存储过程语法

1、语法结构

-- 官方参考网址
https://dev.mysql.com/doc/refman/5.6/en/sql-statements.html
https://dev.mysql.com/doc/refman/5.6/en/sql-compound-statements.html

CREATE
    [DEFINER = user]
	PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
    
-- proc_parameter参数部分，可以如下书写：
	[ IN | OUT | INOUT ] param_name type
	-- type类型可以是MySQL支持的所有类型
	
-- routine_body(程序体)部分，可以书写合法的SQL语句 BEGIN ... END

例如：

-- 声明结束符。因为MySQL默认使用‘;’作为结束符，而在存储过程中，会使用‘;’作为一段语句的结束，导致‘;’使用冲突
DELIMITER $$

CREATE
   -- test为库明
    PROCEDURE test.`hello_procedure`()
    
    BEGIN
	SELECT 'hello_procedure';
    END$$

DELIMITER ;

CALL hello_procedure();

2、变量及赋值

2.1、局部变量

用户自定义，在begin/end块中有效

语法：
声明变量 declare var_name type [default var_value];
举例：declare nickname varchar(32);

-- set赋值
DELIMITER $$
CREATE PROCEDURE test.sp_var01()
BEGIN
	DECLARE nickname VARCHAR(32) DEFAULT 'unkown';
	SET nickname = 'ZS';
	SELECT nickname;
END$$
DELIMITER ;

-- into赋值
DELIMITER $$
CREATE PROCEDURE test.sp_var_into()
BEGIN
	DECLARE emp_name VARCHAR(32) DEFAULT 'unkown' ;
	DECLARE emp_no INT DEFAULT 0;
	SELECT emp_no INTO emp_name ;
	SELECT emp_no,emp_name;
END$$
DELIMITER ;
CALL sp_var_into();

2.2、用户变量

用户自定义，当前会话（连接）有效。

语法： 
@var_name
不需要提前声明，使用即声明

DELIMITER $$
CREATE PROCEDURE test.sp_var02()
BEGIN
	SET @nickname = 'zk';
END$$
DELIMITER ;
CALL sp_var02() ;
SELECT @nickname; 

2.3、全局变量：

由系统提供，整个mysql服务器有效

语法：
@@global.var_name

举例

-- 查看全局变量中变量名有char的记录
show global variables like '%char%'; 

-- 查看全局变量character_set_client的值
select @@global.character_set_client; 

3、出参和入参

-- 语法 参数如果是varchar 要写大小
in | out | inout param_name type

举例

DROP PROCEDURE IF EXISTS `sp_param01`;

DELIMITER $$
CREATE PROCEDURE sp_param01(IN age INT)
BEGIN
	SET @user_age = age;
END$$
DELIMITER ;
CALL sp_param01(10);
SELECT @user_age;

-- OUT类型，只负责输出！
-- 如果查询的是表，且表中列明和参数名相同，那么就换参数或者给列起别名
DELIMITER $$

CREATE PROCEDURE sp_param02(OUT dept_no INT(11))
BEGIN
	SET dept_no = 10;
END$$
DELIMITER ;

-- 测试
SET @dept_no = 100;
CALL sp_param02(@dept_no);
SELECT @dept_no;

-- INOUT类型 
DELIMITER $$
CREATE PROCEDURE sp_param03(INOUT user_name VARCHAR(64))
BEGIN
	SET user_name = CONCAT('hello' ,user_name);
END$$
DELIMITER ;

SET @user_name = '小明';
CALL sp_param03(@user_name);
SELECT @user_name;

4、流程控制

官网说明
https://dev.mysql.com/doc/refman/5.6/en/flow-control-statements.html

4.1、判断

4.1.1、IF

-- 语法
IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

例子

DELIMITER $$
CREATE PROCEDURE sp_hire_if()
BEGIN
	DECLARE result1 VARCHAR(32);
	DECLARE result2 VARCHAR(32);
	IF EXISTS(SELECT 1)  -- 是否存在
		THEN SET result1 = 'EXISTS';
	END IF;
	
	IF result2 <> '' -- 判断相等
	    THEN SET result2 = 'cond1';
	ELSE 
	     SET result2 = 'cond2';
	
	END IF;
	SELECT result1;
	SELECT result2;
END$$
DELIMITER ;

CALL sp_hire_if();

4.1.2、CASE

此语法是不仅可以用在存储过程，查询语句也可以用！

-- 语法一（类比java的switch）：
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE
-- 语法二：
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

例子

-- 需求：入职年限年龄<=38是新手 >38并 <=40老员工 >40元老
DELIMITER $$
CREATE PROCEDURE sp_hire_case()
BEGIN
	DECLARE result VARCHAR(32);
	DECLARE message VARCHAR(64);
	CASE
	WHEN TIMESTAMPDIFF(YEAR,'2001-01-01',NOW()) > 40 
		THEN 
			SET result = '元老';
			SET message = '老爷爷';
	WHEN TIMESTAMPDIFF(YEAR,'2001-01-01',NOW()) > 38
		THEN 
			SET result = '老员工';
			SET message = '油腻中年人';
	ELSE 
		SET result = '新手';
		SET message = '萌新';
	END CASE;
	SELECT result;
END$$
DELIMITER ;
CALL sp_hire_case();

4.2、循环

4.2.1、LOOP

需要说明，loop是死循环，需要手动退出循环，我们可以使用leave来退出。

可以把leave看成我们java中的break；与之对应的，就有iterate（继续循环）——类比java的continue

-- 语法
[begin_label:] LOOP
    statement_list
END LOOP [end_label]

例子

-- 循环打印1到10
-- leave控制循环的退出
DELIMITER $$
CREATE
    PROCEDURE sp_flow_loop01()
    BEGIN
	DECLARE c_index INT DEFAULT 1;
	DECLARE result_Str VARCHAR(256) DEFAULT '1';

	cnt:LOOP
		IF c_index >=10
			THEN LEAVE cnt; -- 退出
		 END IF;
		 SET c_index = c_index+1;
		 SET result_str = CONCAT(result_str,',',c_index);
	END LOOP cnt;
	SELECT result_str;
    END$$
DELIMITER ;

CALL sp_flow_loop();

-- iterate + leave控制循环
DELIMITER $$
CREATE PROCEDURE sp_flow_loop02()
BEGIN
	DECLARE c_index INT DEFAULT 1;
	DECLARE result_str  VARCHAR(256) DEFAULT '1';
	cnt:LOOP

		SET c_index = c_index + 1;
		SET result_str = CONCAT(result_str,',',c_index);
		IF c_index < 10 THEN 
			ITERATE cnt; 
		END IF;
		--  当c_index < 10为false时执行
		LEAVE cnt;
		
	END LOOP cnt;
	SELECT result_str;
	
END$$

CALL sp_flow_loop02();

4.2.1、REPEAT

语法

[begin_label:] REPEAT
    statement_list
UNTIL search_condition	-- 直到…为止，才退出循环
END REPEAT [end_label]

例子

-- 循环打印1到10
DELIMITER $$
CREATE PROCEDURE sp_flow_repeat()
BEGIN
	DECLARE c_index INT DEFAULT 1;
	-- 收集结果字符串
	DECLARE result_str VARCHAR(256) DEFAULT '1';
	count_lab:REPEAT
		SET c_index = c_index + 1;
		SET result_str = CONCAT(result_str,',',c_index);
		UNTIL c_index >= 10
	END REPEAT count_lab;
	SELECT result_str;
END$$

CALL sp_flow_repeat();

4.2.1、WHILE

类比java的while(){}

语法

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

-- 循环打印1到10
DELIMITER $$
CREATE PROCEDURE sp_flow_while()
BEGIN
	DECLARE c_index INT DEFAULT 1;
	-- 收集结果字符串
	DECLARE result_str VARCHAR(256) DEFAULT '1';
	WHILE c_index < 10 DO
		SET c_index = c_index + 1;
		SET result_str = CONCAT(result_str,',',c_index);
	END WHILE;
	SELECT result_str;
END$$
CALL sp_flow_while();

4.3、游标和HANDLER

用游标得到某一个结果集，逐行处理数据。

类比jdbc的ResultSet

特别注意：

在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。

-- 声明语法
DECLARE cursor_name CURSOR FOR select_statement
-- 打开语法
OPEN cursor_name
-- 取值语法
FETCH cursor_name INTO var_name [, var_name] ...
-- 关闭语法
CLOSE cursor_name

例子

CREATE TABLE `dept` (
	`deptno` INT(11) NOT NULL COMMENT '部门编号',
	`dname` VARCHAR(32) NULL COMMENT '部门名称' COLLATE 'utf8_general_ci',
	`loc` VARCHAR(64) NULL COMMENT '部门地址' COLLATE 'utf8_general_ci',
	PRIMARY KEY (`deptno`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

	
CREATE TABLE `emp` (
	`empno` INT(11) NOT NULL COMMENT '员工编号',
	`ename` VARCHAR(32) NULL COMMENT '员工姓名' COLLATE 'utf8_general_ci',
	`job` VARCHAR(10) NULL COMMENT '职位' COLLATE 'utf8_general_ci',
	`mgr` INT(11) NULL COMMENT '上级编号',
	`hiredate` DATE NOT NULL COMMENT '入职时间',
	`sal` DECIMAL(7,2) NOT NULL DEFAULT '0.00' COMMENT '薪资',
	`comm` DECIMAL(7,2) NULL COMMENT '年终奖金',
	`deptno` INT(11) NOT NULL COMMENT '部门编号',
	PRIMARY KEY (`empno`) USING BTREE,
	INDEX `FK_emp_dept` (`deptno`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

-- 按照部门名称查询员工，通过select查看员工的编号、姓名、薪资。（注意，此处仅仅演示游标用法）
DELIMITER $$
CREATE PROCEDURE sp_cursor01(IN dept_name VARCHAR(32))
BEGIN
	DECLARE e_no INT;
	DECLARE e_name VARCHAR(32);
	DECLARE e_sal DECIMAL(7,2);
	
	DECLARE lp_flag BOOLEAN DEFAULT TRUE;
	
	DECLARE emp_cursor CURSOR FOR 
		SELECT e.empno,e.ename,e.sal
		FROM emp e,dept d
		WHERE e.deptno = d.deptno AND d.dname = dept_name;
		
	-- handler 处理游标到最后时的行为
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET lp_flag = FALSE;
		
	OPEN emp_cursor;
	
	emp_loop:LOOP
		FETCH emp_cursor INTO e_no,e_name,e_sal;
		
		IF lp_flag THEN
			SELECT e_no,e_name,e_sal;
		ELSE
			LEAVE emp_loop;
		END IF;
		
	END LOOP emp_loop;
	SET @end_falg = 'exit_flag';
	CLOSE emp_cursor;
END$$

CALL sp_cursor01('RESEARCH');

5、应用

5.1、临时表

DELIMITER $$
CREATE PROCEDURE sp_create_table02(IN dept_name VARCHAR(32))
BEGIN
	DECLARE emp_no INT;
	DECLARE emp_name VARCHAR(32);
	DECLARE emp_sal DECIMAL(7,2);
	DECLARE exit_flag INT DEFAULT 0;
	
	DECLARE emp_cursor CURSOR FOR
		SELECT e.empno,e.ename,e.sal
		FROM emp e INNER JOIN dept d ON e.deptno = d.deptno WHERE d.dname = dept_name;
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_flag = 1;
	
	-- 创建临时表收集数据
	CREATE TEMPORARY TABLE `temp_table_emp` (
		`empno` INT(11) NOT NULL COMMENT '员工编号',
		`ename` VARCHAR(32) NULL COMMENT '员工姓名' COLLATE 'utf8_general_ci',
		`sal` DECIMAL(7,2) NOT NULL DEFAULT '0.00' COMMENT '薪资',
		PRIMARY KEY (`empno`) USING BTREE
	)
	COLLATE='utf8_general_ci'
	ENGINE=INNODB;	
	
	OPEN emp_cursor;
	
	c_loop:LOOP
		FETCH emp_cursor INTO emp_no,emp_name,emp_sal;
		
		
		IF exit_flag != 1 THEN
			INSERT INTO temp_table_emp VALUES(emp_no,emp_name,emp_sal); 
		ELSE
			LEAVE c_loop;
		END IF;
		
	END LOOP c_loop;
	
	SELECT * FROM temp_table_emp;
	
	SELECT @sex_res; -- 仅仅是看一下会不会执行到
	CLOSE emp_cursor;
	
END$$

CALL sp_create_table02('RESEARCH');

5.2、可以在select语句中写case

https://dev.mysql.com/doc/refman/5.7/en/flow-control-functions.html

SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;

5.3、执行sql字符串

https://dev.mysql.com/doc/refman/5.7/en/sql-prepared-statements.html

语法

PREPARE sql_stmt FROM var_sql_str;
EXECUTE sql_stmt;
DEALLOCATE PREPARE sql_stmt;

例子

DELIMITER $$

CREATE
   
    PROCEDURE sp_prepare()
  
    BEGIN
	SET @var_sql_str = 'select 1 into @result'; -- 用户变量直接使用不需要声明
	PREPARE sql_stmt FROM @var_sql_str;
	EXECUTE sql_stmt;
	DEALLOCATE PREPARE sql_stmt;

    END$$

DELIMITER ;

CALL sp_prepare();
SELECT @result;