烦恼一般都是想太多了。

0%

MySQL存储过程中的游标

好久没有写 MySQL 相关的东西了,今天遇到一个棘手的问题。需要计算,两个时间之间所经历过的工作日及工作时。非常的头疼,想到的实现方式,要么直接刷 SQL,比较麻烦;要么把表都导出来,然后 Python 获取处理后进行更新。想到其实这个任务也不是非常重,最终决定用存储过程来解决。

CREATE PROCEDURE | FUNCTION

从语法上来看,我发现,MySQL 中,过程与函数的区别就是:过程没有定义值,而函数定义了返回值。

但实际上过程可以通过参数来进行值的返回的,同时,也只有过程能把参数指定为 IN,OUT

而函数必须包含 RETURN 语句。

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

CREATE FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body

proc_parameter:
[ IN | OUT | INOUT ] param_name type

func_parameter:
param_name type

type:
Any valid MySQL data type

characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

routine_body:
Valid SQL procedure statement or statements

例子

DELIMITER //
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE a CHAR(16);
DECLARE b, c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur1;
OPEN cur2;

read_loop: LOOP
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF done THEN
LEAVE read_loop;
END IF;
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END LOOP;

CLOSE cur1;
CLOSE cur2;
END//

DELIMITER ;

相关语法

BEGIN … END

一般来说,我们把复合语句都用 BEGIN ... END 包裹起来。

DECLARE

SQL变量名不能和列名一样。如果SELECT … INTO这样的SQL语句包含一个对列的参考,并包含一个与列相同名字的 局部变量,MySQL当前把参考解释为一个变量的名字。例如,在下面的语句中,xname 被解释为到xname variable 的参考而不是到xname column的:

DECLARE var_name[,...] type [DEFAULT value]

-- 声明条件,它将一个名字和指定的错误条件关联起来。这个名字可以随后被用在DECLARE HANDLER语句中
DECLARE condition_name CONDITION FOR condition_value

-- 这个语句指定每个可以处理一个或多个条件的处理程序。如果产生一个或多个条件,指定的语句被执行。
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

condition_value:
SQLSTATE [VALUE] sqlstate_value
| mysql_error_code
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

handler_type:
CONTINUE
| EXIT
| UNDO

condition_value:
SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
| mysql_error_code

DECLARE 只能在 BEGIN ... END 内声明变量,条件处理程序等。

游标

声明

光标必须在声明处理程序之前被声明,并且变量和条件必须在声明光标或处理程序之前被声明。

DECLARE cursor_name CURSOR FOR select_statement

这个语句声明一个光标。也可以在子程序中定义多个光标,但是一个块中的每一个光标必须有唯一的名字。

SELECT语句不能有INTO子句。

打开

OPEN cursor_name

这个语句打开先前声明的光标。

获取

这个语句用指定的打开光标读取下一行(如果有下一行的话),并且前进光标指针。变量要先进行声明。

FETCH cursor_name INTO var_name [, var_name] ...

关闭

CLOSE cursor_name

这个语句关闭先前打开的光标。

如果未被明确地关闭,光标在它被声明的复合语句的末尾被关闭。

流程控制

IF

IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF

CASE

CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
Or:

CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE

这里介绍的用在 存储程序里的CASE语句与12.2节,“控制流程函数”里描述的SQL CASE表达式的CASE语句有轻微不同。这里的CASE语句不能有ELSE NULL子句,并且用END CASE替代END来终止。

LOOP

[begin_label:] LOOP
statement_list
END LOOP [end_label]

LEAVE

LEAVE label

ITERATE

ITERATE label

ITERATE只可以出现在LOOP, REPEAT, 和WHILE语句内。ITERATE意思为:“再次循环。”

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
label1: LOOP
SET p1 = p1 + 1;
IF p1 < 10 THEN ITERATE label1; END IF;
LEAVE label1;
END LOOP label1;
SET @x = p1;
END

 REPEAT

[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]

REPEAT语句内的语句或语句群被重复,直至search_condition 为真。

REPEAT 语句可以被标注。 除非begin_label也存在,end_label才能被用,如果两者都存在,它们必须是一样的。

WHILE

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

WHILE语句内的语句或语句群被重复,直至search_condition 为真。

WHILE语句可以被标注。 除非begin_label也存在,end_label才能被用,如果两者都存在,它们必须是一样的。

用户变量

形如 @a 这样的变量。用户变量与连接有关。也就是说,一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时,该客户端连接的所有变量将自动释放。

SET @var_name = expr [, @var_name = expr] ...

或者用 SELECT

SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;

在SELECT语句中,表达式发送到客户端后才进行计算。这说明在HAVING、GROUP BY或者ORDER BY子句中,不能使用包含SELECT列表中所设的变量的表达式。例如,下面的语句不能按期望工作:

SELECT (@aa:=id) AS a,(@aa+3) AS b from tbl_name HAVING b=5

当你用SELECT @@var_name搜索一个变量时(也就是说,不指定global.、session.或者local.),MySQL返回SESSION值(如果存在),否则返回GLOBAL值。