1. 写在前面
本篇内容及部分示例代码基于《MySQL必知必会》,所需要获得和运行的一切东西,可以在 http://www.forta.com/books/0672327120/ 上找到,包括两个SQL脚本,create.sql 和 populate.sql。
本篇内容的理解同时也参考了MySQL8.0的官方文档《MySQL 8.0 Reference Manual》,若有理解不到位的地方,有能力的小伙伴可以参考 https://dev.mysql.com/doc/refman/8.0/en/ 。
2. 存储过程
在刚开始接触MySQL的时候,一直搞不清楚存储过程和函数的区别。尤其,在数据库管理工具Navicat的操作界面中,两者又同样是在函数这个大类下。经过参考官方文档后,发现两者确实很相似,并且是在同一章节进行存储过程和函数的介绍。
2.1 创建存储过程 / 函数
1 CREATE
2 [DEFINER = user]
3 PROCEDURE sp_name ([proc_parameter[,...]])
4 [characteristic ...] routine_body
5
6 CREATE
7 [DEFINER = user]
8 FUNCTION sp_name ([func_parameter[,...]])
9 RETURNS type
10 [characteristic ...] routine_body
11
12 proc_parameter:
13 [ IN | OUT | INOUT ] param_name type
14
15 func_parameter:
16 param_name type
17
18 type:
19 Any valid MySQL data type
20
21 characteristic: {
22 COMMENT 'string'
23 | LANGUAGE SQL
24 | [NOT] DETERMINISTIC
25 | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
26 | SQL SECURITY { DEFINER | INVOKER }
27 }
28
29 routine_body:
30 Valid SQL routine statement
这一段创建代码中有以下几点值得注意的:
1. 创建存储过程名/函数名的参数是不一样的:
存储过程名的参数 " proc_parameter: [IN | OUT | INOUT] param_name type ",包括了类型、名字、数据类型。
函数名的参数 " func_parameter: param_name type ",仅包括名字、数据类型。
2. 函数必须有返回值(关键字 RETURN),而存储过程没有返回值。
第2点其实是和第1点相关联的。正是因为存储过程没有返回值,所以需要在存储过程名中指定参数的类型,是IN、OUT、INOUT中的一种;
而对于函数,虽然没有显式地提及,但其参数类型只能是IN。
3. 代码块中有 routine_body,指的就是存储过程或函数中的一条或多条MySQL语句的集合。stored routines指的就是存储过程或函数,从字面上理解,使用存储过程或函数的意义就在于将一条或多条MySQL语句存储起来,以便在日后方便的重复使用。
4. 代码块中在 routine_body 前面, 是 [characteristic...] ,这里面包含了一些指定存储过程或函数的特性,包括:COMMENT、LANGUANGE SQL、NOT DETERMINISTIC、CONTAINS SQL、SQL SECURITY。它们都是在存储过程或函数名之后,routine_body 之前进行定义。
其中比较常用的两个:
一个是COMMENT,用来注释存储过程,其注释的内容可以通过 SHOW CREATE PROCEDURE 或 SHOW PROCEDURE STATUS 来进行显示查看。
另一个是SQL SECURITY,用来指定存储过程或函数的权限是创建者(DEFINER)还是调用者(INVOKER)。当其为DEFINER时,存储过程或函数执行时具体操作权限时定义它们的user的权限;而当其为INVOKER时,其权限为当前user的权限。如果存储过程或函数中的表,当前user没有访问权限,即便其有执行存储过程或函数的权限,也无法成功调用。
2.2 存储过程的调用
下面以《MySQL必知必会》中的一个例子来展示存储过程的调用。首先,进行存储过程的调用,注意这是在Navicat中创建的存过过程,并不是在命令行客户机中创建的。
使用Navicat创建存储过程有以下需要注意的点:
1. 第一步:首先要给定存储过程名的参数,包括了类型、名字、数据类型;这些信息在进行BEGIN-END块编写时,会出现在底部以供参考。同时可以看到,底部的返回类型是灰色的,因为存储过程没有返回值,函数才有。
2. 第二步:在 BEGIN - END 块中进行具体MySQL语句的编写。
*)其中用到了 DECLARE 关键字进行局部变量的申明。需要注意的是,BEGIN - END 块、控制流语句、局部变量只能用于函数、存储过程、游标、触发器的定义内部。
3. 第三步:点击保存或另存为,并给出存储过程名进行保存。我们将此存储过程命名为 ordertotal 。也就是说在Navicat中没有必要,也不能将 CREATE PROCEDURE sp_name() 放入定义中,整个定义的内容只有 BEGIN -END 块。
接下里,我们来调用刚刚创建的存储过程:
在调用存储过程的过程中也有几点需要注意:
1. 使用 CALL 关键字调用存储过程,CALL ordertotal(20006, 1, @total) 。我们这个存储过程稍微有点复杂,它有三个参数。但即便一个存储过程没有参数,调用存储过程的时候也应该写成 CALL sp_name() ,存储过程名后的()符号不能遗漏。
2. CALL ordertotal(20006, 1, @total) 中的第3个参数,我们在其前面用了 @ 符号,这是一种自变量的申明方式。MySQL申明变量的方式主要有3种:
a):自变量申明,使用@,set @varname = value
b):局部变量申明,使用DECLARE,用于存储过程、函数、游标、触发器的内部
c):系统变量,set session varname = value
注:原书中提到 "所有MySQL变量都必须以@开始。"个人理解,这只是针对自变量而言的。申明后的局部变量以及系统变量并不需要加@。
3. 由于存储过程没有输出,为了显示存储过程中类型为 OUT 或 INOUT 的参数,可直接使用 SELECT。
3. 游标(CUROSR)
MySQL支持在 stored program(个人理解就是存储过程和函数)中使用游标。MySQL检索返回与SQL语句相匹配的行(零行或多行),没有办法在检索出来的行中逐行前进,进行批处理;而游标就是用来解决这个不变的。游标有以下特点:
1)只读:不能被更新
2)不可卷动:即只能沿一个方向,且不能跳行
游标的使用涉及到4个关键字:DECLARE、OPEN、FETCH、CLOSE,下面我们逐一来看看。
3.1 DECLARE
和局部变量一样,游标在使用前也需要申明。游标也常常和句柄(HANDLER)结合在一起使用,而句柄也是需要提前申明的。它们在DECLARE语句中的次数是:局部变量 先于 游标 先于 句柄。
1
select_statement就是检索出的数据,在后续将会使用FETCH关键字进行逐行读取。主要注意的是 select_statement 中不能含有 INTO 子句。
3.2 OPEN
利用 OPEN 关键字打开一个预先声明过的游标。
1
3.3 FETCH
1
获取 select_statement 中的下一行,并前进游标指针。如果行存在,则获取的列将被储存到所命名的变量中。注意,fetch_statement 中列的数量必须和 select_statement 中列的数量一致。
如果没有更多的行了,则将会出现一个状态, SQLSTATE value '02000' ;为了检测到这一个状态,就需要用到之前提到的句柄:
1 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
这行代码的作用是,在出现 SQLSTATE value '02000' 时, SET done=1。经常用于跳出某个循环。
3.4 CLOSE
1 CLOSE
在游标处理完成之后,应当关闭游标,释放内存和资源。即使你不明确地关闭游标,在达到 END 语句时,MySQL会自动关闭它。
3.5 实例
1 BEGIN
2 #Routine body goes here...
3 DECLARE done BOOLEAN DEFAULT 0;
4 DECLARE o INT;
5 DECLARE t DECIMAL(8,2);
6
7 DECLARE ordernumbers CURSOR
8 FOR
9 SELECT order_num FROM orders;
10
11 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
12
13 CREATE TABLE IF NOT EXISTS ordertotals
14 (order_num INT,
15 total DECIMAL(8,2)
16 );
17
18 OPEN ordernumbers;
19
20 REPEAT
21
22 FETCH ordernumbers INTO o;
23 CALL ordertotal(o, 1, t);
24
25 INSERT INTO ordertotals(order_num, total)
26 VALUES(O, t);
27
28 UNTIL done END REPEAT;
29
30 CLOSE ordernumbers;
31
32 END
“在这个例子中,我们增加了另一个名为t的变量(存储每个订单的合计)。 此存储过程还在运行中创建了一个新表(如果它不存在的话), 名为ordertotals。 这个表将保存存储过程生成的结果。 FETCH像以前一样取每个order_num,然后用CALL执行另一个存储过程(第2节中的 ordertotal)来计算每个订单的带税的合计(结果存储到t)。最后,用INSERT保存每个订单的订单号和合计。 此存储过程不返回数据,但它能够创建和填充另一个表,可以用一条简单的SELECT语句查看该表。” -- 《MySQL必知必会第24章》
有一点需要注意,这里在调用之前定义的村存储过程 ordertotal 时,所有变量都没有使用@,这是因为 o 和 t 都是已经申明过的局部变量。