第31次活动：2017-11-08（星期三晚上7:00-9:30）

botang · 发表于 2017-11-8 20:20:23

轻量级作业和作业矩阵：

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：