/*
项目案例：
主要功能：编写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执行占用的资源对其它业务的影响。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
