批量操作初始化序列初始值

作者: 你的笑就像潘金莲开窗户那一刻似的
来源: 51数据库
2022-08-17

DECLARE 
   TYPE tsi IS RECORD(
         t VARCHAR2(100) ,
         s VARCHAR2(100),
         i varchar2(100));
  tsiTemp tsi;
   maxId varchar2(20);
BEGIN
    for tsiTemp in (
      select  'T_xxxx_CONFIG'as t,'SEQ_xxxxx_ID' as s,'xxxx_ID' as i from dual 
      union
      select  'T_ttttt_INFO'as t,'SEQ_ttttt_ID' as s,'tttt_ID' as i from dual      
       union  
      select  'T_ddddd_LOG' as t,'SEQ_dddddd_ID' as s,'dddd_ID' as i from dual             
   )
    loop
    execute immediate  'select nvl(max('||tsiTemp.i||'+1),1) from '||tsiTemp.t into maxId;
   execute immediate 'drop sequence '||tsiTemp.s;
  execute immediate 'create sequence '||tsiTemp.s 
         || ' minvalue '||maxId
         || ' maxvalue 999999999999999999999999999 '
         || ' start with '||maxId
         || ' increment by 1'
         ||' cache 20'
         ||' order'; 
  end loop;
END;

推荐阅读