说明:本文为面向PL/SQL开发初学者的指导手册
标签:PL/SQL、批量创建JOB、LIGHTWEIGHT、匿名块、pipe、管道、
说明:lightweight job适用于多个频繁执行的小作业
易学:文中删去了不需要的多余部分,让初学者一目了然一学就会
温馨提示:如果您发现本文哪里写的有问题或者有更好的写法请留言或私信我进行修改优化
--创建常规存储过程
set serveroutput on
set timing on
create or replace procedure p_select
is
i simple_integer := 1;
j simple_integer := 2000000000;
v simple_integer := 0;
message char(10);
pipename char(20) := 'pipe_zzt_select';
begin
for i in 1 .. j loop
select /*+ no_result_cache */
count(*) into v
from zzt.info a, zzt.info b
where a.sal != b.sal
and a.id < 100
and a.sal < 50000;
if dbms_pipe.receive_message(pipename, 0) = 0 then
dbms_pipe.unpack_message(message);
exit when message = 'stop';
end if;
end loop;
v := dbms_pipe.remove_pipe(pipename);
end;
/
--创建lightweight-job需要的存储过程/转换常规
EXEC DBMS_SCHEDULER.DROP_PROGRAM('ZZT_P_L_SELECT');
begin
dbms_scheduler.create_program(program_name => 'ZZT_P_L_SELECT',
program_type => 'STORED_PROCEDURE',
program_action => 'ZZT.P_SELECT',
enabled => true,
comments => 'lightweight job program');
end;
/
select count(*) from dba_scheduler_programs where PROGRAM_NAME='ZZT_P_L_SELECT';
--批量创建lightweight-job
declare
i simple_integer := 1;
j simple_integer := 20;
interval varchar2(50) := 'freq=secondly;interval=60'; --间隔xx秒
starttime date := sysdate; --立即开始执行
endtime date := sysdate + 1 / 24 / 60 * 10; --持续10分钟
v_job_name varchar2(30);
begin
for i in 1 .. j loop
v_job_name := 'ZZT_JOB_A_' || i;
dbms_scheduler.create_job(job_name => v_job_name,
program_name => 'ZZT_P_L_SELECT',
job_style => 'LIGHTWEIGHT',
repeat_interval => interval,
start_date => starttime,
end_date => endtime,
enabled => true,
comments => 'zzt测试lightweight-job');
end loop;
end;
/
--查看相关信息
select count(*) from dba_scheduler_jobs j where j.owner='ZZT' and j.JOB_STYLE='LIGHTWEIGHT';
select * from dba_scheduler_jobs j where j.owner='ZZT' and j.JOB_STYLE='LIGHTWEIGHT';
select * from dba_scheduler_running_jobs;
※ 如果您觉得文章写的还不错, 别忘了在文末给作者点个赞哦 ~
over