重构SEQUENCE的值
CREATE OR REPLACE PROCEDURE modify_SEQIS/**===============================================================================控制变量定义===============================================================================*/ 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 serveroutputserveroutput OFFsec@ora10g>set serveroutput onsec@ora10g> show serveroutputserveroutput 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() begindeclare 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.tabnamedo set tabName = tabname; --拼装获得每个表的最大的id值，用来重新设置SEQUENCEset sqlStr = 'select max(id) from '||tabName; --将获得的表名拼装到可执行的动态sql中去执行prepare MySql from sqlStr; --开启游标 open myCursor; --动态获得多查询出来的最大的ID fetch myCursor into tabSeq; --关闭 close myCursor;--设置新的SEQUENCEset 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;