存储过程

来源：互联网发布：单片机智能饮水机编辑：程序博客网时间：2024/06/05 21:17

BEGIN
   DECLARE Done INT DEFAULT 0;
   DECLARE t_tjid INT DEFAULT 0;
   DECLARE t_type INT DEFAULT 0;
   DECLARE scount INT DEFAULT 0;
   declare i int DEFAULT 0;
   DECLARE app_except_ids VARCHAR(50) DEFAULT '';
   DECLARE zt_except_ids VARCHAR(50) DEFAULT '';
   #DECLARE t_sql_1 VARCHAR(350) DEFAULT '';
   DECLARE cur_l CURSOR FOR select tjid,type from jptj;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = 1;

   delete from tempRecommand;
   OPEN cur_l;
   REPEAT
   FETCH cur_l INTO t_tjid,t_type;
               IF Done = 0 THEN
                   IF t_type=1 THEN

                       IF EXISTS(select * from soft s,article a,down d where s.aid=t_tjid and s.aid=a.id and d.aid=s.aid and (s.freesrc!='' or RIGHT(TRIM(s.iossrc),4)='.ipa')) THEN
                           INSERT INTO tempRecommand(rid,typeid,icon,`name`,starlev,iossrc,freesrc,iosver,hotcount) select a.id,a.typeid,s.titpic,a.shorttitle,s.pingfen,s.iossrc,s.freesrc,s.iosver,d.downloads from soft s,article a,down d where d.aid=s.aid and s.aid=t_tjid and s.aid=a.id and (s.freesrc!='' or RIGHT(TRIM(s.iossrc),4)='.ipa');
                       end if;
                       set app_except_ids = CONCAT(app_except_ids,',',t_tjid);
                   ELSE
                       IF EXISTS(select id,icon,`name`,starlev,subtit,browsetimes from zhuanti where id=t_tjid) THEN
                           insert into tempRecommand(rid,icon,`name`,starlev,subtit,hotcount) select id,icon,`name`,starlev,subtit,browsetimes from zhuanti where id=t_tjid;
                       end if;
                       set zt_except_ids = CONCAT(zt_except_ids,',',t_tjid);
                   END IF;
               END IF;
           UNTIL Done = 1 END REPEAT;
   CLOSE cur_l;
   set app_except_ids = CONCAT(0,app_except_ids);
   set zt_except_ids = CONCAT(0,zt_except_ids);
   set @t_count = CONCAT("select count(*) into @scount from soft s,article a where s.aid=a.id and a.id not in (",app_except_ids,") and (s.freesrc!='' or RIGHT(TRIM(s.iossrc),4)='.ipa');");
   PREPARE data1 FROM @t_count;
   EXECUTE data1;
#select count(*) INTO scount from soft s,article a where s.aid=a.id and a.id not in (app_except_ids) and (s.freesrc!='' or RIGHT(TRIM(s.iossrc),4)='.ipa');
   loop1: WHILE i<@scount DO
       set @t_sql_1 = CONCAT("INSERT INTO tempRecommand(rid,typeid,icon,`name`,starlev,iossrc,freesrc,iosver,hotcount) select a.id,a.typeid,s.titpic,a.shorttitle,s.pingfen,s.iossrc,s.freesrc,s.iosver,d.downloads from soft s,article a,down d where d.aid=s.aid and s.aid=a.id and (s.freesrc!='' or RIGHT(TRIM(s.iossrc),4)='.ipa') and a.id not in (",app_except_ids,") limit ",i,",",1);
       PREPARE data1 FROM @t_sql_1;
       EXECUTE data1;
       set @t_sql_2 = CONCAT("insert into tempRecommand(rid,icon,`name`,starlev,subtit,hotcount) select id,icon,`name`,starlev,subtit,browsetimes from zhuanti where id not in (",zt_except_ids,") limit ",i,",",1);
       PREPARE data2 FROM @t_sql_2;
       EXECUTE data2;
       set i=i+1;
   END WHILE loop1;
   #select i;
   #select t_sql_1;
   #INSERT INTO tempRecommand(rid,typeid,icon,`name`,starlev,iossrc,freesrc,iosver) select a.id,a.typeid,s.titpic,a.shorttitle,s.pingfen,s.iossrc,s.freesrc,s.iosver from soft s,article a where s.aid=10 and s.aid=a.id and (s.freesrc!='' or RIGHT(TRIM(s.iossrc),4)='.ipa');
END

0 0