oracle 查询结果存新表,Oracle 定时查询数据插入新表中（job+存储过程）

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;

原文：http://www.cnblogs.com/hujinshui/p/7662189.html