修改sequenc number

create or replace procedure CHANGE_SEQ_NUMBER( PI_SEQ_OWNER VARCHAR2 , PI_SEQNAME VARCHAR2 , PI_LAST_NUMBER NUMBER) IS
V_LastValue integer;
V_increment_by integer;
V_cache_size integer;
V_last_number integer;
V_SQL VARCHAR2(2000) ;
BEGIN
SELECT DP.increment_by , DP.cache_size , DP.last_number
INTO V_increment_by , V_cache_size , V_last_number
FROM dba_sequences DP WHERE DP.sequence_owner = PI_SEQ_OWNER AND DP.sequence_name =PI_SEQNAME ;
IF PI_LAST_NUMBER > V_last_number THEN
V_SQL:= ' alter sequence '||PI_SEQ_OWNER||'.'||PI_SEQNAME||' increment by ' ||( PI_LAST_NUMBER-V_last_number )||' nocache ';
EXECUTE IMMEDIATE V_SQL ;

V_SQL:= ' select '||PI_SEQ_OWNER||'.'||PI_SEQNAME||'.nextval from dual ' ;
EXECUTE IMMEDIATE V_SQL INTO V_LastValue ;

V_SQL:= ' alter sequence '||PI_SEQ_OWNER||'.'||PI_SEQNAME||' increment by 1 nocache ';
EXECUTE IMMEDIATE V_SQL ;

LOOP

 V_SQL:= ' select '||PI_SEQ_OWNER||'.'||PI_SEQNAME||'.currval  from dual ' ;
 EXECUTE IMMEDIATE V_SQL INTO V_LastValue ;
 
 exit when V_LastValue >= PI_LAST_NUMBER - 1;
 
 V_SQL:= ' select '||PI_SEQ_OWNER||'.'||PI_SEQNAME||'.nextval  from dual ' ;
 EXECUTE IMMEDIATE V_SQL INTO V_LastValue ;

END LOOP ;

IF V_cache_size >1 THEN
 V_SQL:= ' alter sequence '||PI_SEQ_OWNER||'.'||PI_SEQNAME||' increment by ' ||V_increment_by||' cache '||V_cache_size ;
ELSE
 V_SQL:= ' alter sequence '||PI_SEQ_OWNER||'.'||PI_SEQNAME||' increment by ' ||V_increment_by||' nocache ' ;
END IF;
 EXECUTE IMMEDIATE V_SQL  ;

END IF ;
END;