-
批量修改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;
-
相关阅读:
【Revit API】改变填充区域的填充样式
【Revit API】创建共享参数
【Revit API】墙体分割
【Revit API】FamilyInstance、FamilySymbol、Family的寻找关系
【Revit API】创建相机视角
【Revit API】获取链接模型中构件
【Revit API】梁的净高分析
【Revit API】脱离中心文件
【Revit API】创建工作集并将element加入工作集中
常用的XML解析函数
-
原文地址:https://www.cnblogs.com/wjlstation/p/2409413.html
Copyright © 2020-2023
润新知