MySQL_07_存储过程
@
存储过程
存储过程：接受的IN参数处理后返回OUT参数或INOUT参数
1）存储过程就是存储在MySQL服务器中一个可整体执行的SQL语句
2）存储过程执行一次，会保存在高速缓冲存储器中
3）使用存储过程可以完成数据库的所有操作
//以后再使用只需调用保存在缓冲存储器中编译好的二进制代码即可
| 存储过程作用 | 说明 |
|---|---|
| 简化操作 | 将处理封装在容易使用的单元中 |
| 保证数据完整性 | 代替反复建立一系列处理步骤 |
| 简化变动管理 | 表更新后只需更新存储过程的代码 |
| 提高安全性 | 限制对基础数据的讹误 |
| 提高性能 | 执行存储过程比单独执行存储过程中的SQL语句快 |
创建存储过程
创建存储过程格式：
CREATE PROCEDURE 存储过程名（参数1，参数2，参数N）
[存储过程选项]
BEGIN
存储过程语句块；
END；
1）存储过程选项和函数选项同理
2）与函数相同的是：两者的参数都是局部变量，需创建时指定参数的数据类型
3）存储过程名后必须跟“（）”（即使无参数）
存储过程的参数需在创建时使用IN、OUT或INOUT指定：
| 参数 | 含义 |
|---|---|
| IN参数 | 代表输入参数 （默认情况下都为IN参数） 该参数的值必须由调用程序指定 |
| OUT参数 | 代表输出参数 该参数经存储过程计算后，将计算结果返回至OUT参数OUT参数返回给调用程序 |
| INOUT参数 | 代表既是输入参数，又是输出参数 该参数的值即可以由调用程序指定， 又可以将该参数的计算结果返回给调用程序 |
1）当使用OUT或 INOUT参数时，应设置一个用户会话变量用于接受数据
2）用户会话变量在存储过程调用时，不需带上@开头的标识
3）OUT和INOUT参数只能返回单行数据（返回多行时，使用SELECT返回）
使用存储过程
调用存储过程须使用CALL关键字，同时传递IN、OUT参数或INOUT参数
调用格式1（仅使用IN参数）：CALL 存储名（IN参数）；
1）当IN和OUT参数的数据类型为整数时，可将两者简化为一个INOUT参数
2）当使用OUT或INOUT参数时，存储过程的调用和显示不同
调用格式2（IN参数和OUT参数同时使用或INOUT参数）：
CALL 存储过程名（IN参数，用户会话变量）；
1）用户会话变量必须以@开头
2）查询存储过程结果可：SELECT 用户会话变量；
如：使用存储过程，查询products中prod_price的平均值
//由于存储过程语句块中需调用结束标识符“；”所以在创建存储过程前，应调用DELIMITER修改结束标识符（在创建完后，再调用DELIMITER改回“；”）
如：使用存储过程，查询products中prod_price的最高、最低和平均值
如：使用存储过程计算有税和无税情况下order_num为20005的iterm_price*quantity
1）创建存储过程（并编写注释）
2）查询20005有无税收情况下的iterm_price*quantity
管理存储过程
列出所有存储过程的定义
SHOW PROCEDURE STATUS；
列出指定指定存储过程的详细信息：
SHOW CREATE PROCEDURE 存储过程名；
查看指定数据库中的所有存储过程名：
SELECT name
FROM mysql.proc
WHERE db=‘数据库名’TYPE=‘procedure’；
删除指定存储过程：
DROP PROCEDURE 存储过程名；
1）删除存储过程时，存储过程名后不需跟“（）”
2）存储过程保存的仅是存储过程体，不保存任何用户数据
3）存储过程进行修改时只能先删除，后再建立一个同名存储过程
//对于存储函数、视图、触发器的修改同样适用
函数和存储过程的区别：
| 函数 | 存储过程 |
|---|---|
| 有且仅有一个返回值 必须指定返回值的数据类型 | 可以没有返回值 也可以有一个或多个返回值 但返回值需OUT或INOUT参数代出 也可以使用SELECT子句返回 |
| 函数体内可以使用 SELECT INTO语句为变量赋值 但不能赋值为结果集返回 | 存储过程允许返回结果集 甚至可以返回多个结果集 |
| 函数可以嵌入到 SQL语句或MySQL表达式中 函数可用于扩张标准的SQL语句 | 存储过程需要单独调用 不会嵌入到SQL语句中使用 调用时需要使用CALL关键字 |
| 函数中的函数体限制比较多 如：函数体内不能使用显示或隐式 打开、开始或结束事务的语句； 不能在函数体内使用预处理SQL | 存储过程的限制相对就比较少 基本上所有的SQL语句或MySQL命令 都可以在存储过程中使用 |
| 应用程序调用函数时 将函数封装到SQL字符串中调用 | 应用程序调用存储过程时 必须使用CALL关键字进行调用 若应用程序想获取存储过程的返回值，应用程序必须给存储过程的OUT参数或INOUT参数传递会话变量，才能通过该会话变量获取存储过程的返回值 |
| 函数和存储过程的相同点： |
|---|
| 存储过程或函数可以重复使用 使用存储过程或函数可以增强数据的安全访问控制 应用程序调用存储过程/函数时，只需提供存储过程名/函数名以及参数 |
游标
游标（Cursor）：存储在MySQL服务器上的数据库查询
1）作用：用于交互式应用，使用户可滚动式查看数据（结束后自动释放）
2）游标不属于SELECT语句，而是该语句检索出的结果集；
3）MySQL中游标只能用于存储过程/函数；
游标的使用规则如下：
1）游标必须先定义再使用；
2）游标定义后须打开游标才能使用；
3）对带有数据的游标，根据情况检索各行；
4）结束游标使用时，必须关闭游标；
游标的使用
游标使用步骤：定义游标、打开游标、使用游标、关闭游标
定义游标格式：DECLARE 游标名 CURSOR FOR SELECT查询
打开游标格式：OPEN 游标名
使用游标格式：FETCH 游标名 字段/变量（指定存储位）
1）FETCH在存储完成后，还会自动向前游一位
关闭游标格式：CLOSE 游标名
1）不需要的游标需及时关闭，释放其所占用的内存和资源
2）不手动关闭游标时，系统在执行到END语句时会默认关闭所有游标
如：利用游标进度读orders表中的order_num前两行
1）编写存储过程
2）调用存储过程
如：利用循环和游标循环输出orders表中order_num的所有行
1）编写存储过程
2）调用存储过程
//REPAT语句更适合游标做循环
