1.先简单描述一下问题:**电信有2000万用户都存储在一个分区表中,每个本地网是一个分区,现在需要并行更新所有本地网的用户资料
解决方案:动态创建JOB,源码(不可直接运行,但可以借鉴,你懂的)如下:
-- CREATED ON 2012/5/28 BY WANGHUI ---动态创建并行JOB DECLARE -- LOCAL VARIABLES HERE V_JOB_NAME VARCHAR2(20); BEGIN -- TEST STATEMENTS HERE FOR C IN (SELECT * FROM LATN_AREA_INFO WHERE LATN_ID <> -1) LOOP BEGIN V_JOB_NAME := 'JOB_SERV_' || C.LATN_ID; ---删除已有JOB FOR REC IN (SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = V_JOB_NAME) LOOP SYS.DBMS_SCHEDULER.DROP_JOB(REC.JOB_NAME); END LOOP; ---动态创建新JOB SYS.DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => V_JOB_NAME, JOB_TYPE => 'STORED_PROCEDURE', JOB_ACTION => 'PRO_DAY_USER', NUMBER_OF_ARGUMENTS => 1, SCHEDULE_NAME => 'DAY_SCHEDULE', JOB_CLASS => 'DEFAULT_JOB_CLASS', ENABLED => FALSE, AUTO_DROP => TRUE, COMMENTS => ''); SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(JOB_NAME => V_JOB_NAME, ARGUMENT_POSITION => 1, ARGUMENT_VALUE => C.LATN_ID); SYS.DBMS_SCHEDULER.RUN_JOB(V_JOB_NAME); END; END LOOP; END;
2.过程1,过程2之间有关联关系,过程1运行完后才能运行过程2;
解决方案:
伪代码如下:
JOB1 : 运行过程1 ---》运行完成后,代码运行JOB2
SYS.DBMS_SCHEDULER.RUN_JOB(JOB2);
JOB2 : 运行过程2