更新sequence值的动态sql语句

最新推荐文章于 2023-12-26 10:45:26 发布

原创最新推荐文章于 2023-12-26 10:45:26 发布 · 905 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #replace #varchar2 #sequences #code

Java 同时被 3 个专栏收录

50 篇文章

订阅专栏

数据结构与算法

18 篇文章

订阅专栏

DataBase

16 篇文章

订阅专栏

本文详细介绍了如何在数据库中动态更新序列值，并通过创建和调用存储过程实现序列值的高效管理，确保数据库中主键的连续性和一致性。

[size=large][b]一.[/b][/size]

--导出之前把该语句执行一遍,复制执行结果,然后在导入后的数据库中删除sequence,再执行该语句.
select 'create sequence '||sequence_name||    
       ' minvalue '||min_value||    
       ' maxvalue '||max_value||    
       ' start with '||(last_number+1000000)||    
       ' increment by '||increment_by||    
       (case when cache_size=0 then ' nocache' else ' cache '||cache_size end) ||';'   
from user_sequences

[size=large][b]二.[/b][/size]


create or replace procedure p_modify_sequences is  
  --更新各序列值的动态sql语句     
  str_sql varchar2(4000);  
  --是否修改成功,未发生异常返回true     
  r boolean;  

  --修改相应序列的nextval     
  --sequencename:序列名     
  --talename:由序列生成主键的表名     
  --keyf:由序列生成的主键名     
  function f_modify_sequence(sequencename varchar2,  
                             talename     varchar2,  
                             keyf         varchar2) return boolean is  
    lastvalue integer;  
    currvalue integer;  
    f_sql     varchar2(4000);  
    next_num  number;  
    max_num   number;  
  begin  
    --查询表中主键的最大值     
    f_sql := 'select max(' || keyf || ') from ' || talename;  
    execute immediate f_sql  
      into max_num;  
    dbms_output.put_line('表' || talename || '的' || keyf || '最大值为:' ||  
                         max_num);  
    if (max_num is not null) then  
      next_num := max_num + 1;  
      --修改序列的自增量为1     
      f_sql := 'alter sequence ' || sequencename ||  
               ' increment by 1 nocache';  
      execute immediate f_sql;  

      --循环     
      loop  
        --查询当前序列的下一个值     
        f_sql := 'select ' || sequencename || '.nextval from dual';  
        execute immediate f_sql  
          into lastvalue;  

        --当序列的下一个值>= 表中现有主键的最大值时退出循环     
        exit when lastvalue >= next_num - 1;  
        --如果序列的下一个值小于表中现有主键的最大值时继续获取序列的下一个值     
        f_sql := 'select ' || sequencename || '.nextval from dual';  
        execute immediate f_sql  
          into lastvalue;  

      end loop;  
      --修改后的sequencename.currval仍为修改前的值,但sequencename.nextval值为中主键的最大值+1     
      f_sql := 'alter sequence ' || sequencename ||  
               ' increment by 1 cache 20';  
      execute immediate f_sql;  
      dbms_output.put_line('序列' || sequencename || '的下一个值为' || lastvalue);  
      dbms_output.put_line('');  
    end if;  
    commit;  
    return true;  
  exception  
    when others then  
      return false;  
  end f_modify_sequence;  

begin  
  r := f_modify_sequence('SEQ_MENUCODE', 'T_BOSSMENU', 'MENUCODE');  
  r := f_modify_sequence('SEQ_FEE',  
                         'T_BSFEE',  
                         'to_number(substr(FEE_NO,-12))');  
end p_modify_sequences;  



set serveroutput on;  
exec p_modify_sequences;