/*
项目案例：
主要功能：编写scheduler，自动生成投资收益数据
*/
一、准备数据
-- Create table create table INVESTMENT_INCOME_STATEMENT ( SID VARCHAR2(32) not null, BOOK_DATE DATE, TRADING_DATE DATE, TRADE_LOCATION VARCHAR2(64), EXPENDITURE NUMBER(18,2), INCOME NUMBER(18,2), ACCOUNT_BLANCE NUMBER(18,2), PAYMENT_ACCOUNT VARCHAR2(32), ACCOUNT_PAYEE VARCHAR2(32), INVESTMENT_PROJECT_TYPE VARCHAR2(64), CURRENCY_TYPE VARCHAR2(16), SUMMARY VARCHAR2(64) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64 next 1 minextents 1 maxextents unlimited ); -- Create/Recreate primary, unique and foreign key constraints alter table INVESTMENT_INCOME_STATEMENT add primary key (SID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );
-- Create sequence create sequence INVEST_SEQ minvalue 1 maxvalue 999999999 start with 102 increment by 1 nocache;
CREATE OR REPLACE FUNCTION GET_SID RETURN VARCHAR2 IS l_prefix VARCHAR2(2) := 'AT'; l_system_timestamp VARCHAR2(14) :=TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'); l_sequence_no NUMBER(9); BEGIN SELECT INVEST_SEQ.NEXTVAL INTO l_sequence_no FROM DUAL; RETURN l_prefix|| '_' ||l_system_timestamp || '_' || TO_CHAR(l_sequence_no); END GET_SID;
二、编写存储过程,grogram要调用的producre
CREATE OR REPLACE FUNCTION COMPUTE_ACCT_BLANCE_20130301 ( p_current_income ARCER.INVESTMENT_INCOME_STATEMENT.ACCOUNT_BLANCE%TYPE ) RETURN NUMBER IS l_current_acct_blance NUMBER(18,2); l_actual_acct_blance NUMBER(18,2); BEGIN SELECT ACCOUNT_BLANCE INTO l_current_acct_blance FROM INVESTMENT_INCOME_STATEMENT WHERE SID = (select max(SID) from investment_income_statement WHERE INVESTMENT_PROJECT_TYPE='2013_03_01#30000#700#24M') ; l_actual_acct_blance :=l_current_acct_blance + p_current_income; RETURN l_actual_acct_blance; END; CREATE OR REPLACE PROCEDURE AUTO_INST_INVEST_DATA_20130301 IS l_sid VARCHAR2(32) := GET_SID; l_book_date DATE := sysdate; l_trading_date DATE := sysdate; l_trade_location VARCHAR2(64) := '500000902重庆市分行核算中心'; l_expenditure NUMBER(18,2) := 0; l_income NUMBER(18,2) := 700; l_account_blance NUMBER(18,2) := COMPUTE_ACCT_BLANCE_20130301(l_income); l_payment_account VARCHAR2(32) := '6227 0037 **** *533 207'; l_account_payee VARCHAR2(32) := '6227 0037 6900 0475 269'; l_investment_project_type VARCHAR2(64) := '2013_03_01#30000#700#24M'; l_currency_type VARCHAR2(16) := '人民币'; l_summary VARCHAR2(64) := '转账存入'; BEGIN --2013_03_01#20000#700#24M INSERT INTO INVESTMENT_INCOME_STATEMENT( SID, BOOK_DATE, TRADING_DATE, TRADE_LOCATION, EXPENDITURE, INCOME, ACCOUNT_BLANCE, PAYMENT_ACCOUNT, ACCOUNT_PAYEE, INVESTMENT_PROJECT_TYPE, CURRENCY_TYPE, SUMMARY ) VALUES( l_sid, l_book_date, l_trading_date, l_trade_location, l_expenditure, l_income, l_account_blance, l_payment_account, l_account_payee, l_investment_project_type, l_currency_type, l_summary ); COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Err:adding record:' || l_sid); END;
CREATE OR REPLACE FUNCTION COMPUTE_ACCT_BLANCE_20120530 ( p_current_income ARCER.INVESTMENT_INCOME_STATEMENT.ACCOUNT_BLANCE%TYPE ) RETURN NUMBER IS l_current_acct_blance NUMBER(18,2); l_actual_acct_blance NUMBER(18,2); BEGIN SELECT ACCOUNT_BLANCE INTO l_current_acct_blance FROM INVESTMENT_INCOME_STATEMENT WHERE SID = (select max(SID) from investment_income_statement WHERE INVESTMENT_PROJECT_TYPE='2012_05_30#30000#700#30M') ; l_actual_acct_blance :=l_current_acct_blance + p_current_income; RETURN l_actual_acct_blance; END; CREATE OR REPLACE PROCEDURE AUTO_INST_INVEST_DATA_20120530 IS l_sid VARCHAR2(32) := GET_SID; l_book_date DATE := sysdate; l_trading_date DATE := sysdate; l_trade_location VARCHAR2(64) := ''; l_expenditure NUMBER(18,2) := 0.00; l_income NUMBER(18,2) := 700; l_account_blance NUMBER(18,2) := COMPUTE_ACCT_BLANCE_20120530(l_income); l_payment_account VARCHAR2(32) := '6227 0779 **** *220'; l_account_payee VARCHAR2(32) := '6227 0037 6900 0475 269'; l_investment_project_type VARCHAR2(64) := '2012_05_30#30000#700#30M'; l_currency_type VARCHAR2(16) := '人民币'; l_summary VARCHAR2(64) := '转账存入'; BEGIN --2012_05_30#30000#700#30M INSERT INTO INVESTMENT_INCOME_STATEMENT( SID, BOOK_DATE, TRADING_DATE, TRADE_LOCATION, EXPENDITURE, INCOME, ACCOUNT_BLANCE, PAYMENT_ACCOUNT, ACCOUNT_PAYEE, INVESTMENT_PROJECT_TYPE, CURRENCY_TYPE, SUMMARY ) VALUES( l_sid, l_book_date, l_trading_date, l_trade_location, l_expenditure, l_income, l_account_blance, l_payment_account, l_account_payee, l_investment_project_type, l_currency_type, l_summary ); COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Err:adding record:' || l_sid); END;
CREATE OR REPLACE FUNCTION COMPUTE_ACCT_BLANCE_20111102 ( p_current_income ARCER.INVESTMENT_INCOME_STATEMENT.ACCOUNT_BLANCE%TYPE ) RETURN NUMBER IS l_current_acct_blance NUMBER(18,2); l_actual_acct_blance NUMBER(18,2); BEGIN SELECT ACCOUNT_BLANCE INTO l_current_acct_blance FROM INVESTMENT_INCOME_STATEMENT WHERE SID = (select max(SID) from investment_income_statement WHERE INVESTMENT_PROJECT_TYPE='2011_11_02#60000#2250#60M') ; l_actual_acct_blance :=l_current_acct_blance + p_current_income; RETURN l_actual_acct_blance; END; CREATE OR REPLACE PROCEDURE AUTO_INST_INVEST_DATA_20111102 IS l_sid VARCHAR2(32) := GET_SID; l_book_date DATE := sysdate; l_trading_date DATE := sysdate; l_trade_location VARCHAR2(64) := '500000902重庆市分行核算中心'; l_expenditure NUMBER(18,2) := 0; l_income NUMBER(18,2) := 2250; l_account_blance NUMBER(18,2) := COMPUTE_ACCT_BLANCE_20111102(l_income); l_payment_account VARCHAR2(32) := '6227 0037 **** *533 207'; l_account_payee VARCHAR2(32) := '6227 0037 6900 0475 269'; l_investment_project_type VARCHAR2(64) := '2011_11_02#60000#2250#60M'; l_currency_type VARCHAR2(16) := '人民币'; l_summary VARCHAR2(64) := '转账存入'; BEGIN --2011_11_02#60000#2250#60M INSERT INTO INVESTMENT_INCOME_STATEMENT( SID, BOOK_DATE, TRADING_DATE, TRADE_LOCATION, EXPENDITURE, INCOME, ACCOUNT_BLANCE, PAYMENT_ACCOUNT, ACCOUNT_PAYEE, INVESTMENT_PROJECT_TYPE, CURRENCY_TYPE, SUMMARY ) VALUES( l_sid, l_book_date, l_trading_date, l_trade_location, l_expenditure, l_income, l_account_blance, l_payment_account, l_account_payee, l_investment_project_type, l_currency_type, l_summary ); COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Err:adding record:' || l_sid); END;
CREATE OR REPLACE PROCEDURE AUTO_INSERT_INVEST_DATA_PROC IS BEGIN --AUTO_INST_INVEST_DATA_20110630; AUTO_INST_INVEST_DATA_20111102; AUTO_INST_INVEST_DATA_20120530; AUTO_INST_INVEST_DATA_20130301; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Err:adding record:'); END;
三、create program
--create program BEGIN DBMS_SCHEDULER.CREATE_PROGRAM( program_name => 'invest_program', program_action => 'ARCER.AUTO_INSERT_INVEST_DATA_PROC', program_type => 'STORED_PROCEDURE', enabled => TRUE ); END; /
四、create schedule
--create schedule BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE( schedule_name => 'schedule_invest', start_date => SYSTIMESTAMP, end_date => SYSTIMESTAMP + 30, REPEAT_INTERVAL => 'FREQ=MONTHLY; BYMONTHDAY=-1', comments => 'Every Minutely' ); END; /
五、create job
--create job BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name => 'ARCER.job_invest', program_name => 'invest_program', schedule_name => 'schedule_invest' ); END; /
六、enable job
--enable job BEGIN DBMS_SCHEDULER.ENABLE('ARCER.job_invest'); END; /
七、Monitor job
select job_name,enabled from user_scheduler_jobs; SQL> select job_name,enabled,to_char(start_date,'yyyy-mm-dd hh24:mi:ss') start_date,end_date from user_scheduler_jobs; JOB_NAME ENABL START_DATE END_DATE ------------------------------ ----- ---------------------------------------- ------------------------------ JOB_INVEST TRUE 2013-05-31 22:56:52
window
通常job启动后，用户只能被动地等待其执行，一直到其执行地任务完成(或DBA手动kill对应进程)，在此期间，
执行的job将与其它活动的进程共同竞争当前系统中的资源。在9i之前就是这样。在Job Classes中也可以控制job能够
使用的资源，不过单单使用Job Classes并不能灵活的控制job在合适的时间使用适当的资源。进入10g之后，采用
dbms_scheduler的WINDOW可以缓解该问题。WINDOW 可以指定一个时间窗口，在此期间，通过与Job Classes的搭配组合，
能够有效控制job执行时支配(使用)的资源。比如说job通常是在凌晨服务器负载较低时执行，那么就可以通过WINDOW
设置在此期间，允许jobs使用更多的系统资源，而到了工作时间后，如果job仍未执行完成，为其分配另一个有限的
资源，以尽可能降低job执行占用的资源对其它业务的影响。