动态更改数据库触发器引用的序列值

不逼逼直接上代码

使用场景

触发器引用序列赋予唯一主键递增形，可以用到类似
select S_T_AWARDCREDITRECORD.nextval FROM DUAL

DECLARE
  v_MAX_SQL      varchar2(100);
  v_MAX          integer;
  v_SEQUENCE_SQL varchar2(100);
  v_SEQUENCE     integer;
  V_T_SEQUENCE   VARCHAR2(100);
  t_table        varchar2(100);
  cursor p is
    select 'select max(' ||
           SUBSTR(substr(t2.text, INSTR(upper(t2.text), 'NEW.') + 4),
                  1,
                  INSTR(UPPER(substr(t2.text,
                                     INSTR(upper(t2.text), 'NEW.') + 5)),
                        'FROM')) || ') from ' || t.table_name AS TTABLE,
           SUBSTR(substr(t2.text, INSTR(upper(t2.text), 'SELECT')),
                  1,
                  INSTR(UPPER(substr(t2.text,
                                     INSTR(upper(t2.text), 'SELECT'))),
                        'INTO')-1) || ' FROM DUAL' AS TTSEQUENCE,
           SUBSTR(UPPER(substr(t2.text, INSTR(upper(t2.text), 'SELECT') + 7)),
                  1,
                  INSTR(UPPER(substr(t2.text,
                                     INSTR(upper(t2.text), 'SELECT') + 7)),
                        'NEXTVAL') - 2) AS TTSEQUENCE2,
           t.table_name
      from user_triggers t
      left join user_tables t1 on t.table_name = t1.table_name
      left join (select t.name, t.text
                   from user_source t
                  where t.type = 'TRIGGER'
                    and upper(t.text) like '%NEXTVAL%') t2 on t.trigger_name =
                                                              t2.name
     where t1.num_rows > 0
       and t2.name is not null;
       --and t.table_name = 'TPOOL_BOND_LOG';
BEGIN
  open p;
  loop
    fetch p
      into v_MAX_SQL, v_SEQUENCE_SQL, V_T_SEQUENCE, t_table;
    exit when p%notfound;
    execute immediate v_MAX_SQL
      into v_MAX;
    execute immediate v_SEQUENCE_SQL
      into v_SEQUENCE;
    --dbms_output.put_line(v_MAX || '    ' || v_SEQUENCE || '    ' || V_T_SEQUENCE || '    ' || t_table);
    v_MAX := nvl(v_MAX, 0) ;
    
    while v_SEQUENCE < v_MAX loop
    --dbms_output.put_line(v_MAX-v_SEQUENCE||'表'||t_table);
    execute immediate 'alter sequence '||V_T_SEQUENCE||' increment by 1000';
    execute immediate v_SEQUENCE_SQL into v_SEQUENCE;   
    execute immediate 'alter sequence '||V_T_SEQUENCE||' increment by 1'    ;
    --dbms_output.put_line(v_MAX-v_SEQUENCE||'表'||t_table);    
    end loop;
END LOOP; 
END;