Oracle 修改序列的起始值start with

两种方式实现：

    方式一：修改序列的增长值increment by，将其增长到符合要求的开始值，然后再将increment by调回1。

declare
  v_max       int;
  v_end       varchar2(10);
  v_len       int;
  v_in        varchar2(1024);
begin
  select max(tc.c_sixseq_contractcode) into v_max  from table_info tc;  --查询表中最大值
  select length(v_max) into v_len  from dual;
  if v_len=5 then
    select ('0'||to_char(v_max)) into v_end  from dual;
   execute immediate  'ALTER SEQUENCE SEQ_123 INCREMENT BY '||v_end; --更改步长
   select SEQ_APPRAISEMENT.nextval into v_end  from dual;  --获取下次值
   execute immediate 'ALTER SEQUENCE SEQ_123 INCREMENT BY 1'; --改回步长
  end if;
end;
/

  方式二：先删后插入。

declare
  v_max       int;
  v_end       varchar2(10);
  v_len       int;
begin  
if  fc_IsSeqExists('SEQ_123') then
   execute immediate 'drop sequence SEQ_123';
   select max(tc.c_sixseq_contractcode) into v_max  from table_info tc;  --查询表中最大值
   select length(v_max) into v_len  from dual;
   if v_len=5 then
    select (to_char(v_max*10+1)) into v_end  from dual;
    execute immediate 'create sequence SEQ_123
	 minvalue 1
   maxvalue 999999
	 start with '||v_end||' 
   increment by 1 
   cache 200
	 cycle';
   end if;
end if;
end;
/