- 浏览: 211582 次
- 性别:
- 来自: 深圳
文章分类
最新评论
-
liminshaoye:
为什么我的一次成功之后就不能发送第二次了呢?
Java 使用SMSLib通过串口通讯收发短信 -
sziitjiang:
面试的题目,慢慢研究,还是能学到很多东西
JAVA 面试题 -
sziitjiang:
老大,我百度了一下SMG引擎,把你的博客给百度出来了!
SMG引擎 1.3.0 功能说明 -
jinkunaier:
,受教了!
深入探索SOAP1.1--使用SAAJ1.2.1 -
genggeng:
THX,对我入门很有帮助......
Java 使用SMSLib通过串口通讯收发短信
mysql存储过程学习笔记--错误处理 收藏
定义:
DECLARE {CONTINUE | EXIT} HANDLER FOR {SQLSTATE sqlstate_code| MySQL error code| condition_name} handler_actions
上述定义包括:
· Handler type (CONTINUE, EXIT)//处理类型,继续或退出
· Handler condition (SQLSTATE, MySQL error code, named condition) //触发条件
· Hander actions(错误触发的操作)
注意:
a、 exit只退出当前的block。
b、 如果定义了handler_action,会在continue或exit之前执行
发生错误的条件有:
· mysql错误代码
· ANSI-standard SQLSTATE code.
· 命名条件,可自定可使用系统内置的SQLEXCEPTION, SQLWARNING,和 NOT FOUND.
例:
//当错误代码为1062时将duplicate_key的值设为1,并继续执行当前任务
DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key=1;
//下面的跟上面的一样,只是使用的条件为ansi标准错误代码 DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET duplicate_key=1;
//当发生SQLEXCEPTION时,将l_error设为1,并继续 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET l_error=1;
小提示:
当你在mysql客户端执行命令并产生错误时,会得到mysql和ANSI的SQLSTATE code,如:
mysql> CALL nosuch_sp( );
ERROR 1305 (42000): PROCEDURE sqltune.nosuch_sp does not exist
附,常见错误号对照表
MySQL error code SQLSTATE code Error message
1011 HY000 Error on delete of '%s' (errn %d)
1021 HY000 Disk full (%s); waiting for someone to free some space . . .
1022 23000 Can't write; duplicate key in table '%s'
1027 HY000 '%s' is locked against change
1036 HY000 Table '%s' is read only
1048 23000 Column '%s' cannot be null
1062 23000 Duplicate entry '%s' for key %d
1099 HY000 Table '%s' was locked with a READ lock and can't be updated
1100 HY000 Table '%s' was not locked with LOCK TABLES
1104 42000 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
1106 42000 Incorrect parameters to procedure '%s'
1114 HY000 The table '%s' is full
1150 HY000 Delayed insert thread couldn't get requested lock for table %s
1165 HY000 INSERT DELAYED can't be used with table '%s' because it is locked with LOCK TABLES
1242 21000 Subquery returns more than 1 row
1263 22004 Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld
1264 22003 Out of range value adjusted for column '%s' at row %ld
1265 1000 Data truncated for column '%s' at row %ld
1312 0A000 SELECT in a stored program must have INTO
1317 70100 Query execution was interrupted
1319 42000 Undefined CONDITION: %s
1325 24000 Cursor is already open
1326 24000 Cursor is not open
1328 HY000 Incorrect number of FETCH variables
1329 2000 No data to FETCH
1336 42000 USE is not allowed in a stored program
1337 42000 Variable or condition declaration after cursor or handler declaration
1338 42000 Cursor declaration after handler declaration
1339 20000 Case not found for CASE statement
1348 HY000 Column '%s' is not updatable
1357 HY000 Can't drop a %s from within another stored routine
1358 HY000 GOTO is not allowed in a stored program handler
1362 HY000 Updating of %s row is not allowed in %s trigger
1363 HY000 There is no %s row in %s trigger
命名条件:
DECLARE condition_name CONDITION FOR {SQLSTATE sqlstate_code | MySQL_error_code};
例:
DECLARE foreign_key_error CONDITION FOR 1216; DECLARE CONTINUE HANDLER FOR foreign_key_error MySQL_statements;
优先级:
当同时使用MySQl错误码,标准SQLSTATE错误码,命名条件(SQLEXCEPTION)来定义错误处理时,其捕获顺序是(只可捕获一条错误):
MYSQL码->SQLSTATE->命名条件
作用域:
1、包括begni..end;内的语句
DECLARE CONTINUE HANDLER FOR 1048 SELECT 'Attempt to insert a null value';
BEGIN
INSERT INTO a VALUES (6,NULL);
END;
若a表第二字段定义为非空,则会触发1048错误
2、若错误处理在begin..end内定义,则在之外的语句不会触发错误发生
BEGIN
BEGIN
DECLARE CONTINUE HANDLER FOR 1216 select
'Foreign key constraint violated';
END;
INSERT INTO departments (department_name,manager_id,location)
VALUES ('Elbonian HR','Catbert','Catbertia');
END;
3、能够捕获其它存储过程抛出的错误
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/java000/archive/2008/04/29/2342467.aspx
定义:
DECLARE {CONTINUE | EXIT} HANDLER FOR {SQLSTATE sqlstate_code| MySQL error code| condition_name} handler_actions
上述定义包括:
· Handler type (CONTINUE, EXIT)//处理类型,继续或退出
· Handler condition (SQLSTATE, MySQL error code, named condition) //触发条件
· Hander actions(错误触发的操作)
注意:
a、 exit只退出当前的block。
b、 如果定义了handler_action,会在continue或exit之前执行
发生错误的条件有:
· mysql错误代码
· ANSI-standard SQLSTATE code.
· 命名条件,可自定可使用系统内置的SQLEXCEPTION, SQLWARNING,和 NOT FOUND.
例:
//当错误代码为1062时将duplicate_key的值设为1,并继续执行当前任务
DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key=1;
//下面的跟上面的一样,只是使用的条件为ansi标准错误代码 DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET duplicate_key=1;
//当发生SQLEXCEPTION时,将l_error设为1,并继续 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET l_error=1;
小提示:
当你在mysql客户端执行命令并产生错误时,会得到mysql和ANSI的SQLSTATE code,如:
mysql> CALL nosuch_sp( );
ERROR 1305 (42000): PROCEDURE sqltune.nosuch_sp does not exist
附,常见错误号对照表
MySQL error code SQLSTATE code Error message
1011 HY000 Error on delete of '%s' (errn %d)
1021 HY000 Disk full (%s); waiting for someone to free some space . . .
1022 23000 Can't write; duplicate key in table '%s'
1027 HY000 '%s' is locked against change
1036 HY000 Table '%s' is read only
1048 23000 Column '%s' cannot be null
1062 23000 Duplicate entry '%s' for key %d
1099 HY000 Table '%s' was locked with a READ lock and can't be updated
1100 HY000 Table '%s' was not locked with LOCK TABLES
1104 42000 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
1106 42000 Incorrect parameters to procedure '%s'
1114 HY000 The table '%s' is full
1150 HY000 Delayed insert thread couldn't get requested lock for table %s
1165 HY000 INSERT DELAYED can't be used with table '%s' because it is locked with LOCK TABLES
1242 21000 Subquery returns more than 1 row
1263 22004 Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld
1264 22003 Out of range value adjusted for column '%s' at row %ld
1265 1000 Data truncated for column '%s' at row %ld
1312 0A000 SELECT in a stored program must have INTO
1317 70100 Query execution was interrupted
1319 42000 Undefined CONDITION: %s
1325 24000 Cursor is already open
1326 24000 Cursor is not open
1328 HY000 Incorrect number of FETCH variables
1329 2000 No data to FETCH
1336 42000 USE is not allowed in a stored program
1337 42000 Variable or condition declaration after cursor or handler declaration
1338 42000 Cursor declaration after handler declaration
1339 20000 Case not found for CASE statement
1348 HY000 Column '%s' is not updatable
1357 HY000 Can't drop a %s from within another stored routine
1358 HY000 GOTO is not allowed in a stored program handler
1362 HY000 Updating of %s row is not allowed in %s trigger
1363 HY000 There is no %s row in %s trigger
命名条件:
DECLARE condition_name CONDITION FOR {SQLSTATE sqlstate_code | MySQL_error_code};
例:
DECLARE foreign_key_error CONDITION FOR 1216; DECLARE CONTINUE HANDLER FOR foreign_key_error MySQL_statements;
优先级:
当同时使用MySQl错误码,标准SQLSTATE错误码,命名条件(SQLEXCEPTION)来定义错误处理时,其捕获顺序是(只可捕获一条错误):
MYSQL码->SQLSTATE->命名条件
作用域:
1、包括begni..end;内的语句
DECLARE CONTINUE HANDLER FOR 1048 SELECT 'Attempt to insert a null value';
BEGIN
INSERT INTO a VALUES (6,NULL);
END;
若a表第二字段定义为非空,则会触发1048错误
2、若错误处理在begin..end内定义,则在之外的语句不会触发错误发生
BEGIN
BEGIN
DECLARE CONTINUE HANDLER FOR 1216 select
'Foreign key constraint violated';
END;
INSERT INTO departments (department_name,manager_id,location)
VALUES ('Elbonian HR','Catbert','Catbertia');
END;
3、能够捕获其它存储过程抛出的错误
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/java000/archive/2008/04/29/2342467.aspx
发表评论
-
SUSE11 安装SVN-jiangwx
2012-11-01 22:53 2280SUSE安装新SVN,并导入之前SVN版本库操作手记svn服务 ... -
Apache下的开设虚拟主机设置--添加站点2008-01-07 20:27Apache下的开设虚拟主机设置--添加站点
2012-06-05 17:24 868Apache下的开设虚拟主机 ... -
.ubuntu broadcom 43xx 802.11b/g无线驱动安装全过程
2012-01-17 13:59 1678.ubuntu broadcom 43xx 802.11b/g ... -
Linux 源码安装Mysql5.5 .
2011-07-11 12:07 862分类: linux mysql 2010-05-27 19:5 ... -
GeoServer地图开发解决方案(一):环境搭建篇
2011-07-04 17:18 905GeoServer地图开发解决方案(一):环境搭建篇 Ge ... -
arcgis api for flex 学习笔记
2011-07-01 18:17 1422arcgis api for flex 学习笔 ... -
公司升级服务器小结
2011-04-02 13:27 806[原创]公司升级服务器 ... -
mysql 导入导出数据库以及函数、存储过程
2011-03-22 13:47 1362mysql 导入导出数据库以及函数、存储过程 文章分类:Jav ... -
mysql存储过程也提供了对异常处理的功能:通过定义HANDLER来完成异常声明的实现
2011-01-06 12:10 1493mysql存储过程之异常处理篇 文章分类:数据库 mysql ... -
(转)Mysql的游标究竟怎么用--映日荷花别样红
2010-11-13 21:36 739Mysql的游标究竟怎么用--映日荷花别样红 Mysql ... -
mysql-bin log making disk full
2010-10-08 18:39 674linux mysql-bin make disk full ... -
MySQL里创建索引(Create Index)的方法和语法结构及例子,
2010-09-10 13:50 1339MySQL里创建索引(Create Index)的方法和语法结 ... -
Linux下完全卸载ORACLE 10G的方法
2010-09-07 15:08 1000Linux下完全卸载ORACLE 10G的方法 [日期:200 ... -
(转)linux下开启mysql慢查询,分析查询语句一,为什么要开启这个查询呢?
2010-08-18 18:12 1341linux下开启mysql慢查询,分析查询语句一,为什么要开启 ... -
MySQL索引分析和优化(转)
2010-08-06 23:36 584mysql创建多列索引及优 ... -
mysql 如何除重复
2010-08-06 12:19 835有一写如何去除数据表中的重复数据利用数据导入导出的方式.这种方 ... -
10g中占用CPU很高异常oracle进程分析
2010-07-27 22:59 2699上一篇 / 下一篇 2009-07-20 16:15:19 ... -
oracle 查找最消耗内存cpu的进程的sql
2010-07-27 22:51 2629SELECT sql_text FROM v$sqltex ... -
linux系统优化 笔记
2010-07-21 22:57 819今天终于辞职了。 ... -
linux oracle 自启动脚本
2010-07-20 22:40 1665该脚本在启动前具有自检测是否启动功能。防止多次重启。 目前已经 ...
相关推荐
MYSQL学习笔记-索引MYSQL学习笔记-索引MYSQL学习笔记-索引
mysql存储过程编程教程: MySQL 存储过程编程基础 -- 指南,基本语句,存储过程中的 SQL 和错误处理 创建 MySQL 存储过程程序 -- 事务处理,内建函数,存储过程函数和触发器 在应用程序中使用 MySQL 存储...
MySQL学习笔记-JSP操作MySQL
zookeeper云的学习笔记-云的学习笔记系统-云的学习笔记系统源码-云的学习笔记管理系统-云的学习笔记管理系统java代码-云的学习笔记系统设计与实现-基于ssm的云的学习笔记系统-基于Web的云的学习笔记系统设计与实现-...
云的学习笔记-云的学习笔记系统-云的学习笔记系统源码-云的学习笔记管理系统-云的学习笔记管理系统java代码-云的学习笔记系统设计与实现-基于ssm的云的学习笔记系统-基于Web的云的学习笔记系统设计与实现-云的学习...
包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-...
MYSQL学习资源及笔记-入门必备
mysql-connector-java-5.1.46-bin.jar mysql-connector-java-5.1.27-bin.jar
mysql-connector-java-5.1.27.jar mysql-connector-java-5.1.27.jar
mysql-connector-java-5.1.7-bin.jar
mysql-connector-java-5.1.40.zip和mysql-connector-java-5.1.10.jar
mysql驱动包 mysql-connector-java-5.1.13-bin.jar 方便快捷获取。。。