|
轻量级作业和作业矩阵:
- 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 into t05317_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;
- select j.job_name , j.stop_on_window_close ,j.JOB_STYLE, j.ENABLED
- from user_scheduler_jobs j
- where j.job_name like '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_date DATE NOT NULL,
- event_name varchar2(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_name VARCHAR2(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:
|
|