ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 大乐透八等奖4个中奖情况

大乐透八等奖4个中奖情况

原创 Linux操作系统 作者:sieper 时间:2011-05-14 20:22:28 0 删除 编辑

****************************
最终版三个过程,一个临时存储表 一个日志表
#drop procedure  p_stats_dlt_8levels_test; --测试用
#drop procedure  p_stats_dlt_child_8levels_test;--测试

drop table dlt_8_prize_stats purge;
drop table log_operation_dlt purge;
drop procedure p_stats_dlt;
drop procedure  p_stats_dlt_8levels;
drop procedure  p_stats_dlt_child_8levels;
*****************************用


create or replace procedure p_stats_dlt is

  v_drawno1 varchar2(128) := '11041';
  v_drawno2 varchar2(128) := '11050';
begin
  ---单式票
  for vrdc in (select r.rdc_id, 'clct' || r.abbreviation schemas
                 from rdc r
                where rdc_id <> 0) loop
 
    p_stats_dlt_8levels(vrdc.schemas, vrdc.rdc_id, v_drawno1, v_drawno2);
    p_stats_dlt_child_8levels(vrdc.schemas,
                                   vrdc.rdc_id,
                                   v_drawno1,
                                   v_drawno2);
  end loop;

end;


------------------
create or replace procedure p_stats_dlt_8levels(sch       varchar2,
                                                p_rdc     number,
                                                p_drawno1 varchar2,
                                                p_drawno2 varchar2) is
  v_begin_time DATE := SYSDATE;

begin
  ---单式票

  execute immediate 'insert into dlt_8_prize_stats(rdc_id,prize8level,prize_cnt,draw_no)
 select g.rdc_id, case
         when numsofchar(substr(count_result, 1, 5),
                         substr(w.stake_details,
                                1,
                                instr(w.stake_details, ''+'') - 1)) = 3 and
              numsofchar(substr(count_result, 6),
                         substr(w.stake_details,
                                instr(w.stake_details, ''+'') + 1)) = 0 then
          30
         when numsofchar(substr(count_result, 1, 5),
                         substr(w.stake_details,
                                1,
                                instr(w.stake_details, ''+'') - 1)) = 2 and
              numsofchar(substr(count_result, 6),
                         substr(w.stake_details,
                                instr(w.stake_details, ''+'') + 1)) = 1 then
          21
         when numsofchar(substr(count_result, 1, 5),
                         substr(w.stake_details,
                                1,
                                instr(w.stake_details, ''+'') - 1)) = 1 and
              numsofchar(substr(count_result, 6),
                         substr(w.stake_details,
                                instr(w.stake_details, ''+'') + 1)) = 2 then
          12
         when numsofchar(substr(count_result, 1, 5),
                         substr(w.stake_details,
                                1,
                                instr(w.stake_details, ''+'') - 1)) = 0 and
              numsofchar(substr(count_result, 6),
                         substr(w.stake_details,
                                instr(w.stake_details, ''+'') + 1)) = 2 then
          2
       end as flag,
       w.prize_cnt,g.draw_no
  from ' || sch || '.win_lottery w, ' || schs ||
                    '.game_draw g
 where g.draw_id = w.draw_id
   and g.rdc_id <> 0
   and g.game_id = 33800
   and g.draw_no between  :1 and  :2
   and w.prize_level = 80
   and w.stake_num <> 0'
    using p_drawno1, p_drawno2;

  INSERT INTO log_operation_dlt
    (log_id,
     log_time,
     operator_id,
     success_flag,
     description,
     operation_type_code)
  VALUES
    (seq_log.NEXTVAL,
     SYSDATE,
     0,
     1,
     p_rdc || '分中心' || p_drawno1||'~'||p_drawno2 || '奖期复式票,耗时=' ||
     f_days2str(SYSDATE - v_begin_time),
     9);
  COMMIT;

end;

---------------------
create or replace procedure p_stats_dlt_child_8levels(sch       varchar2,
                                                           p_rdc     number,
                                                           p_drawno1 varchar2,
                                                           p_drawno2 varchar2) as
  --复式票
  v_f_d NUMBER; --前胆码中的个数
  v_f_t NUMBER; --前拖码中的个数
  v_b_d NUMBER; --后胆码中的个数
  v_b_t NUMBER; --后拖码中的个数

  v_f_idx   NUMBER; --前区#位置 区分前胆
  v_idx     NUMBER; --+位置  区分前区后区
  v_b_idx   NUMBER; --后区#位置 区分后胆
  v_f_d_len NUMBER; --前胆码投注个数
  v_f_t_len NUMBER; --前拖码投注个数
  v_b_d_len NUMBER; --后胆码投注个数
  v_b_t_len NUMBER; --后拖码投注个数

  TYPE t_tabs IS RECORD(
    stake_details win_lottery.stake_details%TYPE,
    count_result  game_draw.count_result%TYPE,
    draw_no       game_draw.draw_no%TYPE,
    rdc_id        game_draw.rdc_id%TYPE,
    multi_num     win_lottery.multi_num%TYPE,
    lottery_sn    win_lottery.lottery_sn%type);
  TYPE t_tab IS TABLE OF t_tabs INDEX BY BINARY_INTEGER;
  v_tab        t_tab;
  v_sql        varchar2(1000);
  v_begin_time DATE := SYSDATE;
BEGIN
  v_sql := ' SELECT w.stake_details, g.count_result, g.draw_no, g.rdc_id, w.multi_num,w.lottery_sn
    FROM ' || sch || '.win_lottery w, ' || sch ||
           '.game_draw g
   WHERE g.draw_id = w.draw_id
     AND g.rdc_id <> 0
     AND g.game_id = 33800
     AND g.draw_no BETWEEN :1 AND :2
     AND w.prize_level = 80
     AND w.stake_num = 0';
  execute immediate v_sql BULK COLLECT
    INTO v_tab
    using p_drawno1, p_drawno2;

  FOR i IN 1 .. v_tab.count LOOP
    v_f_idx := instr(v_tab(i).stake_details, '#'); --前区#位置
    v_idx   := instr(v_tab(i).stake_details, '+'); --+位置
    v_b_idx := instr(v_tab(i).stake_details, '#', v_idx, 1); --后区#位置
 
    v_f_d_len := v_f_idx - 1;
    v_f_t_len := v_idx - v_f_idx - 1;
    v_b_d_len := v_b_idx - v_idx - 1;
    v_b_t_len := length(v_tab(i).stake_details) - v_b_idx; --总长度-第二个#后的
 
    v_f_d := numsofchar(substr(v_tab(i).count_result, 1, 5),
                        substr(v_tab(i).stake_details, 1, v_f_idx - 1)); --前胆码中的个数
 
    v_f_t := numsofchar(substr(v_tab(i).count_result, 1, 5),
                        substr(v_tab(i).stake_details,
                               v_f_idx + 1,
                               v_f_t_len)); --前拖码中的个数
 
    v_b_d := numsofchar(substr(v_tab(i).count_result, 6),
                        substr(v_tab(i).stake_details, v_idx + 1, v_b_d_len)); --后胆码中的个数
    v_b_t := numsofchar(substr(v_tab(i).count_result, 6),
                        substr(v_tab(i).stake_details, v_b_idx + 1)); --后拖码中的个数
    --30  2种情况  胆码中3个 中的不是3个
    if v_b_d = 0 then
      INSERT INTO dlt_8_prize_stats
        (rdc_id, prize8level, prize_cnt, draw_no, lottery_sn)
        SELECT v_tab(i) .rdc_id,
               '30',
               collocate(5 - v_f_d_len, v_f_t_len - v_f_t) *
               collocate(2-v_b_d_len, v_b_t_len - v_b_t) * v_tab(i) .multi_num,
               v_tab(i) .draw_no,
               v_tab(i) .lottery_sn
          FROM dual
         WHERE (v_f_d + v_f_t) >= 3
           and v_f_d = 3
           AND v_b_d = 0;
      --and (v_b_t_len - v_b_t) > (2 - v_b_d_len);
   
      INSERT INTO dlt_8_prize_stats
        (rdc_id, prize8level, prize_cnt, draw_no, lottery_sn)
        SELECT v_tab(i) .rdc_id,
               '30',
               collocate(3 - v_f_d, v_f_t) *
               collocate(5 - v_f_d_len - (3 - v_f_d), v_f_t_len - v_f_t) *
               collocate(2 - v_b_d_len, v_b_t_len - v_b_t) * v_tab(i) .multi_num,
               v_tab(i) .draw_no,
               v_tab(i) .lottery_sn
          FROM dual
         WHERE (v_f_d + v_f_t) >= 3
           and v_f_d < 3
           AND v_b_d = 0;
      --and (v_b_t_len - v_b_t) > (2 - v_b_d_len);
    end if;
    --02
    if v_f_d = 0 AND
       ((v_b_d >= 1 and v_b_t >= 1) or (v_b_d_len = 0 and v_b_t = 2)) then
      INSERT INTO dlt_8_prize_stats
        (rdc_id, prize8level, prize_cnt, draw_no, lottery_sn)
        SELECT v_tab(i) .rdc_id,
               '2',
               collocate(5 - v_f_d_len, v_f_t_len - v_f_t) * v_tab(i) .multi_num,
               v_tab(i) .draw_no,
               v_tab(i) .lottery_sn
          FROM dual
         WHERE v_f_d = 0
              --and (v_f_t_len - v_f_t) > (5 - v_f_d_len)
           AND ((v_b_d >= 1 and v_b_t >= 1) or
               (v_b_d_len = 0 and v_b_t = 2));
    end if;
 
    --12  2种情况  前胆码为1 或不为1
    if ((v_b_d >= 1 and v_b_t >= 1) or (v_b_d_len = 0 and v_b_t = 2)) then
      INSERT INTO dlt_8_prize_stats
        (rdc_id, prize8level, prize_cnt, draw_no, lottery_sn)
        SELECT v_tab(i) .rdc_id,
               '12',
               collocate(5 - v_f_d_len, v_f_t_len - v_f_t) * v_tab(i) .multi_num,
               v_tab(i) .draw_no,
               v_tab(i) .lottery_sn
          FROM dual
         WHERE (v_f_d + v_f_t) >= 1
           and v_f_d = 1
           AND ((v_b_d >= 1 and v_b_t >= 1) or
               (v_b_d_len = 0 and v_b_t = 2));
   
      INSERT INTO dlt_8_prize_stats
        (rdc_id, prize8level, prize_cnt, draw_no, lottery_sn)
        SELECT v_tab(i) .rdc_id,
               '12',
               collocate(5 - v_f_d_len - 1, v_f_t_len - v_f_t) *
               collocate(1, v_f_t) * v_tab(i) .multi_num,
               v_tab(i) .draw_no,
               v_tab(i) .lottery_sn
          FROM dual
         WHERE (v_f_d + v_f_t) >= 1
           and v_f_d = 0
           AND ((v_b_d >= 1 and v_b_t >= 1) or
               (v_b_d_len = 0 and v_b_t = 2));
    end if;
 
    --21 有4种情况 后胆码中个数为1或0 以及  前胆码中2或  <>2 
    INSERT INTO dlt_8_prize_stats
      (rdc_id, prize8level, prize_cnt, draw_no, lottery_sn)
      SELECT v_tab(i) .rdc_id,
             '21',
             collocate(2 - v_f_d, v_f_t) *
             collocate(5 - v_f_d_len - (2 - v_f_d), v_f_t_len - v_f_t) *
             collocate(1, v_b_t_len - v_b_t) * v_tab(i) .multi_num,
             v_tab(i) .draw_no,
             v_tab(i) .lottery_sn
        FROM dual
       WHERE (v_f_d + v_f_t) >= 2
         and v_f_d < 2
         AND v_b_d = 1;
    INSERT INTO dlt_8_prize_stats
      (rdc_id, prize8level, prize_cnt, draw_no, lottery_sn)
      SELECT v_tab(i) .rdc_id,
             '21',
             collocate(5 - v_f_d_len, v_f_t_len - v_f_t) *
             collocate(1, v_b_t_len - v_b_t) * v_tab(i) .multi_num,
             v_tab(i) .draw_no,
             v_tab(i) .lottery_sn
        FROM dual
       WHERE (v_f_d + v_f_t) >= 2
         and v_f_d = 2
         AND v_b_d = 1;
 
    INSERT INTO dlt_8_prize_stats
      (rdc_id, prize8level, prize_cnt, draw_no, lottery_sn)
      SELECT v_tab(i) .rdc_id,
             '21',
             collocate(2 - v_f_d, v_f_t) *
             collocate(5 - v_f_d_len - (2 - v_f_d), v_f_t_len - v_f_t) *
             collocate(1, v_b_t) *
             collocate(2 - v_b_d_len - 1, v_b_t_len - v_b_t) * v_tab(i) .multi_num,
             v_tab(i) .draw_no,
             v_tab(i) .lottery_sn
        FROM dual
       WHERE (v_f_d + v_f_t) >= 2
         and v_f_d < 2
         AND v_b_d = 0
         and v_b_t >= 1;
    INSERT INTO dlt_8_prize_stats
      (rdc_id, prize8level, prize_cnt, draw_no, lottery_sn)
      SELECT v_tab(i) .rdc_id,
             '21',
             collocate(5 - v_f_d_len, v_f_t_len - v_f_t) *
             collocate(1, v_b_t) *
             collocate(2 - v_b_d_len - 1, v_b_t_len - v_b_t) * v_tab(i) .multi_num,
             v_tab(i) .draw_no,
             v_tab(i) .lottery_sn
        FROM dual
       WHERE (v_f_d + v_f_t) >= 2
         and v_f_d = 2
         AND v_b_d = 0
         and v_b_t >= 1;
 
    if mod(i, 20000) = 0 then
      commit;
    end if;
 
  END LOOP;

  INSERT INTO log_operation_dlt
    (log_id,
     log_time,
     operator_id,
     success_flag,
     description,
     operation_type_code)
  VALUES
    (seq_log.NEXTVAL,
     SYSDATE,
     0,
     1,
     p_rdc || '分中心' || p_drawno1||'~'||p_drawno2 || '奖期复式票,耗时=' ||
     f_days2str(SYSDATE - v_begin_time),
     9);
  COMMIT;
END;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11813230/viewspace-695273/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-03-17

  • 博文量
    87
  • 访问量
    191247