主键生成-存储过程

创建序列
-- Create sequence 
create sequence SEQ_ORDER_CHECK
minvalue 1000000000000001
maxvalue 9999999999999999
start with 1000000000000021
increment by 1
cache 20;

存储过程
CREATE OR REPLACE PROCEDURE SEQ_RESET as
  n    number(10);
  tsql varchar2(100);
begin
  execute immediate 'select SEQ_ORDER_LIST.nextval from dual' into n;
  n    := - (n - 1);
  tsql := 'alter sequence SEQ_ORDER_LIST increment by ' || n;
  execute immediate tsql;
  execute immediate 'select SEQ_ORDER_LIST.nextval from dual' into n;
  tsql := 'alter sequence SEQ_ORDER_LIST increment by 1';        // 还原
  execute immediate tsql;
end seq_reset;
/
调用函数
CREATE OR REPLACE FUNCTION GETSEQ (pre VARCHAR2)
  RETURN VARCHAR2
  IS v_seq VARCHAR2(200);
  seq_temp VARCHAR2(200); 
BEGIN
    SELECT seq_order_list.nextval INTO seq_temp FROM dual; 
    IF length(seq_temp)< 6 THEN 
      seq_temp := lpad(seq_temp,6-length(seq_temp),'0');
    END IF; 
    IF(pre IS NULL) THEN
    RETURN NULL;
    END IF;
    v_seq := pre || to_char(SYSDATE,'YYYY-MM-DD-')|| seq_temp;    
  RETURN v_seq;
end getSeq;
/

VAR job NUMBER;
begin
  sys.dbms_job.submit(:job,'xplatformojt_group10.SEQ_RESET;',SYSDATE,'trunc(sysdate+1)+1/60/24');
  commit;
end;
/

>>>>>>>>>>>>>>>>>>>>
-- Create sequence 
create sequence SEQ_ORDER_LIST   创建序列名
minvalue 1
maxvalue 99999999
start with 1    从1开始
increment by 1      每次增长1
cache 20;  表示一次产生10个号

>>>>>>>>>>>>>>>>
BEGIN
    SELECT seq_order_list.nextval-1 INTO seq_temp FROM dual

>>>>>>>>>>>>>>>>>>>