MySQL编程（自定义变量、存储过程、条件控制、游标）

1.用户自定义变量

1.1 用户会话变量

MySQL用户会话变量以一个”@“开头，并且大小写不敏感。一般情况下，用户会话变量的定义和赋值会同时进行。使用set命令和select语句，可以对用户会话变量进行定义和赋值。

（1）使用set命令定义用户会话变量

语法：set　@user_variable1=expression1　[,@user_variable2=expression2,...]

说明：user_variable1、user_variable2为用户会话变量名；expression1、expression2可以是常量、变量和表达式；set命令可以同时定义多个变量，中间用逗号隔开即可。

 

 

 （2）使用select语句定义用户会话变量

语法：第一种：select　@user_variable1:=expression [,@user_variable2:=expression2,...]；第二种：select　expression1　into　@user_variable1,　expression2　into　@user_variable2,...；

说明：第一种语法格式与第二种语法格式的区别在于，第一种语法格式中的select语句会产生结果集，第二种语法格式中的select语句仅用于会话变量的定义及赋值。

1.2 用户会话变量赋值

检索数据时，如果select语句的结果集是单个值，可以将select语句的返回结果赋予用户会话变量。

1.3 重置命令结束标记

begin-end语句块中通常存在多条MySQL表达式，每条MySQL表达式都使用”;“作为结束标记。在MySQL客户机上输入MySQL命令或SQL语句时，默认情况下MySQL客户机也是使用”;“作为MySQL命令的结束标记。由于begin-end语句块中的多条MySQL表达式密不可分，为了避免这些MySQL表达式被拆开，需要重置MySQL客户机的命令结束标记，亦称为命令分隔符（delimiter）。

 

 

 

2.存储过程

2.1 局部变量

局部变量（local variable）必须定义在存储程序中，如函数、存储过程、触发器以及事件中，而且局部变量的作用范围仅局限于存储程序中。如果脱离存储程序，局部变量将没有丝毫意义，定义局部变量的语法如下：declare 局部变量 数据类型;

局部变量主要应用于以下3种场合：

（1）场合1：局部变量定义在存储程序的begin-end语句块之间时，局部变量必须先进行declare命令定义，并且必须指定其数据类型。只有定义局部变量后，才可以使用set命令或select语句为其赋值。

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

（3）场合3：局部变量也可以用于存储程序的SQL语句中。数据检索时，如果select语句的结果集是单个值，则可以将select语句的返回结果赋予局部变量。局部变量也可以嵌入到select语句、insert语句、update语句以及delete语句的表达式中。

2.2 存储过程介绍

2.2.1 存储过程的定义和特点

SQL语句的执行过程遵循“先编译再执行”原则，而存储过程（Stored Procedure）完美地体现了这一原则。存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

一个存储过程其实就是一个可编程的函数（函数有返回值，存储过程没有返回值），它在数据库中创建并保存，并有SQL语句和一些特殊的控制结构所组成。当希望在不同的应用程序或平台上执行相同的功能，或者封装特定的功能时，使用存储过程是非常实用的解决之道。

2.2.2 存储过程的优点

存储过程的优点主要包括以下5点：

（1）存储过程增强了SQL语言的功能性和灵活性。它可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

（2）存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，而对应用程序源代码毫无影响。

（3）存储过程能实现较快的执行速度。如果某一个操作包含大量的SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多，因为存储过程是预编译的。

（4）存储过程能减少网络流量。针对同一个数据库对象的操作，如果这一操作所涉及的SQL语句被组织成存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该存储过程的调用语句，而不是大量的SQL语句，从而大大减少了网络流量，降低了网络负载。

（5）存储过程还可以被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限限制逻辑，能够实现对相应数据的访问权限的控制，从而避免了非授权用户对数据的访问，保证了数据的安全。

2.3 创建和执行存储过程的语法

创建存储过程时，数据库开发人员需提供存储过程名、存储过程的参数以及存储过程语句块等信息，格式如下：

create proceduire 存储过程名字(

　　[in　|　out　|　inout]　参数1　数据类型1,

　　[in　|　out　|　inout]　参数2　数据类型2,,

　　...

)

[no sql　|　reads sql data　|　modifies sql data]

begin

　　存储过程语句块;

end;

语法说明如下：

（1）存储过程的参数是局部变量。

（2）in代表输入参数（默认为in参数），表示该参数的值必须由调用程序指定。

（3）out代表输出参数，表示经过存储过程的计算后，将out参数的计算结果返回给调用程序。

（4）inout代表既是输入参数又是输出参数，表示该参数的值即可以由调用程序指定，又可以将该参数的计算结果返回给调用程序。

执行存储过程的语法格式如下：call 存储过程名(参数列表)

 

3.条件控制语句

MySQL提供了简单的流程控制语句，其中包括条件控制语句以及循环语句。这些流程控制语句通常放在begin-end语句块中使用。条件控制语句分为两种：一种是if语句，另一种是case语句。

3.1 if语句

if语句根据条件表达式的值确定执行不同的语句块，语法格式如下：

if 　条件表达式1　then　语句块1;

[elseif 　条件表达式2　then　语句块2] ...

[else　语句块n]

end　if;

3.2 case语句

case语句用于实现比if语句分支更为复杂的条件判断，语法格式如下：

case

　　when　表达式1　then　语句块1

　　when　表达式2　then　语句块2

　　...

　　else　语句块n

end;

3.3 while语句

当条件表达式的值为true时，反复执行循环体，直到条件表达式的值为false，while语句的语法格式如下：

[循环标签:]　while条件表达式　do

　　循环体;

end　while[循环标签];

3.4 leave语句

leave语句用于跳出当前的循环语句，相当于高级编程语言的break语句。leave语句的语法格式如下：leave　循环标签;

3.5 iterate语句

iterate语句用于跳出本次循环，进而进行下次循环，它的作用等同于高级编程语言重点continue语句，iterate语句的语法格式如下：iterate　循环标签;

 

4.游标

4.1 游标介绍

数据库开发人员在编写存储过程的给你存储程序时，有时需要使用存储程序中的SQL代码扫描select结果集中的数据，并要求对该结果集中的每条记录进行一些简单的处理。游标本质上是一种能从select结果集重每次提取一条记录的机制。

4.2 MySQL中使用游标的步骤

游标的使用可以概括为声明游标、打开游标、从游标中提取数据和关闭游标4个步骤。

（1）声明游标

语法：declare　游标名　cursor　for　select语句;

（2）打开游标

语法：open　游标名;

（3）从游标中提取数据

语法：fetch　游标名　into　变量名1,变量名2,...;

（4）关闭游标

语法：close　游标名;

4.3 游标的使用