create table EGMAS_COUNT_DATA(TIMES date not null, COUNT NUMBER(30) not null, SYSTEM_NAME VARCHAR2(30) not null, Operation_index VARCHAR2(30) not null);-- Add comments to the columns comment on column EGMAS_COUNT_DATA.TIMES is '日期';comment on column EGMAS_COUNT_DATA.COUNT is '统计量';comment on column EGMAS_COUNT_DATA.SYSTEM_NAME is '系统编码';comment on column EGMAS_COUNT_DATA.Operation_index is '业务运营指标名称';
存储过程：
create or replace procedure orderCount is
begin
insert into EGMAS_COUNT_DATA(DAY, COUNT,SYSTEM_NAME,Operation_index)
SELECT to_date(to_char(sysdate-1,'yyyy-MM-dd'),'yyyy-MM-dd'), (select count(1) from T_ORDERS WHERE ORDER_RESOURCE = '13'
AND to_char(CREATED_TM,'yyyy-MM-dd') = to_char(sysdate-1,'yyyy-MM-dd')), 'EGMAS','Android手机订单量(港澳台)'
FROM dual;
insert into EGMAS_COUNT_DATA(DAY, COUNT,SYSTEM_NAME,Operation_index)
SELECT to_date(to_char(sysdate-1,'yyyy-MM-dd'),'yyyy-MM-dd'), (select count(1) from T_ORDERS WHERE ORDER_RESOURCE = '14'
AND to_char(CREATED_TM,'yyyy-MM-dd') = to_char(sysdate-1,'yyyy-MM-dd')), 'EGMAS','IPHONE手机订单量(港澳台)'
FROM dual;
insert into EGMAS_COUNT_DATA(DAY, COUNT,SYSTEM_NAME,Operation_index)
SELECT to_date(to_char(sysdate-1,'yyyy-MM-dd'),'yyyy-MM-dd'), (select count(1) from T_ORDERS WHERE
to_char(CREATED_TM,'yyyy-MM-dd') = to_char(sysdate-1,'yyyy-MM-dd')), 'EGMAS','速运通订单量(港澳台)'
FROM dual;
commit;
end orderCount;
/
设置job执行时间：
variable jobno number;
begin
dbms_job.submit(:jobno,'orderCount;', sysdate, 'TRUNC(sysdate+1)+1/24');
end;
/
启动job：
begin
dbms_job.run(44);
commit;
end;
/
查看job是否存在：
select job, next_date, next_sec, failures, broken from user_jobs;
