Reset Sequence in Oracle 10g
[lang_it]
Cercando in rete non sono riuscito a trovare una funzione che mi permettesse di cambiare il valore ad una sequence definita sul mio DB oracle, spesso accade che dopo un importazione di dati ci sia un disallinamento delle chiavi fisiche del DB con le relative sequence. A questo proposito ho scritto una funzione che permette appunto di cambiare alcuni parametri di una sequence esistente sul vostro database senza dover ogni volta scrivere una decina di istruzioni SQl.
Il codice di tale funzione :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 |
CREATE OR REPLACE FUNCTION F_RESET_SEQUENCE(SEQUNECE_NAME VARCHAR, CURRENT_VALUE VARCHAR DEFAULT '0', INCREMENT_BY VARCHAR DEFAULT '1', MINVAL VARCHAR DEFAULT '0') RETURN NUMBER IS tmpVar NUMBER; I INTEGER; /****************************************************************************** NAME: F_RESET_SEQUENCE PURPOSE: REVISIONS: Ver Date Author Description --------- ---------- --------------- ------------------------------------ 1.0 30/11/2010 1. Created this function. NOTES: Automatically available Auto Replace Keywords: Object Name: F_RESET_SEQUENCE Sysdate: 30/11/2010 Date and Time: 30/11/2010, 12.40.53, and 30/11/2010 12.40.53 Username: (set in TOAD Options, Procedure Editor) Table Name: (set in the "New PL/SQL Object" dialog) ******************************************************************************/ TYPE EmpCurTyp IS REF CURSOR; -- define weak REF CURSOR type emp_cv EmpCurTyp; -- declare cursor variable emp_cv1 EmpCurTyp; -- declare cursor variable PRAGMA AUTONOMOUS_TRANSACTION; BEGIN OPEN emp_cv FOR -- open cursor variable 'SELECT (0-(CONFIGURAZIONE_SEQ.CURRVAL - 1)) AS VAL FROM DUAL'; LOOP FETCH emp_cv INTO tmpVar; -- fetch next row EXIT WHEN emp_cv%NOTFOUND; -- exit loop when last row is fetched -- process row END LOOP; CLOSE EMP_CV; IF (TMPVAR <> 0) THEN EXECUTE IMMEDIATE('ALTER SEQUENCE ' || SEQUNECE_NAME || ' INCREMENT BY ' || CAST(TMPVAR AS VARCHAR) ||' MINVALUE 0'); OPEN emp_cv FOR -- open cursor variable 'SELECT ' || SEQUNECE_NAME || '.NEXTVAL AS VAL FROM dual'; LOOP FETCH emp_cv INTO tmpVar; -- fetch next row EXIT WHEN emp_cv%NOTFOUND; -- exit loop when last row is fetched -- process row END LOOP; CLOSE EMP_CV; END IF; IF (CURRENT_VALUE <> '0') THEN EXECUTE IMMEDIATE('ALTER SEQUENCE ' || SEQUNECE_NAME || ' INCREMENT BY ' || CURRENT_VALUE ||' MINVALUE 0'); OPEN emp_cv FOR -- open cursor variable 'SELECT ' || SEQUNECE_NAME || '.NEXTVAL FROM dual'; LOOP FETCH emp_cv INTO tmpVar; -- fetch next row EXIT WHEN emp_cv%NOTFOUND; -- exit loop when last row is fetched -- process row END LOOP; CLOSE EMP_CV; END IF; EXECUTE IMMEDIATE('ALTER SEQUENCE ' || SEQUNECE_NAME || ' INCREMENT BY ' || INCREMENT_BY); OPEN emp_cv FOR -- open cursor variable 'SELECT ' || SEQUNECE_NAME || '.CURRVAL FROM dual'; LOOP FETCH emp_cv INTO tmpVar; -- fetch next row EXIT WHEN emp_cv%NOTFOUND; -- exit loop when last row is fetched -- process row END LOOP; CLOSE EMP_CV; COMMIT; RETURN tmpVar; END F_RESET_SEQUENCE; |
per usare la funzione ad esempio , suppenendo che ho una sequnce che si chiama “configurazione_seq” e volessi portare il suo valore attuale a 1000 basterebbe eseguire il seguente comando :
|
1 |
SELECT F_RESET_SEQUENCE('CONFIGURAZIONE_SEQ','1000') FROM dual; |
[/lang_it]
[lang_en]
here is a function that allow to change the state of a sequence defined in an oracle db without drop and recreate the sequence.
the code of the function :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 |
CREATE OR REPLACE FUNCTION F_RESET_SEQUENCE(SEQUNECE_NAME VARCHAR, CURRENT_VALUE VARCHAR DEFAULT '0', INCREMENT_BY VARCHAR DEFAULT '1', MINVAL VARCHAR DEFAULT '0') RETURN NUMBER IS tmpVar NUMBER; I INTEGER; /****************************************************************************** NAME: F_RESET_SEQUENCE PURPOSE: REVISIONS: Ver Date Author Description --------- ---------- --------------- ------------------------------------ 1.0 30/11/2010 1. Created this function. NOTES: Automatically available Auto Replace Keywords: Object Name: F_RESET_SEQUENCE Sysdate: 30/11/2010 Date and Time: 30/11/2010, 12.40.53, and 30/11/2010 12.40.53 Username: (set in TOAD Options, Procedure Editor) Table Name: (set in the "New PL/SQL Object" dialog) ******************************************************************************/ TYPE EmpCurTyp IS REF CURSOR; -- define weak REF CURSOR type emp_cv EmpCurTyp; -- declare cursor variable emp_cv1 EmpCurTyp; -- declare cursor variable PRAGMA AUTONOMOUS_TRANSACTION; BEGIN OPEN emp_cv FOR -- open cursor variable 'SELECT (0-(CONFIGURAZIONE_SEQ.CURRVAL - 1)) AS VAL FROM DUAL'; LOOP FETCH emp_cv INTO tmpVar; -- fetch next row EXIT WHEN emp_cv%NOTFOUND; -- exit loop when last row is fetched -- process row END LOOP; CLOSE EMP_CV; IF (TMPVAR <> 0) THEN EXECUTE IMMEDIATE('ALTER SEQUENCE ' || SEQUNECE_NAME || ' INCREMENT BY ' || CAST(TMPVAR AS VARCHAR) ||' MINVALUE 0'); OPEN emp_cv FOR -- open cursor variable 'SELECT ' || SEQUNECE_NAME || '.NEXTVAL AS VAL FROM dual'; LOOP FETCH emp_cv INTO tmpVar; -- fetch next row EXIT WHEN emp_cv%NOTFOUND; -- exit loop when last row is fetched -- process row END LOOP; CLOSE EMP_CV; END IF; IF (CURRENT_VALUE <> '0') THEN EXECUTE IMMEDIATE('ALTER SEQUENCE ' || SEQUNECE_NAME || ' INCREMENT BY ' || CURRENT_VALUE ||' MINVALUE 0'); OPEN emp_cv FOR -- open cursor variable 'SELECT ' || SEQUNECE_NAME || '.NEXTVAL FROM dual'; LOOP FETCH emp_cv INTO tmpVar; -- fetch next row EXIT WHEN emp_cv%NOTFOUND; -- exit loop when last row is fetched -- process row END LOOP; CLOSE EMP_CV; END IF; EXECUTE IMMEDIATE('ALTER SEQUENCE ' || SEQUNECE_NAME || ' INCREMENT BY ' || INCREMENT_BY); OPEN emp_cv FOR -- open cursor variable 'SELECT ' || SEQUNECE_NAME || '.CURRVAL FROM dual'; LOOP FETCH emp_cv INTO tmpVar; -- fetch next row EXIT WHEN emp_cv%NOTFOUND; -- exit loop when last row is fetched -- process row END LOOP; CLOSE EMP_CV; COMMIT; RETURN tmpVar; END F_RESET_SEQUENCE; |
an example of use , suppose we have a sequence whose name is “configurazione_seq” and we need to set the current value to 1000 :
|
1 |
SELECT F_RESET_SEQUENCE('CONFIGURAZIONE_SEQ','1000') FROM dual; |
[/lang_en]
ciao
Ivan
