Question

1 错误编号

使用 SQL 叙述请资料库执行一些工作的时候，可能会因为输入错误或其它的原因，造成资料库产生错误讯息，下列的 SQL 叙述在 SQL query browser 中执行以后，MySQL 会传回一个错误编号与错误讯息，告诉你查询的表格名称不存在：

MySQL 用来表示错误的编号有两种，一种是 MySQL 资料库伺服器用的错误编号，使用四位数的数字来表示各种不同的错误；另外一种是各种资料库软体都适用的「SQL state」编号，使用五个字元的字串，来表示执行一个叙述以后各种不同的状况：

注：MySQL 的错误编号称为「Server Error Codes」，详细的错误编号与对应的错误讯息可以参考 MySQL 参考手册的附录 B(MySQL 5.0 Reference Manual、Appendix B. Error Codes and Messages、1584 页)。

2 Handlers

在撰写 stored routines 时，MySQL 提供一种很特别的宣告语法，你可以使用它宣告「handler」，handler 用来处理 stored routines 中可能会发生的错误，让你可以针对发生的错误执行必要的补救工作，也可以防止 stored routines 因为发生错误而中止。首先要特别注意宣告「handler」的位置：

Handler 是用来处理错误用的，所以在宣告的时候，要设定处理的错误种类和决定后续的流程。下列是宣告 handler 的语法：

Handler 的宣告包含发生的错误时要执行的叙述，如果有多个叙述时，就一定要使用「BEGIN-END」区块，把这些叙述放在区块中：

下列是一个新增部门资料的 procedur，呼叫它的时候要提供部门编号、名称与地点三个参数，这个 procedure 会使用你的参数帮你新增一笔纪录到「cmdev.dept」表格中，新增后会显示「Success!」的讯息：

下列是呼叫「cmdev.test_handler」procedure 的范例：

因为在「cmdev.dept」表格的定义中，部门编号「deptno」栏位设定为 primary key，所以它的栏位值是不可以重复的。所以如果再执行一次上列呼叫「cmdev.test_handler」procedure 的范例：

在执行一个 stored routine 的过程中，如果发生任何错误，MySQL 都会停止继续执行，再传回错误编号与错误讯息，告诉呼叫的人发生了什么状况：

撰写 stored routines 处理资料库的工作，除了之前已经讨论过的许多好处外，使用 handler 来处理错误，让执行工作的过程可以更加顺利，也是使用 stored routines 的主要原因。

下列的范例同样是提供新增部门资料功能的 procedure，不过为了希望发生索引值重复的错误时，不要因为错误而中断执行的工作，也不要传回错误编号与错误讯息，而是自己显示一个错误讯息，清楚的告诉使用者发生了什么状况。这样的需求就必须在 procedure 中加入 handler 的宣告。索引值重复的 SQL state 是「23000」，这个编号会使用在 handler 的宣告中：

加入 handler 宣告的 stored routines，在执行过程中如果没有发生任何问题，handler 是没有任何作用的，stored routines 会正常的执行完所有的叙述：

呼叫加入 handler 的宣告的「cmdev.test_handler2」，如果没有发生任何问题，在新增部门纪录后会显示「Success!」的讯息：

如果在执行过程中发生任何问题了，MySQL 会使用发生的错误编号，与你在 handler 宣告中指定的错误执行比对的工作，如果一样的话，接下来就交由 handler 来处理这个错误，MySQL 就不会中断执行与回传错误：

呼叫加入 handler 的宣告的「cmdev.test_handler2」时，如果指定的部门编号在资料表中已经存在，执行新增的叙述时就会发生发生索引值重复的错误。这种错误的 SQL state 是「23000」，MySQL 错误编号是「1062」：

在宣告 handler 时，除了指定 handler 要处理哪一种错误外，还要根据自己的需求，决定处理错误以后的后续流程：

一个宣告为「EXIT」的 handler，在执行完 handler 包含的叙述以后，会离开 handler 所在的区块；而宣告为「CONTINUE」的 handler，执行的流程会像这样：

上列新增部门资料的 procedure 范例，根据新增纪录的结果，会显示「Success!」或「Error!」两种结果。如果希望不论新增纪录成功或发生问题，都要把结果储存到下列的「cmdev.deptlog」表格中：

栏位名称	型态	NULL	索引	预设值	其它资讯	说明
logno	bigint(20)	NO	PRI	NULL	auto_increment	纪录编号
logdt	timestamp	NO	CURRENT_TIMESTAMP	日期时间
message	varchar(64)	YES	NULL	讯息

下列的范例使用「CONTINUE HANDLER」来执行新增部门纪录资料，而且会记录执行后的结果：

呼叫「test_handler3」procedure 后，​​如果没有发生任何问题，除了新增部门纪录外，还会新增一笔成功的讯息到「cmdev.deptlop」表格：

如果新增部门纪录时发生错误，「CONTINUE HANDLER」会把「v_message」变数值设定为「Error!」，然后再新增一笔错误的讯息到「cmdev.deptlop」表格：

下列的范例是呼叫「test_handler3」procedure 后，​​纪录在「cmdev.deptlop」表格中的结果：

索引值重复与不允许 NULL 值的错误，都是属于 SQL state 中的「23000」，如果你想要分别处理这两种错误的话，你可以针对每一种错误，宣告不同的 handler 来处理，不过在指定错误时，就要使用 MySQL 错误编号：

下列的范例是呼叫「test_handler4」procedure 后，​​纪录在「cmdev.deptlop」表格中的结果：

在宣告 handler 时指定的错误情况有下列几种：

3 Conditions

如果在 stored routines 中需要宣告 handler 来处理错误的话，你还可以宣告「conditions」给 handler 使用，下列是区块中 conditions 宣告的位置：

你可以宣告 condition 用来代表某一种问题，下列是宣告 condition 的语法：

下列的范例宣告两个 condition，分别代表不允许 NULL 值与索引值重复的错误，宣告好的 condition，就可以使用在 handler 的宣告中：

4 Cursors

如果 stored routines 需要针对一个查询结果中的每一笔纪录执行需要的处理工作，你可以宣告一个「cursor」来代表一个查询的结果，并且使用 cursor 依序处理所有纪录资料。下列是在区块中宣告 cursor 的位置：

宣告好 cursors 以后，可以使用「OPEN」叙述来开启，接着使用「FETCH」叙述读取资料，最后要使用「CLOSE」叙述关闭用完的 cursor：

宣告 cursor 时所指定的查询叙述，与使用「FETCH」读取资料时，要特别注意相对的顺序：

一般来说，都会把 cursor 称为「游标」或「指标」。当你宣告好一个需要的 cursor 以后，接着使用「OPEN」叙述开启 cursor，这时会有一个游标指向查询结果的第一笔纪录：

当你使用「FETCH」叙述时，除了读取目前游标的纪录资料外，还会将游表指向下一笔纪录：

以上列宣告的 cursor 来说，从开启到读取所有纪录资料的游标状况会像这样：

在 stored routines 中使用 cursor，通常需要下列的流程：

下列是流程与对应的叙述：

为了读取 cursor 中所有的纪录资料，要另外宣告 handler 来控制在没有资料读取时可以离开回圈：

除了使用「EXIT HANDLER」外，也可以使用「CONTINUE HANDLER」来控制在没有资料读取时可以离开回圈：

下列的说明表示没有资料可以读取时的流程：

在资料库的应用中，通常是需要针对一个查询的结果执行比较复杂的工作，才会在 sotred routines 中宣告与使用 cursor。如果你常常需要查询月薪在某个金额以上的员工资料，而且要把这些员工资料储存到一个表格中。这样的需求包含执行查询与处理新表格的工作，你就可以考虑使用包含 cursor 的 procedure 来完成这些工作。

下列的范例可以将月薪在指定金额以上的员工资料储存到「cmdev.topemp」表格中：

5 设定、修改与删除 Stored routines

5.1 建立 Stored routines 时的设定

建立 stored routines 时，也可以加入一些额外的设定：

下列是这些额外设定的说明：

• LANGUAGE {SQL}：设定 Stored routine 中用来撰写叙述的语言，目前只有支援 SQL，所以只能在 LANGUAGE 后面指定 SQL
• [NOT] DETERMINISTIC：如果传送相同的参数给 Stored routine，每次执行它以后都会产生同样的结果，这个 Stored routine 就应该设定为「DETERMINISTIC」；否则就要设定为「NOT DETERMINISTIC」。预设值为「NOT DETERMINISTIC」
• SQL SECURITY { DEFINER | INVOKER }：设定 Stored routine 要以建立者或执行者的权限执行
• COMMENT '说明字串'：设定 Stored routine 的说明

5.2 修改 Stored routines 设定

使用「ALTER PROCEDURE」与「ALTER FUNCTION」可以修改它们的额外设定，如果要修改参数或里面的叙述，必须删除后再重新建立。下列是修改 stored routines 设定的语法：

下列的范例执行修改「cmdev.gen_top_emp」的设定：

5.3 删除 Stored routines

如果不再需要一个已经建立的 stored routines，你可以使用下列的语法来删除它们：

6 查询 Stored routines 的相关资讯

如果想要查询 stored routines 的相关资讯，可以查询「information_schema.ROUTINES」表格，下列是它的主要栏位：

栏位名称	型态	说明
ROUTINE_SCHEMA	varchar(64)	资料库
ROUTINE_NAME	varchar(64)	名称
ROUTINE_TYPE	varchar(9)	procedure 或 function
DTD_IDENTIFIER	varchar(64)	procedure 固定为「NULL」；function 为回传值型态
ROUTINE_DEFINITION	longtext	Stored routine 的内容
IS_DETERMINISTIC	varchar(3)	DETERMINISTIC 的设定
SECURITY_TYPE	varchar(7)	DEFINER 或 INVOKER
CREATED	datetime	建立的日期时间
LAST_ALTERED	datetime	最后修改的日期时间
ROUTINE_COMMENT	varchar(64)	说明
DEFINER	varchar(77)	建立 Stored routine 的资料库使用者

你也可以使用 MySQL 提供的「SHOW」指令来查询 stored routines 的相关资讯：

如果你想要查询建立某个 stored routines 的详细资讯，可以使用下列的语法：