1. 语法:
<div class=”cnblogs_code”>
condition_value handler_action:
<span style="color: #0000ff;">CONTINUE
<span style="color: #808080;">| <span style="color: #0000ff;">EXIT
<span style="color: #808080;">|<span style="color: #000000;"> UNDOcondition_value:
mysql_error_code
<span style="color: #808080;">| SQLSTATE <span style="color: #ff0000;">[<span style="color: #ff0000;">VALUE<span style="color: #ff0000;">]<span style="color: #000000;"> sqlstate_value
<span style="color: #808080;">|<span style="color: #000000;"> condition_name
<span style="color: #808080;">|<span style="color: #000000;"> SQLWARNING
<span style="color: #808080;">| <span style="color: #808080;">NOT<span style="color: #000000;"> FOUND
<span style="color: #808080;">| SQLEXCEPTION
If one of these conditions occurs,the specifiedstatementexecutes.statementcan be a simple statement such asvar_name
=value,or a compound statement written usingand
http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html
2. 实例
实例1:
COMMENT COMMENT INNODB CHARSETUTF8 COMMENT
<span style="color: #0000ff;">DROP <span style="color: #0000ff;">PROCEDURE <span style="color: #0000ff;">IF <span style="color: #808080;">EXISTS<span style="color: #000000;"> sp_do_insert;
<span style="color: #0000ff;">CREATE <span style="color: #0000ff;">PROCEDURE<span style="color: #000000;"> sp_do_insert(
<span style="color: #808080;">IN in_param1 <span style="color: #0000ff;">int<span style="color: #000000;">,<span style="color: #808080;">IN in_param2 <span style="color: #0000ff;">int<span style="color: #000000;">,OUT out_status <span style="color: #0000ff;">tinyint<span style="color: #000000;">
)
<span style="color: #0000ff;">BEGIN
<span style="color: #0000ff;">DECLARE <span style="color: #0000ff;">CONTINUE HANDLER <span style="color: #0000ff;">FOR <span style="color: #800000; font-weight: bold;">1062 <span style="color: #0000ff;">SET out_status <span style="color: #808080;">= <span style="color: #800000; font-weight: bold;">1<span style="color: #000000;">;
<span style="color: #0000ff;">SET out_status <span style="color: #808080;">= <span style="color: #800000; font-weight: bold;">0<span style="color: #000000;">;
<span style="color: #0000ff;">INSERT <span style="color: #0000ff;">INTO<span style="color: #000000;"> tb1(field1,field2)
<span style="color: #0000ff;">VALUES<span style="color: #000000;"> (in_param1,in_param2);
<span style="color: #0000ff;">IF out_status<span style="color: #808080;">=<span style="color: #800000; font-weight: bold;">1 <span style="color: #0000ff;">THEN
<span style="color: #0000ff;">select<span style="color: #000000;"> in_param1,in_param2;
<span style="color: #0000ff;">END <span style="color: #0000ff;">IF<span style="color: #000000;">;
<span style="color: #0000ff;">END<span style="color: #000000;">;
CALL sp_do_insert(<span style="color: #800000; font-weight: bold;">2,<span style="color: #800000; font-weight: bold;">200,<span style="color: #008000;">@out<span style="color: #000000;">);
<span style="color: #0000ff;">SELECT <span style="color: #008000;">@out;
实例2:begin 。。。end块异常处理
<span style="color: #0000ff;">CREATE <span style="color: #0000ff;">TABLE<span style="color: #000000;"> tb1(
field1 <span style="color: #0000ff;">INT <span style="color: #808080;">NOT <span style="color: #0000ff;">NULL COMMENT <span style="color: #ff0000;">'<span style="color: #ff0000;">id<span style="color: #ff0000;">'<span style="color: #000000;">,<span style="color: #0000ff;">PRIMARY <span style="color: #0000ff;">KEY<span style="color: #000000;">(field1)
)ENGINE<span style="color: #808080;">=INNODB <span style="color: #0000ff;">DEFAULT CHARSET<span style="color: #808080;">=UTF8 COMMENT <span style="color: #808080;">=<span style="color: #ff0000;">'<span style="color: #ff0000;">临时测试表<span style="color: #ff0000;">'
<span style="color: #0000ff;">DROP <span style="color: #0000ff;">PROCEDURE <span style="color: #0000ff;">IF <span style="color: #808080;">EXISTS<span style="color: #000000;"> sp_do_insert;
<span style="color: #0000ff;">CREATE <span style="color: #0000ff;">PROCEDURE<span style="color: #000000;"> sp_do_insert(
<span style="color: #808080;">IN in_param1 <span style="color: #0000ff;">int<span style="color: #000000;">,<span style="color: #808080;">IN in_param3 <span style="color: #0000ff;">int<span style="color: #000000;">,<span style="color: #808080;">IN in_param4 <span style="color: #0000ff;">int<span style="color: #000000;">,OUT out_status1 <span style="color: #0000ff;">tinyint<span style="color: #000000;">,OUT out_status2 <span style="color: #0000ff;">tinyint<span style="color: #000000;">
)
<span style="color: #0000ff;">BEGIN
<span style="color: #0000ff;">BEGIN</span>
<span style="color: #0000ff;">DECLARE <span style="color: #0000ff;">CONTINUE HANDLER <span style="color: #0000ff;">FOR <span style="color: #800000; font-weight: bold;">1062 <span style="color: #0000ff;">SET out_status1 <span style="color: #808080;">= <span style="color: #800000; font-weight: bold;">1<span style="color: #000000;">;
<span style="color: #0000ff;">SET out_status1 <span style="color: #808080;">= <span style="color: #800000; font-weight: bold;">0<span style="color: #000000;">;
<span style="color: #0000ff;">INSERT <span style="color: #0000ff;">INTO tb1(field1,field2)<span style="color: #0000ff;">VALUES<span style="color: #000000;"> (in_param1,in_param2);
<span style="color: #0000ff;">IF out_status1<span style="color: #808080;">=<span style="color: #800000; font-weight: bold;">1 <span style="color: #0000ff;">THEN
<span style="color: #0000ff;">select</span><span style="color: #000000;"> in_param1,in_param2;
</span><span style="color: #0000ff;">select</span> <span style="color: #ff0000;">'</span><span style="color: #ff0000;">1062--------11</span><span style="color: #ff0000;">'</span> <span style="color: #0000ff;">AS</span> <span style="color: #ff0000;">'</span><span style="color: #ff0000;">first result</span><span style="color: #ff0000;">'</span><span style="color: #000000;">;
<span style="color: #0000ff;">END <span style="color: #0000ff;">IF<span style="color: #000000;">;
<span style="color: #0000ff;">END<span style="color: #000000;">;
</span><span style="color: #0000ff;">BEGIN</span>
<span style="color: #0000ff;">DECLARE</span> <span style="color: #0000ff;">CONTINUE</span> HANDLER <span style="color: #0000ff;">FOR</span> <span style="color: #800000; font-weight: bold;">1062</span> <span style="color: #0000ff;">SET</span> out_status2 <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">;
<span style="color: #0000ff;">SET out_status2 <span style="color: #808080;">= <span style="color: #800000; font-weight: bold;">0<span style="color: #000000;">;