关于MySQL cursor 的应用--结帖(有兴趣的板砖拍过来)

rock0018

MySQL 版本  5.1.30.

以下是一个关于两个指针做nested loop的储存过程,该procedure是将符合条件的记录进行统计，并将结果插入到另一张表。（数据从表pts_log插入到表compcu）

CREATE DEFINER = 'root'@'localhost' PROCEDURE `chatroom_compare`(IN sday DATE, IN eday DATE)
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

declare stime,etime,chat_name,starttimes,endtimes VARCHAR(20);
declare no_more_rows ,a TINYINT(1) default 0;
declare shour,ehour TINYINT(2);
declare user_s,user_s2,durations smallint(6);

declare cursor1 CURSOR for  
       select starttime, endtime,duration,TIME_FORMAT(starttime,"%H"),TIME_FORMAT(endtime,"%H")  from pts_log
       where srcusername = "kaixin001"  and action ='dis' and endtime between cast(date(sday) as datetime) and cast(date(eday) as datetime);

declare cursor2 CURSOR for
        select AA.chatname as chatname ,AA.users as users ,BBBB.users2 as user2,aa.starttimes as starttimes,aa.endtimes as endtimes
        from   
         (
          select   
          p.DESTUSERNAME as chatname,
          count(distinct(p.SRCUSERNAME)) as  users ,
          stime as starttimes,
          etime as endtimes
          from pts_log as p ,chatroom as cu
          where
          p.starttime  between stime and etime and
          p.action = "CLC" and
          p.DESTUSERNAME =  cu.username  AND
          CU.type = 1
          group by p.DESTUSERNAME
          ) as AA
         left join
         (
          select
          cu.username as chatname ,
          COUNT(DISTINCT(BB.srcusername)) as users2
          from chatroom as Cu,
           (
            select
            count(pts_log.srcusername) as times,
            pts_log.DESTUSERNAME as username,
            pts_log.SRCUSERNAME as srcusername,
            stime as startimes,
            etime as endtimes
            from pts_log
            where
            pts_log.action ="CLC" and
            pts_log.starttime between stime and etime
            group by pts_log.srcusername
            ) as BB
         where
         BB.times > 1 and
         cu.UserName = BB.username  AND
         CU.type =1
         group by cu.username
          ) as BBBB
        on AA.chatname=BBBB.chatname ;

declare CONTINUE handler for not found  
       set no_more_rows = 1;

if EXISTS
   (
   SELECT starttime from pts_log where
   action = 'dis' and srcusername= 'kaixin001' and
   endtime between CAST(sday as datetime) and  CAST(eday as datetime)
    ) then
    set a =1;
end if ;
if  a =1 then
    open cursor1;
      LOOP1:loop  
       fetch cursor1 into stime ,etime,durations,shour,ehour ;  
          if no_more_rows THEN
          close cursor1;
          leave LOOP1;
          end if;
         
      if (durations > 600 &&( shour > 9 && ehour <21 ) ||  (shour >21) && durations < 10800  ) then
      
        open cursor2;
           LOOP2:loop  
             fetch cursor2 into chat_name,user_s, user_s2,starttimes,endtimes;
               if no_more_rows then
               close cursor2;
               leave LOOP2;
               end if;
      
               insert into compcu(chatname,users,users2,starttime,endtime) values(chat_name,user_s,user_s2,starttimes,endtimes);
           end loop LOOP2;
       end if ;
      
     end loop LOOP1;
   
end if;
END;

在使用过程中，在compu表中发现第二指针仅仅插入了第一个指针中符合条件的第一条记录。现在还没有想到问题的答案。不知道有哪位达人可以帮鄙人解答，在下不胜感激。

[ 本帖最后由 rock0018 于 2009-1-13 14:03 编辑 ]

rock0018

把它分解为两个储存过程,不使用嵌入循环,第一储存过程调用另一个就解决问题就可以了.估计是Mysql的一个小bug