PL/SQL使用记录

CREATE OR REPLACE FUNCTION USER_EXP_TIME_FUN(iExpTime IN NUMBER)
RETURN NUMBER
IS
iExpMin number:=trunc(iExpTime/60);
iLevel number:=0;
iNowUpExp number:=0;
iLastUpExp number:=2*60;
iCheckLevelExp number:=0;
iNextLevelExp number:=0;
BEGIN
    IF iExpMin < 120 THEN 
       iLevel:=1;
    ELSE
      FOR i IN 1..100 LOOP
          iNowUpExp:=iLastUpExp+(i-1)*120;
          iCheckLevelExp:=iCheckLevelExp+iNowUpExp;
          IF iExpMin >= iCheckLevelExp THEN
              iNextLevelExp:=iCheckLevelExp+iNowUpExp+i*120;
              IF iExpMin < iNextLevelExp THEN
                iLevel:=i+1;
              END IF;
          END IF;
          iLastUpExp:=iNowUpExp;
      END LOOP;
    END IF;
    return iLevel;
END;
/

CREATE OR REPLACE PROCEDURE daily_rank_pro IS
 type rc is ref cursor;
 money_rank_cur rc;
 integral_rank_cur rc;
 exp_time_rank_cur rc;
 competition_rank_cur rc;
 competition_expect_num number;
 competition_level_id number;
 rank_user_name varchar2(20);
 rank_result number;
 rank_count number:=1;
BEGIN
 delete from user_rank;
 open money_rank_cur for select aa.user_name user_name , (aa.money + aa.bank_money) money from ((select user_name , money , bank_money from users t where t.vip_type = 96 and t.money + t.bank_money >= 2000000 order by money + bank_money desc) aa) where rownum <= 10;
 loop
      fetch money_rank_cur into rank_user_name, rank_result;
      exit when money_rank_cur%notfound;
      insert into user_rank values(user_rank_seq.nextval, rank_user_name, rank_count, 0, rank_result);
      rank_count:=rank_count+1;
 end loop;
 close money_rank_cur;
 rank_count:=1;

 open integral_rank_cur for select aa.user_name user_name , (aa.integral + aa.bank_integral) integral from ((select user_name , integral , bank_integral from users t where t.vip_type = 96 and t.integral + t.bank_integral >= 50000 order by integral + bank_integral desc) aa) where rownum <= 10;
 loop
      fetch integral_rank_cur into rank_user_name, rank_result;
      exit when integral_rank_cur%notfound;
      insert into user_rank values(user_rank_seq.nextval, rank_user_name, rank_count, 1, rank_result);
      rank_count:=rank_count+1;
 end loop;
 close integral_rank_cur;
 rank_count:=1;

 open exp_time_rank_cur for select aa.user_name user_name , aa.exp_time exp_time from ((select user_name , exp_time  from users t where t.vip_type = 96 order by exp_time desc) aa) where rownum <= 10;
 loop
      fetch exp_time_rank_cur into rank_user_name, rank_result;
      exit when exp_time_rank_cur%notfound;
      insert into user_rank values(user_rank_seq.nextval, rank_user_name, rank_count, 2, USER_EXP_TIME_FUN(rank_result));
      rank_count:=rank_count+1;
 end loop;
 close exp_time_rank_cur;
 rank_count:=1;

 select EXPECT_NUM, COMPETE_LEVEL_ID into competition_expect_num, competition_level_id from competition_game_log c where c.EXPECT_NUM = (select max(EXPECT_NUM) from competition_game_log where game_id = 121);
 open competition_rank_cur for select pc.user_name,pc.duel_integral from previous_competition pc where pc.expect_num = competition_expect_num and  pc.compete_level_id = competition_level_id and pc.game_id = 121 and pc.emiction <= 10  order by pc.emiction;
 loop
   fetch competition_rank_cur into rank_user_name, rank_result;
   exit when competition_rank_cur%notfound;
   insert into user_rank values(user_rank_seq.nextval, rank_user_name, rank_count, 3, rank_result);
   rank_count:=rank_count+1;
 end loop;
 close competition_rank_cur;
END;
/