• 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变为false
    SET 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 -p12345678
    mysql: [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 7
    Server 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 its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
    
    mysql> use TENNIS;
    Database changed
    mysql> 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)
  • 相关阅读:
    CodeForces 58C Trees
    【转】二分匹配题集
    HDU2604 Queuing
    HDU1281 棋盘游戏
    HDU3360 National Treasures
    HDU2444 The Accomodation of Students
    HDU1498 50 years, 50 colors
    HDU1068 Girls and Boys
    【转】常用的latex宏包
    【转】网络流题集
  • 原文地址:https://www.cnblogs.com/lpeng94/p/12546473.html
Copyright © 2020-2023  润新知