数据库原理及应用知识点整理——存储过程与游标

存储过程与游标
存储过程
一、存储过程的概念
1、概念：是存储在数据库中的一种编译对象，是一组完成特定功能的SQL语句集，编译
后存储在数据库中，可以被客户机管理工具、应用程序和其他存储过程调用。

2、存储过程的主要优点：封装性、可增强SQL语句的功能和灵活性、可减少网络流量、
高性能、提高数据库的安全性和数据的完整性
二、创建存储过程的语法
1、创建存储过程：
CREATE PROCEDURE 存储过程名（参数1，参数2，……）
BEGIN
存储过程体
END
存储过程名以“proc_”为前缀或以“_proc”为后缀。

没有参数也要写括号，括号内包含多个参数时格式为：[ in | out | inout ] 参数名参数类型，分别对应输入参数（作为执行条件）、输出参数（用于存放存储过程执行完需要返回的操作结果）、输入/输出参数（二者皆可）
注意：参数的取名不要与数据表的字段名相同，会报错。

2、存储过程体常用语法有declater声明局部变量、set为局部变量赋值、select…into
加班费查询到的值直接存储到局部变量中、定义错误处理程序、使用流程控制语句实现复杂业务逻辑、使用游标。

3、declter 变量名数据类型 default ‘’;
4、set 变量名 = 变量初始值；
5、select 指定列名[ ] into 指定要复制的变量名[ ] select 语句中的from子句及后面的条件语句部分。

三、定义错误触发条件和错误处理程序（作用：提高语言的安全性）
1、定义错误处理程序：
（1）定义错误触发条件：
方法一：使用sqlstate_value （长度为5的字符串类型的错误代码）
DECLARE 异常名称 CONDITION FOR SQLSTATE ‘’;
方法二:使用mysql_error_code（数值类型错误代码）
DECLARE 异常名称 CONDITION FOR 值；
（2）定义错误处理程序：
DECLARE CONTINUE | EXIT | UNDO HANDLER FOR 错误类型[…] 一些存储过程或函数的执行语句
CONTINUE 表示遇到错误不处理，继续执行；EXIT 表示遇到错误马上退出；UNDO 表示遇到错误后撤回之前的操作，MySQL 暂不执行这种处理方式。

2、错误类型：
（1）sqlstate SQLSTATE_value ：
捕获该错误类型异常：
declare continue handler for sqlstate ‘变量赋值’ set @ info= ‘输出变量名’；
（2）mysql_error_code ：
捕获该错误类型异常：
declare continue handler for 数值 set @ info = ‘输出变量名’；
（3）condition_name ：
先定义条件，然后捕获该错误类型异常：
declare 输出变量名 condition for 变量赋值；
或者
declare continue handler for 输出变量名 set @ info = ‘输出变量名’;
（4）sqlwarning ：匹配所有以01开头的SQLSTATE 错误代码
使用sqlwarning 捕获该错误类型异常：
declare exit handler for sqlwarning set @ info = ‘error’;
（5）not found ：匹配所有以02开头的SQLSTATE错误代码
使用not found 捕获该错误类型异常：
declare exit handler for not found set @ info = ‘输出变量名’;
（6）sqlexception ：
使用sqlexception 捕获该错误类型异常：
declare exit handler for sqlexception set @ = ‘error’;
四、存储过程的调用
1、call 存储过程名 [ 参数列表 ];
参数列表中的参数个数、类型和顺序应该和被调用的存储过程中的参数个数、类型和顺序保持一致，名称不需要一致。

2、select @ 参数名；打印参数
3、in参数（输入参数）的传入值可能是常量或会话变量；
out（输出参数）的传出值只能是会话变量；
inout参数的值只能是会话变量。

（inout参数必须设置为一个变量，且必须先定义为会话变量并初始化之后，再作为参数传递给存储过程，否则设置该参数为常量时将无法接收输出值。

）
4、调用存储过程的语句格式大致为以下两种：
第一种：
call 存储过程名（参数列表）;
select @ 参数;
第二种：
set @参数1 = ‘数值1’;
set @参数2 = ‘数值2’;
call 存储过程名 (参数列表);
select @ 需要打印的参数;
返回值的获取是在call语句，返回值的打印是在select语句；
call语句和select语句都是单独提交执行的，但是这两条语句都属于同一个会话；
使用会话变量可以存储上一条执行语句的值，以便后面调用。

五、游标的概念
1、游标是一种能从select结果集中逐行提取记录的机制，也因此与select语句密切相关。

MySQL中游标只存在存储过程以及函数中。

六、游标的使用方法
1、使用游标的步骤为;声明游标、打开游标、提取游标中的数据、关闭游标。

2、游标的相关语法为：
（1）声明游标：
DECLARE 游标名 CURSOR FOR SELECT 语句;
SELECT 语句返回的结果集是一行或多行记录，使用DECLARE语句声明游标后并不会直接执行SELECT语句，MySQL服务器中并没有SELECT语句对应的结果集。

注：该语句声明一个游标，也可以在存储过程中定义多个游标，但是一个BEGIN-END语句中每一个游标都必须有唯一的名字。

（2）打开游标：
OPEN 游标名;
用该语句打开游标后，与该游标对应的SELECT语句将被执行，MySQL服务器中将存放SELECT语句查询到的结果集，但是一个游标仅能打开一次。

（3）提取游标中的数据：
FETCH 游标名 INTO 变量名1，[,变量名2 ……];
该语句使用前需要在BEGIN…END语句块的开头处声明，FETCH语句是将游标指向的一行数据赋给一些变量，所以要求变量的个数、类型和顺序必须与声明游标时使用的SELECT语句
返回的结果集中的字段个数、类型和顺序保持一致。

注：游标的自定义错误处理程序应该放在声明游标之后；游标通常结合错误处理程序一起使用，以便结束“游标”的遍历。

（4）关闭游标：
close 游标名;
该操作的优点在于释放游标打开时产生的结果集，节省MySQL服务器的内存空间。

即使游标没有被明确地关闭，那么它将会被打开的BEGIN-END语句块关闭。

七、管理存储过程
1、管理存储过程主要分为：查看存储过程的信息、根据需要修改存储过程、删除不需要的存储过程。

2、存储过程相关的语法：
（1）查看存储过程信息：
查看存储创建语句:SHOW CREATE PROCEDURE 存储过程名;
查看所有存储过程的状态：SHOW PROCEDURE STATUS[ LIKE’pattem’];
SHOW PROCEDURE STATUS 可以查看当前服务器连接下的所有存储过程的状态，
[ LIKE’pattern’]参数用来匹配存储过程的名称。

（2）修改存储过程：MySQL中已创建的存储过程不能修改，只能删除后重新创建。

（3）删除存储过程：
DROP PROCEDURE [IF EXISTS] [数据库名.] 存储过程名;
[IF EXISTS] 避免在没有存储过程的情况下删除存储过程而报错；
[数据库名.] 用以在删除存储过程时可以指定数据库，如果省略则默认为当前数据库。

八、存储过程与自定义函数的比较
1、在MySQL中，存储过程和自定义函数都被称为存储程序，且二者都可以重复使用以减少数据库开发人员的工作量。

2、自定义函数和存储过程在使用时应该注意的几点：
（1）一般来说，存储过程可实现的功能比自定义函数要复杂一些，功能强大，可以执行包括修改表等一系列的数据库操作；
用户自定义函数所能够实现的功能针对性比较强，但是它不能用于执行一组修改全局数据状态的操作。

（2）函数只能返回一个值，存储过程可以返回多个值。

（3）函数只能有输入参数，存储过程可以有IN、OUT、INOUT三个不同类型的参数。

（4）函数声明时需要描述返回值类型，且函数体中必须包含一个有效的RETURN语句存储过程声明时不需要返回类型。

（5）函数可以嵌入SQL语句中（如SELECT语句），作为查询语句的一个部分来使用，而存储过程一般是作为一个独立的部分来执行（call语句执行）。