-----------------------------------------定时任务--------------------------------------------
---查看正在运行的 Job
select * from dba_jobs_running;

---创建 JOB
declare
--- job的ID号
job_id number;
begin
--- 每天晚上12点执行一次存储过程
dbms_job.submit(job_id, 'grab_supplement;', sysdate, 'trunc(sysdate + 1)');
end;
---每天晚上12点执行一次：trunc(sysdate + 1)
---每10分钟执行一次：sysdate + 10/1440

---查看job id
select job,what,next_date from dba_jobs where what like 'grab_supplement%';

---删除JOB
begin
dbms_job.remove(892);
end;
/

---运行JOB
begin
dbms_job.run(890);
end;
/

-----------------------------------------存储过程--------------------------------------------
CREATE OR REPLACE PROCEDURE grab_supplement
IS
total_flows number;---临时变量
prize_first number;
prize_first_amount number;
prize_second number;
prize_second_amount number;
prize_three number;
prize_three_amount number;
prize_four number;
prize_four_amount number;
no_prize number;

cursor cur_trophy_pool is select t.prize_level,t.config_jackpot_amount from td_trophy_pool t;

BEGIN
----查询数量-----
select t.prize_amount into prize_first_amount from td_trophy_pool t where t.prize_level = 1;
select t.prize_amount into prize_second_amount from td_trophy_pool t where t.prize_level = 2;
select t.prize_amount into prize_three_amount from td_trophy_pool t where t.prize_level = 3;
select t.prize_amount into prize_four_amount from td_trophy_pool t where t.prize_level = 4;

-----拆分---
select sum(t.prize_name * t.prize_amount) into total_flows from td_trophy_pool t where t.prize_level !=4;
if total_flows>=5 then
update td_trophy_pool t set t.prize_amount = t.prize_amount + (total_flows/5) where t.prize_level = 4;
update td_trophy_pool t set t.prize_amount = 0 where t.prize_level != 4;
commit;
end if;

----补充----
for trophy_pool in cur_trophy_pool loop
update td_trophy_pool t set
t.prize_amount = t.prize_amount + trophy_pool.config_jackpot_amount,
t.update_time = to_char(sysdate,'yyyy-MM-dd HH24:mi:ss')
where t.prize_level = trophy_pool.prize_level;
commit;
end loop;

END grab_supplement;