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;
/

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.tabnam