Oracle定时任务执行存储过程带参数 - 好文

存储过程：
create or replace procedure pro_test (retCode out number, retMsg out varchar2)
is vcrm v_prod_inst%ROWTYPE; TYPE ref_cursor_type IS REF CURSOR; use_cursor
ref_cursor_type; vunit recharge.unit%TYPE; vsummoney
recharge.recharge_money%TYPE; vsumlen flow_detail.length%TYPE; vsumflow
flow_detail.sum_flow%TYPE; vsumd acct_book.init_value%TYPE; vonline
prod_use_daily.is_online%TYPE; BEGIN   retCode := 0; retMsg := '操作成功';
vonline := 0; --获取号码 open use_cursor for select * from v_prod_inst t where
t.prod_status !='D' and t.customer_status!='D'; loop fetch use_cursor into
vcrm ; EXIT WHEN use_cursor%NOTFOUND; select
min(UNIT),nvl(sum(RECHARGE_MONEY),0) into vunit, vsummoney from recharge where
RECHARGE_STATUS='S' and STATUS='E' and ACC_NBR=vcrm.accnbr and
to_char(CREATE_TIME,'YYYY-MM-DD')= to_char(sysdate-1,'YYYY-MM-DD') ; select
nvl(sum(LENGTH),0) ,nvl(sum(SUM_FLOW),0) into vsumlen,vsumflow from
flow_detail where STATUS='E' and ACC_NBR=vcrm.accnbr and
FLOW_DATE=to_char(sysdate-1,'YYYY-MM-DD') ; select nvl(sum(INIT_VALUE),0) into
vsumd from acct_book c,account a ,sub_account b where
to_char(c.create_time,'YYYY-MM_DD')=to_char(sysdate-1,'YYYY-MM_DD') and
a.acc_nbr=vcrm.accnbr and c.ACCT_BOOK_TYPE_ID in (7, 8, 9, 10, 11, 12) and
a.id=b.acct_id and a.status= 'E' and b.status='E' and c.SUB_ACCT_ID=b.id and
c.ACCT_ID=a.id and c.STATUS = 'E' and c.BUSI_STATUS != 'O'; if (vsumlen>0 or
vsumflow>0) then vonline := 1; end if; if (vsummoney>0 or vsumlen>0 or
vsumflow>0 or vsumd>0) then insert into prod_use_daily (id, use_date,
acc_nbr, prod_inst_id, region_code,recharge_money, money_unit,flow_unit,
flow_total, flow_time, buy_flow_total, is_online, status, create_time) values
(S_prod_use_daily.Nextval,to_char(sysdate,'YYYYMMDD'),vcrm.accnbr,vcrm.id,vcrm.REGION_CODE,vsummoney,vunit,'B',vsumflow,vsumlen,vsumd,vonline,'E',sysdate);
end if; end loop; close use_cursor; commit; exception when others then
rollback; retCode := -1; retMsg := sqlerrm; end ;

定时任务：

----功能说明：每日凌晨0点定时执行存储过程
declare job_use_daily number;    begin
dbms_job.submit(job_use_daily,'declare retCode number; retMsg varchar2(200);
begin pro_test (retCode,retMsg); end;',sysdate,'trunc(sysdate)+1');
commit;    end;

热门工具换一换