MySQL 50 新特性教程 存储过程第四讲.docx
《MySQL 50 新特性教程 存储过程第四讲.docx》由会员分享，可在线阅读，更多相关《MySQL 50 新特性教程 存储过程第四讲.docx（12页珍藏版）》请在冰点文库上搜索。
MySQL50新特性教程存储过程第四讲
1.SampleProblem:
LogOfFailures问题样例：
故障记录
当INSERT失败时，我希望能将其记录在日志文件中我们用来展示出错处理的问题样例是很
普通的。
我希望得到错误的记录。
当INSERT失败时，我想在另一个文件中记下这些错误的
信息，例如出错时间，出错原因等。
我对插入特别感兴趣的原因是它将违反外键关联的约束
2.SampleProblem:
LogOfFailures
（2）
mysql>CREATETABLEt2
s1INT,PRIMARYKEY（s1））
engine=innodb;//
mysql>CREATETABLEt3（s1INT,KEY（s1）,
FOREIGNKEY（s1）REFERENCESt2（s1））
engine=innodb;//
mysql>INSERTINTOt3VALUES（5）;//
...
ERROR1216（23000）:
Cannotaddorupdateachildrow:
aforeignkey
constraintfails（这里显示的是系统的出错信息）
我开始要创建一个主键表，以及一个外键表。
我们使用的是InnoDB，因此外键关联检查是打
开的。
然后当我向外键表中插入非主键表中的值时，动作将会失败。
当然这种条件下可以很
快找到错误号1216。
3.SampleProblem:
LogOfFailures
CREATETABLEerror_log（error_message
CHAR（80））//
下一步就是建立一个在做插入动作出错时存储错误的表。
4.SampleProblem:
LogOfErrors
CREATEPROCEDUREp22（parameter1INT）
BEGIN
DECLAREEXITHANDLERFOR1216
INSERTINTOerror_logVALUES
（CONCAT（'Time:
',current_date,
'.ForeignKeyReferenceFailureFor
Value=',parameter1））;
INSERTINTOt3VALUES（parameter1）;
END;//
上面就是我们的程序。
这里的第一个语句DECLAREEXITHANDLER是用来处理异常的。
意思是如果错误1215发生了，这个程序将会在错误记录表中插入一行。
EXIT意思是当动作成功提交后退出这个复合语句。
5.SampleProblem:
LogOfErrors
CALLp22（5）//
调用这个存储过程会失败，这很正常，因为5值并没有在主键表中出现。
但是没有错误信息
返回因为出错处理已经包含在过程中了。
t3表中没有增加任何东西，但是error_log表中记录
下了一些信息，这就告诉我们INSERTintotablet3动作失败。
DECLAREHANDLERsyntax声明异常处理的语法
DECLARE
{EXIT|CONTINUE}
HANDLERFOR
{error-number|{SQLSTATEerror-string}|condition}
SQLstatement
上面就是错误处理的用法，也就是一段当程序出错后自动触发的代码。
MySQL允许两种处理器，一种是EXIT处理，我们刚才所用的就是这种。
另一种就是我们将要演示的，CONTINUE处理，它跟EXIT处理类似，不同在于它执行后，原主程序仍然继续运行，那么这个复合语句就没有出口了。
1.DECLARECONTINUEHANDLERexampleCONTINUE处理例子
CREATETABLEt4（s1int,primarykey（s1））;//
CREATEPROCEDUREp23（）
BEGIN
DECLARECONTINUEHANDLER
FORSQLSTATE'23000'SET@x2=1;
SET@x=1;
INSERTINTOt4VALUES
（1）;
SET@x=2;
INSERTINTOt4VALUES
（1）;
SET@x=3;
END;//
这是MySQL参考手册上的CONTINUE处理的例子，这个例子十分好，所以我把它拷贝到这里。
通过这个例子我们可以看出CONTINUE处理是如何工作的。
2.DECLARECONTINUEHANDLER声明CONTINUE异常处理
CREATETABLEt4（s1int,primarykey（s1））;//
CREATEPROCEDUREp23（）
BEGIN
DECLARECONTINUEHANDLER
FORSQLSTATE'23000'SET@x2=1;<--
SET@x=1;
INSERTINTOt4VALUES
（1）;
SET@x=2;
INSERTINTOt4VALUES
（1）;
SET@x=3;
END;//
这次我将为SQLSTATE值定义一个处理程序。
还记得前面我们使用的MySQL错误代码1216吗？
事实上这里的23000SQLSTATE是更常用的，当外键约束出错或主键约束出错就被调用了。
3.DECLARECONTINUEHANDLER
CREATETABLEt4（s1int,primarykey（s1））;//
CREATEPROCEDUREp23（）
BEGIN
DECLARECONTINUEHANDLER
FORSQLSTATE'23000'SET@x2=1;
SET@x=1;<--
INSERTINTOt4VALUES
（1）;
SET@x=2;
INSERTINTOt4VALUES
（1）;
SET@x=3;
END;//
这个存储过程的第一个执行的语句是"SET@x=1"。
4.DECLARECONTINUEHANDLERexample
CREATETABLEt4（s1int,primarykey（s1））;//
CREATEPROCEDUREp23（）
BEGIN
DECLARECONTINUEHANDLER
FORSQLSTATE'23000'SET@x2=1;
SET@x=1;
INSERTINTOt4VALUES
（1）;
SET@x=2;
INSERTINTOt4VALUES
（1）;<--
SET@x=3;
END;//
运行后值1被插入到主键表中。
5.DECLARECONTINUEHANDLER
CREATETABLEt4（s1int,primarykey（s1））;//
CREATEPROCEDUREp23（）
BEGIN
DECLARECONTINUEHANDLER
FORSQLSTATE'23000'SET@x2=1;
SET@x=1;
INSERTINTOt4VALUES
（1）;
SET@x=2;<--
INSERTINTOt4VALUES
（1）;
SET@x=3;
END;//
然后@x的值变为2。
6.DECLARECONTINUEHANDLERexample
CREATETABLEt4（s1int,primarykey（s1））;//
CREATEPROCEDUREp23（）
BEGIN
DECLARECONTINUEHANDLER
FORSQLSTATE'23000'SET@x2=1;
SET@x=1;
INSERTINTOt4VALUES
（1）;
SET@x=2;
INSERTINTOt4VALUES
（1）;<--
SET@x=3;
END;//
然后程序尝试再次往主键表中插入数值，但失败了，因为主键有唯一性限制。
7.DECLARECONTINUEHANDLERexample
CREATETABLEt4（s1int,primarykey（s1））;//
CREATEPROCEDUREp23（）
BEGIN
DECLARECONTINUEHANDLER
FORSQLSTATE'23000'SET@x2=1;<--
SET@x=1;
INSERTINTOt4VALUES
（1）;
SET@x=2;
INSERTINTOt4VALUES
（1）;
SET@x=3;
END;//
由于插入失败，错误处理程序被触发，开始进行错误处理。
下一个执行的语句是错误处理的语句，@x2被设为2。
8.DECLARECONTINUEHANDLERexample
CREATETABLEt4（s1int,primarykey（s1））;//
CREATEPROCEDUREp23（）
BEGIN
DECLARECONTINUEHANDLER
FORSQLSTATE'23000'SET@x2=1;
SET@x=1;
INSERTINTOt4VALUES
（1）;
SET@x=2;
INSERTINTOt4VALUES
（1）;
SET@x=3;<--
END;//
到这里并没有结束，因为这是CONTINUE异常处理。
所以执行返回到失败的插入语句之后，继续执行将@x设定为3动作。
9.DECLARECONTINUEHANDLERexample
mysql>CALLp23（）//
QueryOK,0rowsaffected（0.00sec）
mysql>SELECT@x,@x2//
+------+------+
|@x|@x2|
+------+------+
|3|1|
+------+------+
1rowinset（0.00sec）
运行过程后我们观察@x的值，很确定的可以知道是3，观察@x2的值，为1。
从这里可以判断程序运行无误，完全按照我们的思路进行。
大家可以花点时间去调整错误处理器，让检查放在语句段的首部，而不是放在可能出现错误的地方，虽然那样看起来程序很紊乱，跳来跳去的感觉。
但是这样的代码很安全也很清楚。
1.DECLARECONDITION
CREATEPROCEDUREp24（）
BEGIN
DECLARE`ConstraintViolation`
CONDITIONFORSQLSTATE'23000';
DECLAREEXITHANDLERFOR
`ConstraintViolation`ROLLBACK;
STARTTRANSACTION;
INSERTINTOt2VALUES
（1）;
INSERTINTOt2VALUES
（1）;
COMMIT;
END;//
这是另外一个错误处理的例子，在前面的基础上修改的。
事实上你可给SQLSTATE或者错误代码其他的名字，你就可以在处理中使用自己定义的名字了。
下面看它是怎么实现的：
我把表t2定义为InnoDB表，所以对这个表的插入操作都会ROLLBACK（回滚），ROLLBACK（回滚事务）也是恰好会发生的。
因为对主键插入两个同样的值会导致SQLSTATE23000错误发生，这里SQLSTATE23000是约束错误。
2.DECLARECONDITION声明条件
CREATEPROCEDUREp24（）
BEGIN
DECLARE`ConstraintViolation`
CONDITIONFORSQLSTATE'23000';
DECLAREEXITHANDLERFOR
`ConstraintViolation`ROLLBACK;
STARTTRANSACTION;
INSERTINTOt2VALUES
（1）;
INSERTINTOt2VALUES
（1）;
COMMIT;
END;//
这个约束错误会导致ROLLBACK（回滚事务）和SQLSTATE23000错误发生。
3.DECLARECONDITION
mysql>CALLp24（）//
QueryOK,0rowsaffected（0.28sec）
mysql>SELECT*FROMt2//
Emptyset（0.00sec）
我们调用这个存储过程看结果是什么，从上面结果我们看到表t2没有插入任何记录。
全部事务都回滚了。
这正是我们想要的。
4.DECLARECONDITION
mysql>CREATEPROCEDUREp9（）
->BEGIN
->DECLAREEXITHANDLERFORNOTFOUNDBEGINEND;
->DECLAREEXITHANDLERFORSQLEXCEPTIONBEGINEND;
->DECLAREEXITHANDLERFORSQLWARNINGBEGINEND;
->END;//
QueryOK,0rowsaffected（0.00sec）
这里是三个预声明的条件：
NOTFOUND（找不到行）,SQLEXCEPTION（错误）,SQLWARNING（警告或注释）。
因为它们是预声明的，因此不需要声明条件就可以使用。
不过如果你去做这样的声明：
"DECLARESQLEXCEPTIONCONDITION..."，你将会得到错误信息提示。
Cursors游标
游标实现功能摘要：
DECLAREcursor-nameCURSORFORSELECT...;
OPENcursor-name;
FETCHcursor-nameINTOvariable[,variable];
CLOSEcursor-name;
现在我们开始着眼游标了。
虽然我们的存储过程中的游标语法还并没有完整的实现，但是已经可以完成基本的事务如声明游标，打开游标，从游标里读取，关闭游标。
1.CursorExample
CREATEPROCEDUREp25（OUTreturn_valINT）
BEGIN
DECLAREa,bINT;
DECLAREcur_1CURSORFORSELECTs1FROMt;
DECLARECONTINUEHANDLERFORNOTFOUND
SETb=1;
OPENcur_1;
REPEAT
FETCHcur_1INTOa;
UNTILb=1
ENDREPEAT;
CLOSEcur_1;
SETreturn_val=a;
END;//
我们看一下包含游标的存储过程的新例子。
2.CursorExample
CREATEPROCEDUREp25（OUTreturn_valINT）
BEGIN
DECLAREa,bINT;<--
DECLAREcur_1CURSORFORSELECTs1FROMt;
DECLARECONTINUEHANDLERFORNOTFOUND
SETb=1;
OPENcur_1;
REPEAT
FETCHcur_1INTOa;
UNTILb=1
ENDREPEAT;
CLOSEcur_1;
SETreturn_val=a;
END;//
这个过程开始声明了三个变量。
附带说一下，顺序是十分重要的。
首先要进行变量声明，然后声明条件，随后声明游标，再后面才是声明错误处理器。
如果你没有按顺序声明，系统会提示错误信息。
3.CursorExample
CREATEPROCEDUREp25（OUTreturn_valINT）
BEGIN
DECLAREa,bINT;
DECLAREcur_1CURSORFORSELECTs1FROMt;<--
DECLARECONTINUEHANDLERFORNOTFOUND
SETb=1;
OPENcur_1;
REPEAT
FETCHcur_1INTOa;
UNTILb=1
ENDREPEAT;
CLOSEcur_1;
SETreturn_val=a;
END;//
程序第二步声明了游标cur_1，如果你使用过嵌入式SQL的话，就知道这和嵌入式SQL差不多。
4.CursorExample
CREATEPROCEDUREp25（OUTreturn_valINT）
BEGIN
DECLAREa,bINT;
DECLAREcur_1CURSORFORSELECTs1FROMt;
DECLARECONTINUEHANDLERFORNOTFOUND<--
SETb=1;<--
OPENcur_1;
REPEAT
FETCHcur_1INTOa;
UNTILb=1
ENDREPEAT;
CLOSEcur_1;
SETreturn_val=a;
END;//
最后进行的是错误处理器的声明。
这个CONTINUE处理没有引用SQL错误代码和SQLSTATE值。
它使用的是NOTFOUND系统返回值，这和SQLSTATE02000是一样的。
5.CursorExample
CREATEPROCEDUREp25（OUTreturn_valINT）
BEGIN
DECLAREa,bINT;
DECLAREcur_1CURSORFORSELECTs1FROMt;
DECLARECONTINUEHANDLERFORNOTFOUND
SETb=1;
OPENcur_1;<--
REPEAT
FETCHcur_1INTOa;
UNTILb=1
ENDREPEAT;
CLOSEcur_1;
SETreturn_val=a;
END;//
过程第一个可执行的语句是OPENcur_1，它与SELECTs1FROMt语句是关联的，过程将执行SELECTs1FROMt，返回一个结果集。
6.CursorExample
CREATEPROCEDUREp25（OUTreturn_valINT）
BEGIN
DECLAREa,bINT;
DECLAREcur_1CURSORFORSELECTs1FROMt;
DECLARECONTINUEHANDLERFORNOTFOUND
SETb=1;
OPENcur_1;
REPEAT
FETCHcur_1INTOa;<--
UNTILb=1
ENDREPEAT;
CLOSEcur_1;
SETreturn_val=a;
END;//
这里第一个FETCH语句会获得一行从SELECT产生的结果集中检索出来的值，然而表t中有多行，因此这个语句会被执行多次，当然这是因为语句在循环块内。
升级会员 