小弟 最近 整 mysql 存储过程 但是 建立一个 存储过程 ,中间用到了 游标 ,但是编译一直不通过 很费解,求 MYSQL 高手 指点 ,感激不尽~~~ ,我用到 2 个 的 游标 其实 是想 从同一个表中获取 数据 ,但是由于不知道MYSQL 游标如何实现动态参数传递所以就改用从不同的2个表中获取数据,但是仍然有问题 ,顾求解~~~原游标:declare fetchSqlCursor_D cursor for SELECT luv1.visi_words as visi_words , DATE_FORMAT(luv1.visi_time , '%Y-%m-%d') as visi_time , count(*) as visi_count FROM lss_userVisit luv1 WHERE DATE_FORMAT( luv1.visi_time ,'%Y-%m') = DATE_FORMAT(CURDATE() , '%Y-%m') GROUP BY luv1.visi_words ORDER BY visi_count desc ;declare fetchSqlCursor_M cursor for SELECT luv1.visi_words as visi_words ,DATE_FORMAT(luv1.visi_time ,'%Y-%m-%d') as visi_time , count(*) as visi_count FROM lss_userVisit luv1 WHERE DATE_FORMAT(luv1.visi_time ,'%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE() ,INTERVAL 1 DAY) , '%Y-%m') GROUP BY luv1.visi_words ORDER BY visi_count desc;语句 基本相同 不同之处 以用 标红~~源码:------------------------------------------------------------------------------BEGIN
DECLARE fetchSqlOk boolean ; ## define the varient for store the data
DECLARE _visi_words varchar(20) ;
DECLARE _visi_time varchar(20) ;
DECLARE _visi_count int ;
DECLARE _day int ;
SET _day = 01 ;/** 改查 2个 表中 的游标 仍然报错**/ DECLARE fetchSqlCursor_D CURSOR for SELECT luv1.visi_words as visi_words , DATE_FORMAT(luv1.visi_time , '%Y-%m') as visi_time , count(1) as visi_count FROM lss_userVisit luv1 WHERE DATE_FORMAT( luv1.visi_time ,'%Y-%m') = DATE_FORMAT(CURDATE() , '%Y-%m') GROUP BY luv1.visi_words ORDER BY visi_count desc ;DECLARE fetchSqlCursor_M CURSOR for SELECT luv1.st1_words as visi_words , DATE_FORMAT(luv1.st1_date , '%Y-%m') as visi_time , sum(luv1.st1_count) as visi_count FROM lss_wordsSortd luv1 WHERE DATE_FORMAT( luv1.st1_date ,'%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE() ,INTERVAL 1 DAY) , '%Y-%m') GROUP BY luv1.st1_words ORDER BY visi_count desc; DECLARE continue handler for NOT FOUND set fetchSqlOk = false; ## define the cursor for not found flagSET fetchSqlOk = true; if day( curdate( ) ) = _day then
## 如果 系统时间 为 当日的 第一日 者 将 执行 上月的 统计
## if month first day no update up month data
open fetchSqlCursor_M;
fetchSqlLoop:Loop
if fetchSqlOk then
fetch fetchSqlCursor_M into _visi_words , _visi_time , _visi_count ;
insert into lss_wordsSortd( st1_date , st1_words , st1_count ) values( _visi_time , _visi_words , _visi_count);
else
leave fetchSqlLoop ;
end if ;
end Loop ;
close fetchSqlCursor_M ;
else
## 如果 系统 时间 不为 当月 第一天 者 执行 当月的 统计
## update this month data
open fetchSqlCursor_D ;
fetchSqlLoop:Loop
if fetchSqlOk then
fetch fetchSqlCursor_D into _visi_words , _visi_time , _visi_count ;
insert into lss_wordsSortd( st1_date , st1_words , st1_count ) values( _visi_time , _visi_words , _visi_count ) ;
else
leave fetchSqlLoop ;
end if ;
end Loop ;
close fetchSqlCursor_D ;
end if ;
#DEALLOCATE fetchSqlCursor_D;
#DEALLOCATE fetchSqlCursor_M;
COMMIT;
END;谁有更好的建议 望求解~~~
DECLARE fetchSqlOk boolean ; ## define the varient for store the data
DECLARE _visi_words varchar(20) ;
DECLARE _visi_time varchar(20) ;
DECLARE _visi_count int ;
DECLARE _day int ;
SET _day = 01 ;/** 改查 2个 表中 的游标 仍然报错**/ DECLARE fetchSqlCursor_D CURSOR for SELECT luv1.visi_words as visi_words , DATE_FORMAT(luv1.visi_time , '%Y-%m') as visi_time , count(1) as visi_count FROM lss_userVisit luv1 WHERE DATE_FORMAT( luv1.visi_time ,'%Y-%m') = DATE_FORMAT(CURDATE() , '%Y-%m') GROUP BY luv1.visi_words ORDER BY visi_count desc ;DECLARE fetchSqlCursor_M CURSOR for SELECT luv1.st1_words as visi_words , DATE_FORMAT(luv1.st1_date , '%Y-%m') as visi_time , sum(luv1.st1_count) as visi_count FROM lss_wordsSortd luv1 WHERE DATE_FORMAT( luv1.st1_date ,'%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE() ,INTERVAL 1 DAY) , '%Y-%m') GROUP BY luv1.st1_words ORDER BY visi_count desc; DECLARE continue handler for NOT FOUND set fetchSqlOk = false; ## define the cursor for not found flagSET fetchSqlOk = true; if day( curdate( ) ) = _day then
## 如果 系统时间 为 当日的 第一日 者 将 执行 上月的 统计
## if month first day no update up month data
open fetchSqlCursor_M;
fetchSqlLoop:Loop
if fetchSqlOk then
fetch fetchSqlCursor_M into _visi_words , _visi_time , _visi_count ;
insert into lss_wordsSortd( st1_date , st1_words , st1_count ) values( _visi_time , _visi_words , _visi_count);
else
leave fetchSqlLoop ;
end if ;
end Loop ;
close fetchSqlCursor_M ;
else
## 如果 系统 时间 不为 当月 第一天 者 执行 当月的 统计
## update this month data
open fetchSqlCursor_D ;
fetchSqlLoop:Loop
if fetchSqlOk then
fetch fetchSqlCursor_D into _visi_words , _visi_time , _visi_count ;
insert into lss_wordsSortd( st1_date , st1_words , st1_count ) values( _visi_time , _visi_words , _visi_count ) ;
else
leave fetchSqlLoop ;
end if ;
end Loop ;
close fetchSqlCursor_D ;
end if ;
#DEALLOCATE fetchSqlCursor_D;
#DEALLOCATE fetchSqlCursor_M;
COMMIT;
END;谁有更好的建议 望求解~~~
要达到的效果就是 :
用 一个 游标 将 标红的地方实现动态 传入,不用再 用两个游标了
declare fetchSqlCursor_M cursor for SELECT luv1.visi_words as visi_words ,DATE_FORMAT(luv1.visi_time ,'%Y-%m-%d') as visi_time , count(*) as visi_count FROM lss_userVisit luv1 WHERE DATE_FORMAT(luv1.visi_time ,'%Y-%m') = ? GROUP BY luv1.visi_words ORDER BY visi_count desc;? : 是 根据条件 判断 的 可以是 DATE_FORMAT(CURDATE() , '%Y-%m') 也可以是 DATE_FORMAT(DATE_SUB(CURDATE() ,INTERVAL 1 DAY) , '%Y-%m'); 不知道你懂么?
你看这个清理测试数据的存储过程,里面就用到游标,传递的参数是数据库名称。你的游标不是在存储过程中???那写在哪里?
begin
if a>100 then a=100;
declare fetchSqlCursor_M cursor for SELECT * from tb where id<a;
.......
end;是要这样的结果么?
DECLARE fetchSqlCursor_M cursor for SELECT luv1.visi_words as visi_words ,DATE_FORMAT(luv1.visi_time ,'%Y-%m') as visi_time , count(*) as visi_count FROM lss_userVisit luv1 WHERE DATE_FORMAT(luv1.visi_time ,'%Y-%m') = _Cursor_M GROUP BY luv1.visi_words ORDER BY visi_count desc;
能看懂么?
SET _day = 01 ;
恩 跟上面不同,请问能有什么方法解决呢?
SET _day = 01 ; ## DECLARE _Cursor_M VARCHAR; IF day( curdate( ) ) = _day then SET _Cursor_M = DATE_FORMAT(DATE_SUB(CURDATE() ,INTERVAL 1 DAY) , '%Y-%m') GROUP BY luv1.visi_words ORDER BY visi_count desc; ELSE SET _Cursor_M = DATE_FORMAT(CURDATE() , '%Y-%m') GROUP BY luv1.visi_words ORDER BY visi_count desc;
END IF; DECLARE fetchSqlCursor_M cursor for SELECT luv1.visi_words as visi_words ,DATE_FORMAT(luv1.visi_time ,'%Y-%m') as visi_time , count(*) as visi_count FROM lss_userVisit luv1 WHERE DATE_FORMAT(luv1.visi_time ,'%Y-%m') = _Cursor_M ;
你说的是这样实现么?但是我还是编译不过去呢?
BEGIN
DECLARE fetchSqlOk boolean ; ## define the varient for store the data
DECLARE _visi_words varchar(20) ;
DECLARE _visi_time varchar(20) ;
DECLARE _visi_count int ; IF day( curdate( ) ) = _day then
SET _Cursor_W = "DATE_FORMAT(DATE_SUB(CURDATE() ,INTERVAL 1 DAY) , '%Y-%m') GROUP BY luv1.visi_words ORDER BY visi_count desc";
ELSE
SET _Cursor_W = "DATE_FORMAT(CURDATE() , '%Y-%m') GROUP BY luv1.visi_words ORDER BY visi_count desc";
END IF;
DECLARE fetchSqlCursor CURSOR FOR SELECT luv1.visi_words as visi_words , DATE_FORMAT( luv1.visi_time , '%Y-%m' ) as visi_time , count(*) as visi_count FROM lss_userVisit luv1 WHERE DATE_FORMAT( luv1.visi_time , '%Y-%m' ) = _Cursor_W ;
DECLARE CONTINUE HANDLER for NOT FOUND set fetchSqlOk = false ; ## define the cursor for not found flag
set fetchSqlOk = true ; OPEN fetchSqlCursor;
fetchSqlLoop:Loop
if fetchSqlOk then
FETCH fetchSqlCursor into _visi_words , _visi_time , _visi_count ;
insert into lss_wordsSortm( st2_month , st2_words , st2_count ) values( _visi_time , _visi_words , _visi_count ) ;
else
leave fetchSqlLoop ;
end if ;
end Loop ;
CLOSE fetchSqlCursor ;
COMMIT;
END;`lss_wordsSortm`DROP TABLE IF EXISTS `lss_wordsSortm`;
CREATE TABLE `lss_wordsSortm` (
`st2_id` int(11) NOT NULL AUTO_INCREMENT,
`st2_month` varchar(16) DEFAULT NULL,
`st2_words` varchar(72) DEFAULT NULL,
`st2_count` int(11) DEFAULT NULL,
`st2_sort` int(11) DEFAULT NULL,
PRIMARY KEY (`st2_id`),
UNIQUE KEY `words_sort2_PK` (`st2_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of lss_wordsSortm
-- ----------------------------
INSERT INTO `lss_wordsSortm` VALUES ('1', '2010-10-02', '月排行1', '34', null);
INSERT INTO `lss_wordsSortm` VALUES ('2', '2010-11-11', '月排行2', '34', null);
INSERT INTO `lss_wordsSortm` VALUES ('3', '2010-11-02', '月排行3', '55', null);
INSERT INTO `lss_wordsSortm` VALUES ('4', '2010-11-02', '月排行4', '78', null);
`lss_userVisit`
CREATE TABLE `lss_userVisit` (
`visi_id` int(11) NOT NULL AUTO_INCREMENT,
`client_id` varchar(64) DEFAULT NULL,
`visi_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`visi_words` varchar(72) DEFAULT NULL,
`visi_condt` varchar(128) DEFAULT NULL,
`client_type` varchar(255) DEFAULT NULL,
PRIMARY KEY (`visi_id`),
UNIQUE KEY `user_visit_PK` (`visi_id`)
) ENGINE=MyISAM AUTO_INCREMENT=130 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of lss_userVisit
-- ----------------------------
INSERT INTO `lss_userVisit` VALUES ('1', '127.0.0.1', '2010-10-28 21:56:44', '张三', null, '1');
INSERT INTO `lss_userVisit` VALUES ('2', null, '2010-10-28 21:56:44', '祝福', null, null);
INSERT INTO `lss_userVisit` VALUES ('3', null, '2010-01-01 22:56:44', '乐PHONE', null, null);
INSERT INTO `lss_userVisit` VALUES ('4', null, '2010-10-26 22:56:44', '联想', null, null);
INSERT INTO `lss_userVisit` VALUES ('5', null, '2010-10-28 21:56:44', '地方', null, null);
INSERT INTO `lss_userVisit` VALUES ('6', null, '2010-10-28 21:56:44', '祝福测试1', null, null);
INSERT INTO `lss_userVisit` VALUES ('7', null, '2010-01-10 22:56:44', '研究院', null, null);
INSERT INTO `lss_userVisit` VALUES ('8', null, '2010-10-28 21:56:44', '测试', null, null);
INSERT INTO `lss_userVisit` VALUES ('9', null, '2010-10-28 21:56:44', '北京', null, null);
INSERT INTO `lss_userVisit` VALUES ('10', null, '2010-10-28 21:56:44', '中关村', null, null);
INSERT INTO `lss_userVisit` VALUES ('11', null, '2010-01-10 22:56:44', '天朝', null, null);
INSERT INTO `lss_userVisit` VALUES ('12', null, '2010-01-26 22:56:44', '河蟹', null, null);
INSERT INTO `lss_userVisit` VALUES ('13', null, '2010-10-28 21:56:44', '兰州', null, null);
可以。
create procedure sp()
begin
declare ID int;
select max(id) into ID from tb;
if ID>100 then a=100;
declare fetchSqlCursor_M cursor for SELECT * from tb where id<a;
.......
end;
改成如下即可。CREATE PROCEDURE `proc_wordsSortm`(IN _Cursor_W VARCHAR(50))
BEGIN
DECLARE fetchSqlOk boolean ;
DECLARE _visi_words varchar(20) ;
DECLARE _visi_time varchar(20) ;
DECLARE _visi_count int ;
DECLARE fetchSqlCursor CURSOR FOR SELECT luv1.visi_words as visi_words , DATE_FORMAT( luv1.visi_time , '%Y-%m' ) as visi_time , count(*) as visi_count FROM lss_userVisit luv1 WHERE DATE_FORMAT( luv1.visi_time , '%Y-%m' ) = _Cursor_W ;
DECLARE CONTINUE HANDLER for NOT FOUND set fetchSqlOk = false ;
set fetchSqlOk = true ; IF day( curdate( ) ) = _day then
SET _Cursor_W = "DATE_FORMAT(DATE_SUB(CURDATE() ,INTERVAL 1 DAY) , '%Y-%m') GROUP BY luv1.visi_words ORDER BY visi_count desc";
ELSE
SET _Cursor_W = "DATE_FORMAT(CURDATE() , '%Y-%m') GROUP BY luv1.visi_words ORDER BY visi_count desc";
END IF; OPEN fetchSqlCursor;
fetchSqlLoop:Loop
if fetchSqlOk then
FETCH fetchSqlCursor into _visi_words , _visi_time , _visi_count ;
insert into lss_wordsSortm( st2_month , st2_words , st2_count ) values( _visi_time , _visi_words , _visi_count ) ;
else
leave fetchSqlLoop ;
end if ;
end Loop ;
CLOSE fetchSqlCursor ;
COMMIT;
END;
只要在OPEN 之前定义好var_id的值就行了。
我发现 var_id 参数 如果是 通过 存储过程 传进来的是可以,但是如果是内部定义并赋值是不可以的。貌似因为 mysql的 DECLARE 执行 优先级 比 set 高,而且是吧所有 DECLARE 执行完成以后才会执行 SET,而MYSQL 的游标 应该是 一次性定义完成 不能更改,所以 针对 你上面要求 改位子的那个我试过也是通不过的,杯具啊 ,只有在建立一个存储过程然后判断后传参了~~~