创建序列

例子：新建一个序列，每日凌晨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_SEQas   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.MESSAGEend ;