存储过程、变量与流程控制

本文将介绍MySQL数据库中的一些编程要素，包括变量、存储过程、条件语句、case语句、循环语句（repeat、while、loop）等。

变量

变量（variable）是在代码运行时保存数据的标识符，在MySQL数据库中，系统变量名使用两个@符号开始，如保存版本号的@@version变量，可以通过如下代码查看。

MySQL

select @@version;

自定义变量名使用一个@符号开始，可以使用set关键字声明并赋值，如下面的代码。

MySQL

set @x=10, @y=99;
select @x+@y;

代码中首先定义了@x和@y两个变量，并分别赋值为10和99，最后通过select语句显示@x加@y的结果；执行代码会显示109；

查询系统中有哪些变量时可以使用“show variables;”语句，按关键字查询时可以使用like条件，如下面的代码。

MySQL

show variables like '%version%';

本例会查询名称中包括“version”的所有变量。

存储过程

存储过程（stored procedure）是数据库管理系统中重要的编程要素，与函数相似，存储过程可以带入数据，并返回处理结果。

创建存储过程的基本语法如下：

MySQL

create procedure [if not exists] <名称> (<参数列表>)
begin
    <语句>
end;

其中：

if not exists为可选，不使用时，如果存储过程已存在则会产生错误。
<名称>为存储过程名称，一般使用sp_前缀，有时也约定自定义存储过程使用usp_作为前缀。
<参数列表>用于指定存储过程输入或输出数据的参数，可以没有参数，也可以有一个或多个参数，多个参数使用逗号分隔。每个参数定义有三部分组成，第一部分为参数输入或输出类型，包括输入参数（in）、输出参数（out）和输入输出参数（inout）；第二部分为参数名称，不需要使用@符号；第三部分为参数的数据类型。
<语句>为存储过程的处理代码，定义在begin和end关键字之间。

下面的代码会创建sp_add存储过程，其功能是将参数x和y的值相加，并赋值到result参数。

MySQL

use cdb_demo;

delimiter //
create procedure sp_add(in x int,in y int, out result int)
begin
	set result = x + y;
end
//
delimiter ;

代码中，由于存储过程可能会有多条语句，所以需要使用delimiter语句修改定界符。sp_add存储过程中定义了三个int类型的参数，x和y定义为输入参数，使用in关键字，result定义为输出参数，使用out关键字。存储过程中，使用set语句将x加y的结果赋值到result参数中。

调用存储过程使用call语句，格式如下：

MySQL

call <名称>(<参数>)

其中，<名称>为调用的存储过程名称，<参数>可以通过数据或变量指定。

下面的代码会调用sp_add存储过程计算两个整数相加的结果。

MySQL

use cdb_demo;

call sp_add(10, 99, @result);
select @result;

执行代码会显示109。

下面的代码创建了sp_pow2存储过程，其中演示了输入输出参数和into关键字赋值。

MySQL

use cdb_demo;

delimiter //
create procedure sp_pow2(inout x bigint)
begin
	select pow(x,2) into x;
end
//
delimiter ;

代码中，sp_pow2存储过程会计算参数x的平方，并将结果直接保存到x中。下面的代码演示了sp_pow2存储过程的使用。

MySQL

use cdb_demo;

set @n = 10;
call sp_pow2(@n);
select @n;

执行代码会显示100。

如果不再需要某个存储过程，可以使用drop procedure语句删除，格式如下：

MySQL

drop procedure <存储过程名称>;

条件语句

对于简单的条件判断，可以使用if()和ifnull()函数，先来看if()函数，其定义如下：

MySQL

if(expr1,expr2,expr3)

其中，如果expr1为“真”（不等于0且不为null）则返回expr2的值，否则返回expr3的值。下面的代码演示了if()函数的应用。

MySQL

set @x=10, @y=99;
select if(@x>=@y, @x, @y);

代码会显示@x和@y变量中值较大的那一个，可以修改变量的值来观察运行结果。

ifnull()函数的定义如下。

MySQL

ifnull(expr1,expr2)

这里，如果expr1不为空（null）则返回expr1的值，否则返回expr2的值。下面的代码演示了ifnull()函数的应用。

MySQL

set @x=10;
select ifnull(@x, 0);

本例会显示@x变量的值10，如果将@x的值修改为null，则会显示0。

MySQL的操作代码中，还可以使用一系列的流程控制语句，如条件语句的基本格式如下。

MySQL

if <条件1> then
<语句1>
elseif <条件2> then
<语句2>
......
else
<语句n>
end if

在此语句结构中，如果<条件1>成立则执行<语句1>；否则如果<条件2>成立则执行<语句2>，如果所有条件都不成立则执行<语句n>。需要注意，elseif部分可以有一个也可以有多个，或者没有；else部分也可以没有。下面的代码演示了如何使用if语句判断一个年份是否为闰年。

MySQL

use cdb_demo;

delimiter //
create procedure sp_isleapyear(in y int,out r int)
begin
if (y%400=0) or (y%100<>0 and y%4=0) then
	set r=1;
else
	set r=0;
end if;
end
//
delimiter ;

代码会创建sp_isleapyear存储过程，其中，参数y指定年份数据，参数r为输出结果，1表示y值是闰年，0表示y值不是闰年。下面的代码会调用sp_isleapyear存储过程判断年份是否为闰年。

MySQL

use cdb_demo;

call sp_isleapyear(2020, @result);
select @result;

这里可以修改sp_isleapyear存储过程第一个参数的值来观察执行结果。

case语句

case语句可以根据同一表达式的不同值分别返回不同的结果，如下面的代码创建了sp_quarter_name存储过程，可以根据季度值返回季度名称。

MySQL

use cdb_demo;

delimiter //
create procedure sp_quarter_name(in q_val int, out q_name char(3))
begin
set q_name = 
case q_val 
	when 1 then '一季度'
	when 2 then '二季度'
	when 3 then '三季度'
	when 4 then '四季度'
	else '无效值'
end;
end
//
delimiter ;

case语句使用case关键字开始，使用end关键字结束。case关键字后是可能有多个值的表达式，如代码中的q_val变量；之后，每一个when...then对应一个值及其返回结果，如1返回'一季度'、2返回'二季度'、……，else关键字则定义了没有列举值时返回的结果，这里会返回'无效值'。

下面的代码演示了sp_quarter_name存储过程的调用。

MySQL

use cdb_demo;

set @q = 1;
call sp_quarter_name(@q, @q_name);
select @q_name;

执行代码会显示'一季度'，可以修改@q变量的值来观察运行结果。

case语句也可以用在查询语句中，如下面的代码。

MySQL

use cdb_demo;

select recid,f1,f2,f3,f6, 
case quarter(f6)
when 1 then '一季度'
when 2 then '二季度'
when 3 then '三季度'
when 4 then '四季度'
end as q_name
from t1;

代码中，case关键字后的表达式为quarter(f6)，即使用quarter()函数获取了f6字段日期信息中的季度值，然后根据此值返回季度名称，并将返回结果的列命名为q_name。下图中显示了部分查询结果。

查询结果

循环语句——repeat语句

先来看repeat语句，其应用格式如下：

MySQL

repeat
    <语句>
until <条件>
end repeat

在此结构中，首先执行一次<语句>，然后会判断<条件>是否成立，如果成立则终止repeat语句结构，否则再次执行<语句>。下面的代码演示了repeat语句的应用。

MySQL

use cdb_demo;

delimiter //
create procedure sp_accum(in n int, out result bigint)
begin
	declare i int default 1;
	set result = 0;
	repeat
		set result = result + i;
		set i = i + 1;
	until i>n
	end repeat;
end;
//
delimiter ;

代码中定义了sp_accum存储过程，其功能是计算1到n的累加，并将结果通过result参数输出；请注意，其中的变量i即是累加数据也是循环控制变量，每次循环时都会加1，这样就修改了条件的判断数据，从而保证循环能够结束。

下面的代码会调用sp_accum存储过程计算1到100的累加值，结果会返回5050。

MySQL

use cdb_demo;

call sp_accum(100,@result);
select @result;

循环语句——while语句

如果需要在每次循环前对条件进行判断，可以使用while语句，其基本应用格式如下：

MySQL

while <条件> do
    <语句>
end while

在此语句结构中，每次循环都会对<条件>进行判断，成立时会执行<语句>，否则退出while语句结构。下面的代码创建了sp_accum1存储过程，会使用while语句结构实现累加。

MySQL

use cdb_demo;

delimiter //
create procedure sp_accum1(in n int, out result bigint)
begin
	declare i int default 1;
	set result = 0;
	while i<=n do
		set result = result + i;
		set i = i + 1;
	end while;
end;
//
delimiter ;

请注意，在while循环结构中同样需要改变条件的语句，这里也是通过变量i来实现。

下面的代码会调用sp_accum1存储过程计算1到100的累加，结果同样会显示5050。

MySQL

use cdb_demo;

call sp_accum1(100,@result);
select @result;

循环语句——loop语句

另一种循环语句是loop语句，其基本应用格式如下：

MySQL

<标签>loop
    <语句>
end loop;

可以看到，loop语句结构中没有循环条件的判断，所以，在<语句>中应该有条件判断语句；语句中，如果继续执行下一次循环使用iterate语句，退出循环则使用leave语句。此外，在定义loop语句结构时，一般会定义一个标签，在结构中可以通过标签名指定继续执行或退出哪个循环结构。

下面的代码会创建sp_accum2存储过程，其中会使用loop语句计算累加。

MySQL

use cdb_demo;

delimiter //
create procedure sp_accum2(in n int,out result bigint)
begin
	declare i int default 1;
	set result = 0;
	lbl1:loop
		if i>n then
			leave lbl1;
		else
			set result = result + i;
			set i = i + 1;
			iterate lbl1;
		end if;
	end loop;
end
//
delimiter ;

代码中定义了标签lbl1，在loop语句结构中，如果循环控制变量i的值大于n则使用“leave lbl1”语句退出循环，否则执行累加操作，同时变量i加1，然后通过“iterate lbl1”语句执行下一次循环。下面的代码会调用sp_accum2存储过程计算1到100的累加，执行结果同样显示5050。

MySQL

use cdb_demo;

call sp_accum2(100, @result);
select @result;

实际上，repeat和while语句都可以使用标签，语句结构中也同样可以使用“leave <标签名>”语句退出循环，使用“iterate <标签名>”语句执行下一次循环。