存储过程之五—条件和异常处理

-- 一：使用sqlstate_value  
DECLARE  can_not_find  CONDITION  FOR  SQLSTATE  '42S02' ;  
-- 二：使用mysql_error_code  
DECLARE  can_not_find  CONDITION  FOR  1146 ;

    -- 一：捕获sqlstate_value  
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='CAN NOT FIND';  

    -- 二：捕获mysql_error_code  
    DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND';  

    -- 三：先定义条件，然后调用  
    DECLARE  can_not_find  CONDITION  FOR  SQLSTATE  '42S02' ;
    -- DECLARE  can_not_find  CONDITION  FOR  1146 ;
    DECLARE CONTINUE HANDLER FOR can_not_find SET @info='CAN NOT FIND';  
    
    -- 四：使用SQLWARNING  
    DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';  

    -- 方法五：使用NOT FOUND  
    DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND'; 
 
    -- 六：使用SQLEXCEPTION  
    DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR'; 

 1 -- ----------------------------
 2 -- Table structure for course
 3 -- ----------------------------
 4 DROP TABLE IF EXISTS `course`;
 5 CREATE TABLE `course` (
 6   `id` int(11) NOT NULL,
 7   `name` varchar(255) NOT NULL,
 8   `score` int(11) NOT NULL,
 9   PRIMARY KEY (`id`)
10 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
11 
12 
13 DROP PROCEDURE IF EXISTS proc_test_exce;
14 CREATE PROCEDURE proc_test_exce(
15     IN uid INT(11),
16     IN uname VARCHAR(255),
17     IN uscore INT(11),
18     OUT result INT(11)
19 )
20 BEGIN 
21     -- DECLARE EXIT HANDLER FOR SQLSTATE '23000' set result = -1; 
22     DECLARE EXIT HANDLER FOR SQLWARNING,NOT FOUND,SQLEXCEPTION set result=-1; 
23     START TRANSACTION;        
24         INSERT INTO course (id, name, score) VALUES(uid, uname, uscore);
25         set result = 1;  
26     COMMIT; 
27 END;
28 
29 -- id 相同则返回-1
30 CALL proc_test_exce(3 ,'中文', 34, @result);
31 SELECT @result;