仓酷云
标题:
公布MySQL 5.0 新特征教程 存储历程:第四讲
[打印本页]
作者:
灵魂腐蚀
时间:
2015-1-16 20:14
标题:
公布MySQL 5.0 新特征教程 存储历程:第四讲
甚至一个有经验的Windows管理者也可以轻松部署并开始学习它，而你不需投入一分钱来了解这个数据库。MySQLAB;翻译:陈朋奕
ErrorHandling非常处置
好了，我们如今要讲的是非常处置
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
FORSQLSTATE23000SET@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
FORSQLSTATE23000SET@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
FORSQLSTATE23000SET@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
FORSQLSTATE23000SET@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
FORSQLSTATE23000SET@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
FORSQLSTATE23000SET@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
FORSQLSTATE23000SET@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
FORSQLSTATE23000SET@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`
CONDITIONFORSQLSTATE23000;
DECLAREEXITHANDLERFOR
`ConstraintViolation`ROLLBACK;
STARTTRANSACTION;
INSERTINTOt2VALUES(1);
INSERTINTOt2VALUES(1);
COMMIT;
END;//
这是别的一个毛病处置的例子，在后面的基本上修正的。现实上你可给SQLSTATE大概毛病代码其他的名字，你就能够在处置中利用本人界说的名字了。上面看它是怎样完成的：我把表t2界说为InnoDB表，以是对这个表的拔出操纵城市ROLLBACK（回滚），ROLLBACK（回滚事件）也是刚好会产生的。由于对主键拔出两个一样的值会招致SQLSTATE23000毛病产生，这里SQLSTATE23000是束缚毛病。
2.DECLARECONDITION声明前提
CREATEPROCEDUREp24()
BEGIN
DECLARE`ConstraintViolation`
CONDITIONFORSQLSTATE23000;
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中有多行，因而这个语句会被实行屡次，固然这是由于语句在轮回块内。
7.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;//
最初当MySQL的FETCH没有取得行时，CONTINUE处置被触发，将变量b赋值为1。
8.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;//
到了这一步UNTILb=1前提就为真，轮回停止。在这里我们能够本人编写代码封闭游标，也能够由体系实行，体系会在复合语句停止时主动封闭游标，可是最好不要太依附体系的主动封闭举动（译注：这大概跟java的Gc一样，不成信）。
9.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;//
这个例程中我们为输入参数指派了一个部分变量，如许在历程停止后的了局仍能利用。
10.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;//
mysql>CALLp25(@return_val)//
QueryOK,0rowsaffected(0.00sec)
mysql>SELECT@return_val//
+-------------+
|@return_val|
+-------------+
|5|
+-------------+
1rowinset(0.00sec)
下面是历程挪用后的了局。能够看到return_val参数取得了数值5，由于这是表t的最初一行。
由此能够晓得游标事情一般，堕落处置也事情一般。
CursorCharacteristics游标的特征
择要：
READONLY只读属性
NOTSCROLLABLE按次读取
ASENSITIVE敏感
在5.0版的MySQL中，你只能够从游标中取值，不克不及对其举行更新。由于游标是（READONLY）只读的。你能够如许做：
FETCHcursor1INTOvariable1;
UPDATEt1SETcolumn1=value1WHERECURRENTOFcursor1;
游标也是不成以转动的，只同意一一读取下一行，不克不及在了局会合行进或前进。上面代码就是毛病的：
FETCHPRIORcursor1INTOvariable1;
FETCHABSOLUTE55cursor1INTOvariable1;
同时也不同意在已翻开游标举行操纵的表上实行updates事件，由于游标是（ASENSITIVE）敏感的。由于假如你不制止update事件，那就不晓得了局会酿成甚么。假如你利用的是InnoDB而不是MyISAM存储引擎的话，了局也会纷歧样。
Security平安措施
择要
Privileges(1)CREATEROUTINE
Privileges(2)EXECUTE
Privileges(3)GRANTSHOWROUTINE?
Privileges(4)INVOKERSANDDEFINERS
这里我们要会商一些关于特权和平安相干的成绩。但由于在MySQL平安措施的功效并没有完整，以是我们不会对其举行过量会商。
1.PrivilegesCREATEROUTINE
GRANTCREATEROUTINE
ONdatabase-name.*
TOuser(s)
[WITHGRANTOPTION];
如今用root就能够了
在这里要先容的特权是CREATEROUTINE，它不但同其他特权一样能够创立存储历程和函数，还能够创立视图和表。Root用户具有这类特权，同时另有ALTERROUTINE特权。
2.PrivilegesEXECUTE
GRANTEXECUTEONpTOpeter
[WITHGRANTOPTION];
下面的特权是决意你是不是可使用或实行存储历程的特权，历程创立者默许具有这个特权。
3.PrivilegesSHOWROUTINE?
GRANTSHOWROUTINEONdb6.*TOjoey
[WITHGRANTOPTION];
由于我们已有把持视图的特权了：GRANTSHOWVIEW。以是在这个基本上，为了包管兼容，往后大概会增加GRANTSHOWROUTINE特权。如许做是不太切合尺度的，在写本书的时分，MySQL还没完成这个功效。
4.PrivilegesInvokersandDefiners特权挪用者和界说者
CREATEPROCEDUREp26()
SQLSECURITYINVOKER
SELECTCOUNT(*)FROMt//
CREATEPROCEDUREp27()
SQLSECURITYDEFINER
SELECTCOUNT(*)FROMt//
GRANTINSERTONdb5.*TOpeter;//
如今我们测试一下SQLSECURITY子句吧。Security是我们后面提到的程序特征的一部分。你root用户，将拔出权赋给了peter。然后利用peter上岸举行新的事情，我们看peter能够怎样利用存储历程，注重：peter没有对表t的select权利，只要root用户有。
5.PrivilegesInvokersandDefiners
/*Loggedonwithcurrent_user=peter*/利用帐户peter上岸
mysql>CALLp26();
ERROR1142(42000):selectcommanddeniedtouser
peter@localhostfortablet
mysql>CALLp27();
+----------+
|COUNT(*)|
+----------+
|1|
+----------+
1rowinset(0.00sec)
当peter实验挪用含有挪用保密措施的历程p26时会失利。那是由于peter没有对表的select的权利。
可是当petre挪用含有界说保密措施的历程时就可以乐成。缘故原由是root有select权利，Peter有root的权利，因而历程能够实行。
这些过程被存储和运行在数据库服务器上，以减少在客户端的处理过程，从而最大限度地提高了处理能力，因为通常情况下数据库服务器会运行地更快。存储过程并不是MySQL独有的功能，但是这个最近新增加的功能使得这个数据库比以前更具吸引力了。
作者:
飘灵儿
时间:
2015-1-18 19:10
无法深入到数据库系统层面去了解和探究
作者:
第二个灵魂
时间:
2015-1-25 12:49
SP4是一个累积性的ServicePack，包含自以前的ServicePack发布以来所有的修补程序（包括MS03-031安全公告）。
作者:
若相依
时间:
2015-2-2 22:07
作了些试验，发现使用CLR的存储过程或函数在达到一定的阀值的时候，系统性能会呈指数级下滑！这是非常危险的！只使用几个可能没有问题，当一旦大规模使用会造成严重的系统性能问题！
作者:
小魔女
时间:
2015-2-8 12:20
SQL语言是学习所有数据库产品的基础，无论你是做数据库管理还是做数据库开发都是这样。不过具体学习的侧重点要看你将来做哪一块，如果是做数据库管理（DBA），侧重点应该放在SQLServer的系统管理上.
作者:
透明
时间:
2015-2-25 12:44
所以你总能得到相应的升级版本，来满足你的需求。
作者:
再见西城
时间:
2015-3-7 21:37
我个人认为就是孜孜不懈的学习
作者:
变相怪杰
时间:
2015-3-15 15:02
varchar(max)\\\\nvarchar(max)类型的引入大大的提高了编程的效率，可以使用字符串函数对CLOB类型进行操作，这是一个亮点。
作者:
冷月葬花魂
时间:
2015-3-22 02:16
我是新手，正在学习数据库和操作系统，深感理论的泛广，唯有一步一步来，但是又感觉时间不够，收集了很多资料却总是没能认真的看完，希望有一个讨论板块，大家共同解决，共同分享，共同努力
欢迎光临 仓酷云 (http://ckuyun.com/)
Powered by Discuz! X3.2