Informes SQL Plus

Introducción 







El uso más común de SQL*Plus SQL*Plus es realizar consultas y obtener informes. informes. No obstante, los datos obtenidos en las consultas se pueden presentar de forma más elegante. En esta unidad se explica como utilizar SQL*Plus SQL*Plus para crear crear listados list ados formateados, controlando los títulos del listado, lis tado, las cabeceras de las columnas, las líneas de totales totales y subtotales, etc. La salida salida formateada de una consulta consulta se puede pued e guardar en un fichero f ichero para ejecutarlo que vez vez que se necesite consultar el listado.

Uso del editor para crear archivos SQL 





Para guardar las órdenes en un archivo hay que teclear la orden EDIT seguida seguid a de la ruta y el nombre del archivo en el que se almacenarán esas órdenes. SQL> EDIT RUTA\NOMBRE_ARCHIVO Con EDIT se invoca el editor (por defecto, el bloc de notas: NOTEPAD.EXE) Si no existe el archivo, archivo, nos preguntará pregun tará si queremos crearlo y pulsamos SI.

Órdenes para generar informes Orden

Función

REMARK

Incluir comentarios

SET HEADSEP

Dividir un tí título en do dos o más líneas

TTITLE

Título superior de cada página

BTITLE

Título inferior de cada página

COLUMN

Dar formato al campo (columna )

BREAK ON

Ruptura de control, calcular totales y subtotales

COMPUTE

Cálculos con grupos de registros (filas)

SET LINESIZE

Número máximo de caracteres por línea

SET PAGESIZE

Número máximo de líneas por página

SET NEWPAGE

Número de líneas en blanco entre páginas

SPOOL

Guarda la salida del informe en un archivo

Órdenes para generar informes   

Orden REMARK (REM) Permite añadir comentarios. Se puede colocar en cualquier parte del listado. REM Nombre:

LISTADO.SQL

REM Descripción:

Informe de ventas

Órdenes para generar informes 







Orden SET. Configuración de las variables del entorno SQL*Plus Permite consultar y cambiar el valor de las variables del entorno SQL*Plus. Para dar valor a las variables se utiliza SET: SET variable valor Para visualizar su valor utilizamos SHOW: SHOW variable




Orden SET. Configuración de las variables del entorno SQL*Plus En el listado se usan las variables HEADSEP, LINESIZE, PAGESIZE y NEWPAGE. 



SET HEADSEP |. El símbolo que se coloca a la

derecha de SET HEADSEP es el carácter que se usará para dividir un título en dos o más líneas. SET LINESIZE. Con esta orden se define el número máximo de caracteres que pueden aparecer en una línea. Por ejemplo, definir 50 caracteres por línea: SET LINESIZE

50


Orden SET. Configuración de las variables del entorno SQL*Plus 

SET PAGESIZE. Con esta orden se define el número

de líneas por cada página, incluyendo TTITLE y BTITLE, las cabeceras de los campos y cualquier línea en blanco. Generalmente este número es 66. Por ejemplo, definir 50 líneas por página: SET PAGESIZE

50



SET NEWPAGE. Esta orden imprime líneas en

blanco antes de la línea superior de cada página del informe, donde está la fecha y el número de página. 

Por ejemplo, definir un informe que comience con 10 líneas en blanco seguidas de 50 líneas de información: SET PAGESIZE

60

SET NEWPAGE

10



SET TERMOUT OFF y SET TERMOUT ON.

Cuando se ejecuta el listado con la orden START, éste aparece en pantalla. 



Con SET TERMOUT OFF se evita que los datos aparezcan en la pantalla. Se suele usar antes de la orden SPOOL. Con SET TERMOUT ON los datos aparecerán en pantalla. Esta opción se suele utilizar después de la orden SPOOL.



SET SQLCASE UPPER. Esta orden indica a

SQL*Plus que convierta el texto introducido por teclado (con la orden ACCEPT) a mayúsculas. 

SET PAUSE ON. Permite controlar el desplazamiento

entre pantallas pulsando la tecla Intro. 

Se puede mostrar un texto: SET PAUSE ‘texto’.



Se desactiva mediante: SET PAUSE OFF.








Órdenes TTILTE y BTITLE. Encabezados y pies de página Con estas órdenes podemos títulos en la parte superior e inferior de cada página del informe. Por defecto, el título aparece centrado, teniendo en cuenta el tamaño de la línea definido con LINESIZE. Podemos cambiar la alineación del título mediante las cláusulas LEFT, RIGHT y CENTER. TTITLE CENTER „Listado BTITLE RIGHT

empleados‟

„Por departamento‟

Órdenes para generar informes  



Órdenes TTILTE y BTITLE Para que aparezcan una o dos líneas en blanco después del título se usa la orden SKIP n+1, donde n es el número de líneas que se van a saltar. Por ejemplo, para saltar una línea después de escribir el título: TTITLE CENTER „Listado SKIP

2

empleados‟


Órdenes TTILTE y BTITLE Cuando se obtiene un informe aparece en la primera línea del listado la fecha y el nº de página, si se usa SKIP, esta primera línea no aparecerá.


Órdenes TTILTE y BTITLE Para que los títulos no vuelvan a aparecer al realizar una consulta, se deben anular de esta forma: SQL> TTITLE OFF SQL>



BTITLE OFF

Para que vuelvan a aparecer, se activan así: SQL> TTITLE ON SQL>

BTITLE ON


Órdenes TTILTE y BTITLE Se puede incluir una serie de variables con información del sistema en las órdenes TTITLE y BTITLE. 

SQL.LNO: devuelve el nº de línea actual.



SQL.RELEASE: devuelve la versión de Oracle.



SQL.PNO: devuelve el nº de página actual.



SQL.USER: devuelve el nombre de usuario actual.


Órdenes TTILTE y BTITLE

Por ejemplo: SQL> TTITLE LEFT „APELLIDO EMPLEADOS‟CENTER „Usuario: „ SQL.USER SKIP 1

LEFT „Página:‟ FORMAT 999 SQL.PNO

Se creará el siguiente título superior: APELLIDO EMPLEADOS Usuario: SCOTT Página:1 



El guión (-) se usa para indicar que la orden TTITLE continúa en la siguiente línea.


Órdenes TTILTE y BTITLE Cualquier consulta que se realice a partir de ahora se mostrará visualizando previamente el título que se ha definido como cabecera del listado. SQL>

SELECT

APELLIDO

APELLIDO EMPLEADOS Página:1 APELLIDO ------------------CEREZO REY MUÑOZ

FROM EMPLEADO;

Usuario: SCOTT




Orden COLUMN. Formato de columnas Esta orden formatea el contenido de las columnas y sus cabeceras. COLUMN NOMBRE_CAMPO FORMATO Formatos: HEADING: Define la cabecera del campo. 

COLUMN 

VENTAS

HEADING

„Artículo|Vendido ‟

El texto que aparece a la derecha de HEADINGD es la nueva cabecera del campo que, además, usa el carácter separador “|” para indicar que la cabecera del campo aparecerá en dos líneas.


Orden COLUMN. Formato de columnas Formatos: FORMAT: Define el formato para el contenido del 

campo. COLUMN 



VENTAS

FORMAT

A15

La A indica que se da formato a una columna de tipo Alfanumérico, y el número que sigue a continuación representa la cantidad de caracteres. Si la longitud del campo VENTAS es mayor de 15, aparecerá dividida en dos líneas.


Orden COLUMN. Formato de columnas Formatos: FORMAT: 

COLUMN 



TOTAL

FORMAT 9G999D00

Define la columna numérica para espacio para 4 dígitos, 2 decimales, el punto de los miles y la coma de los decimales. Si la columna TOTAL tiene más de 4 dígitos en alguna fila, aparecerán caracteres #.


Orden COLUMN. Formato de columnas Formatos: Formatos para FORMAT 

















An Establece el ancho de columna en número de caracteres 9 Dígitos con supresión de ceros 0 Dígitos dejando los ceros $ Símbolo de dólar $ L Moneda local . ó D Posición del punto decimal , ó G Separador de miles | Inserta un salto de línea


Orden COLUMN. Formato de columnas Formatos: TRUNCATED: Trunca el contenido de un campo. 

COLUMN 



VENTAS

FORMAT

A15

TRUNCATED

Sólo mostrará los primeros 15 caracteres del campo VENTAS. Si la longitud del campo VENTAS es mayor de 15, los caracteres a partir de esa posición no aparecerán.


Orden COLUMN. Formato de columnas Formatos: CLEAR: Borra el formato dado a un campo. 

COLUMN 

VENTAS

CLEAR

Si se desean borrar todos los formatos definidos para los campos usaremos la orden CLEAR COLUMNS.






Orden BREAK ON. Ruptura de control Esta orden produce una ruptura de control por la columna que se desee. Esta sentencia hace rupturas en el SELECT de una tabla según los valores de un campo de dicha tabla. Es muy útil en el caso de querer dividir en pequeñas tablas una tabla según el valor que tenga una columna.


Orden BREAK ON. Ruptura de control BREAK ON







DESCRIPCION

SKIP

2

En este ejemplo el campo DESCRIPCION sólo se mostrará una vez, en lugar de aparecer fila a fila. Cuando cambie la descripción, se hará un salto de 2 líneas, especificado en la cláusula SKIP. La orden BREAK ON necesita la cláusula ORDER BY de la sentencia SELECT para que la salida del listado sea la deseada.






Orden BREAK ON. Ruptura de control Si añadimos la cláusula DUPLICATE aparecerá el campo DESCRIPCION en todas las líneas. BREAK ON DESCRIPCION DUPLICATE SKIP 2 Todas las rupturas de control definidas se pueden eliminar con CLEAR BREAKS. Sólo puede haber una orden BREAK activa, por lo que si se especifican varias rupturas, han de especificarse todas en una sola orden.


Orden COMPUTE. Cálculos Con esta orden se hacen cálculos con grupos de filas seleccionados. COMPUTE OF ON

{SUM,AVG,COUNT,MAX,MIN,STD,VAR,NUM} {expresión|campo} {expresión|campo}


Orden COMPUTE. Cálculos 

Opciones: SUM

Calcula la suma

AVG

Calcula la media

COUNT

Cuenta las filas no nulas

MAX

Calcula el valor máximo

MIN

Calcula el valor mínimo

STD

Calcula la desviación estándar

VAR

Calcula la varianza

NUM

Cuenta todas la filas (incluidas las no nulas)


Orden COMPUTE. Cálculos 



{expresión|campo} : campo o expresión sobre la que se realizan los cálculos. ON {expresión|row|page|report} : indica sobre qué punto de BREAK se introduce el cálculo: OF









expresión: cuando cambia el valor de expresión. row: en cada cambio de registro (fila). page: en cada final de página. report: al final de un informe o consulta.




Orden COMPUTE. Cálculos Los totales que calcula COMPUTE son los especificados en la sección indicada por BREAK ON. No se puede usar COMPUTE sin BREAK ON. BREAK ON COMPUTE COMPUTE

DESCRIPCION SKIP 2 SUM OF VENTAS ON DESCRIPCION SUM OF IMPORTE ON DESCRIPCION


Orden COMPUTE. Cálculos Para saber en cualquier momento cuales son los BREAK y COMPUTE activos, basta teclear: SQL> BREAK break on report nodup on ventas nodup SQL>

COMPUTE

COMPUTE SUM LABEL „sum‟ OF IMPORTE ON DESCRIPCION COMPUTE SUM LABEL „sum‟ OF VENTAS ON REPORT COMPUTE SUM LABEL „sum‟ OF VENTAS ON DESCRIPCION


Orden COMPUTE. Cálculos Se puede cambiar la palabra ‘sum’ que aparece en el informe indicando las sumas, con la cláusula LABEL: COMPUTE SUM LABEL „TOTAL VENTAS‟ OF VENTAS ON REPORT



Para eliminar los puntos de ruptura, los cálculos o las definiciones de campos se usa CLEAR:

SQL>CLEAR BREAKS breaks limpiado

SQL>CLEAR COMPUTES computes limpiado

SQL>CLEAR COLUMNS columns limpiado


Orden SPOOL Esta orden posibilita guardar en un archivo toda la salida que SQL*Plus vaya produciendo por pantalla. SQL> SPOOL RUTA\NOMBRE_ARCHIVO



Para que pare usaremos la orden SPOOL OFF. SPOOL C:\reports\listado.lst SELECT * FROM EMPLEADO; SPOOL OFF




Definición y uso de variables. DEFINE Y ACCEPT Con SQL*Plus podemos definir variables con la orden DEFINE, para usarlas después en un informe. SQL>

DEFINE

CABECERA=“VENTAS TOTALES”

SQL>

DEFINE

RAYA=“==============”

SQL>

DEFINE

PIE=“Solo Mayo 2009”

Para ver la lista de variables definidas se teclea DEFINE desde el indicador SQL*Plus: SQL>

DEFINE


Definición y uso de variables. DEFINE Y ACCEPT



Podemos usar las variables definidas en el listado:



TTITLE

CENTER

BTITLE

LEFT

CABECERA SKIP

1 CENTER

RAYA

PIE

Para eliminar las variables definidas usaremos la orden UNDEFINE seguida del nombre de la variable. SQL>

UNDEFINE

CABECERA




Definición y uso de variables. DEFINE Y ACCEPT También es posible diseñar un listado que pida introducir cierta información por teclado, como por ejemplo el nº de departamento que se va a listar. La introducción de valores se realiza con ACCEPT.

ACCEPT

variable [TIPO DE DATO] [FORMAT formato] [PROMPT „texto‟|NOPROMPT]

[HIDE]


Definición y uso de variables. DEFINE Y ACCEPT 





variable es el nombre de la variable que recibe la información tecleada. TIPO DE DATO determina el tipo de dato de la variable, por defecto es char. Si se teclea un dato que no corresponde con el tipo especificado se produce un mensaje de error y vuelve a pedir la entrada. FORMAT formato define el formato para el contenido de la variable.


Definición y uso de variables. DEFINE Y ACCEPT 

 

PROMPT „texto‟

muestra el texto al usuario antes de

teclear los datos. NOPROMPT salta una línea y espera la entrada. HIDE es útil para contraseñas, al escribir el dato se visualizarán asteriscos.


Definición y uso de variables. DEFINE Y ACCEPT

PROMPT ACCEPT

„Proporcione los siguientes datos‟

nom_usuario char DEFAULT „invitado‟

PROMPT „Usuario:‟ ACCEPT

pwd char PROMPT „contraseña:' HIDE

num_emp number FORMAT '99999' PROMPT 'Número de empleado:'

ACCEPT

fecha_inf date FORMAT 'yyyy/mm/dd' PROMPT 'Fecha de informe:'

ACCEPT




Definición y uso de variables. DEFINE Y ACCEPT Al usar variables en una consulta SELECT, el nombre de la variable debe llevar un ampersand (&) delante. Si la variable se define como CHAR debe ir encerrada entre comillas simples. SQL> SELECT * FROM EMPLEADO WHERE N_EMPLEADO = &num_emp AND USUARIO = „&nom_usuario ‟;

Informes SQL Plus

Recommend Documents