mysql 异常处理实例_mysql

概述1. 语法： DECLARE handler_action HANDLERFOR condition_value [, condition_value] ...statementhandler_action:CONTINUE| EXIT| UNDOcondition_value:mysql_error_code| SQLSTATE [VALUE] sqlstate_value| condition_name| SQLWARNING| NOT FOUND| SQLEXCEPTION If one of these conditions occurs, the specified statement executes. statement can be a simple statement such as SETvar_name = value, or a compound statement written using BEGIN and END.http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html2. 实例实例1：DROP TABLE IF EXISTS tb1;CREATE TABLE tb1(field1 INT NOT NULL COMMENT 'id',field2 INT COMMENT 'value',PRIMARY KEY(field1))ENGINE=INNODB DEFAULT CHARSET=UTF8 COMMENT ='临时测试表'DROP PROCEDURE IF EXISTS sp_do_insert;CREATE PROCEDURE sp_do_insert(IN in_param1 int,IN in_param2 int,OUT out_status tinyint)BEGINDECLARE CONTINUE HANDLER FOR 1062 SET out_status = 1;SET out_status = 0;INSERT INTO tb1(field1, field2)VALUES (in_param1, in_param2);IF out_status=1 THENselect in_param1, in_param2;END IF;END;CALL sp_do_insert(2,200,@out);SELECT @out;实例2：begin 。。。end块异常处理DROP TABLE IF EXISTS tb1;CREATE TABLE tb1(field1 INT NOT NULL COMMENT 'id',field2 INT COMMENT 'value',PRIMARY KEY(field1))ENGINE=INNODB DEFAULT CHARSET=UTF8 COMMENT ='临时测试表'DROP PROCEDURE IF EXISTS sp_do_insert;CREATE PROCEDURE sp_do_insert(IN in_param1 int,IN in_param2 int,IN in_param3 int,IN in_param4 int,OUT out_status1 tinyint,OUT out_status2 tinyint)BEGINBEGINDECLARE CONTINUE HANDLER FOR 1062 SET out_status1 = 1;SET out_status1 = 0;INSERT INTO tb1(field1, field2)VALUES (in_param1, in_param2);IF out_status1=1 THENselect in_param1, in_param2;select '1062--------11' AS 'first result';END IF;END;BEGINDECLARE CONTINUE HANDLER FOR 1062 SET out_status2 = 1;SET out_status2 = 0;INSERT INTO tb1(field1, field2)VALUES (in_param3, in_param4);IF out_status2=1 THENselect in_param3, in_param4;select '1062--------22' AS 'secord result';END IF;END;END;CALL sp_do_insert(1,100,2,200,@out1,@out2);SELECT @out1,@out2;select * from tb1; 3. server error codehttp://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html 常见errorcode Error: 1172 SQLSTATE: 42000 (ER_TOO_MANY_ROWS)Message: Result consisted of more than one row Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY)Message: Duplicate entry '%s' for key %d Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)Message: No data - zero rows fetched, selected, or processed

1. 语法：

<div >

 condition_value handler_action:
<span >CONTINUE
<span >| <span >EXIT
<span >|<span > UNDO
condition_value:
MysqL_error_code
<span >| sqlSTATE <span >[<span >VALUE<span >]<span > sqlstate_value
<span >|<span > condition_name
<span >|<span > sqlWARNING
<span >| <span >NOT<span > FOUND
<span >| sqlEXCEPTION

If one of these conditions occurs,the specifIEd statement executes. statement can be a simple statement such as var_name = value,or a compound statement written using and

http://dev.MysqL.com/doc/refman/5.0/en/declare-handler.HTML

2. 实例

实例1：

       COMMENT  COMMENT  INNODB  CHARSETUTF8 COMMENT <span >DROP <span >PROCEDURE <span >IF <span >EXISTS<span > sp_do_insert;
<span >CREATE <span >PROCEDURE<span > sp_do_insert(
<span >IN in_param1    <span >int<span >,<span >IN in_param2    <span >int<span >,OUT out_status  <span >tinyint<span >
)
<span >BEGIN
<span >DECLARE <span >CONTINUE HANDLER <span >FOR <span >1062 <span >SET out_status <span >= <span >1<span >;
<span >SET out_status <span >= <span >0<span >;
<span >INSERT <span >INTO<span > tb1(fIEld1,fIEld2)
<span >VALUES<span > (in_param1,in_param2);
<span >IF out_status<span >=<span >1 <span >THEN
<span >select<span > in_param1,in_param2;
<span >END <span >IF<span >;
<span >END<span >;
CALL sp_do_insert(<span >2,<span >200,<span >@out<span >);
<span >SELECT <span >@out;
实例2：begin 。。。end块异常处理
   <span >CREATE <span >table<span > tb1(
fIEld1 <span >INT <span >NOT <span >NulL COMMENT <span >'<span >ID<span >'<span >,<span >PRIMARY <span >KEY<span >(fIEld1)
)ENGINE<span >=INNODB <span >DEFAulT CHARSET<span >=UTF8 COMMENT <span >=<span >'<span >临时测试表<span >'
<span >DROP <span >PROCEDURE <span >IF <span >EXISTS<span > sp_do_insert;
<span >CREATE <span >PROCEDURE<span > sp_do_insert(
<span >IN in_param1    <span >int<span >,<span >IN in_param3    <span >int<span >,<span >IN in_param4    <span >int<span >,OUT out_status1  <span >tinyint<span >,OUT out_status2  <span >tinyint<span >
)
<span >BEGIN
     <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;BEGIN</span>
<span >DECLARE <span >CONTINUE HANDLER <span >FOR <span >1062 <span >SET out_status1 <span >= <span >1<span >;
<span >SET out_status1 <span >= <span >0<span >;
<span >INSERT <span >INTO tb1(fIEld1,fIEld2)<span >VALUES<span > (in_param1,in_param2);
<span >IF out_status1<span >=<span >1 <span >THEN
    <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;select</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; in_p<a href="https://www.jb51.cc/tag/ara/" target="_blank" >ara</a>m1,in_p<a href="https://www.jb51.cc/tag/ara/" target="_blank" >ara</a>m2;                                 </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;select</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;"&gt;'</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;"&gt;1062--------11</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;"&gt;'</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;AS</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;"&gt;'</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;"&gt;f<a href="https://www.jb51.cc/tag/irs/" target="_blank" >irs</a>t res<a href="https://m.jb51.cc/tag/ul/" target="_blank" >ul</a>t</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;"&gt;'</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;;
<span >END <span >IF<span >;
<span >END<span >;
     </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;BEGIN</span>     <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;DECLARE</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;CONTINUE</span> HANDLER <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;FOR</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;"&gt;1062</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;SET</span> out_status2 <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;"&gt;=</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;"&gt;1</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;;
<span >SET out_status2 <span >= <span >0<span >;
<span >INSERT <span >INTO tb1(fIEld1,fIEld2)<span >VALUES<span > (in_param3,in_param4);
<span >IF out_status2<span >=<span >1 <span >THEN
    <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;select</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; in_p<a href="https://www.jb51.cc/tag/ara/" target="_blank" >ara</a>m3,in_p<a href="https://www.jb51.cc/tag/ara/" target="_blank" >ara</a>m4;                                 </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;select</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;"&gt;'</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;"&gt;1062--------22</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;"&gt;'</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;AS</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;"&gt;'</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;"&gt;secord res<a href="https://m.jb51.cc/tag/ul/" target="_blank" >ul</a>t</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;"&gt;'</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;;
<span >END <span >IF<span >;
     </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;END</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;;
<span >END<span >;
CALL sp_do_insert(<span >1,<span >100,<span >2,<span >@out1,<span >@out2<span >);
<span >SELECT <span >@out1,<span >@out2<span >;
<span >select <span >* <span >from tb1;
3. server error code
http://dev.MysqL.com/doc/refman/5.5/en/error-messages-server.HTML 
常见errorcode
 Error:  sqlSTATE:  ()
Message: Result consisted of more than one row
 Error:  sqlSTATE:  ()
Message: Duplicate entry '%s' for key %d
 Error:  sqlSTATE:  ()
Message: No data - zero rows fetched,selected,or processed
          总结       以上是内存溢出为你收集整理的mysql 异常处理实例全部内容，希望文章能够帮你解决mysql 异常处理实例所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。					
										


					
						欢迎分享，转载请注明来源：内存溢出
原文地址: http://outofmemory.cn/sjk/1169147.html

mysql 异常处理实例

发表评论

评论列表（0条）