Orcale存储过程结合游标使用

最新推荐文章于 2024-08-20 01:01:24 发布

weixin_34195364

最新推荐文章于 2024-08-20 01:01:24 发布

阅读量87

点赞数

文章标签： php python 数据库

原文链接：https://my.oschina.net/u/2472104/blog/637357

版权

2019独角兽企业重金招聘Python工程师标准>>>

需求：我每天凌晨左右执行任务调度，插入微信用户关注分析的总数、关注数、取消关注数进行每日初始化。

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--游标

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;

转载于:https://my.oschina.net/u/2472104/blog/637357