1.创建存储过程(如果是没有参数的存储过程(V_PARAM IN VARCHAR) 这部分是需要删除。
CREATE OR REPLACE PROCEDURE PROCNAME(V_PARAM IN VARCHAR) AS
---定义变量
V_CODE VARCHAR2(100);
V_SUM_CNT NUMBER(22);
V_BEGIN_TIME DATE;
V_END_TIME DATE;
V_SQL VARCHAR2(512);
V_HOUR NUMBER;
----定义游标
Cursor jobcursor is select DEPT FROM TABLE_NAME1;
BEGIN
---变量初始化
V_CODE := 'TEST'||V_REGION;
V_BEGIN_TIME := SYSDATE-1;
V_END_TIME := SYSDATE ;
V_HOUR :=0;
--获取当前分钟数,赋给变量V_HOUR
SELECT TO_CHAR(SYSDATE,'HH24') INTO V_HOUR FROM DUAL;
------游标的遍历
for job in jobcursor LOOP
DBMS_OUTPUT.PUT_LINE(job.job);;
end LOOP;
-----------调用sql变量的执行方式。
V_SQL := 'SELECT /*+ PARALLEL(A ,16) */ COUNT(*) FROM '||V_CODE ||'.TABLE_LOG A
where logtime BETWEEN :V_BEGIN_TIME AND :V_END_TIME' ;
EXECUTE IMMEDIATE V_SQL into V_SUM_CNT using V_BEGIN_TIME,V_END_TIME;
-----------其他的逻辑处理
-------异常处理逻辑
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
END;
---------存储过程的执行方法(注意斜杠前面不要有任何空格)
/
2.oracle定时job的实现
SET SERVEROUTPUT ON;
DECLARE
JOB_ID NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
JOB => JOB_ID, /*自动生成JOB_ID*/
WHAT => 'PROCNAME(''test'');', /*需要执行的过程或SQL语句,注意存储过程后面要有分号*/
NEXT_DATE =>trunc(add_months(last_day(sysdate), -1)+25), /*初次执行时间,每月25日零点*/
INTERVAL =>'TRUNC(LAST_DAY(SYSDATE ))+25' /*每月25日零点,间隔时间*/
);
COMMIT;
DBMS_OUTPUT.PUT_LINE('PROC_CXCRM_CUSM_011_JOB ID:' || JOB_ID);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('PROC_CXCRM_CUSM_011_JOB ERROR MSG:' || SQLERRM(SQLCODE));
END;
/
3.oracle 删除定时job
SET SERVEROUTPUT ON;
DECLARE
Cursor jobcursor is select job FROM DBA_JOBS a where a.WHAT like '%?%' ;
begin
for job in jobcursor LOOP
DBMS_OUTPUT.PUT_LINE(job.job);
DBMS_JOB.REMOVE(job.job);
end LOOP;
commit;
end;
/