存储过程

存储过程:接受的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语句更适合游标做循环