管理sequence

1.首先建一个sequence和表的对应关系表：

create table T_TABLE_SEQ

(

  table_name VARCHAR2(50) not null,

  seq_name   VARCHAR2(50)

)；

把所有需要管理的sequence和对应的表插入此表

 

第一种情形：

 由于迁移或者其他情况需要根据表的字段的值增大sequence

create or replace procedure sp_change_seq

as

  col_name    VARCHAR2(50);

  str_sql     VARCHAR2(150);

  max_val     INTEGER;

  seq_val     INTEGER;

  str_seq_sql VARCHAR2(200);

  tmp_val     INTEGER;

BEGIN

  FOR i IN (SELECT table_name, seq_name FROM t_table_seq) LOOP

    BEGIN

      col_name := '';

 

      SELECT column_name

        INTO col_name

        FROM all_tab_columns

       WHERE wner = USER

         AND column_id = 1

         AND table_name = i.table_name;

 

      str_sql := 'select max(' || col_name || ')  from ' || i.table_name;

 

      EXECUTE IMMEDIATE str_sql INTO max_val;

 

      str_seq_sql := 'SELECT ' || i.seq_name || '.Nextval FROM DUAL';

 

      EXECUTE IMMEDIATE str_seq_sql INTO seq_val;

 

      IF nvl(seq_val, 0) <= nvl(max_val, 0) THEN

 

        FOR x IN 1 .. (nvl(max_val, 0) - nvl(seq_val, 0)) LOOP

 

          EXECUTE IMMEDIATE 'select ' || i.seq_name || '.Nextval  FROM DUAL'

            INTO tmp_val;

 

        END LOOP;

      END IF;

    END;

  END LOOP;

END;

 

 

第二种情形：

需要把sequence统一改为从某个值开始

 

CREATE OR REPLACE PROCEDURE sp_seq_alter_all_count(i_seq_start_count INT,

                                                   i_table_name      VARCHAR2 DEFAULT NULL) AS

 

  v_drop_sql   VARCHAR2(4000);

  v_create_sql VARCHAR2(4000);

BEGIN

  FOR i IN (SELECT dbms_metadata.get_ddl(u.object_type, u.object_name) seq_str

              FROM user_objects u

             WHERE u.object_type IN ('SEQUENCE')

               AND u.object_name IN

                   (SELECT upper(seq_name) FROM t_table_seq WHERE i_table_name IS NULL OR table_name = upper(i_table_name))) LOOP

 

    v_drop_sql   := REPLACE(substr(i.seq_str, 1,instr(i.seq_str, 'MINVALUE') - 1),'CREATE ','DROP ');

 

    v_create_sql := substr(i.seq_str, 1, instr(i.seq_str, 'WITH') + 3) || ' ' || i_seq_start_count || ' ' || substr(i.seq_str, instr(i.seq_str, 'CACHE'));

 

    EXECUTE IMMEDIATE v_drop_sql;

 

    EXECUTE IMMEDIATE v_create_sql;

 

  END LOOP;

END;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11745285/viewspace-751811/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11745285/viewspace-751811/