MySQL 存储过程（上）

1、存储过程的创建

存储过程就是一条或多条 SQL 语句的集合，当对数据库进行一系列复杂操作时，存储过程可以将这些复杂操作封装成一个代码块，以便重复使用，大大减少数据库开发人员的工作量。

1.1 创建存储过程

基本语法格式如在：

create procedure sp_name （[proc_parameter]）
[ characteristics···] routine_body

create procedure 为用来创建存储过程的关键字。sp_name 为存储过程的名称。proc_parameter 为指定存储过程的参数列表，列表形式为[in | out | inout] param_name type
in 表示输入参数，out 表示输出参数，inout 表示既可以输入也可以输出， param_name 表示参数名称，type 表示参数类型。characteristics 用于指定存储过程的特性，其取值为：
（1）language SQL：说明 routine_body 部分是由 SQL 语句组成的，当前系统支持的语言是 SQL，SQL 是 language 的唯一值。
（2）[not] deterministic:指明存储过程执行的结果是否确定。默认为 not deterministic。
（3）{countains SQL | no SQL | reads SQL data | modifies SQL data}:指明子程序使用 SQL 语句的限制。countains SQL 表明子程序包含 SQL 语句，但不包含读写数据的语句； no SQL 表明子程序不包含 SQL 语句；reads SQL data 说明子程序包含读写数据的语句；modifies SQL data 表明子程序包含写数据的语句。默认情况下，系统会指定为 countains SQL 。
（4）SQL security {definer | invokey}:指明谁有权限来执行。definer 表示只有定义者才能执行，invokey 表明拥有权限的调用者可以执行。默认情况下，系统会指定为 definer 。
（5）comment ‘string’：注释信息，可以用来描述存储过程。
routine_body是 SQL 代码的内容，可以用 begin···end 来表示 SQL 代码的开始和结束。

案例演示：

在数据库中创建表：

use ch6; //ch6为你自己建的数据库名称
create table student(
		id int(3) primary key auto_increment,
		name varchar(20) not null,
		grade float,
		gender char(2)
		);

向 student 表中插入4条记录：

insert into student(name,grade,gender)
		values('tom'',70,'男'),
		('jack',80,'男'),
		('lucy',85,'女'）,
		('rose',98,'女');

创建存储过程：

delimiter // (作用是将MySQL的结束符设置为 //)
create procedure proc()
begin
select * from student;
end //
delimiter;

1.2 变量的使用

变量的作用范围是在 begin ··· end 程序中。变量的定义语句：declare var_name[,varname]···data_type[default value];
var_name 为局部变量名称，default value 子句给变量提供一个默认值。
定义变量后，为变量赋值（set语句）可以改变变量的默认值。语句：set var_name = expr[,var_name = expr]···;还可以通过select···into为一个或多个变量赋值。select col_name[···] into var_name[···] table_expr

1.3 定义条件和处理程序

定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式，开且保证存储过程在遇到警告或错误时能继续执行。

1.3.1 定义条件

在编写存储过程时，定义条件使用 DECLARE 语句，语法格式如下:

DECLARE condition_name CONDITION FOR [condition_type];
//condition_type 的两种形式:
condition type] :
QLSTATE [VALUE] sqlstate_value | mysq1_error_code

上述语法格式中.condirion_name表示所定义的条件的名称；condition_type表示条件的类型；sqlstate_value 和 mysql_error_code 都可以表示MySQL的错误，sqlstate 是长度为5的字符串类型错误代码，mysql_ error_code为数值类型的错误代码。

实例：定义“ERROR1148(42000)”错误，名称为 command_ not_ allowed。可以用两种不同的方法来定义，具体代码如下:

//方法一:使用sqlstate value
DECLARE command not allowed CONDITION FOR SQLSTATE ' 42000';
//方法二:使用mysql_ error code
DECLARE command not_ allowed CONDITION FOR 1148;

1.3.2定义处理程序

定义完条件后，还需要定义针对此条件的处理程序。MySQL中用DECLARE语句定义处理程序，具体语法格式如下:

DECLARE handler_ type HANDLER FOR condition_value[···] sp_statement
handler_type:
	CONTINUE | EXIT | UNDO
condition_value:
	| condition_name
	| SQLWARNING
 	| NOT FOUND
 	| SQLEXCEPTION
	| mysql_ error_ code

上述语法格式中，handler_type为错误处理方式，参数取三个值: CONTINUE、
EXIT和UNDO。CONTINUE 表示遇到错误不处理，继续执行；EXIT表示遇到错误马
上退出；UNDO表示遇到错误后撤回之前的操作，MySQL中暂时不支持这样的操作。
sp_satement参数为程序语句段，表示在遇到定义的错误时，需要执行的存储过程；condition_value表示错误类型，可以有以下取值。
(1) SQLSTATE[VALUE] sqlstate_value 包含5个字符的字符串错误值。
(2) condition_name 表示 DECLARE CONDITION 定义的错误条件名称。
(3) SQLWARNING 匹配所有以 01 开头的 SQLSTATE 错误代码。
(4) NOT FOUND匹 配所有以 02 开头的 SQLSTATE 错误代码。
(5) SQLEXCEPTION 匹配所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SLSTATE 错误代码。
直类型错误代码。
(6) mysql_error_code匹配数值类型错误代码。

实例：定义处理程序的几种方式，具体代码如下：

//方法一:捕获 sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';

//方法二:捕获 mysqI_ error_code
BCLARE CONTIUTE HANDER FOR 1146 set @info = 'NO_SUCH_TABLE';

//方法三:先定义条件，然后调用
DECLARE no_ such_ table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'ERROR';

/方法四:使用 SQLWARNING
ECLARE EXIT HANDLER FOR SQLNARNING SET @info= ERROR';

//方法五:使用 NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE';

//方法六:使用 SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info= 'ERROR' ;

1.4 光标的使用

在编写存储过程时，查询语句可能会返回多条记录，如果数据量非常大，则需要使用标来逐条读取查询结果集中的记录。光标是一种用于轻松处理多行数据的机制。

1.4.1 光标的声明

光标必须声明在声明变量、条件之后，声明光标的具体语法格式如下:

DECLARE cursor_ name CURSOR FOR select_statement;

在上述语法格式中,cursor_name表示光标的名称；select_statement表示SELECT
语句的内容，返回一个用于创建光标的结果集。

1.4.2 光标的使用

使用光标的语法格式如下：

OPEN cursor_name 
FETCH cursor_name  INTO var_name[,var_name]···;

在上述语法格式中，cursor_name 表示参数的名称；var_name表示将光标中的 SELECT 语句查询出来的信息存人该参数中，需要注意的是 var_name 必须在声明光标之前就定义好。

1.4.3 光标的关闭

关闭光标的语法格式:CLOSE cursor_name;
如果没有明确地关闭光标，它会在其声明的复合语句的末尾被关闭。

1.5 流程控制的使用

流程控制语句用于将多个SQL语句划分或组合成符合业务逻辑的代码块。MySQL中的流程控制语句包括: IF语句、CASE语句、LOOP语句、WHILE语句、LEAVE语句、ITERATE语句、REPEAT语句和 WHILE语句。每个流程中可能包含一个单独语句，也可能是使用BEGIN…END构造的复合语句，可以嵌套。

1. IF语句

IF 语句是指如果满足某种条件，就根据判断的结果为 TRUE 或 FALSE 执行相应的语句，其语法格式如下:

IF expr_condition THEN statement_list
[ ELSEIF expr_condition THEN statement_list ]
[ELSE statement_list]
END IF

2. CASE语句

CASE 是另一个进行条件判断的语向，该语句有两种语句格式，第一种格式：

CASE case_expr
		WHEN when_value THEN statement_list;
		[WHEN when_value THEN statement_list]···;
		[ELSE statement_list]
END CASE;

在上述语法格式中,case_expr 表示条件判断的表达式,决定了哪一个会被执行；when_ value 表示表达式可能的值，如果某个when_ value 表达式与 case_expr 表达式结果相同，则执行对应THEN关键字后的 statement_list 中的语句，statement_list 表示不同when_value值的执行语句。
CASE 语句的第二种格式如下:

CASE
		WHEN expr_condition THEN statement_list;
		[WHEN expr_condition THEN statement_list]
		[ELSE statement_list]
END CASE;

3. LOOP语句

LOOP 循环语句用来重复执行某些语句，与 IF 和 CASE 语句相比，LOOP 只是创建一个循环操作的过程，并不进行条件判断。 L.OOP 内的语句一直重复执行直到跳出循环语句。LOOP 语句的基本格式如下：

[ loop_label:] LooP
statement_list;
END LOOP [loop_label];

4. LEAVE语句

当不满足循环条件时，需要使用 LEAVE 语句退出循环。LEAVE 语句用于退出任何被标注的流程控制构造，其基本语法格式：LEAVE label
label表示循环的标志。通常情况下,LEAVE 语句与BEGIN···END、循环语句一起使用。

5. ITERATE语句

ITERATE 的意思是再次循环，ITERATE 语句用于将执行顺序转到语句段的开头处。使用ITERATE语句的基本语法格式：ITERATE lable
在上述语法结构中lable表示循环的标志。ITERATE 语句只可以出现在 LOOP、REPEAT 和WHILE语句内。

6. REPEAT语句

REPEAT 语句用于创建一个带有条件判断的循环，每次语句执行完毕之后，会对条件表达式进行判断，如果表达式为真，则循环结束；否则重复执行循环中的语句，REPEAT语句的基本语法格式如下：

[repeat_lable:] REPEAT
	statement_ list
UNTIL expr_condition
END REPEAT [repeat_lable]

7. WHILE语句

WHILE语句创建一个带条件判断的循环过程，与REPEAT不同的是，WHILE在语句执行时，先对指定的表达式进行判断，如果为真，则执行循环内的语句，否则退出循环。WHILE语句的基本格式如下:

[while_lable:] WHILE expr_ condition DO
	Statement_list
END WHILE [whlle_lable]

while_lable 为WHILE语句的标注名称；expr_condition 为进行判断的表达式，如果表达式结果为真，WHILE语句内的语句或语句群被执行,直至expr_condition为假，退出循环。