MySQL数据库基础与实例教程第8章

8.3 游标
8.1.6 存储过程与函数的比较
存储过程与函数之间的不同之处在于： 函数必须有且仅有一个返回值，且必须指定 返回值数据类型（返回值类型目前仅仅支持字符 串、数值类型）。存储过程可以没有返回值，也 可以有返回值，甚至可以有多个返回值，所有的 返回值需要使用out或者inout参数定义。
8.1.6 存储过程与函数的比较
MySQL数据库基础与实例教程
之
存储过程与游标
肖红
内容一览
本章主要讲解如何 在MySQL中使用存 储过程，并结合 “选课系统”讲解 存储过程在该系统 中的应用，最后本 章对存储程序做了 总结。
1 存储过程 2 错误触发条件和错误处理 3 游标
4 预处理SQL语句
5 存储程序的说明
8.1 存储过程
8.1.6 存储过程与函数的比较
存储过程与函数之间的不同之处在于： 函数中的函数体限制比较多，比如函数体内 不能使用以显式或隐式方式打开、开始或结束事 务的语句，如start transaction、commit、 rollback或者set autocommit=0等语句；不能在 函数体内使用预处理SQL语句（稍后讲解）。存 储过程的限制相对就比较少，基本上所有的SQL 语句或MySQL命令都可以在存储过程中使用。
inout代表即是输入参数，又是输出参数， 表示该参数的值即可以由调用程序指定，又 可以将inout参数的计算结果返回给调用程序。
8.1.1 创建存储过程的语法格式
例如下面的存储过程：
delimiter $$ 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.2 存储过程的调用
set @number = '2012001'; call get_choose_number1_proc(@number); select @number;
8.1.3 “选课系统”的存储过程
任务布臵1：上机操作，完成本书场景描述1 的任务要求。 任务布臵2：上机操作，完成本书场景描述2 的任务要求。 任务布臵3：上机操作，完成本书场景描述3 的任务要求。
存储过程也可以看作是 一个“加工作坊”，它 接收“调用者”传递过 来的“原料”（ in参 数），然后将这些“原 料”“加工处理”成 “产品”（ 存储过程的 out参数或inout参数）， 再把“产品”返回给 “调用者”。
1 创建存储过程的语法格式 2 存储过程的调用 3 “选课系统”的存储过程 4 查看存储过程的定义 5 删除存储过程 6 存储过程与函数的比较
8.2.1 自定义错误处理程序
错误触发条件：表示满足什么条件时，自定义 错误处理程序开始运行，错误触发条件定义了自 定义错误处理程序运行的时机。
错误触发条件有 3种取值：MySQL错误代码、 ANSI标准错误代码以及自定义错误触发条件。例 如 1452是 MySQL错误代码，它对应于 ANSI 标准 错误代码 23000 ，自定义错误触发条件稍后讲解。
8.1.4 查看存储过程的定义
3．使用MySQL命令“show create procedure 存储过程名;”可以查看指定数据库指定存储过程 的详细信息。
例如查看get_choose_number_proc()存储过程 的详细信息，可以使用“show create procedure get_choose_number_proc\G”
8.2.1 自定义错误处理程序
自定义错误处理程序：错误发生后， MySQL 会立即执行自定义错误处理程序中的MySQL语句， 自定义错误处理程序也可以是一个 begin-end 语 句块。 任务布臵4：上机操作，完成本书场景描述4：自 定义错误处理程序的任务要求。
8.2.2 自定义错误触发条件
自定义错误触发条件允许数据库开发人员为 MySQL错误代码或者ANSI标准错误代码命名， 语法格式如下。
8.1.2 存储过程的调用
调用存储过程须使用call关键字，另外还要 向存储过程传递in参数、out参数或者inout 参数。 例如：
set @student_no = '2012001'; set @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 count(*) into number from choose where student_no=number ; end $$ delimiter ;
declare 错误触发条件 condition for MySQL错 误代码或者ANSI标准错误代码;
8.2.2 自定义错误触发条件
例如代码片段： … declare continue handler for 1452 begin set @error1 = '外键约束错误!'; end; … 可以替换成代码片段：
8.2.2 自定义错误触发条件
… declare foreign_key_error condition for sqlstate '23000'; declare continue handler for foreign_key_error begin set @error1 = '外键约束错误!'; end; …
8.1.6 存储过程与函数的比较
存储过程与函数之间的共同特点在于： 应用程序调用存储过程或者函数时，只需要 提供存储过程名或者函数名，以及参数信息，无 需 将 若 干 条 MySQL 命 令 或 SQL 语 句 发 送 到 MySQL服务器，节省了网络开销。
8.1.6 存储过程与函数的比较
存储过程与函数之间的共同特点在于： 存储过程或者函数可以重复使用，可以减少 数据库开发人员，尤其是应用程序开发人员的工 作量。 使用存储过程或者函数可以增强数据的安全 访问控制。可以设定只有某些数据库用户才具有 某些存储过程或者函数的执行权。
8.2.3 自定义错误处理程序说明
自定义错误触发条件以及自定义错误处理程序 可以在触发器、函数以及存储过程中使用。
参与软件项目的多个数据库开发人员，如果每 个人都自建一套错误触发条件以及错误处理程序， 极易造成MySQL错误管理混乱。实际开发过程中， 建议数据库开发人员建立清晰的错误处理规范， 必要时可以将自定义错误触发条件、自定义错误 处理程序封装在一个存储程序中。
8.1.6 存储过程与函数的比较
存储过程与函数之间的不同之处在于：
应用程序（例如Java、PHP等应用程序）调用函 数时，通常将函数封装到SQL字符串（例如select语 句）中进行调用；应用程序（例如Java、PHP等应用 程序）调用存储过程时，必须使用call关键字进行调用， 如果应用程序希望获取存储过程的返回值，应用程序 必须给存储过程的out参数或者inout参数传递MySQL 会话变量，才能通过该会话变量获取存储过程的返回 值。
8.1.4 查看存储过程的定义
可以使用下面四种方法查看存储过程的定义、 权限、字符集等信息。
1．使用show procedure status命令查看存储过 程的定义。 2．查看某个数据库（例如choose数据库）中的 所有存储过程名，可以使用下面的SQL语句。 select name from mysql.proc where db = 'choose' and type = 'procedure';
8.1.1 创建存储过程的语法格式
与函数相同之处在于：存储过程的参数也 是局部变量，也需要提供参数的数据类型； 与函数不同的是，存储过程有三种类型的参 数：in参数、out参数以及inout参数。
8.1.1 创建存储过程的语法格式
in代表输入参数（默认情况下为in参数）， 表示该参数的值必须由调用程序指定； out代表输出参数，表示该参数的值经存储 过程计算后，将out参数的计算结果返回给调 用程序；
8.1.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 '注释'
存储过程与函数之间的不同之处在于： 函数体内可以使用select…into语句为某个 变量赋值，但不能使用select语句返回结果（或 者结果集）。存储过程则没有这方面的限制，存 储过程甚至可以返回多个结果集。
பைடு நூலகம்
8.1.6 存储过程与函数的比较
存储过程与函数之间的不同之处在于： 函数可以直接嵌入到SQL语句（例如select 语句中）或者MySQL表达式中，最重要的是函数 可以用于扩展标准的SQL语句。存储过程一般需 要单独调用，并不会嵌入到SQL语句中使用（例 如select语句中），调用时需要使用call关键字。
8.1.4 查看存储过程的定义
4．存储过程的信息都保存在 information_schema数据库中的routines表中， 可以使用select语句查询存储过程的相关信息. 例如下面的SQL语句查看的是 get_choose_number_proc()存储过程的相关信 息。 select * from information_schema.routines where routine_name= 'get_choose_number_proc'\G
8.2 错误触发条件和错误处理
默认情况下，存储程序运行过程中（例如存储 过程或者函数）发生错误时，MySQL将自动终止 存储程序的执行。存储程序运行过程中发生错误 时，数据库开发人员有时希望自己控制程序的运 行流程，并不希望MySQL将自动终止存储程序的 执行，MySQL的错误处理机制可以帮助数据库开 发人员自行控制程序流程。
8.2 错误触发条件和错误处理
存储程序运行过程中 （例如存储过程或者函 数）发生错误时 MySQL的错误处理机制 可以帮助数据库开发人 员自行控制程序流程。
1 自定义错误处理程序 2 自定义错误触发条件 3 自定义错误处理程序说明
8.2.1 自定义错误处理程序
自定义错误处理程序时需要使用declare关键 字，语法格式如下。
declare 错误处理类型 handler for 错误触发条件 自定义错误处理程序;
8.2.1 自定义错误处理程序
错误处理类型的取值要么是 continue，要么是 exit。
当错误处理类型是 continue时，表示错误发生 后，MySQL立即执行自定义错误处理程序，然后 忽略该错误继续执行其他MySQL语句。 当错误处理类型是 exit 时，表示错误发生后， MySQL立即执行自定义错误处理程序，然后立刻 停止其他MySQL语句的执行。