SEQUENCEの値の再構築

1.oracle   



CREATE OR REPLACE PROCEDURE modify_SEQ
IS
/**===============================================================================
      
===============================================================================*/
  V_tablename VARCHAR2(200);    --    
  V_sqlStr    VARCHAR2(1000);   --  SQL  
  V_cnt       NUMBER(10) DEFAULT 0;      --      
  V_seq_name  VARCHAR2(100);    --  SEQ  
  MySql       VARCHAR2(200);
  --      ,          ,         
  type                  cur_type is ref cursor;    --      
  myCursor    cur_type;                         --      ,      
  v_count NUMBER(10) DEFAULT 1;
/*===============================================================================
    ，     SEQ  ,     SQL  ，        
===============================================================================*/
   
   CURSOR temp_cursor IS SELECT sequence_name FROM User_Sequences s INNER JOIN (
                         SELECT T.TABLE_NAME, T.DATA_TYPE
                         FROM USER_TAB_COLUMNS T
                         INNER JOIN USER_OBJECTS OBJ ON T.TABLE_NAME = OBJ.OBJECT_NAME
                         WHERE T.COLUMN_NAME = 'ID'
                         AND T.DATA_TYPE = 'NUMBER'
                         AND T.TABLE_NAME NOT LIKE 'BIN%'
                         AND T.TABLE_NAME NOT LIKE 'JTYH%'
                         AND T.TABLE_NAME NOT IN ('SP_REPORT_RESULT', 'FE_RESOURCE')
                         ORDER BY T.TABLE_NAME) t ON Substr(s.sequence_name,5) = t.TABLE_NAME; 
   BEGIN
/*===============================================================================
     SEQ            
===============================================================================*/
   	 OPEN temp_cursor;
     LOOP fetch temp_cursor INTO V_seq_name;
     --       
      exit when get_procedure_list%NOTFOUND;
     SELECT SUBSTR(V_seq_name,5) INTO V_tablename FROM dual;
/*     5   ，      */
     CASE  V_seq_name
           WHEN 'SEQ_BD_BOND_ACCURAL_INTEREST_C' THEN
                V_tablename:='BD_BOND_ACCURAL_INTEREST_CL';
           WHEN 'SEQ_SP_BM_STRUCT_INDEX_RELATIO' THEN
                V_tablename:='SP_BM_STRUCT_INDEX_RELATIO';
           WHEN 'SEQ_SP_REPORT_MODEL_USER_RI_HS' THEN
                V_tablename:='SP_REPORT_MODEL_USER_RIGHT_HS'; 
           WHEN 'SEQ_TD_BM_ASSETCLASS_YIELD_RAT' THEN
                V_tablename:='TD_BM_ASSETCLASS_YIELD_RATE'; 
           WHEN 'SEQ_TD_YIELD_RATE_PA_BM_AC_HEL' THEN
                V_tablename:='TD_YIELD_RATE_PA_BM_AC_HELD';
           ELSE
                 V_tablename:= V_tablename;
     END CASE;     
/*        ID ，   ID   V_cnt，             */     
     MySql :='SELECT max(id)  FROM ' ||V_tablename;
     open myCursor for MySql; 
     fetch myCursor into V_cnt;
     close myCursor;
     IF V_cnt IS NULL 
     THEN
        V_cnt := 10000;
     END IF;
     V_cnt := V_cnt+1;
     v_count :=v_count +1;
/*             ：
		sec@ora10g> show serveroutput
		serveroutput OFF
		sec@ora10g>set serveroutput on
		sec@ora10g> show serveroutput
		serveroutput ON SIZE 10000 FORMAT WORD_WRAPPED
 */     
     DBMS_OUTPUT.put_line(v_count);   
     /*      SEQUENCE，          ，        SQL */
     V_sqlStr := 'drop SEQUENCE '||V_seq_name;
     execute immediate V_sqlStr;
     V_sqlStr :='CREATE SEQUENCE '||V_seq_name|| ' MINVALUE 1 MAXVALUE 100000000000 INCREMENT BY 1 START WITH '||V_cnt|| ' nocache cycle';
     execute immediate V_sqlStr;
     END LOOP;
     CLOSE temp_cursor;
END modify_SEQ;

begin 
modify_SEQ;
end;
/
  
2.DB 2 

CREATE PROCEDURE PRO_BD_SEQ()
 
begin
	declare tabName VARCHAR(200) ;
	declare tabSeq BIGINT default 0;
	declare seqName varchar(200);
	declare sqlStr varchar(2000);
	declare seqStr varchar(2000);
	
  
    --      ，    
     DECLARE   myCursor   CURSOR   WITH   RETURN   FOR   MySql; 
	--         
	for names as  select t.tabname from
 (select t.tabname  from syscat.tables t where t.tabschema = 'RISK' and t.TYPE = 'T' and t.tabname not like '%JTYH%' and t.tabname not like 'FE_%' )t inner join (
select tabname from SYSCAT.COLUMNS c where c.colname = 'ID'and c.typename = 'INTEGER'
) c on t.tabname = c.tabname and t.tabname != 'PD_PA_ACCOUNT_RELATION' order by t.tabname
	do 
	set tabName = tabname;
   --           id ，      SEQUENCE
	set sqlStr = 'select max(id) from '||tabName;  
    --               sql    
	
	prepare MySql from sqlStr;

    --    
    open myCursor;
    --             ID
    fetch myCursor into tabSeq;
    --  
    close myCursor;
	--    SEQUENCE
	set tabSeq = tabSeq+1;
	set seqName = 'SEQ_'||tabName;
	set seqStr = 'ALTER SEQUENCE '||seqName||' RESTART WITH '||char(tabSeq);
    --    
	execute immediate seqStr;
	set tabName = '';
	set tabSeq = 0;	
	end for;
	end
;

call PRO_BD_SEQ;