第七章-MySQL数据库编程

第七章 - MySQL数据库编程

7.1 MySQL编程基础知识

MySQL的脚本就是通常说的MySQL程序，是通过一套对字符、关键词以及特殊符号的使用规定，利用一条或多条MySQL语句(SQL语句+扩展语句)编写而成的。MySQL的脚本文件保存时后缀名一般为.sql。

MySQL脚本具体说来是由常量、变量、函数、表达式、关键词等组成的语句，外加注释构成的。MySQL语句是组成MySQL脚本的基本单位, 每条语句能完成特定的操作。

MySQL程序包含3种基本结构即顺序结构、选择结构和循环结构。实现这三种基本结构的语句是MySQL中的控制流语句。

7.1.1 常量

1. 字符串常量

字符串常量指用单引号或双引号括起来的字符序列。在MySQL中推荐使用单引号。

字符串是指用单引号或双引号括起来的字符序列，分为ASCII字符串常量和Unicode 字符串常量。

- ASCII字符串常量是用单引号括起来的，由ASCII字符构成的符号串。举例：‘hello’，‘How are you!’

- Unicode 字符串常量与ASCII字符串常量相似，但它前面有一个N标志符（N代表 SQL-92标准中的国际语言（National Language））。N前缀必须为大写。只能用单引号括起字符串。举例：N‘hello’，N‘How are you!’

Unicode 数据中的每个字符用两个字节存储，而每个ASCII字符用一个字节存储。

在字符串中不仅可以使用普通的字符，也可使用几个转义序列，它们用来表示特殊的字符。
           SELECT  'This\nIs\nFour\nLines';
           其中，“\n”表示回车。

2. 数值常量

数值常量可以分为整数常量和浮点数常量。

3. 日期和时间常量

日期和时间常量使用特定格式的字符日期值表示，用单引号括起来。

日期时间常量：用单引号将表示日期时间的字符串括起来构成。日期型常量包括年、月、日，数据类型为DATE，表示为“1999-06-17”这样的值。

时间型常量包括小时数、分钟数、秒数及微秒数，数据类型为TIME，如“12:30:43.00013”。
日期/时间的组合，数据类型为DATETIME或TIMESTAMP，如“1999-06-17 12:30:43”。

4. 布尔值常量

布尔值只有true和false两个值，SQL命令运行结果用1代表true，用0代表false。

【例3-4】查询表emp中所有雇员的姓名ename和工资sal是否大于等于2000的判断结果。

5. NULL值

NULL值适用于各种字段类型，通常表示“不确定的值”，NULL值参与的运算，结果仍为NULL值。

7.1.2 变量

MySQL的每一个客户机成功连接服务器后，都会产生与之对应的会话。会话期间，MySQL服务实例会在MySQL服务器内存中生成与该会话对应的会话系统变量，这些会话系统变量的初始值是全局系统变量值的拷贝。除此之外，MySQL的用户还可以利用自己定义变量。

MySQL语言中的自定义变量由变量名、变量类型和变量值3要素构成。

变量名要求是标识符，不能与关键词和函数名相同。

变量类型和常量类型一样，决定变量存储空间和取值范围，变量值要求符合本类型取值范围的要求。

变量用于临时存放数据，变量有名字及其数据类型两个属性，变量名用于标识该变量，变量的数据类型确定了该变量存放值的格式及允许的运算。

用户自定义变量在MySQL系统中，也存在2种类型，即以@开头的用户会话变量和局部变量。

1. 用户会话变量

系统会话变量与用户会话变量的共同之处在于：变量名大小写不敏感。系统会话变量与用户会话变量的区别在于：

① 用户会话变量一般以一个“@”开头；系统会话变量以两个“@”开头。

② 系统会话变量无需定义可以直接使用。

(1) 用户会话变量的使用过程

一个用户会话变量创建之后，就可以作为表达式或表达式的组成因素用于其他SQL语句中。如图所示。

MySQL客户机A定义了会话变量，会话期间，该会话变量一直有效；MySQL客户机C不能访问客户机A定义的会话变量；客户机A关闭或者客户机A与服务器断开连接后，客户机A定义的所有会话变量将自动释放，以便节省MySQL服务器的内存空间。同样，客户机C中定义的会话变量也是如此。

实际上是MySQL服务器在内存中为每一个会话开辟独立的会话连接空间，不同的会话空间互不干扰，会话结束，会话空间释放。而会话变量的生存期就是所在会话空间开辟到释放的这一段时间。

(2) 用户会话变量的定义与赋值

一般情况下，用户会话变量的定义与赋值会同时进行。定义和初始化一个用户会话变量可以使用set或select语句。

使用set命令定义用户会话变量，并为其赋值，语法格式如下：

set @user_variable1＝expression1 [,@user_variable2= expression2 , …] 

使用select语句定义用户会话变量，并为其赋值，语法格式有两种。

select @user_variable1:＝expression1 [,user_variable2:= expression2 , …]

select expression1 into @user_variable1, expression2 into @user_variable2,…

说明：

① 用户会话变量的数据类型是根据赋值运算符“=”右边表达式的计算结果自动分配的。

② 利用使用select语句定义用户会话变量时，赋值号采用“:＝”形式，能够产生结果集。而利用into赋值的方式的select语句，仅仅用于会话变量的定义及赋值，但不会产生结果集

③ 赋值号“:= ”与“=”的总结比较：
    “:= ”是赋值号，能够实现赋值操作，即将右边的值赋值给左边的变量。
    “=”一般情况下是作为比较操作符使用的。特殊情况下，“=” 则只在set语句里面作为赋值号使用，包括update语句里面的set子句。

【例7.1】 使用查询结果给变量赋值。

mysql> use teaching;

mysql> set @sname=(select sname from student
    -> where studentno=‘19126113307’);

mysql> select studentno, sname, birthdate
    -> from student where sname=@sname;

【例7.2】利用select语句将表中数据赋值给变量。

mysql> select @sname:=sname from student limit 0,1;

2. 局部变量

局部变量是指在其在定义的某个局部程序范围内有效的变量。

(1) 定义局部的定义与赋值

declare命令专门用于定义局部变量及对应的数据类型。

例如，定义局部变量myvar，数据类型为int，默认值为100，代码如下。

declare myvar int default 100;

下面给局部变量myvar赋值为77，代码如下。

set myvar=77;

(2)局部变量的使用

局部变量必须定义在函数、触发器、存储过程等存储程序，局部变量的作用范围仅仅局限于存储程序中。局部变量主要用于下面3种场合：

局部变量必须先定义，才可以使用set命令或者select语句为其赋值。局部变量定义在begin-end语句块之间。此时局部变量首先必须使用declare命令定义，并且必须指定局部变量的数据类型。

局部变量作为存储过程或者函数的参数使用。此时虽然不需要使用declare命令定义，但需要指定参数的数据类型。

在SQL语句中使用局部变量。数据检索时，如果select语句的结果集是单个值，可以将select语句的返回结果赋予局部变量，局部变量也可以直接嵌入到select、insert、update以及delete语句的条件表达式中。

3. 局部变量与用户会话变量的区别

(1) 用户会话变量使用`set`命令或`select`语句定义并进行赋值，定义用户会话变量时无需指定数据类型。诸如`declare @student_no int;`的语句是错误语句，用户会话变量不能使用`declare`命令定义。

(2) 用户会话变量的作用范围与生存周期大于局部变量。用户会话变量在本次会话期间一直有效，直至关闭服务器连接。而局部变量如果作为存储过程或者函数的参数，此时在整个存储过程或函数内中有效；如果定义在存储程序的`begin-end`语句块中，此时仅在当前的`begin-end`语句块中有效。

(3) 如果局部变量嵌入到SQL语句中，由于局部变量名前没有`@`符号，这就要求局部变量名不能与表字段名同名，否则将出现无法预期的结果。

在MySQL数据库中，由于局部变量涉及begin-end语句块、函数、存储过程等知识，局部变量的具体使用方法将结合这些知识稍后一块儿进行讲解。

4. 全局变量

全局变量是MySQL系统提供并赋值的变量。用户不能定义全局变量，只能使用。

全局变量名称	说明
@@back_log	返回MySQL主要连接请求的数量
@@basedir	返回MySQL安装基准目录
@@license	返回服务器的许可类型
@@port	返回服务器侦听TCP/IP连接所用端口
@@storage_engine	返回存储引擎
@@version	返回服务器版本号

7.1.3 MySQL表达式

MySQL表达式是由运算符将常量、变量、字段名和函数等组合连接而成的有意义的字符序列。一个表达式通常可以得到一个值。具体来说，根据分类标准不同，可以对MySQL表达式进行不同的分类。

1. 按照表达式值类型分类

与常量和变量一样，表达式的值也具有某种数据类型，可能的数据类型有字符类型、数值类型、日期时间类型。

这样，根据表达式的值的类型，表达式可分为字符型表达式、数值型表达式和日期型表达式。

2. 按照值形式分类

在MySQL语言中，当表达式的结果只是一个值，如一个数值、一个字符串或一个日期，这种表达式叫做标量表达式。

例如：1+2，'a'>'b'。当表达式的结果是由不同类型数据组成的一行值，这种表达式叫做行表达式。例如，（'18110123456'，'王达田'，'计算机'，500）。当表达式的结果为0个、1个或多个行表达式的集合，那么这个表达式就叫做表表达式。

3. 按照表达式形式分类

表达式还可分为单一表达式和复合表达式。单一表达式就是一个单一的值，如一个常量、变量、函数或列名。

复合表达式是由运算符将多个单一表达式连接而成的表达式，例如：
1+7+3, a=v+3, '2018-01-20'+ interval 6 month

7.1.4 定界符delimiter和begin-end语句块

MySQL语言提供了自定义函数、存储过程等存储程序功能。在这些存储程序中，往往需要多条SQL命令或MySQL语句组合到一起执行。MySQL语言可以利用 begin-end 语句块和重新设置定界符 delimiter来实现。

更改命令结束标记 delimiter。默认MySQL的命令行结束符就是 ;，而函数和存储过程这样的语句中包含了很多的 ;，当创建函数或存储过程的时候就会报错。

默认情况下，不可能等到用户把这些语句全部输入完之后，再执行整段语句。因为MySQL一遇到分号，它就要自动执行。即，在语句 return; 时，MySQL数据库解释器就要执行了。这种情况下，就需要事先把 delimiter 换成其它符号，如//或$$。

1. 更改命令结束标记delimiter

为了避免 begin-end 语句块中的多条MySQL表达式被拆开，需要重置MySQL客户机中的命令结束标记（delimiter）。

利用 delimiter 定界符命令，可以重新定义一个语句执行的结束符。有delimiter定义的新定界符（即重置命令结束标记,如//或$$,），就是告诉MySQL解释器，该段命令的结束和执行有了新的标识。

【例7.3】改变MySQL命令的结束标记示例。

mysql> delimiter  //
mysql> select studentno,sname,phone  
    -> from student where sname like ‘赵%’//

mysql> delimiter  $$
mysql> select studentno,sname,birthdate  
    -> from student where sname like ‘梅%’$$

mysql>  delimiter  ;

其中 delimiter 定义好结束符为 $$ 和 // 。 最后又定义为 ; ，是因为这是MySQL的默认结束符为 ; ，实际编程时要养成习惯。

2. begin-end 语句块

通常利用 begin-end 可以用于定义一组语句块，在其他各大数据库中的客户端工具中可直接调用，但在MySQL语言中不可直接用。在MySQL语言中，局部变量、begin-end语句块和流程控制语句等只能用于函数、存储过程、游标和触发器的定义内部。

通过begin-end语句块的简单形式如下：

begin
    [局部]变量声明;     
    程序代码行集;
end ;                   // end之后以“;”结束

说明：

① BEGIN…END语句块包含了该程序块的所有处理操作，允许语句块嵌套。

② 在MySQL中单独使用BEGIN…END语句块没有任何意义，只有将其封装在存储过程、存储函数、触发器等存储程序内部才有意义。

7.1.5 预处理SQL语句

前面介绍的MySQL语句的在运行期间，SQL语句不能发生动态地变化，这种SQL语句称为静态SQL语句。对于静态SQL语句而言，每次将其发送到MySQL服务实例时，MySQL服务实例都会对其进行解析、执行，然后将执行结果返回给MySQL客户机。

MySQL数据库还可以使用预处理的方式执行SQL语句。该类语句在运行期间，如果SQL语句或SQL所带的参数可以发生动态变化，这种SQL语句称为动态SQL语句或者预处理SQL语句。对于预处理SQL语句而言，预处理SQL语句创建后，第一次运行预处理SQL语句时，MySQL服务实例会对其解析，解析成功后，将其保存到MySQL服务器缓存中，为今后每一次地执行作好准备。

这样就可以将某些SQL语句封装为预处理SQL语句，实现其“一次解析，多次执行”的性能优势。

1. 预处理SQL语句的格式

MySQL数据库中的prepare、execute、deallocate统称为预处理语句（prepare statement）。一般格式如下：

prepare stmt_name from preparable_stmt;
execute stmt_name [using @var_name [, @var_name] ...]; 
{deallocate|drop} prepare stmt_name; 

说明：

① prepare语句用于预备一个语句，并赋予它名称stmt_name，借此在以后引用该语句。preparable_stmt可以是一个文字字符串或一个包含了语句文本的用户会话变量。该文本必须展现一个单一的SQL语句，而不是多个语句。使用本语句，“？”字符可以被用于制作参数，执行查询时，数据值在哪里与查询结合在一起。参数制作符只能被用于数据值应该出现的地方，不用于SQL关键词和标识符等。

如果带有此名称的预处理语句已经存在，则在新的语言被预备以前，它会被隐含地解除分配。这意味着，如果新语句包含一个错误并且不能被预备，则会返回一个错误，并且不存在带有给定名称语句。

预处理语句的范围是客户端会话。在此会话内，语句被创建。其它客户端看不到它。

② execute语句用于执行预处理语句。如果预处理语句包含任何参数制造符，则必须提供一个列举了用户会话变量（其中包含要与参数结合的值）的using子句。参数值只能有用户会话变量提供，using子句必须准确地指明用户会话变量。用户会话变量的数目与SQL语句中的参数制造符的数量一样多。

若需要多次执行一个给定的预处理语句，在每次执行前，把不同的变量传递给它，或把变量设置为不同的值。

③ deallocate prepare语句用于释放预处理语句。如果终止一个客户端会话，同时没有对以前已预制的语句解除分配，则服务器会自动解除分配。

2. 预处理SQL语句使用步骤

MySQL支持预处理SQL语句，预处理SQL语句的使用主要包含3个步骤。创建预处理SQL语句、执行预处理SQL语句以及释放预处理SQL语句。

（1）创建预处理SQL语句。
（2）执行预处理SQL语句。
（3）释放预处理SQL语句。

3. 预处理SQL语句的应用

例7.4】在给定了两个直角边的长度时，计算直角三角形的斜边长度。

分析：可以通过使用文字字符串来创建一个预处理语句，以提供语句的文本，也可以是将提供语句的文本赋值给一个用户会话变量。

mysql> prepare hypo_c from ‘select sqrt(pow(?,2) + pow(?,2)) AS hypotenuse’;
Statement prepared

mysql> set @a = 6;
mysql> set @b = 8;
mysql> execute hypo_c using @a, @b;
mysql> deallocate  prepare  hypo_c;

【例7.5】利用预处理SQL语句输出student中的前2行记录的部分数据。

分析：利用预处理语句，可以使用limit子句指定记录行数。

mysql> set @a=2;

mysql> prepare  STMT  
    -> from “select studentno,sname,entrance from  student  limit ?”;
Statement prepared

mysql> execute STMT using @a;

如果执行下列代码，则可以输出前3行记录，由此可以看出预处理SQL语句的作用。

mysql> set @a=3;
mysql> execute STMT using @a;

说明：

① 使用预处理语句时，最好在编代码前，应该先要测试预处理语句在应用程序中的运行情况。

② 预处理语句的SQL语法不能用于嵌套。也就是说，被传递给prepare的语句本身不能是一个prepare, execute或deallocate prepare语句。

③ 预处理语句的SQL语法与使用预处理语句API调用不同。例如，不能使用API函数来预备一个prepare, execute或deallocate prepare语句。

④ 能够支持预处理操作的SQL语句如下create table, delete, do, insert, replace, select, set, update和多数的show语句，而不支持其他语句。

⑤ 预处理语句的SQL语法可以在已存储的过程中使用，但是不能在已存储的函数或触发程序中使用。

7.1.5 注释

注释是程序代码中不被执行的文本字符串，用于对代码进行说明或进行诊断的部分语句。

（1）##（井号字符）：从该字符到行尾都是注释内容。

（2）--（双连线字符）：从双连线字符到行尾都是注释内容。注意，双连线后一定要加一个空格。

（3）正斜杠星号字符（/*…*/）：开始注释对（/*）和结束注释对（*/）之间的所有内容均视为注释。

例如，下面的程序代码中包含注释符号。

USE  teaching;     -- 打开数据库

## 查看学生的所有信息
select * from student; 

/*  查看所有女生的学号、姓名、和电话
附加条件是女生  */ 
select studentno,sname,phone from student 
WHERE  sex='女'; 

7.2 自定义函数

在MySQL中，可以利用 create function 语句创建自定义函数。创建函数必须具有 create routine 权限，并且 alter routine 和execute权限被自动授予它的创建者。需要注意的是，MySQL的自定义函数定义时，需要指定当前数据库。

7.2.1 创建和调用自定义函数

1. 创建自定义函数的语法格式

在MySQL中，创建存储函数的基本语法如下。

create function func_name([[in | out | inout]func_parameter type[,…]])
returns  return_type
[characteristic…]
begin
	function_body_statements;
	return[return_values];
end; 

函数定义说明：

（1）create function：创建自定义函数的关键字。

（2）func_name：创建自定义函数的函数名。

（3）[in | out | inout] func_parameter  type函数参数及类型列表。参数形式。in表示输入参数， out表示输出参数，inout表示输入输出参数，func_parameter表示参数名， type参数类型。

（4）returns  return_type：函数返回值类型。

（5）characteristics：用于指定函数的特征参数，characteristics（函数选项）由以下一种或几种选项组合而成。
language sql  
|[not] deterministic
|{ contains sql|no sql|reads sql  data|modifies sql  data }
|sql security {definer|invoker }
|comment 'string' 

（6）begin…end: 函数体起止符。内含由function_body_statements描述的函数要实现的任务，一般由begin…end来对描述任务代码的起止。函数体内要有“return return_values；”语句，表示函数返回值表达式。

2. 创建自定义函数举例

【例7.6】 创建一个函数，计算长方形面积。

mysql> delimiter //
mysql> create function rectangle_area(long1 int,wide1 int) 
returns int
    -> begin
    -> return long1 * wide1;
    -> end //
mysql> delimiter ;

【例7.7】创建一个名为func_course的函数返回表course中的指定课程号的课程名。

mysql> delimiter &&
mysql> create  function func_course(c_no varchar(6))
    -> returns  char(6)
    -> begin
    ->    return  (select  cname  from  course
    ->               where  courseno =c_no);
    -> end &&
mysql> delimiter ;

3. 调用自定义函数

在MySQL系统中，因为函数和数据库相关，如果要调用函数时，需要打开相应的数据库或指定数据库名称。存储函数的调用与MySQL内部函数的调用方式相同。

【例7.8】分别调用函数rectangle_area()和func_course()。

mysql> select rectangle_area(5,4);
mysql> select func_course(‘c08123’);

7.2.2 函数的维护管理

函数的维护包括查看函数的定义、修改函数的定义以及删除函数的定义等内容。

1. 查看函数的定义

(1) 查看当前数据库中所有的自定义函数信息

例如：

show function status;             // 函数较少时用 
show function status like 模式;   // 如果自定义函数较多多时用 

(2) 查看指定数据库（如teaching）中的所有自定义函数名

例如，可使用SQL语句。

mysql> select name from MySQL.proc
    -> where db = 'teaching' and type='function' ; 

(3) 可以查看指定函数名的详细信息

使用MySQL命令：

mysql>show create function 函数名; 

(4) 函数的信息都保存在information_schema数据库中的routines表中，可以使用select语句检索routines表，查询函数的相关信息

例如，查看函数func_course信息。

mysql> select * from information_schema.routines
    -> where routine_name='func_course'; 

2. 函数定义的修改

由于函数保存的仅仅是函数体，而函数体实际上是一组MySQL表达式，因此函数自身不保存任何用户数据。当函数的函数体需要更改时，可以使用drop function语句暂时将函数的定义删除，然后使用create function语句重新创建相同名字的函数即可。

MySQL中修改函数的语句的语法形式如下：

alter  function  sp_name [characteristic …]; 

【例7.9】修改存储函数func_course的定义。将读写权限改为reads sql data，并加上注释信息“find function name”。

mysql> alter  function  func_course
     -> reads sql data
     -> comment  ‘find function name’;

mysql> select  SPECIFIC_NAME,SQL_DATA_ACCESS,
-> routine_comment  from  information_schema.Routines
-> where  routine_name='func_course';

3. 函数定义的删除

使用MySQL命令“drop function func_name”删除自定义函数。例如,删除get_name ()函数命令如下：

mysql>drop function get_name;

7.3 控制流语句

利用MySQL语言编程，可以通过控制流语句实现程序的顺序、选择和循环等3种基本结构，乃至编写出能够解决较为复杂问题的存储过程、函数和触发器等。下面介绍相关的控制流语句的使用方法。

7.3.1 条件控制语句

1. if 语句

if 语句用来进行条件判断，根据不同的条件执行不同的操作。该语句在执行时首先判断if后的条件是否为真，则执行then后的语句，如果为假则继续判断if语句直到为真为止，当以上都不满足时则执行else语句后的内容。

if语句表示形式如下：

if condition then 
        …
[else condition then] 
  …
[else] 
…
Endif 

【例7.10】创建函数exam_if，通过if…then…else结构首先判断传入参数的值是否为10，如果是则输出1，如果不是则再判断该传入参数的值是否为20，如果是则输出2，当以上条件都不满足时输出3。然后调用函数exam_if。

mysql> delimiter //
mysql> create function exam_if(x int)
     -> returns int
     -> begin
     -> if x=10 then set x=1;
     -> elseif x=20 then set x=2;
     -> else  set x=3;
     -> end if;
     -> return x;
     -> end //
mysql> delimiter ;
mysql> select exam_if(77);

2. case语句

case语句为多分支语句结构，该语句首先从when后的value中查找与case后的value相等的值，如果查找到则执行该分支的内容，否则执行else后的内容。

case语句表示形式如下：

case value 
      when value then …
      [when valuethen…] 
      [else…] 
end case 

case语句还有另一种语法表示结构：

case 
    when value then …
    [when valuethen…] 
    [else…] 
end case 

说明：

一个CASE语句经常可以充当一个IF-THEN-ELSE语句。

第一种格式中case_value是要被判断的值或表达式，接下来是一系列的WHEN-THEN块，每一块的when_value参数指定要与case_value比较的值，如果为真，就执行statement_list中的SQL语句。如果前面的每一个块都不匹配就会执行ELSE块指定的语句。CASE语句最后以END CASE结束。

第二种格式中CASE关键字后面没有参数，在WHEN-THEN块中，search_condition指定了一个比较表达式，表达式为真时执行THEN后面的语句。与第一种格式相比，这种格式能够实现更为复杂的条件判断，使用起来更方便。

【例7.11】创建函数exam_case，通过case语句首先判断传入参数的值是否为10，如果条件成立则输出1，如果条件不成立则再判断该传入参数的值是否为20，如果成立则输出2，当以上条件都不满足时输出3。

mysql> delimiter //
mysql> create function exam_case( x int)
    -> returns  int
    -> begin
    -> case  x
    -> when 10  then set  x=1;
    -> when 20  then set  x=2;
    -> else  set  x=3;
    -> end case;
    -> return  x;
    -> end //
mysql> delimiter ;
mysql> select exam_case(17);

3. 条件判断函数

MySQL中常用的条件控制函数有if()、ifnull()以及case函数，这些函数的功能是根据条件表达式的值返回不同的值，而且函数可以在MySQL客户机中直接调用。条件判断函数用来在SQL语句中进行条件判断。根据是否满足判断条件，SQL语句执行不同的分支。

(1) if() 函数

if(condition,v1,v2)函数中condition为条件表达式，当condition的值为true时，函数返回v1的值，否则返回v2的值。

【例7.12】从表student中查询学号studentno，入学成绩entrance。成绩大于等于800分，显示“pass!”。否则，显示“bye.”,输出前5条记录。

mysql> select  studentno, entrance,if(entrance >= 800,'pass','bye! ')
    -> from  student  limit  5;

(2) ifnull()函数

ifnull(v1,v2)函数中，如果v1的值为null，则该函数返回v2的值；如果v1的值不为null，则该函数返回v1的值。

(3) case 函数

case函数有2种格式，验算过程相近。如果表达式的值等于when语句中某个“值n”，则case函数返回值为“结果n”；如果与所有的“值n”都不相等，case函数返回值为“其他值”。

case 表达式1                  //格式1
when 值1 then 结果1
[when 值2 then 结果2 ]… 
[else 其他值 ] 
end
case                         //格式2
when表达式1 then值1  
[when 表达式2 then值2…] 
[else  其他值]  
end

7.3.2 循环语句

1. while循环语句

while 循环语句执行时首先判断condition条件是否为真，如果是则执行循环体，否则退出循环。该语句表示形式：

while condition do 
…
end while; 

说明：

语句首先判断search_condition是否为真，不为真则执行statement_list中的语句，然后再次进行判断，为真则继续循环，不为真则结束循环。begin_label和end_label是WHILE语句的标注。除非begin_label存在，否则end_label不能被给出，并且如果两者都出现，它们的名字必须是相同的。

【例7.13】定义函数exam_while，应用while语句求1到100项的和。

分析：首先定义变量m和sum，分别用来控制循环的次数和保存前100项和，当变量m的值小于或等于100时，使sum的值加m，并同时使m的值增1。直到m大于100时退出循环并输出结果。

mysql> delimiter //
mysql> create  function  exam_while(n int) returns int
    -> begin
    -> declare sum int default 0; 
    -> declare m int default 1;
    -> while  m< = n  do
    -> set  sum=sum+m;
    -> set  m=m+1;
    -> end while;
    -> return  sum;
    -> end //
mysql> delimiter ;
mysql> select exam_while(100);

2. loop循环语句

loop循环语句是没有内置的循环条件，但可以通过leave语句退出循环。

loop语句表示形式：

loop 
…
end loop 

loop允许某特定语句或语句群的重复执行，实现一个简单的循环构造，其中间省略的部分是需要重复执行的语句。

在循环内的语句一直重复直至循环被退出，退出循环应用leave语句。leave语句经常和begin…end或循环一起使用，其结构如下：

leave label 

label是语句中标注的名字，这个名字是自定义的。加上LEAVE关键字就可以用来退出被标注的循环语句。

【例7.14】定义函数exam_loop，应用loop语句求1~100之和。通过leave语句退出循环并输出结果。

mysql> delimiter//
mysql> create  function  exam_loop(n int) returns int
     -> begin
     -> declare  sum int default 0;
     -> set n=1;
     -> loop_label:loop
     -> set sum=sum+n;
     -> set n=n+1;
     -> if  n>100  then
     -> leave  loop_label;
     -> end if;
     -> end loop;
     -> return  sum;
     -> end //
mysql> delimiter;
mysql> select exam_loop(100);

循环语句中还有一个iterate语句，它可以出现在loop、repeat和while语句内，其意为结束本次循环。

该语句格式如下：

iterate  label 

该语句的格式与leave大同小异，区别在于：leave语句是结束循环，而iterate语句是结束本次循环。

【例7.15】定义函数exam_iterate，应用while语句和iterate语句求1~100间的偶数之和。通过leave语句退出循环并输出结果。

mysql> delimiter //
mysql> create  function  exam_iterate(n int) returns int
->  begin
->  declare sum char(20) default 0;
-> declare s int default 0;
-> add_num: while  true  do
-> set s=s+1;
-> if (s%2=0) then
-> set sum=sum+s;
->  else
-> iterate  add_num;
-> end if;
-> if (s=n) then
-> leave  add_num;
->  end if;
->  end while add_num;
-> return  sum;
-> end;
->  //
mysql> delimiter ;
mysql> select exam_iterate(100);

3. repeat循环语句

repeat 循环语句是先执行一次循环体，之后判断condition条件是否为真，则退出循环，否则继续执行循环。

repeat语句表示形式：

repeat 
  …
until condition 
end repeat;

说明：

REPEAT语句首先执行statement_list中的语句，然后判断search_condition是否为真，为真则停止循环，不为真则继续循环。REPEAT也可以被标注。

【例7.16】定义函数exam_repeat，应用repeat语句求前1~50的和。

mysql> delimiter //
mysql> create  function  exam_repeat (n int) returns int
    -> begin
    -> declare sum int default 0;
    -> set  n=1;
    -> repeat
    -> set sum=sum+n;
    -> set n=n+1;
    -> until n>50
    -> end repeat;
    -> return sum;
    -> end //
mysql> delimiter ;
mysql> select exam_repeat(50);

在编写循环类的程序时，应特别注意函数参数和函数体内的变量值得变化情况，稍有不慎，就会出现问题，诸如数据错误、死循环等等。

7.4 小结

利用MySQL语言编程，需要先掌握常量、变量、函数、表达式、关键词等语言因素的使用方法，并在此基础上利用创建自定义函数应用这些语言因素去表达或描述算法，实现编写程序进行一系列的操作。

需要重点掌握如下知识点：

• 变量的分类、定义和使用方法。学会使用会话变量的使用方法。存储函数、预处理语句的定义和使用方法。

• 控制流语句的语句格式和使用方法。特别是选择结构和循环结构的各类语句，是MySQL语句的主要组成部分，是MySQL实现较为复杂算法的基础。

• 选择结构分为if语句和case语句，适合判断类的算法处理。循环语句包括while、repeat、loop等方式，适合处理重复执行的语句块。