《MySQL数据库基础与实例教程 工业和信息化普通高等教育“十二五”规划教材 教学课件 ppt 作者 孔祥盛 MySQL数据库基础与实例教程第8章》由会员分享，可在线阅读，更多相关《MySQL数据库基础与实例教程 工业和信息化普通高等教育“十二五”规划教材 教学课件 ppt 作者 孔祥盛 MySQL数据库基础与实例教程第8章（57页珍藏版）》请在金锄头文库上搜索。
1、,孔祥盛,MySQL数据库基础与实例教程 之 存储过程与游标,1,2,3,本章主要讲解如何在MySQL中使用存储过程，并结合“选课系统”讲解存储过程在该系统中的应用，最后本章对存储程序做了总结。,4,错误触发条件和错误处理,存储过程,游标,预处理SQL语句,5,存储程序的说明,内容一览,1,2,3,存储过程也可以看作是一个“加工作坊”，它接收“调用者”传递过来的“原料”（ in参数），然后将这些“原料”“加工处理”成“产品”（ 存储过程的out参数或inout参数），再把“产品”返回给“调用者”。,存储过程的调用,创建存储过程的语法格式,“选课系统”的存储过程,4,5,查看存储过程的定义,删除
2、存储过程,6,存储过程与函数的比较,8.1 存储过程,创建存储过程的语法格式如下。 create procedure 存储过程名（参数1，参数2，） 存储过程选项 begin 存储过程语句块; end;,8.1.1 创建存储过程的语法格式,存储过程选项由以下一种或几种选项组合而成。具体含义请查看函数选项的内容。 language sql | not deterministic | contains sql | no sql | reads sql data | modifies sql data | sql security definer | invoker | comment 注释,8.1
3、.1 创建存储过程的语法格式,与函数相同之处在于：存储过程的参数也是局部变量，也需要提供参数的数据类型；与函数不同的是，存储过程有三种类型的参数：in参数、out参数以及inout参数。,8.1.1 创建存储过程的语法格式,in代表输入参数（默认情况下为in参数），表示该参数的值必须由调用程序指定； out代表输出参数，表示该参数的值经存储过程计算后，将out参数的计算结果返回给调用程序； inout代表即是输入参数，又是输出参数，表示该参数的值即可以由调用程序指定，又可以将inout参数的计算结果返回给调用程序。,8.1.1 创建存储过程的语法格式,例如下面的存储过程：,delimiter
4、$ create procedure get_choose_number_proc(in student_no1 int,out choose_number int) reads sql data begin select count(*) into choose_number from choose where student_no=student_no1; end $ delimiter ;,8.1.1 创建存储过程的语法格式,调用存储过程须使用call关键字，另外还要向存储过程传递in参数、out参数或者inout参数。 例如：,set student_no = 2012001; set
5、 choose_number = 0; call get_choose_number_proc(student_no,choose_number); select choose_number;,8.1.2 存储过程的调用,存储过程get_choose_number_proc()中的in参数与out参数的数据类型都为整数，也可以将这两个参数简化为一个inout参数。,8.1.2 存储过程的调用,delimiter $ create procedure get_choose_number1_proc(inout number int) reads sql data begin select cou
6、nt(*) into number from choose where student_no=number ; end $ delimiter ;,8.1.2 存储过程的调用,set number = 2012001; call get_choose_number1_proc(number); select number;,8.1.2 存储过程的调用,任务布置1：上机操作，完成本书场景描述1的任务要求。 任务布置2：上机操作，完成本书场景描述2的任务要求。 任务布置3：上机操作，完成本书场景描述3的任务要求。,8.1.3 “选课系统”的存储过程,可以使用下面四种方法查看存储过程的定义、权限、字
7、符集等信息。 1使用show procedure status命令查看存储过程的定义。 2查看某个数据库（例如choose数据库）中的所有存储过程名，可以使用下面的SQL语句。 select name from mysql.proc where db = choose and type = procedure;,8.1.4 查看存储过程的定义,3使用MySQL命令“show create procedure 存储过程名;”可以查看指定数据库指定存储过程的详细信息。 例如查看get_choose_number_proc()存储过程的详细信息，可以使用“show create procedure
8、get_choose_number_procG”,8.1.4 查看存储过程的定义,4存储过程的信息都保存在information_schema数据库中的routines表中，可以使用select语句查询存储过程的相关信息. 例如下面的SQL语句查看的是get_choose_number_proc()存储过程的相关信息。 select * from information_schema.routines where routine_name= get_choose_number_procG,8.1.4 查看存储过程的定义,存储过程与函数之间的共同特点在于： 应用程序调用存储过程或者函数时，只需要
9、提供存储过程名或者函数名，以及参数信息，无需将若干条MySQL命令或SQL语句发送到MySQL服务器，节省了网络开销。,8.1.6 存储过程与函数的比较,存储过程与函数之间的共同特点在于： 存储过程或者函数可以重复使用，可以减少数据库开发人员，尤其是应用程序开发人员的工作量。 使用存储过程或者函数可以增强数据的安全访问控制。可以设定只有某些数据库用户才具有某些存储过程或者函数的执行权。,8.1.6 存储过程与函数的比较,存储过程与函数之间的不同之处在于： 函数必须有且仅有一个返回值，且必须指定返回值数据类型（返回值类型目前仅仅支持字符串、数值类型）。存储过程可以没有返回值，也可以有返回值，甚至
10、可以有多个返回值，所有的返回值需要使用out或者inout参数定义。,8.1.6 存储过程与函数的比较,存储过程与函数之间的不同之处在于： 函数体内可以使用selectinto语句为某个变量赋值，但不能使用select语句返回结果（或者结果集）。存储过程则没有这方面的限制，存储过程甚至可以返回多个结果集。,8.1.6 存储过程与函数的比较,存储过程与函数之间的不同之处在于： 函数可以直接嵌入到SQL语句（例如select语句中）或者MySQL表达式中，最重要的是函数可以用于扩展标准的SQL语句。存储过程一般需要单独调用，并不会嵌入到SQL语句中使用（例如select语句中），调用时需要使用ca
11、ll关键字。,8.1.6 存储过程与函数的比较,存储过程与函数之间的不同之处在于： 函数中的函数体限制比较多，比如函数体内不能使用以显式或隐式方式打开、开始或结束事务的语句，如start transaction、commit、rollback或者set autocommit=0等语句；不能在函数体内使用预处理SQL语句（稍后讲解）。存储过程的限制相对就比较少，基本上所有的SQL语句或MySQL命令都可以在存储过程中使用。,8.1.6 存储过程与函数的比较,存储过程与函数之间的不同之处在于： 应用程序（例如Java、PHP等应用程序）调用函数时，通常将函数封装到SQL字符串（例如select语句
12、）中进行调用；应用程序（例如Java、PHP等应用程序）调用存储过程时，必须使用call关键字进行调用，如果应用程序希望获取存储过程的返回值，应用程序必须给存储过程的out参数或者inout参数传递MySQL会话变量，才能通过该会话变量获取存储过程的返回值。,8.1.6 存储过程与函数的比较,默认情况下，存储程序运行过程中（例如存储过程或者函数）发生错误时，MySQL将自动终止存储程序的执行。存储程序运行过程中发生错误时，数据库开发人员有时希望自己控制程序的运行流程，并不希望MySQL将自动终止存储程序的执行，MySQL的错误处理机制可以帮助数据库开发人员自行控制程序流程。,8.2 错误触发条
13、件和错误处理,1,2,3,存储程序运行过程中（例如存储过程或者函数）发生错误时 MySQL的错误处理机制可以帮助数据库开发人员自行控制程序流程。,自定义错误触发条件,自定义错误处理程序,自定义错误处理程序说明,8.2 错误触发条件和错误处理,自定义错误处理程序时需要使用declare关键字，语法格式如下。 declare 错误处理类型 handler for 错误触发条件 自定义错误处理程序;,8.2.1 自定义错误处理程序,错误处理类型的取值要么是continue，要么是exit。 当错误处理类型是continue时，表示错误发生后，MySQL立即执行自定义错误处理程序，然后忽略该错误继续执
14、行其他MySQL语句。 当错误处理类型是exit时，表示错误发生后，MySQL立即执行自定义错误处理程序，然后立刻停止其他MySQL语句的执行。,8.2.1 自定义错误处理程序,错误触发条件：表示满足什么条件时，自定义错误处理程序开始运行，错误触发条件定义了自定义错误处理程序运行的时机。 错误触发条件有3种取值：MySQL错误代码、ANSI标准错误代码以及自定义错误触发条件。例如1452是MySQL错误代码，它对应于ANSI标准错误代码23000，自定义错误触发条件稍后讲解。,8.2.1 自定义错误处理程序,自定义错误处理程序：错误发生后，MySQL会立即执行自定义错误处理程序中的MySQL语
15、句，自定义错误处理程序也可以是一个begin-end语句块。 任务布置4：上机操作，完成本书场景描述4：自定义错误处理程序的任务要求。,8.2.1 自定义错误处理程序,自定义错误触发条件允许数据库开发人员为MySQL错误代码或者ANSI标准错误代码命名，语法格式如下。 declare 错误触发条件 condition for MySQL错误代码或者ANSI标准错误代码;,8.2.2 自定义错误触发条件,例如代码片段： declare continue handler for 1452 begin set error1 = 外键约束错误!; end; 可以替换成代码片段：,8.2.2 自定义错误
16、触发条件, declare foreign_key_error condition for sqlstate 23000; declare continue handler for foreign_key_error begin set error1 = 外键约束错误!; end; ,8.2.2 自定义错误触发条件,自定义错误触发条件以及自定义错误处理程序可以在触发器、函数以及存储过程中使用。 参与软件项目的多个数据库开发人员，如果每个人都自建一套错误触发条件以及错误处理程序，极易造成MySQL错误管理混乱。实际开发过程中，建议数据库开发人员建立清晰的错误处理规范，必要时可以将自定义错误触发条件、自定义错误处理程序封装在一个存储程序中。,8.2.3 自定义错误处理程序说明,1,2,数据库开发人员编写存储过程（或者函数）等存储程序时，有时需要存储程序中的MySQL代码扫描select结果集中的数据，并对结果集中的每条记录进行简单处理，通过My
