secuencia para generar numeros con fechas

[azukita]

Hola a todos
necesito ayuda para crear una secuencia que me genere numeros con el formato de: año/numero.

el numero se va incrementando de uno en uno y cuando cambia el año actual, comienza el numero otra vez en uno.
Esta sequencia se utilizaria para cada vez que se ingresa un registro nuevo.
He creado secuencia para hacer numeros autoincrementales pero no se como realizar esta secuencia con la fecha.

Gracias y un saludo.

[MutenRo]

Hola azukita,

se me hace algo extraño que realmente se necesite hacer algo así (¿por qué no una campo para la secuencia y otro para el año aunque la secuencia de cada año no empiece por 1?), pero bueno, se me ha ocurrido esta manera:

- Para la secuencia de cada año tendrás un máximo en mente, supongo, de n cifras (supongamos que son 6, así podremos tener casi un millón), que en ningún caso se va a alcanzar. Entonces creas para empezar una secuencia que empiece por 2008000001:

Código:

CREATE SEQUENCE SEQ_PRUEBA START WITH 2008000001 INCREMENT BY 1;

- Por otra parte creas esta función, que será a la que vas a llamar siempre que quieras obtener un valor de la secuencia:

Código:

CREATE OR REPLACE FUNCTION FN_NEXTVAL RETURN NUMBER 
 IS
 v_num_sec NUMBER;
 v_primera NUMBER;
 
 BEGIN
 
 SELECT SEQ_PRUEBA.NEXTVAL INTO v_num_sec FROM DUAL;
 
 IF(SUBSTR(TO_CHAR(v_num_sec),1,4) <> TO_CHAR(SYSDATE,'YYYY')) THEN
     v_primera := TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) * POWER(10,LENGTH(v_num_sec)-4) + 1;
     WHILE(v_num_sec < v_primera)
     LOOP
          SELECT SEQ_PRUEBA.NEXTVAL INTO v_num_sec FROM DUAL;
     END LOOP;
 END IF;
 
 RETURN v_num_sec;
 
END FN_NEXTVAL;
/

De esta manera vas a obtener el número de secuencia adecuado en cada momento, sabiendo que los 4 primeros dígitos son el año y el resto son la secuencia para cada año.
Y si estás trabajando con Oracle 11g recuerda que no hace falta hacer una select para obtener el siguiente número de secuencia, basta con escribir 'variable := seq.NEXTVAL;'.

Un saludo.

[azukita]

Gracias por la respuesta MutenRo, pero yo estaba intentado otra cosa. Lo que quiero es un trigger (no se si se como utilizar las funciones, soy muuuuuy novato) que por cada insercion de registro me cree un codigo, por ejemplo 2008/0013 y cuando se cambie de año pase a ser de nuevo 2009/0001.

No se si me podrias decirme algo mas, pero muchas gracias por la solución (que la tengo que revisar aun).

Un saludo y gracias.

[MutenRo]

pero ese código dónde se introduce, en un campo VARCHAR2 como cadena ( '2008/0013 ')? ó en dos campos separados uno para el año y otro para la secuencia?

De todas maneras en este caso no te recomiendo utilizar triggers porque no vas a procesar/modificar campos. Te recomiendo que hagas la insert pasándole como parámetro lo que te devuelve la función que te he puesto (puedes procesar lo que devuelve la función para obtener el formato que te interesa).

Un saludo.

[azukita]

Buenas de nuevo.
He repasado la funcion y esta muy bien, pero me queda una dudita, ¿para que utilizas el power?
Gracias por la ayuda, ha sido muy buena.

Iniciado por MutenRo

Código:

CREATE OR REPLACE FUNCTION FN_NEXTVAL RETURN NUMBER 
 IS
 v_num_sec NUMBER;
 v_primera NUMBER;
 
 BEGIN
 
 SELECT SEQ_PRUEBA.NEXTVAL INTO v_num_sec FROM DUAL;
 
 IF(SUBSTR(TO_CHAR(v_num_sec),1,4) <> TO_CHAR(SYSDATE,'YYYY')) THEN
     v_primera := TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) * POWER(10,LENGTH(v_num_sec)-4) + 1;
     WHILE(v_num_sec < v_primera)
     LOOP
          SELECT SEQ_PRUEBA.NEXTVAL INTO v_num_sec FROM DUAL;
     END LOOP;
 END IF;
 
 RETURN v_num_sec;
 
END FN_NEXTVAL;
/

[MutenRo]

POWER lo que hace es elevar un número a otro, en este caso eleva 10 a la longitud de la secuencia menos 4, para multiplicarlo por el año actual y sumarle uno, así obtenemos la secuencia nº1 del año actual (solamente se ejecutará una vez al año, la primera vez que se solicite la secuencia ese año).
De esta manera el código de la función es independiente de la longitud con que se había creado la secuencia.

Un saludo.

[Drako_18]

buenas amigos;

yo sinceramente te aconsejo que hagas esto dentro de una funcion y retornes su valor...

select to_char(sysdate,'yyyy')||'/'||trim(to_char((max(substr(id_tabla,6))+1),'000000')) id_alias
from nombre_tabla
where substr(id_tabla, 0, 4) = to_char(sysdate, 'yyyy');


si el id mas alto de tu tabla es 2008/000009,
coge los digitos que estan a la derecha del / y le suma 1 y despues construye la nueva id, colocando el año. Hara esto, siempre que el año de la id sea el año actual, creo que es la mejor opcion y la mas completita y no creo que consuma demasiado recursos.



un saludo amig@!!!

[MutenRo]

Hola Drako_18,

discrepo de tu solución puesto que no estás utilizando secuencias. Tomando el máximo y sumándole 1 no garantizas que dos usuarios no vayan a obtener la misma id (es un error muy común implementar así las secuencias).

Fíjate además en que esa consulta dará la excepción NO_DATA_FOUND cuando haya un cambio de año. Lo sé, puedes capturar la excepción y devolver el año actual con la secuencia 000001, pero no lo has dicho

Un saludo.

[Drako_18]

Es cierto compañero!!!

no utilizo secuencias, y puede devolver dicho error.

aunque tambien podemos utilizar una secuencia numerica normal, y añadiendole el año, y en el no_data_found, reiniciamos la secuencia, puesto que ya estaríamos empezando un año nuevo (realizando todo esto en una funcion).

si no la cuelgo hoy, la colgaré mañana!!!

haber si tengo un poco de time libre...

Saludo!!!

PD: Gracias por corregirme MutenRo!!!