二十四、MySQL基础系列笔记之异常处理

作者: 温新

分类: 【MySql】

阅读: 2425

时间: 2020-11-18 16:31:47

什么是异常

所写的程序在执行过程中可能会出错，运行时的错误就是异常。

在MySQL中，当存储过程运行出错时，存储过程终止执行并抛出错误信息。

什么情况下需要异常处理

插入数据违反唯一约束；
插入或更新的数据超过字段最大长度；
更新数据与期望的结果不一致。如更新1条数据，却显示更新了5条。

定义异常处理条件

定义异常条件语法

DECLARE 条件名称 CONDITION FOR [条件类型]

条件名称：自定义的名称，建议见名知意

条件类型：mysql_error_code | SQLSTATE sqlstate_value

mysql_error_code和sqlstate_value，sqlstate_value是长度为5的字符串类型错误代码，mysql_error_code为数值类型错误代码。如ERROR 1142(42000)中，sqlstate_value的值是42000，mysql_error_code的值是1142。

定义异常条件简单案例

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 方式一</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">DECLATE notAllowed <span style="box-sizing: border-box;color: rgb(119, 0, 136)">CONDITION</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">FOR</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">SQLSTATE</span> <span style="box-sizing: border-box;color: rgb(34, 162, 201)">'42000'</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 方式二</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">DECLARE</span> notAllowed <span style="box-sizing: border-box;color: rgb(119, 0, 136)">CONDITION</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">FOR</span> <span style="box-sizing: border-box;color: rgb(17, 102, 68)">1142</span>;</span>

定义条件处理程序语法

DECLARE 处理类型 HANDLER FOR 条件类型

处理类型：CONTINUE、EXIT

CONTINUE：表示遇到错误不处理，继续执行；

EXIT：表示遇到错误立即退出；

条件类型：其取值为SQLSTATE [VALUE] sqlstate_value、condition_name、SQLWARNING、NOT FOUND、SQLEXCEPTION、mysql_error_code

SQLSTATE[VALUE] sqlstate_value 包含5个字符串的字符串错误值；
condition_name表示DECLARE CONDITION定义的错误条件名称;
SQLWARNING匹配所有以01开头的SQLSTATE错误代码;
NOT FOUND 匹配所有以02开头的SQLSTATE错误代码。主要用于游标或select into 变量名；
SQLEXCEPTION匹配所有没有被SQLWARNING捕获的SQLSTAT错误代码；
mysql_error_code匹配数值类型错误代码

定义异常处理程序案例方式

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 方式一 捕获sqlstate_value</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">DECLARE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">CONTINUE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">HANDLER</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">FOR</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">SQLSTATE</span> <span style="box-sizing: border-box;color: rgb(34, 162, 201)">'42S02'</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">SET</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@info</span>=<span style="box-sizing: border-box;color: rgb(34, 162, 201)">'noSuchTable'</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 方式二 捕获mysql_error_code</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">DECLARE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">CONTINUE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">HANDLER</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">FOR</span> <span style="box-sizing: border-box;color: rgb(17, 102, 68)">1146</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">SET</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@info</span> = <span style="box-sizing: border-box;color: rgb(34, 162, 201)">'noSuchTable'</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 方式三 先定义条件</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">DECLARE</span> noSuchTable <span style="box-sizing: border-box;color: rgb(119, 0, 136)">CONDITION</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">FOR</span> <span style="box-sizing: border-box;color: rgb(17, 102, 68)">1146</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">DECLARE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">CONTINUE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">HANDLER</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">FOR</span> noSuchTable <span style="box-sizing: border-box;color: rgb(119, 0, 136)">SET</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@info</span>=<span style="box-sizing: border-box;color: rgb(34, 162, 201)">'noSuchTable'</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 方式四 SQLWARNING</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">DECLARE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">EXIT</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">HANDLER</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">FOR</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">SQLWARNING</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">SET</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@info</span>=<span style="box-sizing: border-box;color: rgb(34, 162, 201)">'error'</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 方式五 NOT FOUND</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">DECLARE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">EXIT</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">HANDLER</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">FOR</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">NOT</span> FOUND <span style="box-sizing: border-box;color: rgb(119, 0, 136)">SET</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@info</span>=<span style="box-sizing: border-box;color: rgb(34, 162, 201)">'not found'</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 方式六 SQLEXCEPTION</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">DECLARE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">EXIT</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">HANDLER</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">FOR</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">SQLEXCEPTION</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">SET</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@info</span>=<span style="box-sizing: border-box;color: rgb(34, 162, 201)">'sqlexception'</span>;</span>

异常处理案例

创建表

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">CREATE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">TABLE</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">`user`</span> (</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">  <span style="box-sizing: border-box;color: rgb(0, 85, 170)">`id`</span> <span style="box-sizing: border-box;color: rgb(51, 0, 170)">int</span>(<span style="box-sizing: border-box;color: rgb(17, 102, 68)">10</span>) <span style="box-sizing: border-box;color: rgb(51, 0, 170)">unsigned</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">NOT</span> <span style="box-sizing: border-box;color: rgb(34, 17, 153)">NULL</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">AUTO_INCREMENT</span>,</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">  <span style="box-sizing: border-box;color: rgb(0, 85, 170)">`name`</span> <span style="box-sizing: border-box;color: rgb(51, 0, 170)">varchar</span>(<span style="box-sizing: border-box;color: rgb(17, 102, 68)">60</span>) <span style="box-sizing: border-box;color: rgb(119, 0, 136)">COLLATE</span> utf8_unicode_ci,</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">  <span style="box-sizing: border-box;color: rgb(0, 85, 170)">`email`</span> <span style="box-sizing: border-box;color: rgb(51, 0, 170)">varchar</span>(<span style="box-sizing: border-box;color: rgb(17, 102, 68)">40</span>) <span style="box-sizing: border-box;color: rgb(119, 0, 136)">COLLATE</span> utf8_unicode_ci <span style="box-sizing: border-box;color: rgb(119, 0, 136)">DEFAULT</span> <span style="box-sizing: border-box;color: rgb(34, 17, 153)">NULL</span>,</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">  <span style="box-sizing: border-box;color: rgb(119, 0, 136)">PRIMARY</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">KEY</span> (<span style="box-sizing: border-box;color: rgb(0, 85, 170)">`id`</span>),</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">  <span style="box-sizing: border-box;color: rgb(119, 0, 136)">KEY</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">`name_and_email`</span> (<span style="box-sizing: border-box;color: rgb(0, 85, 170)">`name`</span>(<span style="box-sizing: border-box;color: rgb(17, 102, 68)">20</span>),<span style="box-sizing: border-box;color: rgb(0, 85, 170)">`email`</span>(<span style="box-sizing: border-box;color: rgb(17, 102, 68)">20</span>))</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">) <span style="box-sizing: border-box;color: rgb(119, 0, 136)">ENGINE</span>=<span style="box-sizing: border-box;color: rgb(119, 0, 136)">InnoDB</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">DEFAULT</span> <span style="box-sizing: border-box;color: rgb(255, 85, 0)">CHARSET</span>=utf8;</span>

案例一、使用存储过程创建2张相同的表

错误案例

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">DROP</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">PROCEDURE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">IF</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">EXISTS</span> demo03//</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">create</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">procedure</span> demo03()</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">begin</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">create</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">table</span> t1(id <span style="box-sizing: border-box;color: rgb(51, 0, 170)">int</span>);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">create</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">table</span> t1(id <span style="box-sizing: border-box;color: rgb(51, 0, 170)">int</span>);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">select</span> <span style="box-sizing: border-box;color: rgb(34, 162, 201)">'ziruchu'</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">end</span>//</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">call</span> demo03()//</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">ERROR <span style="box-sizing: border-box;color: rgb(17, 102, 68)">1050</span> (<span style="box-sizing: border-box;color: rgb(17, 102, 68)">42</span>S01): <span style="box-sizing: border-box;color: rgb(119, 0, 136)">Table</span> <span style="box-sizing: border-box;color: rgb(34, 162, 201)">'t1'</span> already <span style="box-sizing: border-box;color: rgb(119, 0, 136)">exists</span></span>

调用存储过程，由于报错导致 t1表后面的select语句没有执行，若要后面的select语句执行，就需需要忽略报错的地方，然后顺利执行select语句，这就使用到了mysql异常处理。

正确案例

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 方式一、定义条件结合条件异常处理</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">delimiter</span> //</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">DROP</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">PROCEDURE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">IF</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">EXISTS</span> demo03//</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">create</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">procedure</span> demo03()</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">begin</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 定义条件</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">declare</span> existed <span style="box-sizing: border-box;color: rgb(119, 0, 136)">condition</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">for</span> <span style="box-sizing: border-box;color: rgb(17, 102, 68)">1050</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 条件异常处理,并定义一个变量</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">declare</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">continue</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">handler</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">for</span> existed <span style="box-sizing: border-box;color: rgb(119, 0, 136)">set</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@fail</span> = <span style="box-sizing: border-box;color: rgb(17, 102, 68)">1</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> </span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">create</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">table</span> t1(id <span style="box-sizing: border-box;color: rgb(51, 0, 170)">int</span>);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">create</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">table</span> t1(id <span style="box-sizing: border-box;color: rgb(51, 0, 170)">int</span>);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">select</span> <span style="box-sizing: border-box;color: rgb(34, 162, 201)">'ziruchu'</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">end</span>//</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 调用存储过程</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 可以看到，错误被忽略，ziruchu被输出</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">call</span> demo03()//</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 方式二、直接使用条件处理程序</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">DROP</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">PROCEDURE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">IF</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">EXISTS</span> demo03//</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">create</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">procedure</span> demo03()</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">begin</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">declare</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">exit</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">handler</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">for</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">sqlstate</span> <span style="box-sizing: border-box;color: rgb(34, 162, 201)">'42S01'</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">select</span> <span style="box-sizing: border-box;color: rgb(34, 162, 201)">'42S01异常'</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">create</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">table</span> t1(id <span style="box-sizing: border-box;color: rgb(51, 0, 170)">int</span>);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">create</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">table</span> t1(id <span style="box-sizing: border-box;color: rgb(51, 0, 170)">int</span>);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">select</span> <span style="box-sizing: border-box;color: rgb(34, 162, 201)">'ziruchu'</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">end</span>//</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">call</span> demo03()//</span>

方式一：使用continue关键字，即遇到错误并跳过它，执行后面的程序，因此就可以看到 ziruchu 被输出了。

方式二：使用exit关键字，即遇到错误立即停止后面的程序执行，且遇到错误后执行条件异常处理程序后的一个语句，并终止后面的程序执行，因此方式二输出了 '42S01异常'

测试

<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">call</span> demo0()//</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">ERROR <span style="box-sizing: border-box;color: rgb(17, 102, 68)">1062</span> (<span style="box-sizing: border-box;color: rgb(17, 102, 68)">23000</span>): Duplicate entry <span style="box-sizing: border-box;color: rgb(34, 162, 201)">'1'</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">for</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">key</span> <span style="box-sizing: border-box;color: rgb(34, 162, 201)">'PRIMARY'</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">delimiter</span> //</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">DROP</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">PROCEDURE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">IF</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">EXISTS</span> demo04//</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">create</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">procedure</span> demo04()</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">begin</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">declare</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">continue</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">handler</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">for</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">SQLSTATE</span> <span style="box-sizing: border-box;color: rgb(34, 162, 201)">'23000'</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">set</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@a</span>=<span style="box-sizing: border-box;color: rgb(17, 102, 68)">1</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">insert</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">into</span> t1 <span style="box-sizing: border-box;color: rgb(119, 0, 136)">values</span> (<span style="box-sizing: border-box;color: rgb(17, 102, 68)">1</span>);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">insert</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">into</span> t1 <span style="box-sizing: border-box;color: rgb(119, 0, 136)">values</span> (<span style="box-sizing: border-box;color: rgb(17, 102, 68)">1</span>);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">end</span>//</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">delimiter</span> //</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">DROP</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">PROCEDURE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">IF</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">EXISTS</span> demo04//</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">create</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">procedure</span> demo04()</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">begin</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">      declare</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">continue</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">handler</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">for</span> <span style="box-sizing: border-box;color: rgb(17, 102, 68)">1062</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">set</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@a</span>=<span style="box-sizing: border-box;color: rgb(17, 102, 68)">1</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">      insert</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">into</span> t1 <span style="box-sizing: border-box;color: rgb(119, 0, 136)">values</span> (<span style="box-sizing: border-box;color: rgb(17, 102, 68)">1</span>);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">      insert</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">into</span> t1 <span style="box-sizing: border-box;color: rgb(119, 0, 136)">values</span> (<span style="box-sizing: border-box;color: rgb(17, 102, 68)">1</span>);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">      select</span> <span style="box-sizing: border-box;color: rgb(34, 162, 201)">'ziruchu'</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">end</span>//</span>

注意：SQLSTATE后的错误码必须是字符串，因此必须加引号；for后直接跟mysql_error_code，错误码必须是数值。

我是小白，期待和优秀的你一起同行!

小白

2020年11月19日

请登录后再评论