MySQL入门 (十四) : Stored Routines 进阶

1 错误编号

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

mysql_15_snap_01

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

mysql_15_snap_02

注: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」的位置:

mysql_15_snap_03

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

mysql_15_snap_04

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

mysql_15_snap_05

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

mysql_15_snap_06

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

mysql_15_snap_07

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

mysql_15_snap_08

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

mysql_15_snap_09

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

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

mysql_13_snap_66

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

mysql_15_snap_11

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

mysql_15_snap_12

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

mysql_15_snap_13

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

mysql_15_snap_14

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

mysql_15_snap_15

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

mysql_15_snap_16

上列新增部门资料的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」来执行新增部门纪录资料,而且会记录执行后的结果:

mysql_15_snap_17

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

mysql_15_snap_18

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

mysql_15_snap_19

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

mysql_15_snap_20

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

mysql_15_snap_21

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

mysql_15_snap_22

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

mysql_15_snap_23

3 Conditions

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

mysql_15_snap_24

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

mysql_15_snap_25

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

mysql_15_snap_26

4 Cursors

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

mysql_15_snap_27

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

mysql_15_snap_28

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

mysql_15_snap_29

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

mysql_15_snap_30

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

mysql_15_snap_46

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

mysql_15_snap_32

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

mysql_15_snap_33

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

mysql_15_snap_34

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

mysql_15_snap_35

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

mysql_15_snap_36

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

mysql_15_snap_37

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

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

mysql_15_snap_38

5 设定、修改与删除Stored routines

5.1 建立Stored routines时的设定

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

mysql_15_snap_39

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

  • 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设定的语法:

mysql_15_snap_40

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

mysql_15_snap_41

5.3 删除Stored routines

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

mysql_15_snap_42

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的相关资讯:

mysql_15_snap_43

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

mysql_15_snap_44

翻译自: http://www.codedata.com.tw/database/mysql-tutorial-15-stored-routines-advanced/

更多参考:

MySQL 超新手入门教程系列

MySQL入门 (一) : 资料库概论与MySQL的安装

MySQL入门 (二) : SELECT 基础查询

MySQL入门 (三) : 运算式与函式

MySQL入门 (四) : JOIN 与UNION 查询

MySQL入门 (五) : CRUD 与资料维护

MySQL入门 (六) : 字元集与资料库

MySQL入门 (七) : 储存引擎与资料型态

MySQL入门 (八) : 表格与索引

MySQL入门 (九) : 子查询 Subquery

MySQL入门 (十) : Views

MySQL入门 (十一) : Prepared Statements

MySQL入门 (十二) : Stored Routines 入门

MySQL入门 (十三) : Stored Routines 的变数与流程

MySQL入门 (十五) : Triggers

 

本文: MySQL入门 (十四) : Stored Routines 进阶

Leave a Reply