需求:我每天凌晨左右执行任务调度,插入微信用户关注分析的总数、关注数、取消关注数进行每日初始化。
1、Job
declare
jobChart number;
begin
dbms_job.submit(jobChart, 'PRO_CHART_DATA;', sysdate, 'TRUNC(sysdate) + 1 +1/ (24)');--每晚1点执行
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || jobChart );
COMMIT;
end;
2、存储过程加游标循环
CREATE OR REPLACE
Procedure PRO_CHART_DATA as
cursor c_job--游标
is
select * from TBL_WX_MP_CONFIG;
c_count NUMBER;
c_total NUMBER;
Begin
for c_row in c_job loop--(加上循环就出错)
--逻辑开始
select count(*) into c_count from TBL_WX_MP_USER where WECHAT_ID = c_row.WECHAT_ID AND UNSUBSCRIBE_TIME is NULL;
select count(*) into c_total from TBL_WX_MP_STATISTICS where WECHAT_ID = c_row.WECHAT_ID and YEARS = to_char(sysdate,'yyyy-MM') and "CATEGORY" = to_char(sysdate,'dd');
if(c_total >= 1)
THEN dbms_output.put_line('数据已存在');
else
--进行数据库操作
insert into HOLLY_WECHAT.TBL_WX_MP_STATISTICS ("LEGEND","CATEGORY","VALUE",YEARS,WECHAT_ID,WECHAT_NAME) values ('新增关注人数',to_char(sysdate,'dd'),'0',to_char(sysdate,'yyyy-MM'),c_row.WECHAT_ID ,c_row.WECHAT_NAME);
insert into HOLLY_WECHAT.TBL_WX_MP_STATISTICS ("LEGEND","CATEGORY","VALUE",YEARS,WECHAT_ID,WECHAT_NAME) values ('累计关注人数',to_char(sysdate,'dd'),c_count,to_char(sysdate,'yyyy-MM'),c_row.WECHAT_ID ,c_row.WECHAT_NAME);
insert into HOLLY_WECHAT.TBL_WX_MP_STATISTICS ("LEGEND","CATEGORY","VALUE",YEARS,WECHAT_ID,WECHAT_NAME) values ('取消关注人数',to_char(sysdate,'dd'),'0',to_char(sysdate,'yyyy-MM'),c_row.WECHAT_ID ,c_row.WECHAT_NAME);
Commit;
end if;
--逻辑结束
end loop;
end PRO_CHART_DATA;