第68次：2014-11-20, 电子邮件通知和作业阵列

botang · 发表于 2014-11-21 15:39:17

本帖最后由 botang 于 2014-11-27 16:04 编辑

2014-11-20-A.sql：

create or replace procedure hr.proc05317
is
begin
update employees set salary=salary+0.1 where employee_id=100;
commit;
end;
select * from hr.employees where employee_id=100;
begin
hr.proc05317;
end;
select * from hr.employees where employee_id=100;
begin
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
('email_server','192.168.0.90:25');
end;
begin
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
('email_sender','oracle@example.com');
end;
begin
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
job_name =>'HR.JOB1',
recipients =>'oracle@example.com',
sender =>'oracle@example.com',
events=>'job_started'
);
end;
select * from dba_scheduler_notifications;
create table hr.tbig as select * from dba_source;
select s.RESOURCE_CONSUMER_GROUP from v_$session s where terminal='pts/18';

复制代码

2014-11-20-B.sql：

select owner, j.job_name, j.job_style from dba_scheduler_jobs j ;
create table hr.t05317_lw ( a date ) ;
create or replace procedure hr.proc05317_lw
is
begin
insert into hr.t05317_lw values(sysdate);
commit;
end;
select * from hr.t05317_lw;
begin
hr.proc05317_lw;
end;
select * from hr.t05317_lw;
-----EM programlw
begin
dbms_scheduler.create_job(
job_name => 'hr.joblw',
program_name=>'hr.programlw',
schedule_name=>'hr.schedule2',
job_style=>'LIGHTWEIGHT');
end;
select owner, j.job_name, j.job_style,j.state from dba_scheduler_jobs j ;
begin
dbms_scheduler.enable('HR.JOBLW');
end;
select * from hr.t05317_lw;
---------------------------------------------------------------------------------
truncate table hr.t05317_lw;
select * from 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(100);
-- Add definitions for jobs
FOR i IN 1..100 LOOP
-- Create a JOB object type
newjob := sys.job(job_name => 'HR.JOBLW' || to_char(i),
job_style => 'LIGHTWEIGHT',
job_template => 'HR.PROGRAMLW',
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 * from hr.t05317_lw;
-------

复制代码

2014-11-20-C.sql：

CREATE TABLE hr.scheduler_test (
id NUMBER(10) NOT NULL,
created_date DATE NOT NULL,
CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
);
select * from hr.scheduler_test;
CREATE SEQUENCE hr.scheduler_test_seq;
CREATE OR REPLACE TYPE hr.t_event_queue_payload AS OBJECT (
event_name VARCHAR2(30)
);
BEGIN
-- Create a queue table to hold the event queue.
DBMS_AQADM.create_queue_table(
queue_table => 'hr.event_queue_tab',
queue_payload_type => 'hr.t_event_queue_payload',
multiple_consumers => TRUE,
comment => 'Queue Table For Event Messages');
-- Create the event queue.
DBMS_AQADM.create_queue (
queue_name => 'hr.event_queue',
queue_table => 'hr.event_queue_tab');
-- Start the event queue.
DBMS_AQADM.start_queue (queue_name => 'hr.event_queue');
END;
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'hr.event_based_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
INSERT INTO hr.scheduler_test (id, created_date)
VALUES (scheduler_test_seq.NEXTVAL, SYSDATE);
COMMIT;
END;',
start_date => SYSTIMESTAMP,
event_condition => 'tab.user_data.event_name = ''give_me_a_prod''',
queue_spec => 'hr.event_queue',
enabled => TRUE);
END;
select * from hr.scheduler_test;
DECLARE
l_enqueue_options DBMS_AQ.enqueue_options_t;
l_message_properties DBMS_AQ.message_properties_t;
l_message_handle RAW(16);
l_queue_msg hr.t_event_queue_payload;
BEGIN
l_queue_msg := hr.t_event_queue_payload('give_me_a_prod');
DBMS_AQ.enqueue(queue_name => 'hr.event_queue',
enqueue_options => l_enqueue_options,
message_properties => l_message_properties,
payload => l_queue_msg,
msgid => l_message_handle);
COMMIT;
END;
select * from hr.scheduler_test;
--------------------------------------------------------------------------------------------------------------------------------------------
truncate table hr.scheduler_test;
select hr.scheduler_test_seq.currval from dual;
-----
------
create or replace procedure proc_test( p_1 number , p_2 out number)
is
begin
update employees set salary=10000 where employee_id=p_1;
select salary into p_2 from employees where employee_id=p_1;
end;
select * from user_errors;
declare
v_salary number(8,2);
begin
proc_test(100,v_salary);
dbms_output.put_line(v_salary);
end;
----
create or replace procedure proc_test_io( p_1 in out number )
is
begin
p_1 := p_1*2;
end;
select * from user_errors;
------
---
declare
v_1 number;
begin
v_1 := 1;
proc_test_io( v_1);
dbms_output.put_line(v_1);
end;
-----
-----------------------------------------------------------------------------------
create or replace procedure hr.procnew( p_1 hr.t_event_queue_payload )
is
BEGIN
INSERT INTO hr.scheduler_test (id, created_date, event_name)
VALUES (hr.scheduler_test_seq.NEXTVAL, SYSDATE, p_1.event_name );
COMMIT;
END;
select * from user_errors;
begin
dbms_scheduler.create_program (
program_name => 'hr.programnew',
program_action=> 'hr.procnew',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 1,
enabled => false) ;
end;
select * from dba_scheduler_programs p where p.owner='HR';
select * from dba_scheduler_program_args pa where pa.owner='HR' ;
begin
dbms_scheduler.define_metadata_argument (
program_name => 'hr.programnew',
argument_position => 1 ,
metadata_attribute => 'EVENT_MESSAGE') ;
end;
select * from dba_scheduler_program_args pa where pa.owner='HR' ;
begin
dbms_scheduler.enable ('hr.programnew');
end;
alter table hr.scheduler_test add ( event_name varchar2(40)) ;
select * from hr.scheduler_test;
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOBNEW"',
program_name => '"HR"."PROGRAMNEW"',
event_condition => 'tab.user_data.event_name = ''give_me_a_prod''',
queue_spec => '"HR"."EVENT_QUEUE"',
start_date => systimestamp at time zone 'Asia/Shanghai',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'jobnew',
auto_drop => FALSE,
enabled => TRUE);
select * from hr.scheduler_test;
DECLARE
l_enqueue_options DBMS_AQ.enqueue_options_t;
l_message_properties DBMS_AQ.message_properties_t;
l_message_handle RAW(16);
l_queue_msg hr.t_event_queue_payload;
BEGIN
l_queue_msg := hr.t_event_queue_payload('give_me_a_prod');
DBMS_AQ.enqueue(queue_name => 'hr.event_queue',
enqueue_options => l_enqueue_options,
message_properties => l_message_properties,
payload => l_queue_msg,
msgid => l_message_handle);
COMMIT;
END;
select * from hr.scheduler_test;

复制代码