oracle存储过程创建与job创建执行

– Create table
create table ZB
(
zbnum varchar2(15),
ZBBM VARCHAR2(500) not null,
ZBFL VARCHAR2(50) not null,
ZBMC VARCHAR2(100),
ZBZS VARCHAR2(500),
ZBGS VARCHAR2(500)
)
select * from zb for update;
drop table zb;
–月收入 ZBBM:对应option的value ZBZS:对应option的text
select a.* from crm.zb a where a.zbfl=‘MONTHIMCOME’ for update;
select * from zb a where a.zbfl=‘MONTHIMCOME’;
–创建存储过程
create or replace procedure pro_getYearMonth as
cursor cur_ym is SELECT TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(sysdate,‘yyyy-MM’), ‘yyyy-MM’), - ROWNUM ),‘yyyyMM’) as monthlist FROM DUAL CONNECT BY ROWNUM <=12;
begin
delete from zb k where k.zbfl=‘MONTHIMCOME’;
commit;
for cur_ in cur_ym
loop
insert into zb values(cur_.monthlist,cur_.monthlist,‘MONTHIMCOME’,cur_.monthlist,cur_.monthlist,‘MONTHIMCOME’);
commit;
end loop;
commit;
end;
/
–执行job
declare
job number;
begin
dbms_job.submit(job, ‘pro_getYearMonth;’, sysdate, ‘TRUNC(sysdate,’‘mi’’) + 1 / (2460)’);
end;
/
–查询zb表
select * from zb k where k.zbfl=‘MONTHIMCOME’;
select job,broken,what,interval,t. from user_jobs t;
delete from zb a where a.zbfl=‘MONTHIMCOME’;
–删除job
begin
dbms_job.remove(21);
end;