delimiter $$
use `test`$$
drop procedure if exists `proc_cursor_t`$$
use `test`$$CREATE PROCEDURE `proc_cursor_t`(p_code varchar(30), p_cumu int)
SQL SECURITY INVOKER
begin
declare _done int default 0;
declare v_flag tinyint default 0;
declare v_item_id int default 0;
declare v_item_code varchar(20) default '';
declare cur_code cursor for
select '0001' item_id, '0002' item_code union all
select '1001' item_id, '1002' item_code union all
select '2001' item_id, '2002' item_code union all
select '3001' item_id, '3002' item_code union all
select '4001' item_id, '4002' item_code ;
declare continue handler for not found
begin
set _done=1;
select 'not found';
commit;
end;
-- handler exception
declare exit handler for sqlexception
begin
-- 异常处理
select 'exception';
end;
-- 打开游标
open cur_code;
allcodes:loop
-- 开始遍历游标
fetch cur_code into v_item_id, v_item_code;
if not _done then
-- 开始业务操作
if p_cumu = 2 then
select 'cumu 2', v_item_id, v_item_code,now();
else
select 'cumu 1', v_item_id, v_item_code,now();
end if;
else
leave allcodes;
end if;
end loop allcodes;
-- 关闭游标
close cur_code;
end
执行call proc_cursor_t(82,2); 之后,怎么遍历了一遍啊?
是不是msyql数据库服务器的环境有问题呢?
use `test`$$
drop procedure if exists `proc_cursor_t`$$
use `test`$$CREATE PROCEDURE `proc_cursor_t`(p_code varchar(30), p_cumu int)
SQL SECURITY INVOKER
begin
declare _done int default 0;
declare v_flag tinyint default 0;
declare v_item_id int default 0;
declare v_item_code varchar(20) default '';
declare cur_code cursor for
select '0001' item_id, '0002' item_code union all
select '1001' item_id, '1002' item_code union all
select '2001' item_id, '2002' item_code union all
select '3001' item_id, '3002' item_code union all
select '4001' item_id, '4002' item_code ;
declare continue handler for not found
begin
set _done=1;
select 'not found';
commit;
end;
-- handler exception
declare exit handler for sqlexception
begin
-- 异常处理
select 'exception';
end;
-- 打开游标
open cur_code;
allcodes:loop
-- 开始遍历游标
fetch cur_code into v_item_id, v_item_code;
if not _done then
-- 开始业务操作
if p_cumu = 2 then
select 'cumu 2', v_item_id, v_item_code,now();
else
select 'cumu 1', v_item_id, v_item_code,now();
end if;
else
leave allcodes;
end if;
end loop allcodes;
-- 关闭游标
close cur_code;
end
执行call proc_cursor_t(82,2); 之后,怎么遍历了一遍啊?
是不是msyql数据库服务器的环境有问题呢?
+--------+-----------+-------------+---------------------+
| cumu 2 | v_item_id | v_item_code | now() |
+--------+-----------+-------------+---------------------+
| cumu 2 | 1 | 0002 | 2011-03-16 08:44:43 |
+--------+-----------+-------------+---------------------+
1 row in set (0.00 sec)+--------+-----------+-------------+---------------------+
| cumu 2 | v_item_id | v_item_code | now() |
+--------+-----------+-------------+---------------------+
| cumu 2 | 1001 | 1002 | 2011-03-16 08:44:43 |
+--------+-----------+-------------+---------------------+
1 row in set (0.01 sec)+--------+-----------+-------------+---------------------+
| cumu 2 | v_item_id | v_item_code | now() |
+--------+-----------+-------------+---------------------+
| cumu 2 | 2001 | 2002 | 2011-03-16 08:44:43 |
+--------+-----------+-------------+---------------------+
1 row in set (0.01 sec)+--------+-----------+-------------+---------------------+
| cumu 2 | v_item_id | v_item_code | now() |
+--------+-----------+-------------+---------------------+
| cumu 2 | 3001 | 3002 | 2011-03-16 08:44:43 |
+--------+-----------+-------------+---------------------+
1 row in set (0.01 sec)+--------+-----------+-------------+---------------------+
| cumu 2 | v_item_id | v_item_code | now() |
+--------+-----------+-------------+---------------------+
| cumu 2 | 4001 | 4002 | 2011-03-16 08:44:43 |
+--------+-----------+-------------+---------------------+
1 row in set (0.01 sec)+-----------+
| not found |
+-----------+
| not found |
+-----------+
1 row in set (0.01 sec)Query OK, 0 rows affected (0.01 sec)mysql>用你的代码测试没有问题,5。1.32
我知道啊,我的是跑在虚拟机上面的linux下的mysql,你能说下问题所在吗?
SQL_MODE 是什么东东啊?show variables like '%SQL_MODE%';
(1).通过设置不同的sql mode,可以在不同严格程序进行数据校验.有效地保证了数据准确性.
(2).通过设置sql mode为ANSI模式,来保证大多数SQL符合标准SQL的语法,这样在不同数据库之间迁移时,不需要对业务 修改太多.在Mysql 5.0以下,查询默认的sql mode(sql mode参数)有:real_as_float,pipes_as_concat,ansi_quotes,gnore_space和ANSI。在这些模式下可以插入超过字段定义长度的数据,或是在字段中没有定义的元素数据(如,enum)。不过在插入后会有一个warning(可以用 show warnings来查看)。可以通过设置sql mode为STRICT_TRANS_TABLES(严格模式)来实现数据的严格校检,使错误数据不能插入,从而保证数据准确性。TRADITIONAL模式也属于严格模式,同样可以实现严格校检,使错误数据不能插入,从而保证数据准确性。不过在这种模式MAX(X,0)返回的结果是NULL,所以在包含有MAX的运算中根据实际情况设定好sql mode.
我没有windows下的mysql,我这里都是linux下的mysql。我的mysql版本是5.1.41-log
-- 结果如下
'sql_mode', ''
不会 not found啊,我有5条记录,为什么只跑一条啊?
use `test` $$
drop procedure if exists proc_alter_engine_all $$
use `test` $$create procedure proc_alter_engine_all()
SQL SECURITY INVOKER
begin
declare v_tbname varchar(500) default '';
declare v_schema varchar(500) default '';
declare v_flag int default 0;
declare _done int default 0;
declare v_sqlcounts varchar(500) default '';
declare cur_tbnames cursor for
select table_schema,table_name
from information_schema.tables
where table_schema not in('information_schema','mysql','test') and engine='MyISAM' order by table_schema, table_name limit 3;
declare continue handler for not found
begin
set v_flag=1;
set _done=1;
select 'continue handler not found';
commit;
end;
-- handler exception
declare exit handler for sqlexception
begin
set v_flag=0;
set _done=1;
select 'exit handler sqlexception';
end;
open cur_tbnames;
select ' cur start 1 !'; allcode:loop
fetch cur_tbnames into v_schema, v_tbname;
if not _done then
set v_sqlcounts = '';
set v_sqlcounts = concat('alter table ',v_schema,'.',v_tbname, ' engine=\'innodb\'');
-- set @sqlcounts := v_sqlcounts;
-- prepare stmt from @sqlcounts;
-- execute stmt ;
-- deallocate prepare stmt;
-- select v_sqlcounts;
else
leave allcode;
end if;
select v_schema, v_tbname;
end loop allcode;
close cur_tbnames;
select ' cur end !';
end