数据库

mysql常用操作语法（十四）~~复杂的存储过程

前言

我看到在很多教程中，都是把存储过程和自定义函数一起讲，主要是因为他们两个非常的相像，而且自定义函数从某种程度上讲，更像是存储过程中的特例。
在这种情况下，我就暂时省略掉自定义函数的笔记，直接继续了解更复杂的存储过程。
所谓的更复杂，实际上也就是定义变量，变量赋值，游标以及结构控制语句和循环等，有java语言基础的情况下，就很好理解了，只是其中有些细节需要稍微注意一下。

定义变量和赋值

之前的例子中，在存储过程里只是简单地做一些基本sql操作，而实际应用中自然远不止这么简单，经常需要声明变量，并进行一定的操作。
声明变量的语法是：

DECLARE 变量名 变量类型  约束条件

例如：

DECLARE count INTEGER DEFAULT 100;

给变量赋值的常用语法是：

SET 变量名 = 值;

例如：

SET count = 10;

上边赋值的语法比较常用，但是还有其他的语法也可以赋值，例如：

SELECT COUNT(*) INTO COUNT FROM USER;

上边这种根据sql语句的结果进行赋值需要注意的是，当前sql返回值必须是单一的而不能是多个。

结构控制

结构控制我目前了解的有两种，使用if和case，结合上边变量的知识以及之前存储过程一起举例进行说明：

DELIMITER $
CREATE PROCEDURE proce_test0()
BEGIN
DECLARE COUNT INTEGER DEFAULT 0;
DECLARE result VARCHAR(10) DEFAULT '';
SELECT COUNT(*) INTO COUNT FROM USER;
IF COUNT < 2 THEN
SET result='不合格';
ELSEIF COUNT = 2 THEN
SET result ='及格';
ELSE
SET result = '优秀';
END IF;
SELECT result;
END
$
DELIMITER ;

对上边sql的解释如下：
使用$分隔符确定sql语句的起始位置；
创建一个名称是proce_test0的无参存储过程；
在存储过程里声明一个COUNT变量，类型是INTEGER，默认值0；
再声明一个result变量，类型是varchar，长度是10，默认值是空字符串；
查询user表的数据条数，并把结果赋值给变量count；
根据count变量的值，进行if判断：
如果count小于2，给result赋值为“不合格”；
如果coutn等于2，给result赋值为“及格”；
否则，给result赋值为“优秀”；
结束if判断；
输出变量result的值；
结束存储过程；
分隔符定义结尾；

那么，上边的逻辑完全可以用case代替if实现，改过之后的sql应该如下：

DROP PROCEDURE IF EXISTS proce_test0;
DELIMITER $
CREATE PROCEDURE proce_test0()
BEGIN
DECLARE COUNT INTEGER DEFAULT 0;
DECLARE result VARCHAR(10) DEFAULT '';
SELECT COUNT(*) INTO COUNT FROM USER;
CASE COUNT 
WHEN COUNT < 2 THEN
SET result='不合格';
WHEN 2 THEN
SET result ='及格';
ELSE
SET result = '优秀';
END CASE;
SELECT result;
END
$
DELIMITER ;

游标

在上边的例子中，我们声明了变量，并且把sql语句的结果赋值给了变量，但是这里的问题就是只能把单结果的sql赋值给变量。如果要把一个结果集赋值给某个变量进行操作，就需要借助游标实现，例如：

DELIMITER $
CREATE PROCEDURE proce_test0()
BEGIN
DECLARE user_id INTEGER;
DECLARE cursor_userId CURSOR FOR SELECT id FROM USER;
OPEN cursor_userId;
FETCH cursor_userId INTO user_id;
SELECT user_id;
CLOSE cursor_userId;
END
$
DELIMITER ;

对上述示例的红色标记部分解释如下：
声明一个user_id变量，类型是INTEGER；
创建一个游标，名称是cursor_userId，游标里的内容是user表中的id结果集；
打开游标；
从游标当前位置取出一条数据，赋值给变量user_id，并把游标向前移动一位；
输出变量user_id的值；
关闭游标。

循环

上述示例中，只是从游标中取出了一条数据，如果要取出所有数据，比较好的方法就是使用循环遍历，有while、loop以及repeat,例如：

DELIMITER $
CREATE PROCEDURE proce_test0()
BEGIN
DECLARE user_id INTEGER;
DECLARE flag INTEGER DEFAULT 0;
DECLARE idStr NVARCHAR(20) DEFAULT '';
DECLARE cursor_userId CURSOR FOR SELECT id FROM USER;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
OPEN cursor_userId;
WHILE flag != 1 DO
FETCH cursor_userId INTO user_id;
IF user_id = 5 THEN
IF idStr = '' THEN
SET idStr = user_id;
ELSE
SET idStr = CONCAT(idStr,'|',user_id);
END IF;
END IF;
END WHILE;
CLOSE cursor_userId;
SELECT idStr;
END
$
DELIMITER ;

上述存储过程中，我想做的事是这样的：
把user表中所有id是5的数据用“|”连接成一个字符串。
对上述红色部分的解释是：
声明一个名称为user_id的变量，类型是INTEGER；
声明一个名称为flag的变量，类型是INTEGER，默认值是0。这个变量的作用是作为while循环的条件；
声明一个名称是idStr的变量，类型是varchar，长度20，默认值空字符串；
声明一个游标，上边解释过；
设置游标读取到末尾的时候，改变变量flag的值；
打开游标；
使用while循环，添加条件flag ！= 1；
从游标读取数据，并移动游标位置，上边解释过；
外层if判断user_id的值，等于5的时候进入内层if；
内层if判断idStr是否是空字符串，如果是，就把user_id的值赋值给idStr，也就是第一个id前边不要“|”，否则进行字符串拼接；
结束内层if；
结束外层if；
结束while循环；
关闭游标；
输出变量idStr的值。

上边的存储过程把while循环替换成loop循环之后如下：

DELIMITER $
CREATE PROCEDURE proce_test0()
BEGIN
DECLARE user_id INTEGER;
DECLARE flag INTEGER DEFAULT 0;
DECLARE idStr NVARCHAR(20) DEFAULT '';
DECLARE cursor_userId CURSOR FOR SELECT id FROM USER;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
OPEN cursor_userId;
loop_test:LOOP
IF flag =1 THEN
LEAVE loop_test;
END IF;
FETCH cursor_userId INTO user_id;
IF user_id = 5 THEN
IF idStr = '' THEN
SET idStr = user_id;
ELSE
SET idStr = CONCAT(idStr,'|',user_id);
END IF;
END IF;
#FETCH cursor_userId INTO user_id;
END LOOP;
CLOSE cursor_userId;
SELECT idStr;
END
$
DELIMITER ;

重点在红色部分，其中loop_test是自定义的名字，可以理解成java中for循环前的标签。然后需要if判断一个临界条件来终止循环，终止的语法就是leave 循环名，相当于java的for循环里的break。最后的end loop结束循环。

同样的，上边的写法也可以用repeat循环来代替：

DELIMITER $
CREATE PROCEDURE proce_test0()
BEGIN
DECLARE idStr VARCHAR(20) DEFAULT '';
DECLARE user_id INTEGER;
DECLARE flag INTEGER DEFAULT 0;
DECLARE cursor_id CURSOR FOR SELECT id FROM USER;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
OPEN cursor_id;
REPEAT
FETCH cursor_id INTO user_id;
IF user_id =5 THEN
IF idStr = '' THEN
SET idStr = user_id;
ELSE
SET idStr = CONCAT(idStr,"|",user_id);
END IF;
END IF;
UNTIL flag =1 END REPEAT;
CLOSE cursor_id;
SELECT idStr;
END
$
DELIMITER ;

repeat循环的终止，需要借助于until关键字，这种语法结构的写法其实很像是java中的do while循环，先给一个关键词，然后是循环体，最后写终止循环的条件。

通过上边的示例，就可以基本了解变量声明和赋值，结构控制语句，游标，循环等语法的用法，从而组合起来生成相对复杂的存储过程了。

推荐文章