MySQL操作之存储过程

一、概念

在开发过程中，经常会用到某一功能重复使用，为此MySQL引入了存储过程。

是一条或者多条的SQL语句的集合，存储过程就这些SQL封装成一个代码块，以便重复使用。

二、存储过程的创建

2.1、创建存储过程

使用 create PROCEDURE 语句创建存储过程。

CREATE PROCEDURE sp_name ([proc_parameter])
[characters ...]routine_body

• CREATE PROCEDURE ：创建存储过程的关键字。
• sp_name ：为存储过程的名称。
• proc_parameter ：存储过程的参数列表。
• **characters：**用于指定存储过程的特性。
• routine_body ：是SQL代码的内容。也可以只是用 begin ...end 来表示SQL代码的开始和结束。

proc_parameter 参数列表：

[IN|OUT|INOUT]param_name type

• IN： 表示输入参数。
• OUT： 输出参数
• INOUT： 既可表示输入，也可表示输出参数。
• param_name： 表示参数名称。
• type： 表示参数的类型(可以是MySQL中任意一种类型)。

characters 参数列表：

• Language SQL ：说明routine_body部分是由SQL语句组成的，当前系统支持语句为SQL，SQL是language的唯一值。
• [Not]Deterministic ： 指明存储过程执行的结果是否确定。 Not Deterministic 不确定，为默认值。
• {CONTAINS SQL|NO SQL |READS sql data|modifies sql data} : 指明自语句使用SQL语句的限制。表示自语句中含有sql。
• SQL security{definer|invoker} ： 指明谁有权限来执行。definer：只有定义者才能执行。 invoker ：表示拥有权限的调用者可以执行。默认为： definer 。
• COMMENT'String ：注释信息。

案例：

CREATE PROCEDURE Proc ()
BEGIN
SELECT * FROM student;
END

2.2、变量的使用

在编写存储过程中，会使用变量保存数据处理过程中的值。MySQL中，变量可以在子程序中声明并使用，变量的作用范围是在 BEGIN...END 程序中。

想要在存储过程中使用变量，首先需要定义变量。使用 declare 语句定义变量。语法如下：

DECLARE var_name [,varname]...date_type[DEFAULT value]

• var_name ：为局部变量的名称。
• DEFAULT value ：子句给变量提供一个默认值。该值除了可以被声明为一个常数之外，还可以被指定为一个表达式。
• 如果没有Default子句，变量的初始值为null。

案例：

DECLARE myvariable INT DEFAULT 100;

更改变量值 SET ：

SET var_name = expr[,var_name =expr]...;

案例：

DECLARE var1,var2,var3 INT ;
SET var1=10,var2=20;
SET var3=var1+var3;

还可以通过 SELECT ... INTO 为一个或多个变量赋值。

SELECT col_name[...] INTO var_name [...]table_expr;

• col_name ：表示字段名称。
• var_name ：表示定义的变量名称。
• table_expr ：表示查询条件表达式，包括表名称和WHERE子句。

案例：

DECLARE s_grade FLOAT;
DECLARE s_grader CHAR(2);
SELECT grade,gender INTO s_grade,s_gender FROM student WHERE name='rose';

2.3、定义条件和处理程序

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

1、定义条件

在编写存储过程中，用 DECLARE 语句。

DECLARE condition_name CONDITION FOR [condition_type];
// conditoin_type的两种形式：
[condition_type]
SQLSTATE [VALUE] sqlstate_value|mysql_error_code

• condition_name ：表示所定义的条件的名称。
• condition_type ：表示条件的类型。
• sqlstate_value 和 mysql_error_code ：**都可以表示MySQL的错误。
• sqlstate_value ：表示长度为5的字符串类型的错误代码。
• mysql_error_code ：为数值类型的错误代码。

案例：

//方式一：使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE `42000`;
//方式二：使用mysql_error_code
DECLARE command_not_allowed CONDITION FOR SQLSTATE 1148;

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_statement ：参数为程序语句段，表示在遇到定义的错误时，需要执行的存储过程。
• condition_value ：表示错误类型。可以有

SQLSTATE[VALUE]sqlstate_value ：包含5个字符的字符串错误值。

condition_name ：表示 DECLARE CONTINUE 定义的错误条件名称。

SQLWARNING ：匹配所有以01开头的 SQLSTATE 错误代码。

NOT FOUND ：匹配所有以02开头的 SQLSTATE 错误代码。

SQLEXCEPTION ：匹配所有没有被 SQLWARING 和 NOT FOUND 捕获的 SQLSTATE 错误代码。

mysql_error_code ：匹配数值类型错误代码。

案例：

//方法一：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE `42s02` SET @info=`NO_SUCH_TABLE`;
//方法二：捕获mysql_error_code
DECLARE CONTINUE HANDLER FOR 1146 SET @info=`NO_SUCH_TABLE`;
//方法三：先定义条件，然后调用
DECLARE no_such_table CONTINUE FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='ERROR';
//方法四：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';
//方法五：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info ='NO_SUCH_FOUND';
//方法六：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

2.4、光标的使用

在编写存储过程时，查询语句可能返回多条记录，如果数据量过大，则需要使用光标来逐条读取查询结果集中的记录。

在使用光标前需要先声明光标。光标必须声明在声明变量、条件之后、声明处理程序之前。

1、光标的声明

MySQL中使用 DECLARE 关键字来声明光标。

DECLARE cursor_name CURSOR FOR select_statement

• cursor_name ：表示光标的名称。
• select_statement ：表示select语句的内容，返回一个创建光标的结果集。

案例(声明cursor_student的光标)：

DECLARE cursor_name CURSOR FOR select s_name,s_gender FROM student;

2、光标的使用

OPEN cursor_name
FETCH cursor_name INTO var_name[，var_name]...

• cursor_name ：表示参数的名称。
• var_name ：表示将光标中 SELECT 语句查询出来的信息存入该参数中。
• var_name ：必须在声明光标之前就定义好。

案例：

FETCH cursor_student INTO s_name ,s_gender;

3、光标的关闭

使用完光标后，要将其关闭。

CLOSE cursor_name

2.5、流程控制的使用

将多个SQL语句划分或者组合成符合业务逻辑的代码块。

包括： If 语句、 case 语句、 loop 语句、 while 语句、 leave 语句、 iterate 语句、 repeat 语句。

1、IF语句

if 语句是满足某个条件，则执行某个操作。

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

• expr_condition ：表示判断条件。
• statement_list ：表示SQL语句列表，可以包括一个或多个语句。

案例：

IF val IS NULL
	THEN SELECT 'val is NULL‘;
	ELSE SELECT 'val is NOT NULL';
END IF;

判断val值是否为空，假如val为空，输出字段’val is NULL’；否则，输出"val is NOT NULL"。

2、CASE语句

格式一：

CASE case_expr
	WHEN when_value THEN statement_list
	[WHEN when_value THEN statement_list]...
	[ELSE statement_list]
END IF

案例：

CASE val
	WHEN 1 THEN SELECT ’val is 1‘
	WHEN 2 THEN SELECT 'val is 2'
	ELSE SELECT ’val is not 1 or 2'
END IF

SELECT
	last_name,job_id,salary,
CASE job_id 
			WHEN 'IT_PROG'  THEN 1.10 * salary 
			WHEN 'ST_CLERK' THEN 1.15 * salary 
			WHEN 'SA_REP'   THEN 1.20 * salary 
			ELSE salary 	END  "REVISED_SALARY" 
FROM employees;

格式二：

CASE 
	WHEN expr_codition THEN statement_list
	[WHEN expr_codition THEN statement_list]
	[ELSE statement_list]
END IF

3、LOOP语句

LOOP 循环语句用来重复执行某些语句，与 if 和 case 语句相比， loop 只是创建一个循环操作的过程，并不进行条件判断。

LOOP 内的语句一直重复执行，直到跳出循环语句。

[loop_label:]LOOP
	statement_list
END LOOP [loop_label]

• loop_label ：表示LOOP语句的标注名称，该参数可以省略。
• statement_list ：表示需要循环执行的语句。

案例：

DECLARE id INT DEFAULT 0:
add_loop:LOOP
SET id=id +1;
	IF id>=10  THEN LEAVE add_loop;
	END IF;
END LOOP add_loop;

循环执行了id+1的操作。当id的值小于10时，循环重复执行；当id的值大于或等于10时，使用 LEAVE 语句退出循环。

4、LEAVE语句

当不满足循环条件时，需要使用 LEAVE 语句退出循环。

LEAVE label

5、ITERATE语句

ITERATE 是再次循环，用于将执行顺序顺序转到语句段的开头处。

ITERATE lable

• lable ：表示循环的标志。
• ITERATE 语句只可以出现在 LOOP 、 REPEAT 和 while 语句内。

CREATE PROCEDURE doiterate()
BEGIN
DECLARE p1 INT DEFAULT 0;
my_loop:LOOP
	SET p1=p1+1;
	IF p1<10 THEN ITERATE my_loop;
	ELSEIF p1>20 THEN LEAVE my_loop;
	END IF;
	SELECT 'p1 is between 10 and 20';
END LOOP my_loop;
END

p1的初始值为0，如果p1的值小于10时，重复执行p1+1的操作；当p1大于或等于10并且小于20时，打印内容"p1 is between 10 and 20";当p1大于20时，退出循环。

6、REPEAT语句

用于创建一个带有条件判断的循环过程，每次语句执行完毕后，会对条件表达式进行判断，如果表达式为真，则循环结束；否则重复执行循环中的语句。

[repeat_lable:]REPEAT
	statement_list
UNTIL expr_condition
END REPEAT [repeat_lable]

• repeat_lable ：为 REPEAT 语句的标注名称，该参数是可选的。
• REPEAT 语句内的语句或语句群被重复，知道 expr_condition 为真。

DECLARE id INT DEFAULT 0;
REPEAT
SET id=id+1;
UNTIL id>=10;
END REPEAT;

7、WHILE 语句

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

[while_lable:]WHILE expr_condition DO
	statement_list
END WHILE [while_lable]

• while_lable ：为 while 语句的标注名称。
• expr_condition ：为进行判断的表达式，如果表达式为真， WHILE 语句内的语句或语句群将被执行，直至 expr-condition 为假，退出循环。

DECLARE i INT DEFALULT 0;
WHILE i< 10 DO
SET i=i+1;
END WHILE;

三、存储过程的使用

3.1、调用存储过程

存储过程有多种调用方法。必须使用 CALL 语句调用，并且存储过程和数据库相关。如果要执行其他数据库中的存储过程，需要指定数据库的名称。

CALL sp_name([parameter],...)

• sp_name ：为存储过程的名称。
• parameter ：为存储过程的参数。