第31次活动：2017-11-08（星期三晚上7:00-9:30）
轻量级作业和作业矩阵：create table t05317_lw( a timestamp ) ;
create or replace procedure proc05317_lw
is
v_1 number;
begin
select count(*) into v_1 from employees a, employees b;
insert intot05317_lw values(systimestamp);
commit;
end;
begin
dbms_scheduler.create_job(
job_name => 'hr.job8',
program_name=>'hr.program8',
schedule_name=>'hr.schedule8',
job_style=>'LIGHTWEIGHT');
end;
select* from t05317_lw;
selectj.job_name, j.stop_on_window_close,j.JOB_STYLE, j.ENABLED
from user_scheduler_jobsj
where j.job_namelike 'JOB%';
begin
dbms_scheduler.enable('HR.JOB8');
end;
select* from hr.t05317_lw;
truncate table hr.t05317_lw;
-----
DECLARE
newjob sys.job;
newjobarr sys.job_array;
BEGIN
-- Create an array of JOB object types
newjobarr := sys.job_array();
-- Allocate sufficient space in the array
newjobarr.extend(10000);
-- Add definitions for jobs
FOR i IN 1..10000 LOOP
-- Create a JOB object type
newjob := sys.job(job_name => 'HR.JOB8' || to_char(i),
job_style => 'LIGHTWEIGHT',
job_template => 'HR.PROGRAM8',
enabled => TRUE );
-- Add job to the array
newjobarr(i) := newjob;
END LOOP;
-- Call CREATE_JOBS to create jobs in one transaction
DBMS_SCHEDULER.CREATE_JOBS(newjobarr, 'TRANSACTIONAL');
END;
-----
select count(*) from t05317_lw;
-----
select max(a) - min(a) from t05317_lw;
----
truncate table hr.t05317_lw;
DECLARE
newjob sys.job;
newjobarr sys.job_array;
BEGIN
-- Create an array of JOB object types
newjobarr := sys.job_array();
-- Allocate sufficient space in the array
newjobarr.extend(10000);
-- Add definitions for jobs
FOR i IN 1..10000 LOOP
-- Create a JOB object type
newjob := sys.job(job_name => 'HR.JOB80_' || to_char(i),
job_style => 'REGULAR',
job_template => 'HR.PROGRAM8',
enabled => TRUE );
-- Add job to the array
newjobarr(i) := newjob;
END LOOP;
-- Call CREATE_JOBS to create jobs in one transaction
DBMS_SCHEDULER.CREATE_JOBS(newjobarr);
END;
select count(*) from t05317_lw;
-----
select max(a) - min(a) from t05317_lw;
----重做job4，带元数据参数：
HR：
CREATE TABLE scheduler_test (
id NUMBER(10) NOT NULL,
created_dateDATE NOT NULL,
event_namevarchar2(40) ,
CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
);
select* from scheduler_test;
CREATE SEQUENCE scheduler_test_seq;SYS：
CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT (
event_nameVARCHAR2(30)
);
grant execute on t_event_queue_payload to hr;
BEGIN
-- Create a queue table to hold the event queue.
DBMS_AQADM.create_queue_table(
queue_table => 'event_queue_tab',
queue_payload_type => 't_event_queue_payload',
multiple_consumers => TRUE,
comment => 'Queue Table For Event Messages');
-- Create the event queue.
DBMS_AQADM.create_queue (
queue_name=> 'event_queue',
queue_table => 'event_queue_tab');
-- Start the event queue.
DBMS_AQADM.start_queue (queue_name => 'event_queue');
END;
begin
dbms_aqadm.grant_queue_privilege(privilege => 'ENQUEUE',queue_name => 'event_queue',grantee => 'HR');
end;
begin
dbms_aqadm.grant_queue_privilege(privilege => 'DEQUEUE',queue_name => 'event_queue',grantee => 'HR');
end;
----
HR:
如果创建的程序需要输入参数，则必须定义完参数后在激活，即创建这个program时将enable设为false，否则提示：
Ora-27456：程序“ ”的参数并未全部定义；然后再对该program定义参数即执行define_program_argument过程（见1.3）。
create or replace procedure procnew( p_1 sys.t_event_queue_payload )
is
BEGIN
INSERT INTO scheduler_test
VALUES (scheduler_test_seq.NEXTVAL, SYSDATE,p_1.event_name );
COMMIT;
END;
select* from user_errors;
----
begin
dbms_scheduler.create_program (
program_name => 'programnew',
program_action=> 'procnew',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 1,
enabled => false) ;
end;
select* from user_scheduler_programs;
select* from user_scheduler_program_args;
begin
dbms_scheduler.define_metadata_argument (
program_name => 'programnew',
argument_position => 1 ,
metadata_attribute => 'EVENT_MESSAGE') ;
end;
select* from user_scheduler_program_args;
begin
dbms_scheduler.enable ('programnew');
end;
定除了元数据参数外的其他参数：
/*
1.3 define_program_argument
参数说明：
program_name——程序名称
argument_position——参数位置（也可以知道参数名称选择argument_name）
argument_type——参数类型
default_value——参数默认值
例子：
BEGIN
dbms_scheduler.define_program_argument(program_name => 'myprogram',
argument_position => 1,
argument_name => 'p_lttid',
argument_type => 'varchar2',
default_value => 'daaa');
END;
--查看定义的program参数
SELECT *FROM User_Scheduler_Program_Args;
1.4 define_anydata_argument（用于定义输入参数为复杂类型需采用sys.AnyData类型来包装的参数）
参数说明：
program_name——程序名称
argument_position——参数位置（也可以知道参数名称选择argument_name）
argument_type——参数类型为sys.AnyData
default_value——参数默认值
1.5 define_metadata_argument
有效的metadata attributes有: 'job_name', 'job_subname', 'job_owner', 'job_start', 'window_start',
'window_end', and 'event_message'.
Metadata Attribute Data Type Description
job_name VARCHAR2 当前执行的job名称
job_subname VARCHAR2 当前执行的job子名称
job_owner VARCHAR2 当前执行的job所有者
job_start TIMESTAMP WITH TIME ZONE job启动的时间
window_start TIMESTAMP WITH TIME ZONE window打开的时间
window_end TIMESTAMP WITH TIME ZONE window关闭的时间
event_message 事件触发job启动的信息
例子：（以下metadata_attribute设置为job_name，即以job_start这个值作为输入参数）
BEGIN
dbms_scheduler.define_metadata_argument(program_name => 'myprogram',
argument_position=> 1,
argument_name => 'p_lttid',
metadata_attribute => 'job_start');
END;
1.6 drop_program_argument
例子：
BEGIN
dbms_scheduler.drop_program_argument(program_name => 'myprogram',
argument_position => 1);
END;
*/
------
通过IN型参数（p_1 sys.t_event_queue_payload ）捕获queue环境中的event_name这个event_message：
页:
[1]