游标,存储过程 无 delimiter $$use `eloan_tj`$$drop procedure if exists `pro_d_tender_updatecount`$$create definer=`dba`@`%` procedure `pro_d_tender_updatecount`(in pownerid int)begin -- 借款成功次数 i=循环标识符 declare cid,i int; -- 成功借
游标,存储过程 delimiter $$use `eloan_tj`$$drop procedure if exists `pro_d_tender_updatecount`$$create definer=`dba`@`%` procedure `pro_d_tender_updatecount`(in pownerid int)begin -- 借款成功次数 i=循环标识符 declare cid,i int; -- 成功借款时间','号分割 2012-03-19,2012-03-19 declare cdatestr text; -- 两次时间 declare dates,datee varchar(50); set i = 1; -- 借款成功次数,借款成功时间 select count(distinct from_unixtime(cdate,'%y-%m-%d')),group_concat(distinct from_unixtime(cdate,'%y-%m-%d') order by cdate) into cid,cdatestr from d_tender_repayment where borrowerid = pownerid; select group_concat(distinct from_unixtime(cdate,'%y-%m-%d') order by cdate) gcdate into cdatestr from d_tender_repayment where borrowerid = pownerid; while i <= cid do select substring_index(cdatestr,',',i-1) into dates; -- 上次时间 select substring_index(cdatestr,',',i) into datee; -- 这次时间 set dates = unix_timestamp(substring_index(dates,',',-1));-- 上次','最后时间 set datee = unix_timestamp(substring_index(datee,',',-1));-- 这次','最后时间 if i = 1 then update d_tender set bidding = 0 where cdate = dates and cdate = datee and ownerid = pownerid; -- 未借款成功 update d_tender set bidding = 0 where bidding is null;end$$delimiter ;call pro_d_tender(94)select ownerid,bidding,from_unixtime(cdate,'%y-%m-%d') from d_tender where ownerid = 94-- 2012-05-17-- 2013-06-25-- 2013-09-04-- 2013-12-11select distinct from_unixtime(cdate,'%y-%m-%d') gcdate from d_tender_repayment where borrowerid =94;select ownerid,bidding,from_unixtime(cdate,'%y-%m-%d') from d_tender where ownerid = 416-- 2012-03-19-- 2012-09-19select distinct from_unixtime(cdate,'%y-%m-%d') gcdate from d_tender_repayment where borrowerid =416;update d_tender set bidding = null call pro_updatecountselect * from d_tenderdrop procedure if exists pro_updatecount;delimiter $$create procedure pro_updatecount()begin -- 声明一个标志done， 用来判断游标是否遍历完成 declare done int default 0; -- 声明一个变量，用来存放从游标中提取的数据 -- 特别注意这里的名字不能与由游标中使用的列明相同，否则得到的数据都是null declare pownerid varchar(50) default null; -- 声明游标对应的 sql 语句 declare cur cursor for select ownerid from d_tender group by ownerid; -- 在游标循环到最后会将 done 设置为 1 declare continue handler for not found set done = 1; -- 执行查询 open cur; -- 遍历游标每一行 repeat -- 把一行的信息存放在对应的变量中 fetch cur into pownerid; if not done then -- 这里就可以使用 pownerid对应的信息了 call pro_d_tender_updatecount(pownerid); end if; until done end repeat; close cur;end$$delimiter ;