關(guān)于MySQL cursor 的應(yīng)用--結(jié)帖(有興趣的板磚拍過來(lái))

rock0018

MySQL 版本  5.1.30.

以下是一個(gè)關(guān)于兩個(gè)指針做nested loop的儲(chǔ)存過程,該procedure是將符合條件的記錄進(jìn)行統(tǒng)計(jì)，并將結(jié)果插入到另一張表。（數(shù)據(jù)從表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表中發(fā)現(xiàn)第二指針僅僅插入了第一個(gè)指針中符合條件的第一條記錄�，F(xiàn)在還沒有想到問題的答案。不知道有哪位達(dá)人可以幫鄙人解答，在下不勝感激。

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

rock0018

把它分解為兩個(gè)儲(chǔ)存過程,不使用嵌入循環(huán),第一儲(chǔ)存過程調(diào)用另一個(gè)就解決問題就可以了.估計(jì)是Mysql的一個(gè)小bug