批量修改Oracle序列值的存储过程
- create or replace procedure p_modify_sequences is
-
- str_sql varchar2(4000);
-
- r boolean;
-
-
-
-
-
- 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;
-
- 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;
-
- 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;
原文地址:https://www.cnblogs.com/wjlstation/p/2409413.html