急急急:
下面是我写的存储过程,但是有两个问题:
1.游标只有出现一条记录
2.去掉declare CONTINUE HANDLER FOR not found set fetchSeqOk = 1;这句话后数据能出来,但是是跳跃的,隔一条数据就没有了.
DELIMITER $$CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `SM_SHOW_DETIAL`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
declare TSM_ENTITY_ID int default -1;
declare TSM_ENTITY_CHECK_ID int default -1;
declare TSM_CRITERIA_ID int default -1;
declare TSM_CHECK_ID int default -1;
declare fetchSeqOk boolean;
declare cur cursor for select te.ENTITY_ID,te.CHECK_ID from TSM_ENTITY te;
declare CONTINUE HANDLER FOR not found set fetchSeqOk = 1;
set fetchSeqOk = 0;
open cur;
REPEAT
fetch cur into TSM_ENTITY_ID,TSM_ENTITY_CHECK_ID;
select CRITERIA_ID into TSM_CRITERIA_ID from TSM_CRI_PRIV where ENTITY_ID = TSM_ENTITY_ID;
##如果是公有阀值
if TSM_CRITERIA_ID = -1 then
select CRITERIA_ID into TSM_CRITERIA_ID from TSM_CRI_COMM where CHECK_ID = TSM_ENTITY_CHECK_ID;
select th.HOST_IP,th.HOST_NAME,tcg.GROUP_NAME,tc.CHECK_NAME,te.ENTITY_NAME,te.SEVERITY,tcc.CRITICAL,tcc.MAJOR,tcc.MINOR,tcc.NORMAL,te.ENTITY_ID
from TSM_CHECK tc,TSM_CHECK_GROUP tcg,TSM_ENTITY te,TSM_HOST th,TSM_CRI_COMM tcc
where te.HOST_ID=th.HOST_ID
and te.CHECK_GROUP_ID = tcg.CHECK_GROUP_ID
and te.CHECK_ID = tc.CHECK_ID
and te.CHECK_ID = tcc.CHECK_ID
and tcc.CRITERIA_ID = TSM_CRITERIA_ID
and te.ENTITY_ID = TSM_ENTITY_ID
ORDER by th.HOST_IP,tcg.GROUP_NAME;
else
select th.HOST_IP,th.HOST_NAME,tcg.GROUP_NAME,tc.CHECK_NAME,te.ENTITY_NAME,te.SEVERITY,tcc.CRITICAL,tcc.MAJOR,tcc.MINOR,tcc.NORMAL,te.ENTITY_ID
from TSM_CHECK tc,TSM_CHECK_GROUP tcg,TSM_ENTITY te,TSM_HOST th,TSM_CRI_PRIV tcc
where te.HOST_ID=th.HOST_ID
and te.CHECK_GROUP_ID = tcg.CHECK_GROUP_ID
and te.CHECK_ID = tc.CHECK_ID
and te.ENTITY_ID = tcc.ENTITY_ID
and tcc.CRITERIA_ID = TSM_CRITERIA_ID
and te.ENTITY_ID = TSM_ENTITY_ID
ORDER by th.HOST_IP,tcg.GROUP_NAME;
set TSM_CRITERIA_ID = -1;
end if;
until fetchSeqOk
end REPEAT;
close cur;
END$$DELIMITER ;
下面是我写的存储过程,但是有两个问题:
1.游标只有出现一条记录
2.去掉declare CONTINUE HANDLER FOR not found set fetchSeqOk = 1;这句话后数据能出来,但是是跳跃的,隔一条数据就没有了.
DELIMITER $$CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `SM_SHOW_DETIAL`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
declare TSM_ENTITY_ID int default -1;
declare TSM_ENTITY_CHECK_ID int default -1;
declare TSM_CRITERIA_ID int default -1;
declare TSM_CHECK_ID int default -1;
declare fetchSeqOk boolean;
declare cur cursor for select te.ENTITY_ID,te.CHECK_ID from TSM_ENTITY te;
declare CONTINUE HANDLER FOR not found set fetchSeqOk = 1;
set fetchSeqOk = 0;
open cur;
REPEAT
fetch cur into TSM_ENTITY_ID,TSM_ENTITY_CHECK_ID;
select CRITERIA_ID into TSM_CRITERIA_ID from TSM_CRI_PRIV where ENTITY_ID = TSM_ENTITY_ID;
##如果是公有阀值
if TSM_CRITERIA_ID = -1 then
select CRITERIA_ID into TSM_CRITERIA_ID from TSM_CRI_COMM where CHECK_ID = TSM_ENTITY_CHECK_ID;
select th.HOST_IP,th.HOST_NAME,tcg.GROUP_NAME,tc.CHECK_NAME,te.ENTITY_NAME,te.SEVERITY,tcc.CRITICAL,tcc.MAJOR,tcc.MINOR,tcc.NORMAL,te.ENTITY_ID
from TSM_CHECK tc,TSM_CHECK_GROUP tcg,TSM_ENTITY te,TSM_HOST th,TSM_CRI_COMM tcc
where te.HOST_ID=th.HOST_ID
and te.CHECK_GROUP_ID = tcg.CHECK_GROUP_ID
and te.CHECK_ID = tc.CHECK_ID
and te.CHECK_ID = tcc.CHECK_ID
and tcc.CRITERIA_ID = TSM_CRITERIA_ID
and te.ENTITY_ID = TSM_ENTITY_ID
ORDER by th.HOST_IP,tcg.GROUP_NAME;
else
select th.HOST_IP,th.HOST_NAME,tcg.GROUP_NAME,tc.CHECK_NAME,te.ENTITY_NAME,te.SEVERITY,tcc.CRITICAL,tcc.MAJOR,tcc.MINOR,tcc.NORMAL,te.ENTITY_ID
from TSM_CHECK tc,TSM_CHECK_GROUP tcg,TSM_ENTITY te,TSM_HOST th,TSM_CRI_PRIV tcc
where te.HOST_ID=th.HOST_ID
and te.CHECK_GROUP_ID = tcg.CHECK_GROUP_ID
and te.CHECK_ID = tc.CHECK_ID
and te.ENTITY_ID = tcc.ENTITY_ID
and tcc.CRITERIA_ID = TSM_CRITERIA_ID
and te.ENTITY_ID = TSM_ENTITY_ID
ORDER by th.HOST_IP,tcg.GROUP_NAME;
set TSM_CRITERIA_ID = -1;
end if;
until fetchSeqOk
end REPEAT;
close cur;
END$$DELIMITER ;
解决方案 »
- MYSQL能实现这种搜索吗
- show innodb status有关Spin Waits,Spin Rounds,Os Waits是如何解释的
- MySQL命中率的计算方式汇总
- 我的mysql经常启动不起来,无奈啊
- 数据库同步问题,发现一个新问题,请指点!!!!
- 如何合并MYSQL数据库
- 如何上传mysql数据库啊
- 在mySQL Query Browser中倒入.sql中文显示乱码,手工写还是乱码。。。
- c操作mysql数据库时错误处理有问题,请大虾们来看看,谢谢啦!
- vs2010与mysql数据库如何建立连接 进行数据交互
- 为什么能查出count(*)但是找不出 select *
- MYSQL的 Concat 函数不存在? 昨晚还存在呢!!
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `SM_SHOW_DETIAL`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
declare TSM_ENTITY_ID int default -1;
declare TSM_ENTITY_CHECK_ID int default -1;
declare TSM_CRITERIA_ID int default -1;
declare TSM_CHECK_ID int default -1;
declare fetchSeqOk boolean;
declare cur cursor for select te.ENTITY_ID,te.CHECK_ID from TSM_ENTITY te;
declare CONTINUE HANDLER FOR not found set fetchSeqOk = 1;
set fetchSeqOk = 0;
open cur;
label1:REPEAT
set fetchSeqOk=0;
fetch cur into TSM_ENTITY_ID,TSM_ENTITY_CHECK_ID;
if fetchSeqOk=1 then
LEAVE label1;
end if;
select CRITERIA_ID into TSM_CRITERIA_ID from TSM_CRI_PRIV where ENTITY_ID = TSM_ENTITY_ID;
##如果是公有阀值
if TSM_CRITERIA_ID = -1 then
select CRITERIA_ID into TSM_CRITERIA_ID from TSM_CRI_COMM where CHECK_ID = TSM_ENTITY_CHECK_ID;
select th.HOST_IP,th.HOST_NAME,tcg.GROUP_NAME,tc.CHECK_NAME,te.ENTITY_NAME,te.SEVERITY,tcc.CRITICAL,tcc.MAJOR,tcc.MINOR,tcc.NORMAL,te.ENTITY_ID
from TSM_CHECK tc,TSM_CHECK_GROUP tcg,TSM_ENTITY te,TSM_HOST th,TSM_CRI_COMM tcc
where te.HOST_ID=th.HOST_ID
and te.CHECK_GROUP_ID = tcg.CHECK_GROUP_ID
and te.CHECK_ID = tc.CHECK_ID
and te.CHECK_ID = tcc.CHECK_ID
and tcc.CRITERIA_ID = TSM_CRITERIA_ID
and te.ENTITY_ID = TSM_ENTITY_ID
ORDER by th.HOST_IP,tcg.GROUP_NAME;
else
select th.HOST_IP,th.HOST_NAME,tcg.GROUP_NAME,tc.CHECK_NAME,te.ENTITY_NAME,te.SEVERITY,tcc.CRITICAL,tcc.MAJOR,tcc.MINOR,tcc.NORMAL,te.ENTITY_ID
from TSM_CHECK tc,TSM_CHECK_GROUP tcg,TSM_ENTITY te,TSM_HOST th,TSM_CRI_PRIV tcc
where te.HOST_ID=th.HOST_ID
and te.CHECK_GROUP_ID = tcg.CHECK_GROUP_ID
and te.CHECK_ID = tc.CHECK_ID
and te.ENTITY_ID = tcc.ENTITY_ID
and tcc.CRITERIA_ID = TSM_CRITERIA_ID
and te.ENTITY_ID = TSM_ENTITY_ID
ORDER by th.HOST_IP,tcg.GROUP_NAME;
set TSM_CRITERIA_ID = -1;
end if;
until fetchSeqOk
end REPEAT;
close cur;
END$$DELIMITER ;
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
'截至2010-12-28 15:08:00 用户结帖率50.00% 正常结帖:0 当您的问题得到解答后请及时结贴.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
http://topic.csdn.net/u/20100428/09/BC9E0908-F250-42A6-8765-B50A82FE186A.html
http://topic.csdn.net/u/20100626/09/f35a4763-4b59-49c3-8061-d48fdbc29561.html8、如何给分和结贴?
http://community.csdn.net/Help/HelpCenter.htm#结帖
一般出问题的地方有:初始值没有,状态变化值没有。