- 浏览: 2649936 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
80后的童年2:
深入浅出MongoDB应用实战开发网盘地址:https://p ...
MongoDB入门教程 -
shliujing:
楼主在不是精通java和php的前提下,请不要妄下结论。
PHP、CakePHP哪凉快哪呆着去 -
安静听歌:
希望可以一给一点点注释
MySQL存储过程之代码块、条件控制、迭代 -
qq287767957:
PHP是全宇宙最强的语言!
PHP、CakePHP哪凉快哪呆着去 -
rryymmoK:
深入浅出MongoDB应用实战开发百度网盘下载:链接:http ...
MongoDB入门教程
1,简介
MySQL支持HANDLER来处理错误:
Duplicate entry Handler
Last Row Handler
2,Handlers
语法:
Handlers类型:
Handlers条件:
优先级:
MySQL Error code > SQLSTATE code > 命名条件
使用SQLSTATE还是MySQL Error Code?
1,SALSTATE是标准,貌似会更portable,但是实际上MySQL、DB2、Oracle等等的存储程序语法大相径庭,所以portable的优势不存在
2,MySQL error code与SQLSTATE并不是一一对应的,比如很多MySQL error code都映射到同一SQLSTATE code(HY000)
当MySQL客户端碰到错误时,它会报告MySQL error code和相关的SQLSATE code:
上面Error code是1305,SQLSTATE code是42000
常见的MySQL error code和SQLSTATE code:
可以在http://dev.mysql.com/doc/的MySQL reference manual的附录B找到完整的最新的error codes
3,命名条件
MySQL error code或者SQLSTATE code的可读性太差,所以引入了命名条件:
使用:
4,SQL:2003的特性
可以使用SIGNAL语句来触发错误
MySQL5.2才支持 SQL:2003
5,Error Handling的例子
MySQL支持HANDLER来处理错误:
Duplicate entry Handler
CREATE PROCEDURE sp_add_location (in_location VARCHAR(30), in_address1 VARCHAR(30), in_address2 VARCHAR(30), zipcode VARCHAR(10), OUT out_status VARCHAR(30)) BEGIN DECLARE CONTINUE HANDLER FOR 1062 SET out_status='Duplicate Entry'; SET out_status='OK'; INSERT INTO locations (location,address1,address2,zipcode) VALUES (in_location,in_address1,in_address2,zipcode); END;
Last Row Handler
CREATE PROCEDURE sp_not_found() READS SQL DATA BEGIN DECLARE l_last_row INT DEFAULT 0; DECLARE l_dept_id INT: DECLARE c_dept CURSOR FOR SELECT department_id FROM departments; DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row=1; OPEN c_dept; dept_cursor: LOOP FETCH c_dept INTO l_dept_id; IF (l_last_row=1) THEN LEAVE dept_cursor; END IF; END LOOP dept_cursor; CLOSE c_dept; END;
2,Handlers
语法:
DECLARE {CONTINUE | EXIT} HANDLER FOR {SQLSTATE sqlstate_code | MySQL error code | condition_name} handler_actions
Handlers类型:
1, EXIT: 发生错误时退出当前代码块(可能是子代码块或者main代码块) 2, CONTINUE: 发送错误时继续执行后续代码
Handlers条件:
1, MySQL error code,如1062 2, ANSI标准SQLSTATE code,如23000 3, 命名条件,如NOT FOUND
优先级:
MySQL Error code > SQLSTATE code > 命名条件
使用SQLSTATE还是MySQL Error Code?
1,SALSTATE是标准,貌似会更portable,但是实际上MySQL、DB2、Oracle等等的存储程序语法大相径庭,所以portable的优势不存在
2,MySQL error code与SQLSTATE并不是一一对应的,比如很多MySQL error code都映射到同一SQLSTATE code(HY000)
当MySQL客户端碰到错误时,它会报告MySQL error code和相关的SQLSATE code:
mysql > CALL nosuch_sp(); ERROR 1305 (42000): PROCEDURE sqltune.nosuch_sp does not exist
上面Error code是1305,SQLSTATE code是42000
常见的MySQL error code和SQLSTATE code:
MySQL error code SQLSTATE code Error message 1011 HY000 Error on delete of '%s' (errno: %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
可以在http://dev.mysql.com/doc/的MySQL reference manual的附录B找到完整的最新的error codes
3,命名条件
MySQL error code或者SQLSTATE code的可读性太差,所以引入了命名条件:
DECLARE condition_name CONDITION FOR {SQLSTATE sqlstate_code | MySQL_error_code};
使用:
# original DECLARE CONTINUE HANDLER FOR 1216 MySQL_statements; # changed DECLARE foreign_key_error CONDITION FOR 1216; DECLARE CONTINUE HANDLER FOR foreign_key_error MySQL_statements;
4,SQL:2003的特性
可以使用SIGNAL语句来触发错误
SIGNAL SQLSTATE sqlstate_code|condition_name [SET MESSAGE_TEXT=string_or_variable];
MySQL5.2才支持 SQL:2003
5,Error Handling的例子
CREATE PROCEDURE sp_add_department (p_department_name VARCHAR(30), p_manager_surname VARCHAR(30), p_manager_firstname VARCHAR(30), p_location VARCHAR(30), OUT p_sqlcode INT, OUT p_status_message VARCHAR(100)) BEGIN /* START Declare Conditions */ DECLARE duplicate_key CONDITION FOR 1062; DECLARE foreign_key_violated CONDITION FOR 1216; /* END Declare COnditions */ /* START Declare variables and cursors */ DECLARE l_manager_id INT; DECLARE csr_mgr_id CURSOR FOR SELECT employee_id FROM employees WHERE surname=UPPER(p_manager_surname) AND firstname=UPPER(p_manager_firstname); /* END Declare variables and cursors */ /* START Declare Exception Handlers */ DECLARE CONTINUE HANDLER FOR duplicate_key BEGIN SET p_sqlcode=1052; SET p_status_message='Duplicate key error'; END; DECLARE CONTINUE HANDLER FOR foreign_key_violated BEGIN SET p_sqlcode=1216; SET p_status_message='Foreign key violated'; END; DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN SET p_sqlcode=1329; SET p_status_message='No record found'; END; /* END Declare Exception Handlers */ /* START Execution */ SET p_sqlcode=0; OPEN csr_mgr_id; FETCH csr_mgr_id INTO l_manager_id; IF p_sqlcode<>0 THEN /* Failed to get manager id */ SET p_status_message=CONCAT(p_status_message,' when fetching manager id'); ELSE /* Got manager id, we can try and insert */ INSERT INTO departments (department_name, manager_id, location) VALUES(UPPER(p_department_name), l_manager_id, UPPER(p_location)); IF p_sqlcode<>0 THEN /* Failed to insert new department */ SET p_status_message=CONCAT(p_status_message, ' when inserting new department'); END IF; END IF; CLOSE csr_mgr_id; /* END Execution */ END
发表评论
-
HPM Note5, Query Performance Optimization
2009-07-21 18:05 1434Slow Query Basics: Optimize Dat ... -
HPM Note4, Schema Optimization and Indexing
2009-07-16 18:04 1430Choosing Optimal Data Types Sma ... -
HPM Note3, Benchmarking and Profiling
2009-07-02 14:07 1452Note3, Finding Bottlenecks: Ben ... -
HPM Note2, MySQL Architecture
2009-06-30 17:13 1715MySQL's Logical Architecture Th ... -
HPM Note1,Book Organization
2009-06-23 09:49 1581How This Book Is Organization ... -
MySQL Architecture
2009-01-18 00:12 3050MySQL Core Modules: Server In ... -
MySQL优化笔记
2008-10-28 17:59 3402MySQL 5.1参考手册 :: 7. 优化 一、查询优化 ... -
MySQL里获取当前week、month、quarter的start_date/end_date
2008-10-21 14:14 7404当前week的第一天: select date_sub(cur ... -
mysql里找出一个表的主键被谁作为外键约束
2008-08-13 17:16 2142SELECT ke.referenced_table_n ... -
SQL性能调优:2.1 排序的一般性调优
2008-08-05 10:21 3532影响排序速度的原因(从大到小): 1,选择的行数 2,ORDE ... -
TCP-IP详解笔记1.5 RARP:逆地址解析协议
2008-07-25 14:05 2230from http://www.beyondrails.com ... -
SQL性能调优:1.3 其他语法调优
2008-07-25 13:38 1343from http://www.beyondrails.com ... -
SQL性能调优:1.2 特别语法调优
2008-07-24 12:15 2660from http://www.beyondrails.com ... -
SQL性能调优:1.1 一般性语法调优
2008-07-23 14:47 2462from http://www.beyondrails.com ... -
MySQL存储程序开发最佳实践
2008-05-28 13:56 1653MySQL存储程序开发最佳 ... -
MySQL join的文章
2008-05-28 13:00 1529MySQL的联结(Join)语法 -
MySQL索引系列文章
2008-05-28 12:51 1424MySQL索引使用 MySQL索引 MySQL 5.1参考手册 ... -
MySQL存储程序权限控制
2008-05-28 12:29 1359MySQL存储程序权限控制 MySQL5.0引入了一些管理存 ... -
MySQL的Stored Function和Trigger
2008-05-27 18:58 3072MySQL的Stored Function和Trigger ... -
MySQL内建Function
2008-05-22 17:25 6442MySQL内建Function 在MySQL存储程序(存储过 ...
相关推荐
微信小程序php后台内嵌mysql微信小程序php后台内嵌mysql微信小程序php后台内嵌mysql微信小程序php后台内嵌mysql微信小程序php后台内嵌mysql微信小程序php后台内嵌mysql微信小程序php后台内嵌mysql微信小程序php后台...
JAVA ORACLE MYSQL 驱动程序 JAR包。
《PHP与MySQL程序设计 第4版 》pdf与源码 是全面讲述PHP与MySQL的经典之作 书中不但全面介绍了两种技术的核心特性 还讲解了如何高效地结合这两种技术构建健壮的数据驱动的应用程序 《PHP与MySQL程序设计 第4版 》...
Error installing ODBC driver MySQL ODBC5.3 ANSI Driver,ODBC error 13:无法加载 MySQL ODBC5.3 ANSI Driver ODBC驱动程序的安装例程, 因为存在系统错误代码 126:找不到指定的模块。 解决方案:如果电脑是...
主要介绍了mysql存储过程之返回多个值的方法,结合实例形式分析了mysql存储过程返回多个值的实现方法与PHP调用技巧,需要的朋友可以参考下
php中连接mysql时出现Fatal error Call to undefined function mysql_connect() 解决方法
MySQL数据库修复程序,表查看,读取,表设计。SQL数据库内容读取。
存储过程与存储函数的区别之一是存储过程不能包含 ______。 A.SET语句 B.局部变量 C.RETURN语句 D.游标 4.现有如下语句:能够直接调用过程 p,并能得到正确返回结果的语句是 ______。 计算机二级mysql数据库程序设计...
mysql windows 驱动程序 如果要用sqlserver 做链接服务器的话 就要安装该驱动程序,用来创建一个odbc 数据源
mysql经典教程+mysql存储过程讲解 重点讲解Mysql的存储过程,触发器,游标的使用 对mysql不太熟的朋友可以好好学习。。。
最新全国计算机等级考试二级MySQL数据库程序设计大纲(2018版)
在作.net開發的環境下,對MySQL數據庫的連接驅動程序。
MySQL存储过程 ERROR Handler 异常处理
MySQL Error Code 详细说明
mysql安装程序 安装简便 官网原版
mysql中文手册.chm+mysql命令大全.chm+mysql存储过程.pdf
mysql存储过程编程教程: MySQL 存储过程编程基础 -- 指南,基本语句,存储过程中的 SQL 和错误处理 创建 MySQL 存储过程程序 -- 事务处理,内建函数,存储过程函数和触发器 在应用程序中使用 MySQL 存储...
MYSQL安装出现could not start the service mysql error0处理
环境变量配置错误 mysql 命令行错误 ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: N
mysql存储过程实现分页 mysql存储过程实现分页 mysql存储过程实现分页 mysql存储过程实现分页