Este artículo describe como duplicar una solo fila en varias filas con ORACLE.
A pesar de lo que digan mis compañeros ‘PRO- JAVA’ del blog , a mi esto me ha parecido de bastante utilidad, así que os lo escribo:
Imaginad que tenéis una tabla con dos registros.
SQL> desc prueba1
Name Null? Type
—————————————– ——– —————————-
ANNIO VARCHAR2(4)
MES_INI VARCHAR2(2)
MES_FIN VARCHAR2(2)
SQL> select * from prueba1;
2008 01 12
2009 01 08
2010 01 10
En cada año tenemos un mes inicial y mes final. Con una sóla select quiero devolver tantas filas como meses hay comprendidos entre ese intervalo.
Esto lo realizamos con una especie de bucle con CONNECT BY PRIOR por el mismo campo. Lo más importante de esta select es poder utilizar la pseudocolumna que nos da el connect by, LEVEL, con la que podremos limitar el bucle.
SELECT ANNIO,
TO_CHAR(ADD_MONTHS(TO_DATE(MES_INI,’MM’),LEVEL-1),’MM’) MONTH,
LEVEL
FROM PRUEBA1
CONNECT BY PRIOR ANNIO = ANNIO
AND PRIOR MES_INI=MES_INI
AND LEVEL <= TO_NUMBER(MES_FIN)
AND PRIOR DBMS_RANDOM.STRING (‘p’, 10) IS NOT NULL
Con esto obtendríamos lo siguiente:
ANNI MO LEVEL
—- — ———-
2008 01 1
2008 02 2
2008 03 3
2008 04 4
2008 05 5
2008 06 6
2008 07 7
2008 08 8
2008 09 9
2008 10 10
2008 11 11
2008 12 12
2009 01 1
2009 02 2
2009 03 3
2009 04 4
2009 05 5
2009 06 6
2009 07 7
2009 08 8
2010 01 1
2010 02 2
2010 03 3
2010 04 4
2010 05 5
2010 06 6
2010 07 7
2010 08 8
2010 09 9
2010 10 10
Que no se nos olvide meter la última condición o nos dará un error tipo:
ORA-01436: CONNECT BY loop in user data
Aquí he encontrado una explicación sobre el paquete dbms_random, por si os interesa:
[Link roto]
Otra de las utilidades que he visto a esto, es poder devolver carácter a carácter cada fila:
SQL> select * from prueba2;
AAA BBB CCC
BBB CCC DDD
DDD EEE FFF
Así podríamos, por ejemplo, contar el número de veces que un determinado carácter aparece en cada fila
SELECT SUBSTR(FILA , LEVEL , 1) as FIL FROM prueba2
CONNECT BY PRIOR FILA = FILA
AND LEVEL <= LENGTH(FILA)
AND PRIOR dbms_random.string (‘a’, 10) IS NOT NULL;
FIL
– –
A
A
A
B
B
B
C
C
C
B
B
B
C
C
C
D
D
D
D
D
D
E
E
E
F
F
F
33 rows selected.
Y la última utilidad que he encontrado, devolver un registro formado por varias palabras una a una:
Creamos una tabla:
create table prueba2 as select ‘ESTOY,DEVOLVIENDO,LA,FILA,POR,PALABRAS’ frase from dual;
Y ahora devolveremos ese registro palabra por palabra:
SQL> select * from prueba2;
FRASE
————————————–
ESTOY,DEVOLVIENDO,LA,FILA,POR,PALABRAS
SELECT SUBSTR (FRASE,
INSTR (FRASE, ‘,’, 1, ROWNUM) + 1,
INSTR (FRASE, ‘,’, 1, ROWNUM+1) – INSTR(FRASE, ‘,’, 1, ROWNUM) -1 ) PALABRAS
FROM ( SELECT ‘,’||FRASE||’,’ FRASE FROM PRUEBA2)
CONNECT BY ROWNUM < LENGTH(FRASE)-LENGTH(REPLACE(FRASE,’,’,”))
AND PRIOR DBMS_RANDOM.RANDOM IS NOT NULL
PALABRAS
—————————————-
ESTOY
DEVOLVIENDO
LA
FILA
POR
PALABRAS