Dando formato a la salida de una consulta

En esta sección, abordo el tema de cómo dar el formato a las consultas que hacemos a la base de datos, por medio del cliente de Oracle, el Sql Plus.

Todos los que hemos tenido acceso a un prompt de Oracle por medio del Sql Plus, nos hemos topado con el problema de que las consultas que hacemos, nos arrojan resultados algo revueltos como lo que se muestra a continuación:

SQL> select * from dba_users;
USERNAME                          USER_ID PASSWORD
------------------------------ ---------- ------------------------------
ACCOUNT_STATUS                   LOCK_DAT EXPIRY_D
-------------------------------- -------- --------
DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED
------------------------------ ------------------------------ --------
PROFILE                        INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
EXTERNAL_NAME
--------------------------------------------------------------------------
SALT                                   25 4195FE23CF28FA4E
OPEN
USERS                          TEMP                           01/07/08
USERNAME                          USER_ID PASSWORD
------------------------------ ---------- ------------------------------
ACCOUNT_STATUS                   LOCK_DAT EXPIRY_D
-------------------------------- -------- --------
DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED
------------------------------ ------------------------------ --------
PROFILE                        INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
EXTERNAL_NAME
--------------------------------------------------------------------------
PRUEBA                         DEFAULT_CONSUMER_GROUP
USERNAME                          USER_ID PASSWORD
------------------------------ ---------- ------------------------------
ACCOUNT_STATUS                   LOCK_DAT EXPIRY_D
-------------------------------- -------- --------
DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED
------------------------------ ------------------------------ --------
PROFILE                        INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
EXTERNAL_NAME
--------------------------------------------------------------------------
SYSTEM                                  5 9887149C9890214A
OPEN
SYSTEM                         TEMP                           01/07/08

¿Le entendieron? Ja, creo que no mucho verdad? Bueno, después de ver lo que muestro en éste post, tendrán muchas herramientas para poder dar formato a la salida de las consultas. Así pues, comencemos:

Definiendo el área de trabajo

Primero, tenemos que definir el comportamiento del área donde estaremos trabajando. Para esto, usarémos los siguientes comandos que no requieren de un punto y coma (;):

set linesize n

Cambia el tamaño de la línea a los n caracteres. después del último caracter en un renglón, le agrega espacios para rellenar hasta el valor asignado. Se puede abreviar como set lines n.

Por ejemplo:

SQL> set lines 30
SQL> select username, password
  2  from   dba_users
  3  where  rownum < 5
  4  /

USERNAME
------------------------------
PASSWORD
------------------------------
SALT
4195FE23CF28FA4E

SYSTEM
9887149C9890214A

SYS
4790272FCF800D71

USERNAME
------------------------------
PASSWORD
------------------------------
ADMAUD
A101B2757316EC78

4 rows selected.

y con el cambio pertinente:

SQL> set lines 80
SQL> /

USERNAME                       PASSWORD
------------------------------ ------------------------------
SALT                           4195FE23CF28FA4E
SYSTEM                         9887149C9890214A
SYS                            4790272FCF800D71
ADMAUD                         A101B2757316EC78

4 rows selected.

set pagesize n

Define la cantidad de renglones que se mostrarán entre títulos. Los renglones que ocupa cada título también cuentan en n. Se puede abreviar como set pages n. Así, en el siguiente ejemplo se vería:

SQL> set pages 4
SQL> /

USERNAME                       PASSWORD
------------------------------ ------------------------------
SALT                           4195FE23CF28FA4E

USERNAME                       PASSWORD
------------------------------ ------------------------------
SYSTEM                         9887149C9890214A

USERNAME                       PASSWORD
------------------------------ ------------------------------
SYS                            4790272FCF800D71

USERNAME                       PASSWORD
------------------------------ ------------------------------
ADMAUD                         A101B2757316EC78

4 rows selected.

y con el cambio:

SQL> set pages 100
SQL> /

USERNAME                       PASSWORD
------------------------------ ------------------------------
SALT                           4195FE23CF28FA4E
SYSTEM                         9887149C9890214A
SYS                            4790272FCF800D71
ADMAUD                         A101B2757316EC78

4 rows selected.

Si n = 0, entonces, en la salida, no aparecen títulos como se ve en el ejemplo siguiente:

SQL> set pages 0
SQL> /
SALT                           4195FE23CF28FA4E
SYSTEM                         9887149C9890214A
SYS                            4790272FCF800D71
ADMAUD                         A101B2757316EC78

4 rows selected.

set feedback on|off

Dependiendo de si está prendido (on) o apagado (off), muestra o no, la cantidad de registros que se obtuvieron en la consulta:

SQL> set feedback off
SQL> /

USERNAME                       PASSWORD
------------------------------ ------------------------------
SALT                           4195FE23CF28FA4E
SYSTEM                         9887149C9890214A
SYS                            4790272FCF800D71
ADMAUD                         A101B2757316EC78
SQL> set feedback on
SQL> /

USERNAME                       PASSWORD
------------------------------ ------------------------------
SALT                           4195FE23CF28FA4E
SYSTEM                         9887149C9890214A
SYS                            4790272FCF800D71
ADMAUD                         A101B2757316EC78

4 rows selected.

set timing on|off

 De acuerdo a su valor, prendido (on) o apagado (off); se implementará o no, un cronometro para la consulta que se ejecute como se muestra a continuación:

SQL> set timing on
SQL> /

USERNAME                       PASSWORD
------------------------------ ------------------------------
SALT                           4195FE23CF28FA4E
SYSTEM                         9887149C9890214A
SYS                            4790272FCF800D71
ADMAUD                         A101B2757316EC78

4 rows selected.

Elapsed: 00:00:00.01

set pause on|off

 Si está prendido (on), genera una pausa al iniciar la consulta y cada vez que se cumplan los n registros correspondientes al comando set pagesize n. La pausa se “rompe” cuando se teclea [Enter].

Formateando las columnas

Ahora, después de haber formateado el área de trabajo, al consultar algunas tablas, el ancho de cada columna, hace que aún así, la información de un solo registro, se vea en varios renglones como se muestra a continuación:

TBS                                 TOTAL      LIBRE      USADO PORC_USADO
------------------------------ ---------- ---------- ---------- ----------
SUGERENCIA
----------
DATOS                                 600        439        161 26.8333333
-410.58824
INDICES                               400        390         10        2.5
-388.23529
SYSAUX                                300   178.9375   121.0625 40.3541667
-157.57353
SYSTEM                                300  88.359375 211.640625  70.546875
-51.011029
TB_AUD                                500        496          4         .8
-495.29412
TB_AUD_INDX                           500        494          6        1.2
-492.94118
UNDOTBS                               500     450.75      49.25       9.85
-442.05882
USERS                                 500    16.0625   483.9375    96.7875
69.3382353

8 rows selected.

Para lograr que se vea de mejor forma, tendremos que darle un correcto formato a las columnas. Para esto, tenemos el comando:

column nombre_columna format formato

Donde:

nombre_columna   es el nombre que tendrá nuestra columna o el alias que la representa.

formato   es la forma en que daremos una longitud en caracteres para un tipo caracter y el formato para los datos numéricos de la siguiente forma:

Caracter:

an [word_wrapped] donde n es la longitud de caracteres que tendrá; la palabra word_wrapped, hace que los datos contenidos por esta columna, sean recortados a la longitud asignada sin cortar las palabras, y cada línea es justificada a la izquierda sin espacios ni caracteres como TAB.

Numérico:

9|0 con cualquiera de los dos, se reserva una posición para el número que aparecerá, con 9, se reserva un espacio en blanco, con 0 en vez de un espacio, se pone un 0 que ocupará la posición.; se pueden usar comas y puntos para delimitar las cifras.

Si por alguna razón, se reusa una columna que originalmente era caracter de tipo word_wrapped y después se cambia a númerico con un formato con 0s y 9s, el número tendrá este último formato, pero justificado a la izquierda. Para evitar esto, hay que limpiar el formato de la columna con la opción que se ve aquí después de los siguientes ejemplos.

Ejemplos:

column tbs         format a25 word_wrapped
column porc_usado  format 990.00
column libre       format 999,990.00
column sugerencia  format 999,990.00
column total       format 999,990.00
column usado       format 999,990.00

Con esto, cambia radicalmente la forma en que se ve la información resultante de la consulta:

SQL> /
Enter value for orden: 1
old  12: order by &orden
new  12: order by 1

TBS                             TOTAL       LIBRE       USADO PORC_USADO  SUGERENCIA
------------------------- ----------- ----------- ----------- ---------- -----------
DATOS                          600.00      439.00      161.00      26.83     -410.59
INDICES                        400.00      390.00       10.00       2.50     -388.24
SYSAUX                         300.00      178.38      121.63      40.54     -156.91
SYSTEM                         300.00       87.19      212.81      70.94      -49.63
TB_AUD                         500.00      495.00        5.00       1.00     -494.12
TB_AUD_INDX                    500.00      490.00       10.00       2.00     -488.24
UNDOTBS                        500.00      429.69       70.31      14.06     -417.28
USERS                          500.00      499.81        0.19       0.04     -499.78

8 rows selected.

Para limpiar el formato de una columna, se usa el comando:

column nombre_columna clear

Añadiendo títulos

Para añadir un títulos, subtítulos, encabezados, pies de página al resultado de la consulta, tenemos varios comandos: TTITLE, BTITTLE, REPHEADER, REPFOOTER. Estos, se describen de la siguiente forma:

comando” | [skip num_renglones [center | right | left | col num_columna] ‘Título’  … | off

  • Si se usa la opción de comando ”, por default, pone como título la parte del select que muestra las columnas que se están trayendo, así como el número de página y la fecha.
  • El encabezado (repheader) y el pie de página (repfooter), se presentan cada vez que se repiten los encabezados de un resultado de consulta de acuerdo al valor de set pagesize.
  • Si se usa la otra opción, se puede poner como tal, un título predefinido por nosotros, con un salto de num_renglones (con skip) y después de saltar el num_renglones, se centra, alínea a la izquierda o la derecha (center, left, right) o se inicia en una columna en específico (col num_columna) a poner el texto que se ponga. Se puede repetir más veces para poner subtítulos.
  • Si se elige la opción off, se desactivan las opciones.

Por ejemplo:

SQL> select sysdate from dual;

SYSDATE
--------
15/09/08

SQL> set lines 60
SQL> ttitle ''
SQL> btitle ''
SQL> /

Lun Sep 15                                         page    1
                  select sysdate from dual

SYSDATE
--------
15/09/08

                  select sysdate from dual

SQL> ttitle skip 2 center 'Fecha del sistema'
SQL> btitle skip 1 left   'Esa fue la fecha'
SQL> /

                      Fecha del sistema
SYSDATE
--------
15/09/08

Esa fue la fecha

SQL> ttitle skip 1 center 'Fecha del sistema' -
>           skip 1 left   'aquí se presenta la fecha...'
SQL> btitle skip 1 left   'Esa fue la fecha' -
>           skip 1 center 'aquí se presentó la fecha...'
SQL> /

                      Fecha del sistema
aquí se presenta la fecha...
SYSDATE
--------
15/09/08

Esa fue la fecha
                aquí se presentó la fecha...

Para desactivar los títulos, nada más se tienen que usar los comandos:

ttitle off
btitle off

Agregando espacio, subtotales…

Ahora, con las instrucciones break y compute, podemos agregar líneas en blanco para el resultado de las consultas, así como calcular operaciones sobre alguna de las columnas. En el siguiente texto, muestro el formato de cada una de ellas:

break on columna

Sirve para determinar en base a qué columna se hará un brinco.

compute función of columna1 on columna2

Este comando, nos permite aplicar una función a la columna1 de acuerdo al valor de la columna2. La función a aplicar será una de las que aparecen a continuación:

    • SUM          Calcula la suma de columna1.
    • MINIMUM  Calcula el mínimo valor de columna1.
    • MAXIMUM Calcula el máximo valor de columna1.
    • AVG          Calcula el promedio de columna1.
    • STD           Calcula la desviación estándar de columna1.
    • VARIANCE Calcula la varianza de columna1.
    • COUNT      Calcula la cantidad de registros NOT NULL de columna1.
    • NUMBER    Calcula el número de registros de columna1.

Por ejemplo:

  1  select programa, descripcion, clave
  2  from   tipo
  3  where  programa in ('SUAVIZANTE', 'EMPLEADO', 'ESTAMPADO', 'CHECADOR', 'CLIENTE')
  4* order by programa
SQL> /

PROGRAMA             DESCRIPCION                    CLAVE
-------------------- ------------------------------ -----
CHECADOR             ENTRADA                        ENT
CHECADOR             FALTA                          FAL
CHECADOR             SALIDA                         SAL
CLIENTE              CLIENTE EXCELENTE              CAAA
CLIENTE              CLIENTE BUENO                  CA
CLIENTE              CLIENTE MALO                   CC
CLIENTE              CLIENTE MUY BUENO              CAA
CLIENTE              CLIENTE REGULAR                CB
EMPLEADO             EMPLEADO                       EMPL
EMPLEADO             GERENTE                        GTE
EMPLEADO             CHOFER                         CHOF
ESTAMPADO            OTRO TIPO DE ESTAMPADO         OTRES
ESTAMPADO            ESTAMPADO CON CUADROS          CUADR
ESTAMPADO            ESTAMPADO LISO                 LISO
ESTAMPADO            ESTAMPADO CON RAYAS            RAYAS
SUAVIZANTE           SUAVITEL                       SUAVI
SUAVIZANTE           DOWNY                          DOWNY

17 rows selected.

SQL> break on programa
SQL> /

PROGRAMA             DESCRIPCION                    CLAVE
-------------------- ------------------------------ -----
CHECADOR             ENTRADA                        ENT
                     FALTA                          FAL
                     SALIDA                         SAL
CLIENTE              CLIENTE EXCELENTE              CAAA
                     CLIENTE BUENO                  CA
                     CLIENTE MALO                   CC
                     CLIENTE MUY BUENO              CAA
                     CLIENTE REGULAR                CB
EMPLEADO             EMPLEADO                       EMPL
                     GERENTE                        GTE
                     CHOFER                         CHOF
ESTAMPADO            OTRO TIPO DE ESTAMPADO         OTRES
                     ESTAMPADO CON CUADROS          CUADR
                     ESTAMPADO LISO                 LISO
                     ESTAMPADO CON RAYAS            RAYAS
SUAVIZANTE           SUAVITEL                       SUAVI
                     DOWNY                          DOWNY

17 rows selected.

SQL> compute count of clave on programa
SQL> /

PROGRAMA             DESCRIPCION                    CLAVE
-------------------- ------------------------------ -----
CHECADOR             ENTRADA                        ENT
                     FALTA                          FAL
                     SALIDA                         SAL
********************                                -----
count                                                   3
CLIENTE              CLIENTE EXCELENTE              CAAA
                     CLIENTE BUENO                  CA
                     CLIENTE MALO                   CC
                     CLIENTE MUY BUENO              CAA
                     CLIENTE REGULAR                CB
********************                                -----
count                                                   5
EMPLEADO             EMPLEADO                       EMPL
                     GERENTE                        GTE
                     CHOFER                         CHOF
********************                                -----
count                                                   3
ESTAMPADO            OTRO TIPO DE ESTAMPADO         OTRES
                     ESTAMPADO CON CUADROS          CUADR
                     ESTAMPADO LISO                 LISO
                     ESTAMPADO CON RAYAS            RAYAS
********************                                -----
count                                                   4
SUAVIZANTE           SUAVITEL                       SUAVI
                     DOWNY                          DOWNY
********************                                -----
count                                                   2

17 rows selected.

Para mostrar los valores para compute y para break, sólo se ejecutan los comandos sin ningún parámetro, Por ejemplo:

SQL> compute
COMPUTE number LABEL 'number' OF clave ON programa
SQL> break
break on programa nodup

Para limpiar los valores de cada uno de los comandos, se ejecutan las siguientes sentencias:

  • clear breaks
  • clear computes

Por ejemplo:

SQL> clear breaks
breaks cleared
SQL> clear computes
computes cleared
SQL>

Caso especial: Añadir total general (o promedio o mínimo o…)

Cuando se requiere que al final del resultado de una consulta, se muestre un total general, se requiere agregar un break a nivel reporte por medio del comando:

break on report

Para después, agregar como se vio en el punto anterior, un comando que calcule una función a nivel de reporte, en nuestro ejemplo, usaré la suma como muestro en el comando siguiente:

compute sum label etiqueta of column on report

Nota importante. Para que funcione este tipo de funciones, la columna a la que se le calculará la función, no debe ser la primera.

Poniendo en práctica lo que comento, vamos a ver un ejemplo:

SQL> break on report
SQL> compute sum label SUMA of cantidad on report
SQL>
SQL> select object_type, count(*) cantidad
  2  from   dba_objects
  3* group by object_type;  

OBJECT_TYPE            CANTIDAD
-------------------- ----------
CONSUMER GROUP                5
INDEX PARTITION              80
SEQUENCE                     93
QUEUE                        19
SCHEDULE                      1
TABLE PARTITION              72
RULE                          1
PROCEDURE                    49
OPERATOR                      6
LOB PARTITION                 1
WINDOW                        2
LOB                         120
PACKAGE                     461
PACKAGE BODY                447
LIBRARY                     113
RULE SET                      9
PROGRAM                       3
TYPE BODY                    81
CONTEXT                       1
TRIGGER                       3
JOB CLASS                     2
UNDEFINED                     6
DIRECTORY                     2
TABLE                       856
INDEX                       891
SYNONYM                    2404
VIEW                       2840
FUNCTION                     65
WINDOW GROUP                  1
CLUSTER                      10
TYPE                        940
RESOURCE PLAN                 3
EVALUATION CONTEXT            7
JOB                           6
                     ----------
SUMA                       9600

34 rows selected.

Enviando el resultado a un archivo

Una vez que se ha configurado todo lo que hasta el momento se ha mencionado, se puede enviar el resultado a un archivo de texto. Con la instrucción:

spool [directorio]nombre_archivo.extensión

comandos y resultados de información…

spool off

Donde directorio, será de acuerdo al sistema operativo con el que se esté trabajando. Si se omite éste dato, el archivo de generará en el directorio de instalación de Oracle si se trabaja en windows, o en el directorio desde el cual, se ejecutó el sqlplus para linux o unix.

Por ejemplo:

spool /home/oracle/datos.txt

USERNAME                       PASSWORD
------------------------------ ------------------------------
SALT                           4195FE23CF28FA4E
SYSTEM                         9887149C9890214A
SYS                            4790272FCF800D71
ADMAUD                         A101B2757316EC78

spool off

Con esto, se generará un archivo llamado datos.txt en /home/oracle. Aquí, si se genera un registro con separación de tabuladores entre datos y le ponemos una extensión .xls al archivo, lo podremos cargar muy facil en excel.

Conclusiones

Como se puede ver, es muy útil toda ésta información para poder presentar los resultados de las consultas ejecutadas. Los ejemplos y formatos que muestro, son los que he usado en algún momento.

Si la información de este post te ha sido de utilidad o quieres que agregue algo más, deja por favor un comentario, contestaré a la brevedad.

Anuncios

9 Responses to Dando formato a la salida de una consulta

  1. Pingback: Manos a la obra – Instalando PeopleSoft « unmexicanodistinto

  2. Benja says:

    La información me sirvió mucho, gracias por el aporte.
    Ahora quisiera saber como hacer que se imprima solo el total de registros encontrados o borrados, es decir.. que solo muestre “4 rows selected” sin la visualizar los registros.

  3. Orlando Olguín Olvera says:

    Hola Benja.

    Esa sí está difícil de esa manera como la pides.

    Lo más fácil, es que hagas un select count(*) from tabla where condiciones;

    Orlando.

  4. Rafael says:

    Muy buen aporte, excelente XD. Quisiera saber si existe alguna forma de indicar en una columna, que muestre los datos juntados a la derecha; y no como es de costumbre que aparecen juntados a la izquierda. Igual gracias de antemano por la información.

  5. Orlando Olguín Olvera says:

    Hola Rafael.

    No hay algo como un comando que justifique la información, tendrias qué hacer uso de cosas como RPAD, LPAD, TRIM, TO_CHAR, etcétera.

    Orlando.

  6. luisbenajmin says:

    hola una pregunta como hago para poder dar color a una fila si el archivo a exportar es .csv , ademas como hago para hacer una consulta con parametros dinamicos es decir que a la hora que ejecute el .sql , en sqlplus me pida algun parametro para la consulta, muchas gracias.

  7. Orlando Olguín Olvera says:

    Hola Luis.

    Si es un archivo csv, es un archivo de texto, por lo que no tiene atributos como para dar color a un registro, eso lo tendrías qué hacer ya en la hoja de cálculo donde lo cargues.

    Por otro lado, en mis scripts útiles para un DBA, podrás ver cómo paso parámetros a un archivo sql:

    https://orlandoolguin.wordpress.com/2010/02/26/scripts-utiles-para-un-dba/

    Espero te sirvan.

    Orlando.

  8. Rafa VM says:

    Buen a porte para los que empezamos en esto. Cuando Mando una búsqueda a un archivo *.csv, mi campo Razon Social con tiene comas, entonces al abrir el csv me separa ese campo. Como podre hacer caso omiso de esas comas?

  9. Orlando Olguín Olvera says:

    Hola Rafa.

    Puedes poner comillas (“) para delimitar tu información y separarla por pipes (|):

    select ‘”‘ || campo1 || ‘”|”‘ || campo2 || ‘”‘
    from tabla;

    De tal forma, que el resultado final será algo así como:

    “James Hetfield”|”MetallicA”
    “Tom Araya”|”Slayer”

    Así, podrás usar el | como delimitador de campos y luego como estará entre comillas, tomará todo el texto para subirlo como un campo.

    Ten cuidado si el campo tiene comillas en el dato que guardó porque eso te causará “ruido” en tu archivo.

    Orlando.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: