22/05/2017
Inicializar una secuencia creada en Oracle
Procedimiento en PL SQL para inicializar una secuencia que fue creada previamente en la base de datos Oracle.Con el siguiente procedimiento creado en PL/SQL se puede inicializar un secuencia creada en Oracle con el objetivo de hacerla reutilizable durante la ejecución de otros subprocesos.
Digamos que desea generar un número consecutivo a través de la secuencia creada para asignar un valor único relacionado con cada uno de los registros procesados. En condiciones normales la secuencia le asignará un valor que se incrementa según sea el caso con «nextval». Sin embargo, existe la posibilidad de eliminar los registros para nuevamente re-procesarlos pero sin mantener la secuencia antes ejecutada y crear valores únicos para un campo de tipo ID completamente inicializados.
El procedimiento pasa como parámetros tres valores de los cuales el primero indica el nombre de la secuencia, el segundo el valor mínimo al cual será inicializada la secuencia y el tercer parámetro, que es de salida, devolverá verdadero o falso dependiendo de si la inicialización se completó con éxito.
Como veremos en el procedimiento, es necesario consultar el último valor generado de la secuencia para luego incrementar negativamente dicho valor consultado, seguidamente es obligatorio realizar una nueva consulta que permitirá incrementar la secuencia a su valor inicial que a su vez, nos permitirá definitivamente inicializar su valor según el parámetro p_min_val.
Puedes apoyar mi contribución en la publicación de recursos como éste, realizando una donación (BTC). Igualmente te invito a enviarme tus comentarios e inquietudes a través de nuestro sistema de contacto.
Procedimiento que permite inicializar una secuencia creada en Oracle.
CREATE OR REPLACE PROCEDURE P_RESET_SEQ(p_squence IN VARCHAR2, p_min_val IN NUMBER, p_salida IN OUT BOOLEAN)
AS
/* ************************************************************************************
* Procedimiento: p_reset_seq
* Descripción: inicializa una secuencia en su valor a cero
* Parámetros: p_squence Nombre de la Secuencia
* p_min_val Valor mínimo de la secuencia inicializada
* p_salida Retorno del procedimiento (TRUE o FALSE)
* *********************************************************************************
*/
v_valor NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT '||p_squence||'.nextval FROM DUAL' INTO v_valor;
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || p_squence || ' INCREMENT BY -' || v_valor ||' MINVALUE ' || p_min_val;
EXECUTE IMMEDIATE 'SELECT ' || p_squence || '.nextval FROM DUAL' INTO v_valor;
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || p_squence || ' INCREMENT BY 1 MINVALUE ' || p_min_val;
p_salida := TRUE;
EXCEPTION
WHEN
OTHERS THEN
p_salida := FALSE;
END P_RESET_SEQ;
/
AS
/* ************************************************************************************
* Procedimiento: p_reset_seq
* Descripción: inicializa una secuencia en su valor a cero
* Parámetros: p_squence Nombre de la Secuencia
* p_min_val Valor mínimo de la secuencia inicializada
* p_salida Retorno del procedimiento (TRUE o FALSE)
* *********************************************************************************
*/
v_valor NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT '||p_squence||'.nextval FROM DUAL' INTO v_valor;
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || p_squence || ' INCREMENT BY -' || v_valor ||' MINVALUE ' || p_min_val;
EXECUTE IMMEDIATE 'SELECT ' || p_squence || '.nextval FROM DUAL' INTO v_valor;
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || p_squence || ' INCREMENT BY 1 MINVALUE ' || p_min_val;
p_salida := TRUE;
EXCEPTION
WHEN
OTHERS THEN
p_salida := FALSE;
END P_RESET_SEQ;
/
Crear una secuencia para probar nuestro ejemplo
Luego de compilar nuestro procedimiento procedemos a crear una secuencia cualquiera identificada con el nombre my_sequence, que inicie en 1000 y se incremente en un valor de 1 con cada consulta que se le realice.
CREATE SEQUENCE my_sequence
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;
/
Sequence created.START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;
/
Generando el valor siguiente de la secuencia
En este punto lo que hacemos es realizar una consulta de nuestra secuencia recientemente creada para causar el incremento de su valor tantas veces como sean necesarias.
SELECT my_sequence.nextval FROM dual;
NEXTVAL1000
SELECT my_sequence.nextval FROM dual;
NEXTVAL1001
SELECT my_sequence.nextval FROM dual;
NEXTVAL1002
Inicializar la secuencia «my_sequence»
Con nuestra secuencia actualmente en valor 1002, ejecutados un bloque anónimo donde llamaremos a nuestro procedimiento con los parámetros requeridos. En caso de un buen funcionamiento el subproceso devolverá una salida con la palabra TRUE, en caso contrario imprimirá FALSE.
DECLARE
v_salida BOOLEAN;
BEGIN
P_RESET_SEQ('my_sequence',0,v_salida);
IF v_salida THEN
DBMS_OUTPUT.PUT_LINE('Secuencia inicializada');
ELSE
DBMS_OUTPUT.PUT_LINE('Secuencia no inicializada');
END IF;
END;
/
Statement processed.v_salida BOOLEAN;
BEGIN
P_RESET_SEQ('my_sequence',0,v_salida);
IF v_salida THEN
DBMS_OUTPUT.PUT_LINE('Secuencia inicializada');
ELSE
DBMS_OUTPUT.PUT_LINE('Secuencia no inicializada');
END IF;
END;
/
Secuencia inicializada
Confirmar la secuencia inicializada
Tras inicializar nuestra secuencia («my_sequence»), continuamos a realizar una consulta para verificar que el valor de «nextval» ha cambiado correctamente.
SELECT my_sequence.nextval FROM dual;
NEXTVAL1
(Enorable)
Consulta Lo+ Top de Base De Datos