1.建立表
create table PO_JQ_TYPE_STAT_MONTH
(
ID NUMBER not null,
STATSJ NVARCHAR2(30),
STATTYPEDM VARCHAR2(30),
PCSDM VARCHAR2(12),
COUNT NUMBER,
MONTHCOUNT NUMBER,
FJDM VARCHAR2(12)
)
2.建立序列
-- Create sequence
create sequence SEQ_PO_JQ_TYPE_STAT_MONTH
minvalue 1
maxvalue 999999999999999999999999999
start with 1893973
increment by 1
cache 20;
3.建立存儲過程
create or replace procedure T_JQ_PT_MONTH is
begin
INSERT INTO PO_JQ_TYPE_STAT_MONTH (ID,STATSJ,STATTYPEDM,PCSDM,COUNT,MONTHCOUNT,FJDM)
SELECT SEQ_PO_JQ_TYPE_STAT_MONTH.NEXTVAL, TO_CHAR( STATSJ,'YYYYMM') STATSJ, STATTYPEDM, PCSDM, COUNT, DAYS, FJDM FROM
(SELECT P.STATSJ STATSJ ,P.STATTYPEDM STATTYPEDM,P.PCSDM PCSDM,P.COUNT COUNT,P.FJDM FJDM ,
(select to_char( add_months(last_day(sysdate),-1) ,'dd') days from dual) DAYS
-- (select to_char( last_day(p.statsj) ,'DD') from PO_JQ_TYPE_STAT_DAY p WHERE to_char(P.STATSJ, 'yyyy-MM') = to_char( add_months(last_day(sysdate),-1),'yyyy-mm')) DAYS
FROM PO_JQ_TYPE_STAT_DAY P WHERE to_char(P.STATSJ, 'yyyyMM') = to_char( add_months(last_day(sysdate),-1),'yyyymm'))
commit;
end T_JQ_PT_MONTH;
4.建立job
declare job number; begin sys.dbms_job.submit(job, what => 'T_JQ_PT_MONTH;', next_date => to_date('04-09-2013 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), interval => 'to_date(to_char(sysdate+1,''yyyy/mm/dd'') || '' 01:00:00'',''yyyy/mm/dd hh24:mi:ss'')');--每天1440分鐘，即一分鐘運作test過程一次 commit; end;