MySQL中的异常处理,游标

一、异常处理的理解
ERROR 1062 (23000): Duplicate entry ‘2’ for key ‘PRIMARY’
错误 4位error code（5位sql statis）：错误内容

二、异常处理的重要性
没有异常处理的存储过程，执行过程中非常难以预测执行结果。
建议：存储过程中加上异常处理部分。

三、异常处理的实现

异常处理的格式：
DECLARECONTINUE/EXITHANDLER FORSQLSTATE ‘23000’ （错误代码）
1.错误是什么？
eg：1062（23000）
2.怎么处理错误？
先执行SQL，再执行EXIT/CONTINUE

小结：
针对什么错误，首先执行SQL语句，可以是一个begin..end;语句块；
根据是continue还是exit，确定是接着执行还是退出begin..end；
接着执行的话，就是接着执行出错的SQL的下一条语句；
如果是退出，就退出这个declare所在的begin…end。

例子1：

DELIMITER $$CREATE  PROCEDURE small_mistake1(    OUT error VARCHAR(5))    BEGIN   DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'   --》这是个异常     SET error = '23000';      //用来记录错误发生时的一些信息，异常捕获、处理     select error;     SET error = '00000';     select error;   INSERT INTO TEAMS VALUES(2,27,'third');   SET error = '23001';       END$$DELIMITER ;

执行结果：

mysql> call small_mistake1(@a);    --》上来直接就是select error，因为先执行sql+-------+| error |+-------+| NULL  |+-------+1 row in set (0.00 sec)+-------+| error |+-------+| 00000 |+-------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)

mysql> select @a;    --》+-------+| @a    |+-------+| 23001 |+-------+1 row in set (0.00 sec)

===例子2：

CREATE TABLE test.t (s1 INT, PRIMARY KEY (s1));delimiter $$CREATE PROCEDURE handlerdemo ()BEGIN  DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;    SET @x = 1;  INSERT INTO test.t VALUES (1);   ---》能执行  SET @x = 2;            --》报错，因为@x==1  INSERT INTO test.t VALUES (1);  SET @x = 3;END$$delimiter ;CALL handlerdemo()

执行结果：

mysql> select @x2;        --》捕获到异常，就令x2=1+------+| @x2  |+------+|    1 |+------+1 row in set (0.00 sec)mysql> select @x;        --》set @x=3+------+| @x   |+------+|    3 |+------+1 row in set (0.00 sec)

===例子3：

DELIMITER $$CREATE  PROCEDURE small_mistake2(    OUT error VARCHAR(5))    BEGIN   DECLARE EXIT HANDLER FOR SQLSTATE '23000'      --》EXIT直接退出begin..end     SET error = '23000';        select error;     SET error = '00000';     select error;   INSERT INTO TEAMS VALUES(2,27,'third');  --》此语句出错，捕获到异常后，因为是exit，所以不会再执行下面的set error='23001'语句   SET error = '23001';       END$$DELIMITER ;mysql> call small_mistake2(@a);    --》先执行sql+-------+| error |+-------+| NULL  |+-------+1 row in set (0.00 sec)    --》捕获到异常+-------+| error |+-------+| 00000 |+-------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> select @a;            ---》证明没有执行set error='23001'+-------+| @a    |+-------+| 23000 |+-------+1 row in set (0.00 sec)

===例子4：一个begin后面可以接多个DECLARE

DELIMITER $$CREATE  PROCEDURE small_mistake3(    OUT error VARCHAR(5))    BEGIN   DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'       SET error = '23000';      DECLARE CONTINUE HANDLER FOR SQLSTATE '21S01'       SET error = '21S01';   INSERT INTO TEAMS VALUES(2,27,'third',5);     END$$DELIMITER ;mysql> call small_mistake3(@error);Query OK, 0 rows affected (0.00 sec)mysql> select @error;+--------+| @error |+--------+| 21S01  |+--------+1 row in set (0.00 sec)

四、错误捕获快捷方式
异常处理的好处：
①出错不报错
②出错可以进行处理；记录出错时的一些信息
③处理所有的错误：
===例子1：

DELIMITER $$CREATE  PROCEDURE small_mistake5(    OUT error VARCHAR(5))    BEGIN   DECLARE CONTINUE HANDLER FOR SQLWARNING,NOT FOUND,SQLEXCEPTION  ---》错误是1开头的赋给SQLWARNING，2开头的NOT FOUND，其他给SQLEXCEPTION     SET error = 'xxxxx';      INSERT INTO teams VALUES(2,27,'third');     END$$DELIMITER ;mysql> call small_mistake5(@a);Query OK, 0 rows affected (0.00 sec)mysql> select @a;+-------+| @a    |+-------+| xxxxx |+-------+1 row in set (0.00 sec)

===忽略一个错误：
忽略一个条件

DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END; 

–》碰到1开头的就过。

五、异常处理嵌套问题
例子1：增加程序的可读性。

DELIMITER $$CREATE  PROCEDURE small_mistake6(    OUT error VARCHAR(5))    BEGIN   DECLARE non_unique CONDITION FOR SQLSTATE '23000';      DECLARE CONTINUE HANDLER FOR non_unique     begin       SET error = '23000';     select error;     end;        INSERT INTO TEAMS VALUES(2,27,'third');    END$$DELIMITER ;mysql> call small_mistake6(@error);+-------+| error |+-------+| 23000 |+-------+1 row in set (0.01 sec)

例子2：异常处理的嵌套

DELIMITER $$CREATE  PROCEDURE small_mistake7()    BEGIN        DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'       SET @processed = 100;      BEGIN             DECLARE CONTINUE HANDLER FOR SQLSTATE '21000'            SET @processed = 200;           INSERT INTO TEAMS VALUES(2,27,'third');    --》出错，假设能被内层的捕获，就执行200；若不能被内层捕获，内层的begin...end就废了，就执行外层100   END; END$$DELIMITER ;mysql> call small_mistake7;Query OK, 0 rows affected (0.00 sec)mysql> select @processed;+------------+| @processed |+------------+|        100 |+------------+1 row in set (0.00 sec)

====小结
当有多层begin…end的时候，最好每层都有自己完善的异常处理。自己异常，自己这层去处理。

六、游标 CURSOR
游标：处理结果集。多行多列
====例子1：

DELIMITER $$CREATE  PROCEDURE number_of_players(  OUT pnumber INTEGER)    BEGIN    DECLARE a_playerno INTEGER;    --》变量1   DECLARE FOUND BOOLEAN DEFAULT TRUE;    --》变量2   DECLARE c_players CURSOR FOR    --》声明游标（将游标和sql语句关联起来）     SELECT playerno FROM PLAYERS;   DECLARE CONTINUE HANDLER FOR NOT FOUND  --》异常处理，所有的以2开头的错误     SET FOUND = FALSE;     --》异常处理后FOUND变为falseSET pnumber = 0;   OPEN c_players;    --》打开游标（将游标和结果集联系起来）   FETCH c_players INTO a_playerno;    --》fetch...into相当于select into   WHILE FOUND DO     SET pnumber = pnumber + 1;     FETCH c_players INTO a_playerno;    --》循环中的fetch...into，依次指向结果集的一个   END WHILE;       CLOSE c_players; END$$DELIMITER ;mysql> call number_of_players(@pnumber);Query OK, 0 rows affected (0.00 sec)mysql> select @pnumber;+----------+| @pnumber |+----------+|       14 |+----------+1 row in set (0.00 sec)

游标处理方式小结：

• 定义游标：将一个游标和一个select进行关联；
• 打开游标：将一个游标和一个结果集关联，执行了select；
• 获取游标（获取结果集）：需要使用循环进行游标的获取。

{注意：当获取到最后一个结果集时，再次执行循环的时候，会报错，这个错误以2开头，
这个时候，需要定义一个对2开头的错误的捕获：

DECLARE CONTINUE HANDLER FOR NOT FOUND      SET FOUND = FALSE; }

• 关闭游标：结果集消失
• 资源释放。

七、存储过程权限问题

mysql>select ROUTINE_NAME,ROUTINE_SCHEMA,ROUTINE_TYPE from ROUTINES where ROUTINE_SCHEMA in('test','TENNIS');GRANT  EXECUTE             --》授予执行权限   ON PROCEDURE  number_penalties   TO  'u1'@'%';[root@mysqlstudy ~]# mysql -uu1 -p12345678mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 7Server version: 5.7.10 MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use TENNIS;Database changedmysql> call number_penalties(44,@pnumber);Query OK, 0 rows affected (0.01 sec)mysql> select @pnumber;+----------+| @pnumber |+----------+|        3 |+----------+1 row in set (0.00 sec)