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
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>
- 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.
clear breaks
clear computes
No hay comentarios.:
Publicar un comentario