创建序列

例子：新建一个序列，每日凌晨0点序列初始值置为0

1、创建序列 BOOKING_Y_SO_NO_SEQ

create sequence BOOKING_Y_SO_NO_SEQ minvalue -999999 maxvalue 999999 start with 1 increment by 1 Nocache order;

2、创建存储过程 MODIFY_BOOKING_Y_SO_NO_SEQ, 把序列号初始值置为1

create or replace procedure MODIFY_BOOKING_Y_SO_NO_SEQ
as
   vn_number       number; 
   vr_sequence  user_sequences%rowtype;
Begin
    select * into vr_sequence from user_sequences t where t.sequence_name='BOOKING_Y_SO_NO_SEQ';

    execute immediate 'alter sequence '||'BOOKING_Y_SO_NO_SEQ'||' increment by '||(1-vr_sequence.last_number)|| ' nocache';
     Select BOOKING_Y_SO_NO_SEQ.Nextval  into vn_number from dual;
    execute immediate 'alter sequence '||'BOOKING_Y_SO_NO_SEQ'||' increment by 1 nocache'; 
exception
   when others then
      null;
end MODIFY_BOOKING_Y_SO_NO_SEQ;

3、创建JOB定时（每天凌晨执行存储过程MODIFY_BOOKING_Y_SO_NO_SEQ）;

declare      
  job number;
begin
  sys.dbms_job.submit( job => :job,
  what => 'MODIFY_BOOKING_Y_SO_NO_SEQ;',
  next_date => to_date('12-01-2017 14:17:01', 'dd-mm-yyyy hh24:mi:ss'),
  interval => 'TRUNC(SYSDATE + 1)');--每日凌晨执行一次
  commit;
end;
/

JOB常用属性值

Job

• 查看所有job：select * from dba_jobs;
• 查询运行中的JOB：select * from dba_jobs_running;

• 删除job

  begin   
      dbms_job.remove(:job);//  :job可以用dba_jobs.job的值代替如：664 
  end;
  

存储过程

• 查询所有存储过程 select * from user_procedures;
• 删除存储过程 drop procedure MODIFY_BOOKING_Y_SO_NO_SEQ;

序列

• 查询所有序列 select * from user_sequences;
• 创建序列 create sequence BOOKING_Y_SO_NO_SEQ minvalue -999999 maxvalue 999999 start with 1 increment by 1 Nocache order;
• 删除序列 drop sequence BOOKING_Y_SO_NO_SEQ;
• 查询序列序列下一个值 select BOOKING_Y_SO_NO_SEQ.Nextval from dual;
• 查询序列当前值 select BOOKING_Y_SO_NO_SEQ.currval from dual;

队列

1、创建一个集合类型的对象（其实就是你所需要的队列表的表结构）

CREATE OR REPLACE Type mt_struc As Object
( id number(5) ,
  name varchar2(30),
  age varchar2(30)
) ;

2、创建队列表

begin
   sys.dbms_aqadm.create_queue_table(queue_table=>'sms_mt_tab', queue_payload_type=>'mt_struc');
end ;

begin
   sys.dbms_aqadm.create_queue_table(queue_table=>'tets_jms_queueb', queue_payload_type=>'MESSAGE') ; --箱管的是 EEIR.MESSAGE
end ;