Mysql存储过程游标加循环
转自:http://blog.csdn.net/myron_sqh/article/details/15862923
Mysql的存储过程是从版本5才开始支持的,所以目前一般使用的都可以用到存储过程。今天分
享下自己对于Mysql存储过程的认识与了解。
一些简单的调用以及语法规则这里就不在赘述,网上有许多例子。这里主要说说大家常用的游
标加循环的嵌套使用。
首先先介绍循环的分类:
(1)WHILE ... END WHILE
(2)LOOP ... END LOOP
(3)REPEAT ... END REPEAT
(4)GOTO
这里有三种标准的循环方式:WHILE循环,LOOP循环以及REPEAT循环。还有一种非标准的循
环方式:GOTO(不做介绍)。
(1)WHILE ... END WHILE
复制代码代码如下:
CREATE PROCEDURE p14()
BEGIN
DECLARE v INT;
SET v = 0;
WHILE v < 5 DO
INSERT INTO t VALUES (v);
SET v = v + 1;
END WHILE;
END;
这是WHILE循环的方式。它跟IF语句相似,使用"SET v = 0;"语句使为了防止一个常见的错误
,如果没有初始化,默认变量值为NULL,而NULL和任何值操作结果都为NULL。
(2)REPEAT ... END REPEAT
复制代码代码如下:
CREATE PROCEDURE p15 ()
BEGIN
DECLARE v INT;
SET v = 0;
REPEAT
INSERT INTO t VALUES (v);
SET v = v + 1;
UNTIL v >= 5
END REPEAT;
END;
这是REPEAT循环的例子,功能和前面WHILE循环一样。区别在于它在执行后检查结果,而
WHILE则是执行前检查。类似于do while语句。注意到UNTIL语句后面没有分号,在这里可以
不写分号,当然你加上额外的分号更好。
(3)LOOP ... END LOOP
复制代码代码如下:
CREATE PROCEDURE p16 ()
BEGIN
DECLARE v INT;
SET v = 0;
loop_label: LOOP
INSERT INTO t VALUES (v);
SET v = v + 1;
IF v >= 5 THEN
LEAVE loop_label;
END IF;
END LOOP;
END;
以上是LOOP循环的例子。LOOP循环不需要初始条件,这点和WHILE循环相似,同时它又和
REPEAT循环一样也不需要结束条件。
ITERATE 迭代
如果目标是ITERATE(迭代)语句的话,就必须用到LEAVE语句
复制代码代码如下:
CREATE PROCEDURE p20 ()
BEGIN
DECLARE v INT;
SET v = 0;
loop_label: LOOP
IF v = 3 THEN
SET v = v + 1;
ITERATE loop_label;
END IF;
INSERT INTO t VALUES (v);
SET v = v + 1;
IF v >= 5 THEN
LEAVE loop_label;
END IF;
END LOOP;
END;
ITERATE(迭代)语句和LEAVE语句一样也是在循环内部的循环引用, 它有点像C语言中 的
“Continue”,同样它可以出现在复合语句中,引用复合语句标号,ITERATE(迭代)意思 是重
新开始复合语句。
以上是对于循环的几种情况的介绍。接着就是介绍一个带游标的例子来详细解释。
复制代码代码如下:
begin
declare p_feeCode varchar(20);
declare p_feeName varchar(20);
declare p_billMoney float(12);
declare p_schemeMoney float(12);
declare allMoney float(10);
declare allUsedMoney float(10);
declare p_year varchar(50);
declare p_totalCompeleteRate float(12);
declare done int(10);
declare flag int(2);
declare feeCodeCursor cursor for select feeCode from fee;//申明一个游标变量
declare continue handler for not found set done=1;//申明循环结束的标志位
set done=0;
select date_format(now(),'%Y') into p_year;
open feeCodeCursor;//打开游标
loop_label:LOOP
fetch feeCodeCursor into p_feeCode;//将游标插入申明的变量
if done = 1 then
leave loop_label;
else
set flag = 0;
end if;
set p_schemeMoney=0;
set p_billMoney = 0;
select feeName into p_feeName from fee where feeCode=p_feeCode;
select sum(billMoney) into p_billMoney from bill_data where feeCode=p_feeCode and
billDate like Concat(p_year, '%');
select schemeMoney into p_schemeMoney from total_scheme where feeCode=p_feeCode
and schemeDate like Concat(p_year, '%') limit 1;
if flag = 0 then
set done = 0;
end if;
if p_schemeMoney=0 then
set p_totalCompeleteRate=-1.0;
else
set p_totalCompeleteRate=(1.0*p_billMoney)/p_schemeMoney;
end if;
insert into total_summary values
(p_feeCode,p_feeName,p_year,p_billMoney,p_totalCompeleteRate);
commit;
end LOOP;
close feeCodeCursor;//循环结束后需要关闭游标
end
以上只是一个简单的例子来说明如何使用,大家不需要关注具体业务逻辑,只需要关注的是其
中标志位值的修改情况,已经循环何时离开。以及游标如何声明,如何使用,至于里面具体的
操作和普通的sql语句没有太大区别。此处是用一层循环,至于复杂业务需要需要两层三层,可
以继续用同样的方法继续嵌套。以下给出双层嵌套循环的,同样大家只需要关注嵌套结构即可
。
复制代码代码如下:
begin
declare p_projectID varchar(20);
declare p_projectName varchar(20);
declare p_feeCode varchar(20);
declare p_feeName varchar(20);
declare p_projectSchemeMoney float(10);
declare p_projectMoney float(10);
declare p_billMoney float(10);
declare p_year varchar(50);
declare p_projectFeeCompeleteRate float(10);
declare done1 int(10);
declare done2 int(10);
declare flag int(2);
declare feeCodeCursor cursor for select feeCode from fee;
declare continue handler for not found set done1=1;
set done1=0;
select date_format(now(),'%Y') into p_year;
delete from project_fee_summary;
open feeCodeCursor;
repeat //第一层嵌套开始
fetch feeCodeCursor into p_feeCode;
select feeName into p_feeName from fee where feeCode=p_feeCode;
if not done1 then
begin
declare projectIDCursor cursor for select projectID from project;
declare continue handler for not found set done2 = 1;
set done2=0;
open projectIDCursor;
loop_label:LOOP//第二层嵌套开始
fetch projectIDCursor into p_projectID;
select projectName into p_projectName from project where projectID=p_projectID;
if done2 = 1 then
leave loop_label;
else
set flag = 0;
end if;
if not done2 then
set p_projectSchemeMoney=0;
select sum(billMoney) into p_billMoney from bill_data where feeCode=p_feeCode and
projectID=p_projectID and billDate like Concat(p_year, '%');
select projectSchemeMoney into p_projectSchemeMoney from project_scheme where
feeCode=p_feeCode and projectID=p_projectID;
if flag = 0 then
set done2 = 0;
end if;
if p_projectSchemeMoney=0 then
set p_projectFeeCompeleteRate=-1;
else
set p_projectFeeCompeleteRate=(1.0*p_billMoney)/p_projectSchemeMoney;
end if;
insert into project_fee_summary values
(p_feeCode,p_projectID,p_projectName,p_feeName,p_year,p_billMoney,p_projectFeeCompel
eteRate,p_projectFeeCompeleteRate);
end if;
end LOOP;
select sum(billMoney) into p_projectMoney from bill_data where feeCode=p_feeCode and
billDate like Concat(p_year, '%');
set p_projectFeeCompeleteRate=(1.0*p_projectMoney)/p_projectSchemeMoney;
insert into project_fee_summary values
(p_feeCode,"total","total",p_feeName,p_year,p_projectMoney,p_projectFeeCompeleteRate,p
_projectFeeCompeleteRate);
close projectIDCursor;
end;
end if;
until done1
end repeat;
close feeCodeCursor;
end
mysql存储过程---返回列表数据需要用到游标
转自:http://blog.csdn.net/aaa1117a8w5s6d/article/details/8480198
1. 使用存储过程的优点有:
(1)存储过程在服务器端运行,执行速度快。
(2)存储过程执行一次后,其执行规划就驻留在高速缓冲存储器,在以后的操作中,只需从
高速缓冲存储器中调用已编译好的二进制代码执行,提高了系统性能。
(3)确保数据库的安全。使用存储过程可以完成所有数据库操作,并可通过编程方式控制上
述操作对数据库信息访问的权限。
2.创建存储过程可以使用create procedure语句。
要在MySQL 5.1中创建存储过程,必须具有CREATE routine权限。要想查看数据库中有哪些存
储过程,可以使用SHOW PROCEDURE STATUS命令。要查看某个存储过程的具体信息,可使
用SHOWCREATE PROCEDURE sp_name命令,其中sp_name是存储过程的名称。
CREATE PROCEDURE的语法格式:
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
其中,proc_parameter的参数如下:
[ IN | OUT | INOUT ] param_name type
characteristic特征如下:
language SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
说明:
● sp_name:存储过程的名称,默认在当前数据库中创建。需要在特定数据库中创建存储过
程时,则要在名称前面加上数据库的名称,格式为:db_name.sp_name。值得注意的是,这
个名称应当尽量避免取与MySQL的内置函数相同的名称,否则会发生错误。
● proc_parameter:存储过程的参数,param_name为参数名,type为参数的类型,当有多
个参数的时候中间用逗号隔开。存储过程可以有0个、1个或多个参数。MySQL存储过程支持三
种类型的参数:输入参数、输出参数和输入/输出参数,关键字分别是IN、OUT和INOUT。输
入参数使数据可以传递给一个存储过程。当需要返回一个答案或结果的时候,存储过程使用输
出参数。输入/输出参数既可以充当输入参数也可以充当输出参数。存储过程也可以不加参数
,但是名称后面的括号是不可省略的。
注意:参数的名字不要等于列的名字,否则虽然不会返回出错消息,但是存储过程中的SQL语
句会将参数名看做列名,从而引发不可预知的结果。
characteristic:存储过程的某些特征设定,下面一一介绍:
language sql:表明编写这个存储过程的语言为SQL语言,目前来讲,MySQL存储过程还不能
用外部编程语言来编写,也就是说,这个选项可以不指定。将来将会对其扩展,最有可能第一
个被支持的语言是PHP。
deterministic:设置为DETERMINISTIC表示存储过程对同样的输入参数产生相同的结果,设置
为NOT DETERMINISTIC则表示会产生不确定的结果。默认为NOTDETERMINISTIC。
contains SQL:表示存储过程不包含读或写数据的语句。NO SQL表示存储过程不包含SQL语句
。reads SQL DATA表示存储过程包含读数据的语句,但不包含写数据的语句。modifies SQL
DATA表示存储过程包含写数据的语句。如果这些特征没有明确给定,默认的是CONTAINS SQL
。
SQL SECURITY:SQL SECURITY特征可以用来指定存储过程使用创建该存储过程的用户
(DEFINER)的许可来执行,还是使用调用者(INVOKER)的许可来执行。默认值是DEFINER
。
COMMENT 'string':对存储过程的描述,string为描述内容。这个信息可以用SHOWCREATE
PROCEDURE语句来显示。
● routine_body:这是存储过程的主体部分,也叫做存储过程体。里面包含了在过程调用的
时候必须执行的语句,这个部分总是以begin开始,以end结束。当然,当存储过程体中只有一
个SQL语句时可以省略BEGIN-END标志。
3. 在开始创建存储过程之前,先介绍一个很实用的命令,即delimiter命令。在MySQL中,服
务器处理语句的时候是以分号为结束标志的。但是在创建存储过程的时候,存储过程体中可能
包含多个SQL语句,每个SQL语句都是以分号为结尾的,这时服务器处理程序的时候遇到第一
个分号就会认为程序结束,这肯定是不行的。所以这里使用DELIMITER命令将MySQL语句的结
束标志修改为其他符号。
DELIMITER语法格式为:DELIMITER $$
说明:$$是用户定义的结束符,通常这个符号可以是一些特殊的符号,如两个“#”,一个“¥”
、数字、字母等都可以。当使用DELIMITER命令时,应该避免使用反斜杠(“\”)字符,因为那
是MySQL的转义字符。
例:创建存储过程,实现的功能是删除一个特定学生的信息。
DELIMITER $$
CREATE PROCEDURE DELETE_STUDENT(IN XH CHAR(6))
BEGIN
DELETE FROM XS WHERE 学号=XH;
END $$
DELIMITER ;
说明:当调用这个存储过程时,MySQL根据提供的参数XH的值,删除对应在XS表中的数据。
在关键字BEGIN和END之间指定了存储过程体,当然,BEGIN-END复合语句还可以嵌套使用。
4. 局部变量
在存储过程中可以声明局部变量,它们可以用来存储临时结果。要声明局部变量必须使用
declare语句。在声明局部变量的同时也可以对其赋一个初始值。
DECLARE语法格式:DECLARE var_name[,...] type [DEFAULT value]
说明:var_name为变量名;type为变量类型;default子句给变量指定一个默认值,如果不指
定默认为NULL的话。可以同时声明多个类型相同的局部变量,中间用逗号隔开。
例: 声明一个整型变量和两个字符变量。
DECLARE num INT(4);
DECLARE str1, str2 VARCHAR(6);
declare n char(10) default ‘abcdefg’;
说明:局部变量只能在BEGIN…END语句块中声明。
局部变量必须在存储过程的开头就声明,声明完后,可以在声明它的BEGIN…END语句块中使
用该变量,其他语句块中不可以使用它。
在存储过程中也可以声明用户变量,不过千万不要将这两个混淆。局部变量和用户变量的区别
在于:局部变量前面没有使用@符号,局部变量在其所在的BEGIN…END语句块处理完后就消
失了,而用户变量存在于整个会话当中。
5. 使用SET语句赋值
要给局部变量赋值可以使用SET语句,SET语句也是SQL本身的一部分。语法格式为:SET
var_name = expr [,var_name = expr] ...
例: 在存储过程中给局部变量赋值。
SET num=1, str1= 'hello';
说明:与声明用户变量时不同,这里的变量名前面没有@符号。注意,例中的这条语句无法单
独执行,只能在存储过程和存储函数中使用。
6. SELECT...INTO语句(重点)
使用这个SELECT…INTO语法可以把选定的列值直接存储到变量中。因此,返回的结果只能有
一行。语法格式为:
SELECT col_name[,...] INTO var_name[,...] table_expr
说明:col_name是列名,var_name是要赋值的变量名。table_expr是SELECT语句中的FROM
子句及后面的部分,这里不再叙述。
例: 在存储过程体中将XS表中的学号为081101的学生姓名和专业名的值分别赋给变量name和
project。
SELECT 姓名,专业名 INTO name, project
FROMXS; WHERE 学号= '081101';
7. 流程控制语句
在MySQL中,常见的过程式SQL语句可以用在一个存储过程体中。例如:IF语句、CASE语句、
LOOP语句、WHILE语句、iterate语句和LEAVE语句。
(1)IF语句
IF-THEN-ELSE语句可根据不同的条件执行不同的操作。
语法格式为:
IF 判断的条件THEN 一个或多个SQL语句
[ELSEIF判断的条件THEN一个或多个SQL语句] ...
[ELSE一个或多个SQL语句]
END IF
说明:当判断条件为真时,就执行相应的SQL语句。
IF语句不同于系统的内置函数IF()函数,IF()函数只能判断两种情况,所以请不要混淆。
例: 创建XSCJ数据库的存储过程,判断两个输入的参数哪一个更大。
DELIMITER $$
CREATE PROCEDURE XSCJ.COMPAR
(IN K1INTEGER, IN K2 INTEGER, OUT K3 CHAR(6) )
BEGIN
IFK1>K2 THEN
SET K3= '大于';
ELSEIFK1=K2 THEN
SET K3= '等于';
ELSE
SET K3= '小于';
ENDIF;
END$$
DELIMITER ;
说明:存储过程中K1和K2是输入参数,K3是输出参数。
(2)CASE语句
前面已经介绍过了,这里介绍CASE语句在存储过程中的用法,与之前略有不同。语法格式为
:
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
或者:
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
说明:一个CASE语句经常可以充当一个IF-THEN-ELSE语句。
第一种格式中case_value是要被判断的值或表达式,接下来是一系列的WHEN-THEN块,每一
块的when_value参数指定要与case_value比较的值,如果为真,就执行statement_list中的SQL
语句。如果前面的每一个块都不匹配就会执行ELSE块指定的语句。CASE语句最后以END CASE
结束。
第二种格式中CASE关键字后面没有参数,在WHEN-THEN块中,search_condition指定了一个
比较表达式,表达式为真时执行THEN后面的语句。与第一种格式相比,这种格式能够实现更
为复杂的条件判断,使用起来更方便。
例: 创建一个存储过程,针对参数的不同,返回不同的结果。
DELIMITER $$
CREATE PROCEDURE XSCJ.RESULT
(IN str VARCHAR(4), OUT sex VARCHAR(4) )
BEGIN
CASE str
WHEN'M' THEN SET sex='男';
WHEN'F' THEN SET sex='女';
ELSE SET sex='无';
ENDCASE;
END$$
DELIMITER ;
例: 用第二种格式的CASE语句创建以上存储过程。程序片段如下:
CASE
WHENstr='M' THEN SET sex='男';
WHENstr='F' THEN SET sex='女';
ELSE SET sex='无';
END CASE;
(3)循环语句
MySQL支持3条用来创建循环的语句:while、repeat和loop语句。在存储过程中可以定义0个、
1个或多个循环语句。
● WHILE语句语法格式为:
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
说明:语句首先判断search_condition是否为真,不为真则执行statement_list中的语句,然后
再次进行判断,为真则继续循环,不为真则结束循环。begin_label和end_label是WHILE语句的
标注。除非begin_label存在,否则end_label不能被给出,并且如果两者都出现,它们的名字必
须是相同的。
例: 创建一个带WHILE循环的存储过程。
DELIMITER $$
CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT5;
WHILE v1 > 0 DO
SET v1 = v1-1;
END WHILE;
END $$
DELIMITER ;
● repeat语句格式如下:
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
说明:REPEAT语句首先执行statement_list中的语句,然后判断search_condition是否为真,为
真则停止循环,不为真则继续循环。REPEAT也可以被标注。
例: 用REPEAT语句创建一个如例7.9的存储过程。程序片段如下:
REPEAT
v1=v1-1;
UNTIL v1<1;
END REPEAT;
说明:REPEAT语句和WHILE语句的区别在于:REPEAT语句先执行语句,后进行判断;而
WHILE语句是先判断,条件为真时才执行语句。
● LOOP语句语法格式如下:
[begin_label:] LOOP
statement_list
END LOOP [end_label]
说明:LOOP允许某特定语句或语句群的重复执行,实现一个简单的循环构造,statement_list
是需要重复执行的语句。在循环内的语句一直重复至循环被退出,退出时通常伴随着一个
LEAVE 语句。
LEAVE语句经常和BEGIN...END或循环一起使用。结构如下:
LEAVE label ; label是语句中标注的名字,这个名字是自定义的。加上LEAVE关键字就可以用来
退出被标注的循环语句。
例: 创建一个带LOOP语句的存储过程。
DELIMITER $$
CREATE PROCEDURE doloop()
BEGIN
SET @a=10;
Label: LOOP
SET @a=@a-1;
IF @a<0 THEN
LEAVELabel;
END IF;
END LOOPLabel;
END$$
DELIMITER ;
循环语句中还有一个iterate语句,它只可以出现在LOOP、REPEAT和WHILE语句内,意为“再次
循环”。它的格式为:ITERATE label
说明:该语句格式与LEAVE差不多,区别在于:LEAVE语句是离开一个循环,而ITERATE语句
是重新开始一个循环。
8.我们调用此存储过程来查看最后结果。调用该存储过程使用如下命令:CALL doloop();
接着,查看用户变量的值: SELECT@a;
语法格式:CALL sp_name([parameter[,...]])
说明:sp_name为存储过程的名称,如果要调用某个特定数据库的存储过程,则需要在前面加
上该数据库的名称。parameter为调用该存储过程使用的参数,这条语句中的参数个数必须总
是等于存储过程的参数个数。
例:创建一个存储过程,有两个输入参数:XH和KCM,要求当某学生某门课程的成绩小于60
分时将其学分修改为零,大于等于60分时将学分修改为此课程的学分。
DELIMITER $$
CREATE PROCEDURE XSCJ.DO_UPDATE(IN XHCHAR(6), IN KCM CHAR(16))
BEGIN
DECLARE KCH CHAR(3);
DECLARE XF TINYINT;
DECLARE CJ TINYINT;
SELECT课程号, 学分 INTO KCH, XFFROM KC WHERE 课程名=KCM;
SELECT成绩 INTO CJ FROM XS_KC WHERE 学号=XH AND 课程号=KCH;
IF CJ<60 THEN
UPDATE XS_KC SET 学分=0 WHERE 学号=XH AND 课程号=KCH;
ELSE
UPDATE XS_KC SET 学分=XF WHERE 学号=XH AND 课程号=KCH;
END IF;
END$$
DELIMITER ;
9. 存储过程创建后需要删除时使用DROP PROCEDURE语句。
在此之前,必须确认该存储过程没有任何依赖关系,否则会导致其他与之关联的存储过程无法
运行。
语法格式为: DROPPROCEDURE [IF EXISTS] sp_name
说明:sp_name是要删除的存储过程的名称。IF EXISTS子句是MySQL的扩展,如果程序或函
数不存在,它防止发生错误。
例: 删除存储过程dowhile:DROP PROCEDURE IF EXISTS dowhile;
10. 使用ALTER PROCEDURE语句可以修改存储过程的某些特征。
语法格式为:ALTER PROCEDURE sp_name [characteristic ...]
其中,characteristic为:
{ CONTAINS SQL | NO SQL | READS SQLDATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
说明:characteristic是存储过程创建时的特征,在CREATE PROCEDURE语句中已经介绍过。只
要设定了其中的值,存储过程的特征就随之变化。
如果要修改存储过程的内容,可以使用先删除再重新定义存储过程的方法。
例: 使用先删除后修改的方法修改例7.12中的存储过程。
DELIMITER $$
DROP PROCEDURE IF EXISTS DO_QUERY;
CREATE PROCEDURE DO_QUERY()
BEGIN
SELECT * FROM XS;
END$$
DELIMITER ;
***11 往后为选看内容。。非重点!!
11. SQL语句中的错误提示
在存储过程中处理SQL语句可能导致一条错误消息。例如,向一个表中插入新的行而主键值已
经存在,这条INSERT语句会导致一个出错消息,并且MySQL立即停止对存储过程的处理。每
一个错误消息都有一个唯一代码和一个SQLSTATE代码。例如,SQLSTATE 23000属于如下的出
错代码:
Error 1022, "Can't write;duplicate(重复) key intable"
Error 1048, "Column cannot benull"
Error 1052, "Column is ambiguous(歧义)"
Error 1062, "Duplicate entry forkey"
MySQL手册的“错误消息和代码”一章中列出了所有的出错消息及它们各自的代码。
为了防止MySQL在一条错误消息产生时就停止处理,需要使用到DECLAREhandler语句。该语
句语句为错误代码声明了一个所谓的处理程序,它指明:对一条SQL语句的处理如果导致一条
错误消息,将会发生什么。
DECLARE HANDLER语法格式为:
DECLARE handler_type HANDLER FOR condition_value[,...]sp_statement
其中,handler_type为:
Continue
| EXIT
| UNDO
condition_value为:
SQLstate [VALUE] sqlstate_value
| condition_name
| SQLwarning
| NOT FOUND
| SQLexception
| mysql_error_code
说明:
● handler_type:处理程序的类型,主要有三种:CONTINUE、EXIT和UNDO。对CONTINUE
处理程序,MySQL不中断存储过程的处理。对于EXIT处理程序,当前 BEGIN...END复合语句
的执行被终止。UNDO处理程序类型语句暂时还不被支持。
● condition_value:给出SQLSTATE的代码表示。
condition_name是处理条件的名称,接下来会讲到。
SQLWARNING是对所有以01开头的SQLSTATE代码的速记。NOT FOUND是对所有以02开头
的SQLSTATE代码的速记。SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获
的SQLSTATE代码的速记。当用户不想为每个可能的出错消息都定义一个处理程序时可以使用
以上三种形式。
mysql_error_code是具体的SQLSTATE代码。除了SQLSTATE值,MySQL错误代码也被支持,表
示的形式为:ERROR= 'xxxx'。
● sp_statement:处理程序激活时将要执行的动作。
例: 创建一个存储过程,向XS表插入一行数据('081101', '王民', '计算机', 1, '1990-02-10',50
, NULL, NULL),已知学号081101在XS表中已存在。如果出现错误,程序继续进行。
USE XSCJ;
DELIMITER $$
CREATE PROCEDURE MY_INSERT ()
BEGIN
DECLARECONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;
INSERTINTO XS VALUES('081101', '王民', '计算机', 1, '1990-02-10', 50 , NULL, NULL);
END$$
DELIMITER ;
说明:在调用存储过程后,未遇到错误消息时处理程序未被激活,当执行INSERT语句出现出
错消息时,MySQL检查是否为这个错误代码定义了处理程序。如果有,则激活该处理程序,本
例中,INSERT语句导致的错误消息刚好是SQLSTATE代码中的一条。接下来执行处理程序的附
加语句(SET @x2=1)。此后,MySQL检查处理程序的类型,这里的类型为CONTINUE,因此
存储过程继续处理,将用户变量x赋值为3。如果这里的INSERT语句能够执行,处理程序将不
被激活,用户变量x2将不被赋值。
注意:不能为同一个出错消息在同一个BEGIN-END语句块中定义两个或更多的处理程序。
为了提高可读性,可以使用DECLARE CONDITION语句为一个SQLSTATE或出错代码定义一个
名字,并且可以在处理程序中使用这个名字。
DECLARE CONDITION语法格式为:
DECLARE condition_name CONDITION FORcondition_value
其中,condition_value:
SQLSTATE [VALUE] sqlstate_value
| mysql_error_code
说明:condition_name是处理条件的名称,condition_value为要定义别名的SQLSTATE或出错
代码。
例: 修改上例中的存储过程,将SQLSTATE '23000' 定义成NON_UNIQUE,并在处理程序中使
用这个名称。程序片段为:
BEGIN
DECLARE NON_UNIQUE CONDITION FOR SQLSTATE '23000';
DECLARE CONTINUE HANDLER FOR NON_UNIQUE SET @x2=1;
SET @x=2;
INSERT INTO XS VALUES('081101', '王民', '计算机', 1, '1990-02-10', 50 , NULL, NULL);
SET @x=3;
END;
12. 游标
一条SELECT...INTO语句返回的是带有值的一行,这样可以把数据读取到存储过程中。但是常
规的SELECT语句返回的是多行数据,如果要处理它需要引入游标这一概念。MySQL支持简单
的游标。在MySQL中,游标一定要在存储过程或函数中使用,不能单独在查询中使用。
使用一个游标需要用到4条特殊的语句:DECLARE CURSOR(声明游标)、OPEN CURSOR(打
开游标)、FETCH CURSOR(读取游标)和CLOSE CURSOR(关闭游标)。
如果使用了DECLARE CURSOR语句声明了一个游标,这样就把它连接到了一个由SELECT语句
返回的结果集中。使用OPEN CORSOR语句打开这个游标。接着,可以用FETCH CURSOR语句
把产生的结果一行一行地读取到存储过程或存储函数中去。游标相当于一个指针,它指向当前
的一行数据,使用FETCH CORSOR语句可以把游标移动到下一行。当处理完所有的行时,使用
CLOSECURSOR语句关闭这个游标。
(1)声明游标
语法格式:DECLAREcursor_name cursor for select_statement
说明:cursor_name是游标的名称,游标名称使用与表名同样的规则。select_statement是一
个SELECT语句,返回的是一行或多行的数据。这个语句声明一个游标,也可以在存储过程中
定义多个游标,但是一个块中的每一个游标必须有唯一的名字。
注意:这里的SELECT子句不能有INTO子句。
下面的定义符合一个游标声明:
DECLARE XS_CUR1 CURSOR FOR
SELECT 学号,姓名,性别,出生日期,总学分
FROM XS
WHERE 专业名 = '计算机';
注意:游标只能在存储过程或存储函数中使用,例中语句无法单独运行。
(2)打开游标
声明游标后,要使用游标从中提取数据,就必须先打开游标。在MySQL中,使用OPEN语句打
开游标,其格式为:OPEN cursor_name
在程序中,一个游标可以打开多次,由于其他的用户或程序本身已经更新了表,所以每次打开
结果可能不同。
(3)读取数据
游标打开后,就可以使用fetch…into语句从中读取数据。
语法格式:FETCH cursor_nameINTO var_name [, var_name] ...
说明:FETCH ...INTO语句与SELECT...INTO语句具有相同的意义,FETCH语句是将游标指向的
一行数据赋给一些变量,子句中变量的数目必须等于声明游标时SELECT子句中列的数目。
var_name是存放数据的变量名。
(4)关闭游标
游标使用完以后,要及时关闭。关闭游标使用CLOSE语句,格式为:
CLOSE cursor_name语句参数的含义与OPEN语句中相同。
例如: CLOSE XS_CUR2 将关闭游标XS_CUR2。
例: 创建一个存储过程,计算XS表中行的数目。
DELIMITER $$
CREATE PROCEDURE compute (OUT NUMBERINTEGER)
BEGIN
DECLAREXH CHAR(6);
DECLAREFOUND BOOLEAN DEFAULT TRUE;
DECLARENUMBER_XS CURSOR FOR
SELECT学号 FROM XS;
DECLARECONTINUE HANDLER FOR NOT FOUND
SETFOUND=FALSE;
SETNUMBER=0;
OPENNUMBER_XS;
FETCHNUMBER_XS INTO XH;
WHILEFOUND DO
SETNUMBER=NUMBER+1;
FETCHNUMBER_XS INTO XH;
ENDWHILE;
CLOSENUMBER_XS;
END$$
DELIMITER ;
利用游标循环更新数据提示出错 No data - zero rows fetched, selected, or processed
转自:http://blog.csdn.net/codegallery/article/details/14384963
更新数据时发现利用游标更新数据时出错,但是数据还是可以更新的。
错误提示就是如下
No data - zero rows fetched, selected, or processed
这个是因为FETCH没有退出循环
简单光标在存储程序和函数内被支持,语法如同在嵌入的SQL中,光标当前是不敏感的,只读
的及不滚动的。不敏感以为服务器可以活不可以复制它的结果表。
光标必须在声明处理程序之前被声明,并且变量和条件必须在声明光标或处理程序之前被声明
。
例如
[html] view plaincopyprint?
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
OPEN cur2;
REPEAT
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF NOT done THEN
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
CLOSE cur2;
END
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
OPEN cur2;
REPEAT
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF NOT done THEN
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
CLOSE cur2;
END
加入这么一句 [html] view plaincopyprint?
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;就不会提示错误了
MySQL 修改表字段优化方案
转自:http://blog.csdn.net/m582445672/article/details/5880878
要求:现在需要要改一下字段名,增加一个字段,在删除另一个字段,改一个字段类型.
建立如下表(原表):
CREATE TABLE month(
id INT,
week VARCHAR(10),
day VARCHAR(10),
hour VARCHAR(10),
PRIMARY KEY (id)
);
得到的最终表为:
CREATE TABLE month(
id INT ,
week VARCHAR(10),
today VARCHAR(20), -- 把day VARCHAR(10)改为 today VARCHAR(20)
minute VARCHAR(20), -- 增加新的字段 minute VARCHAR(20),删除字段hour VARCHAR
(10)
PRIMARY KEY (id)
);
方案1:
ALTER TABLE month CHANGE day today VARCHAR(20);
ALTER TABLE month DROP COLUMN hour;
ALTER TABLE month add minute VARCHAR(20);
案例1 分析:
ALTER TABLE 在MYSQL后台的操作是
1. 新建一临时表
2. 复制数据到临时表
3. 删除原表
4. 更名临时表为原表名
时间主要的消耗在 "复制数据到临时表".
复制数据到临时表这一步,影响时间的因素有,索引维护,数据插入,锁
所以随着修改字段的数目增加,都需要重复以上的操作.当数据量很大时,复制数据到临时表的
时间就会更长.
案例2:
CREATE TABLE temp_month(
id INT,
week VARCHAR(10),
today VARCHAR(20),
minute VARCHAR(20) DEFAULT '00'
);
INSERT INTO temp_month(id, week, today) (SELECT id, week, day FROM month);
DROP TABLE month;
ALTER TABLE temp_month RENAME TO month;
ALTER TABLE month ADD PRIMARY KEY(id);
案例2 分析:
自己创建一个临时表,把需要改的字段一次性修改完,用insert对所有修改的字段只用了一次“复
制数据”.然后更改表名(更改表名是不需要扫描全表的)
最在后加主键(这样可以避免复制表的过程中,每插入一行数据都要对索引进行的维护操作.最后
加索引是一次性对全表进行维护)
==========================================================
==========================================================
========
以上两种方案可以看出,随着需要更改的字段的增加.方案2比方案1的速度是单调递增的.
如果使用方案1:建议设置tmp_table_size参数大小,此参数用于系统建立临时表的大小.
建议设置:sort_buffer_size 参数大小,此参数用于排序的缓存.对索引维护会使用到这个参数.
如果你使用表的存储引擎是InnoDB.可以更改innodb_buffer_pool_size(windows:%
mysql%/my.ini. linux:/etc/mysql/my.cnf)设置来加快修改的速度.
特别是对于大量数据的表.建议设置大小为内存大小的50%-80%.