课程第47次（2017-10-24星期二）

botang · 发表于 2017-10-24 19:45:01

关于参数的预备知识：

create or replace procedure proc_test( p_1 number , p_2 out number)
is
begin
update employees set salary=salary+1 where employee_id=p_1;
commit;
select salary into p_2 from employees where employee_id=p_1;
dbms_output.put_line('The new salary is '||p_2||'.');
end;
select * from user_errors;
select salary from employees where employee_id=100;
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;
declare
v_2 number ;
begin
proc_test(100, v_2);
end;

复制代码

带meta_data参数的program：

[oracle@station26 ~]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 24 19:16:45 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
SQL> conn hr/oracle_4U
Connected.
SQL> DROP TABLE scheduler_test;
Table dropped.
SQL> DROP SEQUENCE scheduler_test_seq;
Sequence dropped.
SQL> CREATE TABLE scheduler_test (
id NUMBER(10) NOT NULL,
created_date DATE NOT NULL, 2 3
4 event_name varchar2(40),
5 CONSTRAINT scheduler_test_pk PRIMARY KEY (id));
Table created.
SQL> desc scheduler_test
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(10)
CREATED_DATE NOT NULL DATE
EVENT_NAME VARCHAR2(40)
SQL> CREATE SEQUENCE scheduler_test_seq;
Sequence created.
SQL> select * from scheduler_test;
no rows selected
1 create or replace procedure procnew( p_1 sys.t_event_queue_payload )
2 is
3 BEGIN
4 INSERT INTO scheduler_test
5 VALUES (scheduler_test_seq.NEXTVAL, SYSDATE, p_1.event_name );
6 COMMIT;
7* END;
SQL> /
Procedure created.
SQL> begin
dbms_scheduler.create_program (
program_name => 'programnew',
program_action=> 'procnew',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 1,
enabled => false) ;
end; 2 3 4 5 6 7 8
9 /
PL/SQL procedure successfully completed.
SQL>

复制代码

SQL> begin
dbms_scheduler.enable ('programnew');
end; 2 3
4 /
PL/SQL procedure successfully completed.
SQL>
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB9"',
program_name => '"HR"."PROGRAMNEW"',
schedule_name => '"HR"."SCHEDULE4"',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'JOB9',
auto_drop => FALSE,
enabled => TRUE);
END; 2 3 4 5 6 7 8 9 10
11 /
PL/SQL procedure successfully completed.
SQL>

复制代码

SELECT * FROM scheduler_test order by id;

---
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          sys.t_event_queue_payload;
BEGIN
  l_queue_msg := sys.t_event_queue_payload('give_me_a_prod');

  DBMS_AQ.enqueue(queue_name       => 'sys.event_queue',
               enqueue_options    => l_enqueue_options,
               message_properties  => l_message_properties,
               payload          => l_queue_msg,
               msgid             => l_message_handle);
  COMMIT;
END;

----
SELECT * FROM scheduler_test order by id;

---
Filewatcher 在64位Linux上，要做：
[root@station26 lib64]# ls -l libpam*
lrwxrwxrwx. 1 root root 17 1月  31 2017 libpamc.so.0 -> libpamc.so.0.82.1
-rwxr-xr-x. 1 root root 14528 3月  29 2016 libpamc.so.0.82.1
lrwxrwxrwx. 1 root root 21 1月  31 2017 libpam_misc.so.0 -> libpam_misc.so.0.82.0
-rwxr-xr-x. 1 root root 17248 3月  29 2016 libpam_misc.so.0.82.0
lrwxrwxrwx. 1 root root 16 1月  31 2017 libpam.so.0 -> libpam.so.0.82.2
-rwxr-xr-x. 1 root root 58480 3月  29 2016 libpam.so.0.82.2
[root@station26 lib64]# ln -s libpam.so.0.82.2 libpam.so

观察界面原先jobs和schedule的file_watcher关闭：

select * from dba_scheduler_file_watchers;
select * from dba_scheduler_credentials;
begin
dbms_scheduler.create_file_watcher(
file_watcher_name => 'filewatcher1',
directory_path => '/home/oracle/myfile',
file_name => 'botang*.txt',
credential_name => 'cred1');
end;
select * from dba_scheduler_file_watchers;

复制代码

-----------------启动了--------------------------

grant execute on filewatcher1 to hr;
grant execute on SYS.SCHEDULER_FILEWATCHER_RESULT to hr;
grant create external job to hr;
grant create job to hr;
--------
create table hr.tfilewatcher1 ( a varchar2(200) ) ;
create or replace procedure hr.procfilewatcher ( p_1 SYS.SCHEDULER_FILEWATCHER_RESULT)
is
begin
insert into hr.tfilewatcher1 values ( to_char( p_1.file_timestamp,'YYYY-MM-DD:HH24:MI:SS') ||' '
||p_1.directory_path||' '
||p_1.actual_file_name||' '
||p_1.file_size ) ;
commit;
end;
-------
begin
dbms_scheduler.create_program(
program_name => 'HR.program10',
program_type => 'STORED_PROCEDURE',
program_action => 'HR.procfilewatcher',
number_of_arguments => 1,
enabled => false);
end;
----------
begin
dbms_scheduler.define_metadata_argument(
program_name => 'HR.program10',
metadata_attribute => 'event_message',argument_position => 1 ) ;
end;
-------
begin
dbms_scheduler.enable('HR.program10');
end;
------

复制代码

组装Filewatcher作业：

当你再看这个作业时，定义变成：

begin
dbms_scheduler.set_attribute('hr.job10','parallel_instances',true);
end;
select * from hr.tfilewatcher1;

复制代码