修改表触发器对应的序列号 （当增加或修改数据时需要用）

create or replace procedure SP_Change_Sequence_Num(table_name_in varchar2, pk_name_in varchar2, is_positive_growth_in number)
is
/***********************************************************      
      when change table data, need to change sequence next number
      reference its trigger
      
      is_positive_growth_in means positive or not. 
      if positive: >0
        else <0
*************************************************************/
begin
  declare
    sql_l varchar2(1000);
    trigger_name_l varchar2(30);
    sequence_name_l varchar2(30);
    max_or_min_pk_value_l number;
    imcrement_by_l number;
  begin
    if is_positive_growth_in>0 then
       execute immediate ‘select nvl(max(‘||pk_name_in||‘)+1,1) from ‘||table_name_in
       into max_or_min_pk_value_l;
       imcrement_by_l:=1;
      -- dbms_output.put_line(‘AAA‘);
    else
       execute immediate ‘select nvl(min(‘||pk_name_in||‘)-1,-1) from ‘||table_name_in
       into max_or_min_pk_value_l;
       
       if max_or_min_pk_value_l is null then
         max_or_min_pk_value_l:=-1;
       end if;
       
       imcrement_by_l:=-1;
    end if;
    
    select trigger_name into trigger_name_l 
    from user_triggers where table_name=table_name_in;
    
   -- dbms_output.put_line(‘BBB‘);
    
    if length(trigger_name_l)>1 then
      select REFERENCED_NAME into sequence_name_l from User_DEPENDENCIES where 
      type=‘TRIGGER‘ AND NAME=trigger_name_l AND REFERENCED_TYPE=‘SEQUENCE‘ AND rownum=1;
      
      dbms_output.put_line(‘CCC‘);
      
      if length(sequence_name_l)>1 then
         sql_l := ‘drop sequence ‘||sequence_name_l;
         execute immediate sql_l;
         
         dbms_output.put_line(‘DDD‘);
         
         sql_l := ‘create sequence ‘||sequence_name_l||‘ increment by ‘||imcrement_by_l||‘ start with ‘||max_or_min_pk_value_l||‘ nomaxvalue nocycle‘;
         execute immediate sql_l;
         
         dbms_output.put_line(‘EEE‘);
         
         sql_l := ‘alter trigger ‘||trigger_name_l||‘ compile‘;
         execute immediate sql_l;
         
          dbms_output.put_line(‘FFF‘);
      end if;
    end if;
  Exception
    when OTHERS then
      dbms_output.put_line(‘The SQLCode is: ‘||SQLCODE);
      dbms_output.put_line(‘The SQLERRM is: ‘||SQLERRM);
  end;
end;

-- test 

select max(id) from table_name;
call SP_Change_Sequence_Num(upper(‘table_name‘), upper(‘id‘), 1);
select seq_id.nextval from dual;