工作项目中用到的计划任务和存储过程,记录一下:
存储过程:
create or replace procedure PROC_HRM_ATTENDANCE_SIGN as
CURSOR emploeeCur is
select basic.user_pk_id user_pk_id,detail.dept_id dept_id
from JC_SYS_USER_BASIC basic,JC_SYS_USER_DETAIL detail
where basic.user_pk_id=detail.user_pk_id
and basic.user_status != '3'
--and rownum<=1
;
iCount number;
--pkIdCount number;
pkId varchar2(128);
begin
for c1 in emploeeCur loop
select count(*) into iCount
from jc_hrm_attendance_sign
where atte_user_id = c1.user_pk_id
--and atte_dept_id = c1.dept_id
and to_char(atte_date,'yyyy-MM-dd') = to_char(sysdate,'yyyy-MM-dd');
if iCount = 0 then
pkId := dbms_random.string('A',8) || '-' || dbms_random.string('A',4) || '-' || dbms_random.string('A',4) || '-' || dbms_random.string('A',12) || '-' || to_char(current_timestamp, 'YYYYMMDDHH24MISSFF');
insert into JC_HRM_ATTENDANCE_SIGN
(
atte_pk_id,
atte_dept_id,
atte_user_id,
atte_date,
--sign_in,
sign_in_status,
--sign_out,
sign_out_status,
atte_status,
validity
)
values
(
--dbms_random.string('A',8) || '-' || dbms_random.string('A',4) || '-' || dbms_random.string('A',4) || '-' || dbms_random.string('A',12),
pkId,
c1.dept_id,
c1.user_pk_id,
sysdate,
--sysdate,
'0',
--sysdate,
'0',
'111004',
'1'
);
commit;
end if;
end loop;
end PROC_HRM_ATTENDANCE_SIGN;
计划任务:
begin
sys.dbms_scheduler.create_job(job_name => 'XXOA.JOB_HRM_ATTENDANCE_SIGN',
job_type => 'STORED_PROCEDURE',
job_action => 'PROC_HRM_ATTENDANCE_SIGN',
start_date => to_date('30-10-2012 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
repeat_interval => 'Freq=Daily;Interval=1;ByHour=00;ByMinute=00;BySecond=01',
end_date => to_date(null),
job_class => 'DEFAULT_JOB_CLASS',
enabled => true,
auto_drop => true,
comments => '每天零时定时生成XXX初始数据');
end;