Oracle扩展包(pipe,alert,job,scheduler)

--定义包中函数的纯度级别
create or replace package purityTest is
    type dept_typ is table of dept%rowtype index by binary_integer;
    dept_tab dept_typ;
    procedure newdept(
      p_deptno dept.deptno%type,
      p_dname dept.dname%type,
      p_loc dept.loc%type
      );
      function getraisedsalary (p_empno emp.empno%type)
        return number;
      pragma restrict_references(newdept,WNPS);
      pragma restrict_references(getraisedsalary,WNDS);  
end puritytest;
wnds:不能修改数据库数据, 即禁止DML操作
wnps:不能修改包变量
rnds: 不能读取数据库,禁止执行select操作
rnps: 不能将包变量赋给其他值      
--包权限
grant execute on scott.puritytest to userb;


Oracle内部提供的在数据库内部和应用程序间通信的方式有两种
--DBMS_PIPE管道权限赋予scott
grant  execute on dbms_pipe to scott;
--管道在不同模式中传递消息
  create or replace procedure sen_pipe_message(pipename varchar2,message
   varchar2) is flag int;
   begin flag:=dbms_pipe.create_pipe(pipename);  --创建管道
    if flag=0 then                                --如果创建管道成功
      dbms_pipe.pack_message(message);   --将消息存到本地缓冲区
      flag:=dbms_pipe.send_message(pipename);
      --将本地缓冲区中的消息发送到管道
      end if;
      end;
      
-- 从管道中接受消息
create or replace procedure receive_pipe_message
(pipename varchar2,message out varchar2)
is flag int; 
begin
  flag:=dbms_pipe.receive_message(pipename);
  if flag=0 then
    dbms_pipe.unpack_message(message);
    flag:=dbms_pipe.remove_pipe(pipename);
    end if;
  end;
  
 --scott 
begin
 sen_pipe_message('pipe_demo','向管道中发送一条消息');
 end;
 grant execute  on scott.receive_pipe_message to userb;      
--userb
declare
 v_message varchar2(100);
begin
  scott.receive_pipe_message('pipe_demo',v_message);
  dbms_output.put_line(v_message);
  end; 
  
  
--DBMS_ALTER包
grant execute on dbms_alert to scott; 
多个会话可以并发执行同一个报警,每个会话发布报警时会阻塞其他会话
发布报警, 直到报警被提交, 事务会以序列的方式发生.
--产生报警示例
  declare
      v_alertname varchar2(30) :='alert_demo';
   begin
     dbms_alert.signal(v_alertname,'这是一个报警消息!');
     commit;
     end;   

--等待报警示例,用两个会话测试
declare
      v_alertname varchar2(30) :='alert_demo';  --报警名称
      v_status integer;        --等待状态
      v_msg varchar2(200); --报警消息
 begin
   --注册报警, 指定名字
   dbms_alert.register(v_alertname);
   --监听报警,等待报警发生
   dbms_alert.waitone(v_alertname,v_msg,v_status );
   --如果不返回0,则表示报警失败
   if v_status !=0
  then
    dbms_output.put_line('error');
  end if;
  dbms_output.put_line(v_msg);     
   end;     
   

DBMS_JOB:安排和管理作业队列。通过作业队列，可以让Oracle数据库定期执行特定的任务。
当使用DBMS_JOB管理作业的时候，必须确保设置了初始化参数JOB_QUEUE_PROCESSES（不能为0）。
ALTER  SYSTEM  SET  job_queue_processes=39 SCOPE=SPFILE;  --DBA
select job,next_date,next_sec,INTERVAL,what from user_jobs where job=23;--查询job信息

1、 SUBMIT
该过程用于建立一个新的作业，当建立作业的时候，需要通过设置相应的参数来告诉Oracle要执行的内容，
要执行的时间，要执行任务的间隔。

DBMS_JOB.SUBMIT(
   JOB OUT BINARY_INTERGER,                    --用于指定作业编号
   WHAT IN VARCHAR2，                          --用于指定作业要执行的操作
   NEXT_DATE IN DATE DEFAULT SYSDATE,          --用于指定该操作的下一次运行的日期
   INTERVAL IN VARCHAR2 DEFAULT ‘NULL’,        --用于指定该操作的时间间隔
   NO_PARSE IN BOOLEAN DEFAULT FALSE,          --用于指定是否需要解析与作业相关的过程
   INSTANCE IN BINARY_INTEGER DEFAULT ANY_INSTANCE,  --用于指定哪个例程可以运行作业？
   FORCE IN BOOLEAN DEFAULT FALSE                --用于指定是否强制运行与作业相关的例程
);


建立Oracle作业的例子：
DECLARE
JOBNO NUMBER;                    --通过查看该变量可以得到返回的作业编号
BEGIN
DBMS_JOB.SUBMIT(JOBNO,'PRC_SENDTOGX;',SYSDATE,'SYSDATE+1');  --执行工信局提供数据的脚本程序
END;

interval参数值
每天午夜12点:         'TRUNC(SYSDATE + 1)'
每天早上8点30分        'TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)'
每星期二中午12点       'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24'
每个月第一天的午夜12点  'TRUNC(LAST_DAY(SYSDATE ) + 1)'
每个季度最后一天的晚上11点   'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'
每分钟执行一次             'SYSDATE+1/1440'

2、 REMOVE
这个过程的作用是用于删除作业队列当中的特定的作业，它的语法如下：
DBMS_JOB.REMOVE(JOB IN BINARY_INTEGER);
下面是一个删除作业的例子：
首先查看DBA_JOBS表，看表里面有哪些任务正在执行着？
SELECT *　FROM DBA_JOBS;
可以看到里面的JOB就是我们要删除的作业的编号,LOG_USER是创建该任务的人。
SQL> EXEC DBMS_JOB.REMOVE(467);
SQL>COMMIT;
这样就能把已经建立的作业删除了。

3、 CHANGE
该过程改变与作业相关的所有的信息，其中包括作业的操作内容，作业运行的时间以及运行时间间隔信息等等。语法如下：
DBMS_JOB.CHANGE(
JOB IN BINARY_INTEGER,
WHAT IN VARCHAR2，
NEXT_DATE,
INTERVAL IN VARCHAR2，
INSTANCE IN BINARY_INTEGER DEFAULT NULL,
FORCE IN BOOLEAN DEFAULT FALSE
);

例子：
SQL>EXEC DBMS_JOB.CHANGE(2,NULL,NULL,’SYSDATE+2’);
SQL>COMMIT;

4、 WHAT
WHAT用来改变作业要执行的操作，例如：
SQL>EXEC DBMS_JOB.WHAT(268，’BEIGN FUNCTION(8);END;’);

5、 NEXT_DATE
用来改变作业的下次运行日期
SQL>EXEC DBMS_JOB.NEXT_DATE(‘478’,’SYSDATE+2’);

6、 INTERVAL
该过程用来改变作业的运行时间间隔,下面的运行时间间隔修改为每分钟执行一次:
SQL>exec dbms_job.interval(478,’SYSDATE+1/24/60

7、BROKEN
该过程用于给该作业打上中断标志,可以在DBA_JOBS表里面观察该作业的BROKEN标志知否为中断。例子：
SQL>EXEC DBMS_JOB.BROKEN(478，TRUE);
SQL>COMMIT;

8、RUN
该过程用来执行该作业，例子：
SQL>EXEC DBMS_RN(478);
SQL>COMMIT;

1、 如何停止一个作业？
SQL>DBMS_JOBS.BROKEN(2，TRUE);
SQL>COMMIT;

2、如何启动一个作业？
SQL>DBMS_JOBS.BROKEN(2,FALSE);
SQL>COMMIT;

3、 如何计算一个过程运行的时间（DATE和TimeStamp）？

可以在过程的开始设置一个时间，然后在过程的结尾处设置一个时间，然后两个时间的时间差可以计算出该过程运行的时间。通过实践发现使用SYSESTAMP来计算时间比较准确一些：

SELECT to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:ff4') FROM dual;

DBMS_SCHEDULER  [ˈskɛdʒʊələ]  Oracle调度                        
BEGIN  
DBMS_SCHEDULER.CREATE_JOB (  
job_name => 'job_do_main_rtt',  
job_type => 'STORED_PROCEDURE',  
job_action => 'com_job.docachezmondate',  
start_date => sysdate,  
repeat_interval => 'FREQ=SECONDLY;INTERVAL=1',  
comments => '每3秒汇总主界面的统计数据'  
);  
END;

begin
dbms_scheduler.create_job(  
    job_name            => ,  
    job_type            => ,  
    jog_action          => ,  
    number_of_arguments => ,  
    start_date          => ,  
    repeat_interval     => ,  
    end_date            => ,  
    job_class           => ,  
    enabled             => ,  
    auto_drop           => ,  
    comments            =>  
); 
end;

    参数介绍：
    job_name：job名字
    job_type：job类型，支持三种类型：
           1）PLSQL_BLOCK——PL/SQL语句块；
    　　2）STORED_PROCEDURE——存储过程；
           3）EXECUTABLE——外部程序（外部程序可以是一个shell脚本，也可以是操作系统级别的指令）。
    job_action：根据job_type的不同，job_action有相对应的内容。
    number_of_arguments：参数个数。
    start_date：执行开始时间。
    repeat_interval：指定job执行频率（如每分钟执行一次、每天执行一次等）。
    end_date：执行结束时间。
    job_class：jobclass的名字。
    enabled：指定是否自动激活job，为true代表自动激活，false代表不激活。
    auto_drop：执行完是否自动drop
    comments：对于job的简单说明
    

FREQ 关键字用来指定间隔的时间周期，可选参数有：YEARLY（年）, MONTHLY（月）, WEEKLY（周）, DAILY（日）, HOURLY（时）, MINUTELY（分）, SECONDLY（秒）等单位。

INTERVAL 关键字用来指定间隔的频繁，可指定的值的范围从1-999。

BYHOUR  指定一天中的小时。可指定的值的范围从1-24。16，17，18就表示每天下午的4、5、6点。
BYDAY 关键字用来指定每周的哪天运行。
BYMONTHDAY 关键字用来指定每月中的哪一天。-1 表示每月最后一天。
BYMONTH  关键字用来指定每年的月份。
BYDATE 指定日期。0310就表示3月10日。

例如：
运行每星期五。（所有这三个例子是等价的。）
    FREQ=DAILY; BYDAY=FRI;  
    FREQ=WEEKLY; BYDAY=FRI;  
    FREQ=YEARLY; BYDAY=FRI;  

设置任务隔一周运行一次，并且仅在周5运行：
FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI;  

在每月的最后一天运行
FREQ=MONTHLY; BYMONTHDAY=-1;  

三月十日开。（两个例子是等价的）
FREQ=YEARLY; BYMONTH=MAR; BYMONTHDAY=10;  
FREQ=YEARLY; BYDATE=0310;  

设置任务每10隔天运行：
REPEAT_INTERVAL => 'FREQ=DAILY; INTERVAL=10';  

设置任务在每天的下午4、5、6点时运行：
REPEAT_INTERVAL => 'FREQ=DAILY; BYHOUR=16,17,18';     

设置任务在每月29日运行：
REPEAT_INTERVAL => 'FREQ=MONTHLY; BYMONTHDAY=29';  

设置任务在每年的最后一个周5运行：
REPEAT_INTERVAL => 'FREQ=YEARLY; BYDAY=-1FRI';  

设置任务每隔50个小时运行：
REPEAT_INTERVAL => 'FREQ=HOURLY; INTERVAL=50';  

repeat_interval => 'FREQ=HOURLY; INTERVAL=2'
每隔2小时运行一次job

repeat_interval => 'FREQ=DAILY'
每天运行一次job

repeat_interval => 'FREQ=WEEKLY; BYDAY=MON,WED,FRI"
每周的1,3,5运行job

repeat_interval => 'FREQ=YEARLY; BYMONTH=MAR,JUN,SEP,DEC; BYMONTHDAY=30;
每年的3,6,9,12月的30号运行job


对job的各种操作查询：
select owner, job_name, state from dba_scheduler_jobs;
select job_name, state from user_scheduler_jobs;

注意：相应的查询都需要有相对应的权限。
运行：dbms_scheduler.run_job('job_name');
停止：dbms_scheduler.stop_job('job_name');
删除：dbms_scheduler.drop_job('job_name')


/*  
** 创建一个存储过程，输出当前系统时间  
*/  
create or replace procedure My_Time  
is  
begin  
    dbms_output.put_line(to_char(sysdate, 'yyyy-mm-hh HH24:MI:SS'));  
end;  
  
/*  
** 创建一个job来调用以上存储过程  
*/  
declare  
    n_JobExist  integer; --标记job是否存在  
    s_Job_Name  varchar2(32) := 'My_Job';  
begin      
    n_JobExist := 0;  
    --查询My_Job是否存在，如果存在则先drop掉  
    begin  
      select  1   into  n_JobExist   from  user_scheduler_jobs  
        where   job_name = upper(s_Job_Name);       
    exception  
        when NO_DATA_FOUND then  
            NULL;  
    end;       
    if (n_JobExist = 1) then  
        sys.dbms_scheduler.drop_job(s_Job_Name);  
    end if;      
    --创建My_Job  
    sys.dbms_scheduler.create_job(  
        job_name   => s_Job_Name,  
        job_type   => 'plsql_block',  
        --要调用的存储过程  
        job_action => 'begin  My_Time; end;',  
        repeat_interval => 'freq=MINUTELY;interval=1',  
        start_date      => sysdate,  
        --end_date        => sysdate + 3/(24*60),  
        job_class       => 'DEFAULT_JOB_CLASS',  
        auto_drop       => false,  
        enabled         => true  
    );  
end;