****************************
最终版三个过程，一个临时存储表 一个日志表
#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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/11813230/viewspace-695273/
扫一扫
2416
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
