mysql存储过程

本文主要介绍mysql存储过程的一般用法

附件为以下示例用到的表的建表脚本

数据库的存储过程其实是一系列sql语句的集合，其实是一种领域特定语言（DSL）。
mysql存储过程有变量声明，赋值，判断与循环（游标遍历），任何一个学过编程语言的人对这些都不会陌生。
还可以在存储过程中调用另一个存储过程，类似于函数调用。与函数不同的是，存储过程没有返回值，但是除了输入参数外，还有一种输出参数，其实相当于可以有多个返回值。
mysql存储过程可以使用dbForge Studio for MySQL调试，尽管这个调试工具不太好用，凑合着用呗。

下面介绍的内容包括：

一、存储过程的声明

二、变量声明、赋值、注释与判断语句

三、游标及其三种遍历方式

四、在存储过程中使用事务

五、嵌套游标遍历

六、子过程与嵌套游标声明

七、Java使用jdbc调用存储过程

一、存储过程的声明
存储过程声明

DELIMITER @@
PROCEDURE select_test.procedure_demo()
BEGIN
	select * from student_table;
END
@@
DELIMITER ;

select_test是mysql的schema名字(即数据库的名字)，procedure_demo是存储过程的名字
这是一段最简单的存储过程，只执行了一个select语言，没有输入参数也没有输出参数，可以说没卵用。这里用来展示一个mysql存储过程的基本结构
DELIMITER @@ 这一行声明了mysql存储过程结束分隔符。因为mysql存储过程默认的结束分隔符是";"，这样就不能再存储过程中使用";"来作为一条sql语句的结束符了。也就是说执行完select语句存储过程就结束了，没有匹配到END，存储过程会报错。
最后将分隔符重新声明成";"
其实分隔符的问题只在mysql命令行下存在
若是使用dbForge Studio for MySQL编写存储过程，则不需要这行代码声明，声明了也没有，它会帮你删掉你的分隔符相关的代码，并会帮你处理好分隔符的问题。

BEGIN 表示存储过程开始
END 表示存储过程结束
存储过程所有的sql语句都是写在BEGIN和END之间的

同时在使用dbForge Studio for MySQL时，默认会生成一行CREATE DEFINER = 'root'@'%'这样的代码，即完整代码如下

CREATE DEFINER = 'root'@'%'
PROCEDURE select_test.procedure_demo()
BEGIN
	select * from student_table;
END

这里涉及到mysql的权限问题，DEFINER表示指定调用该存储过程的用户，即'用户名'@'localhost'之类的意思。这个存储过程若是A用户调用，假设A用户没有student_table的select权限，但还是会以root用户权限执行。
还有一种权限是INVOKER权限，表示使用调用存储过程的用户的权限执行，若A用户没有student_table的select权限，则会报错

CREATE PROCEDURE select_test.procedure_demo()
  SQL SECURITY INVOKER
BEGIN
	select * from student_table;
END

若DEFINER和INVOKER同时使用，则以INVOKER权限为主。

关于存储过程的参数，IN表示输入参数（与一般编程语言类似），OUT表示输出参数（可以用来表示返回值），参数类型可以是mysql表字段的类型，比如int、varchar或date等等

CREATE DEFINER = 'root'@'%'
PROCEDURE select_test.procedure_demo(IN id int(11), OUT student_name varchar(255))
BEGIN
	select student_table.studnet_name into student_name from student_table where student_table.studnet_id = id;
END

这里用到了into将查到的student_table.student_name 赋值给输出参数studnet_name

# 查询数据库中的存储过程：
show procedure status;
# 查询存储过程的创建信息
show create procedure select_test.procedure_demo;
# 调用存储过程：
call select_test.procedure_demo();
# 删除一个存储过程：
drop procedure select_test.procedure_demo;

二、变量声明、赋值、注释与判断语句

变量声明，声明一个varchar(255)类型的变量student_name

DECLARE student_name varchar(255);

赋值
赋值有两种方式
1、直接对变量赋值,使用set关键字

set student_name = 'jisonami.org';

2、将select语句查询结果赋值给变量，使用into关键字

select student_table.studnet_name into student_name from student_table where student_table.studnet_id = id;

赋值多个变量

select student_table.studnet_name,student_table.teacher_name into student_name, teacher_name from student_table where student_table.studnet_id = id;

除了可以对declare声明的变量赋值外，还可以对输入输出参数进行赋值
mysql存储过程里可以使用+、-、*、/四则预算，也可以使用mysql内置的函数以及调用已有的存储过程。

注意，使用into赋值时，若将null赋值给变量会抛出not found异常

三种注释方式

# 后面是单行注释
-- 后面也是单行注释
/* 这里是多行注释 */

判断语句

if 条件表达式 then
	# 此处可插入多行sql语句
end if;

或者

if 条件表达式 then
	# 此处可插入多行sql语句
else
	# 此处可插入多行sql语句
end if;

或者

if 条件表达式 then
	# 此处可插入多行sql语句
elseif 条件表达式 then
	# 此处可插入多行sql语句
else
	# 此处可插入多行sql语句
end if;

这里的条件表达式可以是select语句，但是需要保证返回值为int类型的单条记录单个字段, 0为假，非0为真
例如：

if (select count(studnet_name) from student_table where student_table.studnet_id = id group by student_table.studnet_id) then
	# 此处可插入多行sql语句
end if;

三、游标及其三种遍历方式
游标类似于数组的指针，也可以说类似于Java集合里的iterator迭代器，只不过游标指向的是select语句返回的视图集合

# 声明右边遍历是否结束的标志变量
declare done int default 0;

# 声明一个游标, DECLARE后面接游标名字students，for后面接视图。
declare students cursor for 
	select student_table.student_name from student_table;
# 注意，匹配表里的字段时最好带上表名或者别名，否则与declare声明的同名变量可能会有冲突。

# 游标遍历溢出时会触发not found错误，这是把标志变量改为1，并且游标 必须在此语句之前声明，否则会报错
declare continue handler for not found set done = 1;

# 打开游标students
open students；

# 关闭游标students
close students；

# 往下一条记录移动游标，将匹配到的数据赋值到student，若游标溢出，则触发not found错误
fetch next from students into student;

下面介绍三种游标的遍历方式
1、while遍历

while 表达式 do
	# 此处可插入多行sql语句
end while;

例如：

CREATE DEFINER = 'root'@'%'
PROCEDURE select_test.procedure_demo()
BEGIN
	declare student varchar(255);
	declare done int default 0;
	declare students cursor for 
		select student_table.student_name from student_table;
	declare continue handler for not found set done = 1;
	open students;
	while not done do
	fetch next from students into student;
		if not done then
			set student = concat('2016', student);
		end if;
	
	end while;
	close students;
END

2、repeat遍历(相当于Java里的do...while)

repeat
	# 此处可插入多行sql语句
until 表达式 end repeat;

例如：

CREATE DEFINER = 'root'@'%'
PROCEDURE select_test.procedure_demo()
BEGIN
	declare student varchar(255);
	declare done int default 0;
	declare students cursor for 
		select student_table.student_name from student_table;
	declare continue handler for not found set done = 1;
	open students;
	while not done do
	fetch next from students into student;
		if not done then
			set student = concat('2016', student);
		end if;
	
	end while;
	close students;
END

3、loop遍历

loopLabel：LOOP
	if 表达式 then
		leave loopLabel；
	end if;
	if 表达式 then
		iterate loopLabel;
	end if;
END LOOP loopLabel;

这里的leave相当于类C编程语言的break，而iterate则相当于continue。
例如：

CREATE DEFINER = 'root'@'%'
PROCEDURE select_test.procedure_demo()
BEGIN
	declare student varchar(255);
	declare done int default 0;
	declare students cursor for 
		select student_table.student_name from student_table;
	declare continue handler for not found set done = 1;
	open students;
	student_loop: loop
	fetch next from students into student;
		if done then
			leave student_loop;
		end if;
		if not done then
			iterate student_loop;
		end if;
	end loop student_loop;
	close students;
END

四、在存储过程中使用事务
 

# 事务失败回滚
  declare exit handler for sqlexception rollback;
  # 开启事务
  START TRANSACTION;
  
  # 此处可插入多行需要事务的sql语句
  
  # 提交事务
  COMMIT;

五、嵌套游标遍历
这里以loop-repeat嵌套遍历为例

loopLabel：LOOP
	if 表达式 then
		leave loopLabel；
	end if;
	# 嵌套repeat遍历
	repeat
	# 此处可插入多行sql语句
	until 表达式 end repeat;
END LOOP loopLabel；

例如：

CREATE DEFINER = 'root'@'%'
PROCEDURE select_test.procedure_demo()
BEGIN
  declare student varchar(255);
  DECLARE teacher varchar(255);
	declare done int default 0;
	declare students cursor for select student_table.student_name from student_table;
  DECLARE teachers CURSOR FOR SELECT teacher_table.teacher_name FROM teacher_table;
	declare continue handler for not found set done = 1;
	open students;
	student_loop: loop
	  fetch next from students into student;
		if done then
			leave student_loop;
		end if;
    
    OPEN teachers;
    FETCH teachers INTO teacher;
	# 注意内层循环遍历之前将标志变量设为0
    SET done = 0;
	REPEAT
      IF NOT done THEN
        SET teacher = CONCAT('2016', teacher);
      end IF;
      FETCH teachers INTO teacher;
    until done end REPEAT;
    CLOSE teachers;
	# 注意内层循环遍历之后也将标志变量设为0
    SET done = 0;
	end loop student_loop;
	close students;
END

上面的例子在内层循环遍历前后都将标志变量设为0，这是因为内循环遍历之前可能有不预期的意外将标志变量改为1了，实际的开发过程中出现过这个问题，按道理遍历之前这个一行是不需要的。
而内循环遍历之后标志变量已经变为1了，需要重新设为0，才能进行外层循环的遍历
这是由于一个标志变量控制嵌套的两个循环造成的不优雅写法，如果你发现了更好的写法，请告诉我。
这里的问题是因为将null值into给变量时抛出not found异常造成的。
在下一部分子过程中，我们可以分别对不同的循环声明标志变量，可以优雅的解决因为一个标志变量控制两个循环的问题。

六、子过程与嵌套游标声明
子过程就是在begin...end之间在嵌套begin...end的写法

BEGIN
	# 此处可声明变量和执行多条sql语句
	BEGIN
		# 此处也可以重新声明变量和执行多条sql语句，并且可以访问子过程外定义的变量
		# 当定义的游标需要根据子过程外的变量来声明时，就回起到强大的作用
	END;
END

例如：在子过程声明中嵌套的游标(这个例子只是简单说明了子过程的用法，并没有使用子过程外的变量来声明游标)

CREATE DEFINER = 'root'@'%'
PROCEDURE select_test.procedure_demo()
BEGIN
  declare student varchar(255);
  DECLARE teacher varchar(255);
	declare done int default 0;
	declare students cursor for select student_table.student_name from student_table;
	declare continue handler for not found set done = 1;
	open students;
	student_loop: loop
	  fetch next from students into student;
		if done then
			leave student_loop;
		end if;
    
    BEGIN
	  # 在子过程中声明变量、游标以及遍历游标
      declare flag int default 0;
	  DECLARE teachers CURSOR FOR SELECT teacher_table.teacher_name FROM teacher_table;
      declare continue handler for not found set flag = 1;
      OPEN teachers;
      FETCH teachers INTO teacher;
  		REPEAT
        IF NOT flag THEN
          SET teacher = CONCAT('2016', teacher);
        end IF;
        FETCH teachers INTO teacher;
      until flag end REPEAT;
      CLOSE teachers;
    END;
	end loop student_loop;
	close students;
END

七、Java使用jdbc调用存储过程
Java调用存储过程时，比如procedure_demo(IN id int(11), OUT student_name varchar(255))这个存储过程
我们首先得到一个Connection对象，一般是从数据库连接池中获取
接着

CallableStatement cstat = conn.prepareCall("{call procedure_demo(?, ?)}");

得到一个CallableStatement对象
这里使用了？作为参数占位符
设置输入参数

cstat.setInt(1, 1);

设置输出参数的类型

cstat.registerOutParameter(2, Types.VARCHAR);

调用存储过程

cstst.execute();

获取存储过程输出参数返回值

cstst.getString(2);