如何设置Oracle Sequence 的值

rksqucnzvr

写道
CREATE SEQUENCE gokhan.sample_seq START WITH 1000;

SELECT last_number
FROM user_sequences
WHERE sequence_name = 'SAMPLE_SEQ';

LAST_NUMBER
-----------
1000

ALTER SEQUENCE gokhan.sample_seq INCREMENT BY -500;
SELECT gokhan.sample_seq.NEXTVAL FROM dual;
ALTER SEQUENCE gokhan.sample_seq INCREMENT BY 1;

SELECT last_number
FROM user_sequences
WHERE sequence_name = 'SAMPLE_SEQ';

LAST_NUMBER
-----------
500
CREATE OR REPLACE PROCEDURE SYS.SEQUENCE_NEWVALUE(
seqowner VARCHAR2,
seqname VARCHAR2,
newvalue NUMBER) AS
ln NUMBER;
ib NUMBER;
BEGIN
SELECT last_number, increment_by
INTO ln, ib
FROM dba_sequences
WHERE sequence_owner = upper(seqowner)
AND sequence_name = upper(seqname);
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seqowner || '.' || seqname ||
' INCREMENT BY ' || (newvalue - ln);
EXECUTE IMMEDIATE 'SELECT ' || seqowner || '.' || seqname ||
'.NEXTVAL FROM DUAL' INTO ln;
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seqowner || '.' || seqname 
|| ' INCREMENT BY ' || ib;
END;
 
GRANT EXECUTE ON sequence_newvalue TO gokhan;
 
EXEC sequence_newvalue( 'GOKHAN', 'SAMPLE_SEQ', 10000 );