游标应用例子

drop procedure if exists test;
create procedure test(IN MGR varchar(10),IN ACCESST DATETIME)
begin  
   declare no_more_record INT DEFAULT 0;
   DECLARE dn varchar(255);
   declare kw varchar(255);
   declare url varchar(255);
   declare cur_record CURSOR FOR SELECT domain FROM tbl_siteinfo where manager=MGR;
   
   declare CONTINUE HANDLER FOR NOT FOUND SET no_more_record = 1;
   
   drop table if exists tmp_table;
   CREATE TEMPORARY TABLE tmp_table(
          keyword varchar(255),
          uri varchar(255),
          se varchar(255),
          ip varchar(255),
          region varchar(255),
          accesstime datetime
   );   
   truncate tmp_table;
   
   OPEN cur_record;
   FETCH cur_record INTO dn;
   
   WHILE no_more_record != 1 DO
         insert into tmp_table(keyword,uri,se,ip,region,accesstime) select keyword,uri,se,ip,region,accesstime from tbl_keywordaccess where uri like concat('%',dn,'%') and accesstime >= ACCESST;   
         FETCH cur_record INTO dn;
   END WHILE;
   select * from tmp_table;
   CLOSE cur_record;
end