miércoles, 15 de mayo de 2013

Colecciones de datos en Oracle




No me gusta mucho la empresa Oracle, ni su base de datos, pero hay que reconocer que tiene cosas fuera de serie. Ojo esta funcionalidad no es ninguna novedad es de 10g; pero es una novedad para mi. :P

Explico el problema, en ciertos casos tenemos que mostrar datos agrupados pero estos se pueden repetir, por lo tanto generan una nueva fila y nosotros no queremos esto. Los complique! Vamos con un ejemplo que es más fácil  tengo una tabla persona y esta persona tiene mails, uno o muchos. Quiero mostrar la persona y sus mails pero siempre tiene que haber una linea por persona. Ahora si?

En Oracle existe collect esta función nos permite agrupar datos, por ejemplo:


 SELECT p.nombre
    ,      COLLECT(m.mail) AS mails
 FROM   persona p, mails m
 WHERE p.id = m.persona_id
 GROUP  BY p.nombre;

Con esta sencilla query tengo la lista de mail por nombre de persona. Ahora quiero los mails que no se repiten:


 SELECT p.nombre
    ,      COLLECT( DISTINCT m.mail) AS mails
 FROM   persona p, mails m
 WHERE p.id = m.persona_id
 GROUP  BY p.nombre;

Si lo ejecutamos vamos a ver que no se muestra muy lindo, lo que podemos hacer es hacer un tipo y castearlo a ese tipo:

CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);


 SELECT p.nombre
    ,      CAST(COLLECT( DISTINCT m.mail) AS varchar2_ntt) AS mails
 FROM   persona p, mails m
 WHERE p.id = m.persona_id
 GROUP  BY p.nombre;

Bueno ahora se ve mejor pero no esta de diez. Si queremos que quede pippicucu tenemos que hacer uns función. Es raro que oracle no proponga o tenga ninguna:


  CREATE FUNCTION to_string (
                    nt_in        IN varchar2_ntt,
                    delimiter_in IN VARCHAR2 DEFAULT ','
                   ) RETURN VARCHAR2 IS

     v_idx PLS_INTEGER;
     v_str VARCHAR2(32767);
     v_dlm VARCHAR2(10);

  BEGIN

      v_idx := nt_in.FIRST;
      WHILE v_idx IS NOT NULL LOOP
         v_str := v_str || v_dlm || nt_in(v_idx);
         v_dlm := delimiter_in;
         v_idx := nt_in.NEXT(v_idx);
      END LOOP;

      RETURN v_str;

 END to_string;
  /

Con esta función es todo más fácil:


SELECT p.nombre
    ,      to_string(CAST(COLLECT( DISTINCT m.mail) AS varchar2_ntt)) AS mails
 FROM   persona p, mails m
 WHERE p.id = m.persona_id
 GROUP  BY p.nombre;


Y si queremos que quede más cheto agregamos el separador como ', '


SELECT p.nombre
    ,      to_string(CAST(COLLECT( DISTINCT m.mail) AS varchar2_ntt),  ', ' ) AS mails
 FROM   persona p, mails m
 WHERE p.id = m.persona_id
 GROUP  BY p.nombre;

La verdad muy útil!!

Dejo link:
http://docs.oracle.com/cd/E11882_01/server.112/e10592/functions031.htm#i1271564
http://www.oracle-developer.net/display.php?id=306