Oracle Live

01/07/2016

SQL – SQLPLUS Utilidades

Filed under: Queries — mogukiller @ 10:14 am
Tags: ,
-- ACCEPT
-- ATAJOS
-- BREAK
-- CHANGE
-- COLUMN
-- COMPUTE
-- DEFINE
-- EDIT
-- GET
-- PROMPT
-- SAVE
-- SET
-- SETs UTILES para el spool
-- SHOW PARAMETERS
-- SHOW ERRORS
-- TIMING
-- VARIABLE
-- GLOGIN FILE
-- EJEMPLOS:
	-- Pasar parametros a scripts
	-- Creamos un script que pida los parametros
	-- Esperamos que pulse intro

----------------------
-- ACCEPT
----------------------
ACCEPT variable [NUMBER | CHAR | DATE | BINARY_FLOAT | BINARY_DOUBLE] [FORMAT format] [DEFAULT] default] [PROMPT text|NOPROMPT] [HIDE]

PARAMETROS:

-- variable: indica el nombre de la variable donde almacenamos el valor.
-- [NUMBER | CHAR | DATE | BINARY_FLOAT | BINARY_DOUBLE]: Hace un cast de la variable al tipo de dato que le digamos.
-- FORMAT format: Indicamos el formato que debe de tener la entrada. Sino lo cumple devolvemos un error.
-- [DEFAULT] default]: Valor por defecto en el caso de que no se inserte valor.
-- [PROMPT text|NOPROMPT]: Texto en pantalla antes de que se inserte la variable.
-- [HIDE]: No se visualiza lo introducido por el usuario.

Ejemplos:

ACCEPT pswd CHAR PROMPT 'Password:  ' HIDE

ACCEPT salario NUMBER FORMAT '999.99' DEFAULT '000.0' -
PROMPT 'Cuanto ganas:  '

ACCEPT fecha DATE FORMAT 'dd/mm/yyyy' DEFAULT '01/01/2003'-
PROMPT 'Fecha cumple:  '

ACCEPT lastname CHAR FORMAT 'A20' -
PROMPT 'Enter employee lastname:  '

----------------------
-- COLUMN
----------------------
COL[UMN] [{column | expr} [option ...]]

-- {column | expr}: Es el nombre de la columna de la SELECT o exactamente la expresion que utlizamos a+b

-- [option ...]:

	CLEAR: 							--> Borra las opciones de configuracion sobre una columna. CLEAR COLUMNS borra todo lo que este definido
	FOR[MAT] format
	HEA[DING] text: 						--> Le asigna una cabecera a la columna indicada. Ponemos | para que nos lo ponga en dos lineas.
	JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]}:	-- > Define la alineacion de la cabecera.
	LIKE {expr | alias}: 					-- > Copia los atributos de otra columna
	NEW_V[ALUE] variable
	NOPRI[NT] | PRI[NT] : 					-- > Controla si se visualiza o no una columna.
	WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]: 	-- > Indica como se han de tratar los registros que sobrepasan el tamaño de la columna. WOR en el caso de textos funciona mejor.
	COLUMN columna: 						--> Nos muestra los parametros configurados para esa columna.

Ejemplos:

COL TABLE_NAME FORMAT A30 HEADING Tabla JUSTIFY CENTER
COL NUM_ROWS FORMAT 99999999 HEADING Actual JUSTIFY CENTER
COL TABLESPACE_NAME LIKE TABLE_NAME HEADIN 'Tablespaces'
SELECT TABLE_NAME, TABLESPACE_NAME, NUM_ROWS FROM USER_TABLES WHERE ROWNUM<10;
CLEAR COLUMNS
/*
            Tabla                       Tablespaces            Actual
------------------------------ ------------------------------ ---------
CUSTOMER                       DATOS                              30000
DISTRICT                       DATOS                                 10
HISTORY                        DATOS                              30000
ITEM                           DATOS                             100000
WAREHOUSE                      DATOS                                  1
STOCK                          DATOS                             100000
NEW_ORDER                                                          9140
ORDERS                         DATOS                              32652
ORDER_LINE                                                       326190
*/

----------------------
-- BREAK
----------------------

BREAK ON columna 			-- > Suprime todos los valores repetidos de una columna.
BREAK ON columna SKIP n 	-- > Añade n lineas en blanco despues de cada grupo.
BREAK ON columna SKIP page 	-- > Muy util junto con funciones analiticas y SET PAUSE ON
BREAK						-- > Vemos todas las definiciones de break.
BREAK ON REPORT				-- > Hay que utilizarlo junto con COMPUTE para que ponga el resultado al final
CLEAR BREAKS				-- > Borra todas las definiciones de break.


Ejemplo:

BREAK ON TABLE_NAME SKIP 1;
SELECT TABLE_NAME, INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME LIKE '%POGE_RECARGAS%' ORDER BY TABLE_NAME;

          Tabla                    Indice
------------------------- -------------------------
POGE_RECARGAS             POGE_RECARGAS_I01
                          POGE_RECARGAS_I02
                          POGE_RECARGAS_I03
                          POGE_RECARGAS_PK

POGE_RECARGASRASC         POGE_RECARGASRASC_PK
                          POGE_RECARGASRASC_UK

POGE_RECARGASRASC_BCK     POGE_RECARGASRASC_PK_BCK
                          POGE_RECARGASRASC_UK_BCK
					  		
----------------------
-- COMPUTE
----------------------

COMP[UTE] [function [LAB[EL] text] ... OF {expr | column | alias} ... ON {expr | column | alias | REPORT | ROW} ...]

Se utiliza en convinacion con BREAK para sacar informes.

Parametros:

-- function: Las diferentes funciones que podemos aplicar sobre las columnas.
	-- AVG: Promedio de los valores no nulos.
	-- COUNT: Cuenta los valores no nulos.
	-- NUMBER: Cuenta todas las filas.
	-- MIN | MAX
	-- SUM: Suma todos los valores.
--[LAB[EL] text]: Indica el texto que se va a visualizar. Sino se indica nada aparecer el valor de la funcion.

Ejemplos:
 
BREAK ON TABLE_NAME SKIP 1;
COMPUTE MAX LABEL 'Actual' OF NUM_ROWS  ON TABLE_NAME
SELECT TABLE_NAME, INDEX_NAME, NUM_ROWS FROM USER_INDEXES WHERE TABLE_NAME LIKE '%POGE_RECARGAS%' ORDER BY TABLE_NAME;

/*
            Tabla              INDEX_NAME                      Actual
------------------------------ ------------------------------ ---------
POGE_RECARGAS                  POGE_RECARGAS_I01                     17
                               POGE_RECARGAS_I02                     17
                               POGE_RECARGAS_I03                     17
                               POGE_RECARGAS_PK                      17
******************************                                ---------
Actual                                                               17

POGE_RECARGASRASC              POGE_RECARGASRASC_PK                   1
                               POGE_RECARGASRASC_UK                   1
******************************                                ---------
Actual                                                                1
*/

Ejemplo 2:

Para no mostrar el label de la funcion

COLUMN TABLA NOPRINT;
COMPUTE MAX LABEL 'Actual' OF NUM_ROWS  ON TABLA;
BREAK ON TABLA SKIP 1;

SELECT TABLE_NAME TABLA,TABLE_NAME, INDEX_NAME, NUM_ROWS FROM USER_INDEXES WHERE TABLE_NAME LIKE '%POGE_RECARGAS%' ORDER BY TABLE_NAME;
/*

            Tabla              INDEX_NAME                      Actual
------------------------------ ------------------------------ ---------
POGE_RECARGAS                  POGE_RECARGAS_I01                     17
POGE_RECARGAS                  POGE_RECARGAS_I02                     17
POGE_RECARGAS                  POGE_RECARGAS_I03                     17
POGE_RECARGAS                  POGE_RECARGAS_PK                      17
                                                              ---------
                                                                     17

POGE_RECARGASRASC              POGE_RECARGASRASC_PK                   1
POGE_RECARGASRASC              POGE_RECARGASRASC_UK                   1
                                                              ---------
                                                                      1
*/

Ejemplo 3:

BREAK ON REPORT
COL CABECERA HEADING '	' FORMAT A10
COL TABLE_NAME JUSTIFY LEFT FORMAT A20 
COMPUTE COUNT LABEL Total OF TABLE_NAME ON REPORT
SELECT ' ' AS CABECERA, TABLE_NAME FROM USER_TABLES;

/*
                         TABLE_NAME
---------- ------------------------------
           NEW_ORDER
           ORDER_LINE
           ORDERS
           STOCK
           WAREHOUSE
           ITEM
           HISTORY
           DISTRICT
           CUSTOMER
           ------------------------------
Total                                   9
*/

Ejemplo 4:

BREAK ON REPORT
COMPUTE SUM LABEL Total OF TOTAL_SIZE TOTAL_DBFS ON REPORT
SELECT TABLESPACE_NAME, ROUND(SUM(BYTES/1024/1024)) TOTAL_SIZE, COUNT(*) TOTAL_DBFS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;
/*

TABLESPACE_NAME                TOTAL_SIZE TOTAL_DBFS
------------------------------ ---------- ----------
DATOS                                1024          1
SYSAUX                                550          1
UNDOTBS1                              420          1
USERS                                 238          1
SYSTEM                                700          1
INDICES                               500          1
                               ---------- ----------
Total                                3432          6

*/

----------------------
-- CHANGE
----------------------

C[HANGE] sepchar old [sepchar [new [sepchar]]]

Cambia la primera aparicion en la linea activa del buffer de la cadena buscada y la cambia por la nueva cadena. La linea activa del buffer se indica con *, si queremos modificar la linea activa introducimos el numero de la linea.

A la cadena buscada podemos ponerle como prefijo|sufijo ..., y me cambiara todo lo que haya antes|despues por la nueva cadena

QUERY:
	SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES
	WHERE TABLE_NAME IN ('PPGA_RECARGAS','PPGA_CAMBEST');

	Ejemplo 1: Vemos como se ha cargado en el buffer y cual es la linea activa. Cambiamos la linia activa.

12:51:08 PEOPGE SQL>LIST
  1     SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES
  2*    WHERE TABLE_NAME IN ('PPGA_RECARGAS','PPGA_CAMBEST')
  
12:51:11 PEOPGE SQL>1
  1*    SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES
  
	Ejemplo 2: Susititucion simple
	
	12:56:01 PEOPGE SQL>c /PPGA/POGE/
  2*    WHERE TABLE_NAME IN ('POGE_RECARGAS')
  
	Ejemplo 3: Sustituimos todo lo que haya entre los parantesis
  
  12:55:17 PEOPGE SQL>c /(...)/('PPGA_RECARGAS')/
  2*    WHERE TABLE_NAME IN ('PPGA_RECARGAS')


change/<valor>/		--> Quita todas las apariciones de valor

----------------------
-- EDIT
----------------------

EDIT: Permite editar lo que haya en el buffer o en un fichero.

SET _EDITOR=vi

-- Editamos el fichero
> EDIT [fichero]
-- Volvemos a ejecutar el buffer o el fichero con @
> /

; 				-- Lista todas las filas del buffer
clear buffer 	-- Borrar todas las lines del buffer
n				-- Carga la linea que indicamos
append text		-- Añade a la linea cargada el texto

----------------------
-- VARIABLE
----------------------

VARIABLE mi_variable NUMBER

EXEC :mi_variable := valor
PRINT mi_variable

----------------------
-- GET
----------------------

GET mi_fichero

Carga el fichero en el buffer. La idea es cargar el fichero en el buffer, editarlo y ejecutarlo.

----------------------
-- PROMPT
----------------------

PROMPT text

Muestra texto al usuario

----------------------
-- SAVE
----------------------

SAVE fichero [CRE[ATE] | REP[LACE] | APP[END]]

te guarda lo que haya en el buffer en un fichero.

--------------------------------
-- Pasar parametros a scripts
--------------------------------

SELECT NAME, VALOR FROM TABLA WHERE CLAVE='&1';
SAVE mi_script.sql

@mi_script.sql mi_clave

----------------------------------------------------------------
-- Creamos un script que pida los parametros
----------------------------------------------------------------

INPUT
PROMPT Enter a valid employee ID
PROMPT For Example 145, 206
ACCEPT ENUMBER NUMBER PROMPT 'Employee ID. :'
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE EMPLOYEE_ID=&ENUMBER
.
SAVE mi_script

Esperamos que pulse intro

PROMPT Before continuing, make sure you have your account card.
PAUSE Press RETURN to continue.

----------------------------------------------------------------
-- SHOW ERRORS
----------------------------------------------------------------

SHOW ERR[ORS] [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER
| VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS} [schema.]name]

Podemos ver los errores que nos ha dado al compilar un paquete.

SHOW PARAMETERS

Te muestra todos los parametros configurados con set

show all

Te muestra el valor de un parametro

show parameter 

----------------------------------------------------------------
-- ATAJOS
----------------------------------------------------------------

CTL+D 	 	-- > Sales de la sesion
LIST | ; | L 	-- > Te listas en contenido del buffer

----------------------------------------------------------------
-- SET
----------------------------------------------------------------

set pause ON|OFF 		-- > Habilita la pausa despues para queries largas.
set underline '-'		-- > El caracter que se va a mostrar debajo de las columnas
set veriffy ON|OFF		-- > Evita que salga en old new en una sustitucion.
set LONG n				-- > Configura los caracteres a imprimir de un campo LONG.
set NUMF ""				-- > Configura el formato de los campos numericos.

SET AUTOTRACE {ON | OFF | TRACE} [EXP][STAT] -- > Muestra las estadisticas y el explain plan de la query que estamos lanzando
	'Opciones: 
		TRACE: 	No saca los datos de la query.
		EXP: 	Solo saca el plan de ejecucion de la query.
		STAT: 	Estadisticas de ejecucion.
	'
set pause ON | OFF		--> Combinado con set pages n pausa la salida cada n lineas.
set serveroutput ON | OFF	--> Habilita la salida de los PLs
set sqlprompt text		--> Configura el prompt del sqlplus
	'text:
		_CONNECT_IDENTIFIED
		_USER
		_DATE
	'
set timing ON | OFF		--> Habilita | Deshabilita el Elapset time de las queries.
----------------------------------------------------------------
-- DEFINE
----------------------------------------------------------------

- Para poder utilizar variables de sustitucion en vez de ACCEPT podemos utilizar DEFINE

DEFINE variable = valor
DEFINE 	--> Muestra todas las variables de definidas
UNDEFINE variable

SELECT mi_columna FROM mi_tabla WHERE mi_columna = &valor;


----------------------------------------------------------------
-- SETs UTILES para el spool
----------------------------------------------------------------

SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0		--> Numero de filas por pagina. 0 suprime cualquier cabecera.
SET ECHO OFF		--> No visualiza la SQL que esta ejecutando.
SET FEEDBACK OFF	--> Suprime mensajes de Table Created.
SET VERIFY OFF		--> Informa de la sustitucion de variables.
SET HEADING OFF		--> Suprime el nombre de las columnas.
SET MARKUP HTML OFF SPOOL OFF


----------------------------------------------------------------
-- TIMING
----------------------------------------------------------------

Los utilizamos para crear relojes de tiempo

TIMING START reloj1		-->	Crea un reloj y lo inicia
TIMING SHOW				-->	Visualiza el valor del reloj
TIMING STOP				-->	Para el timer mas antiguo

----------------------------------------------------------------
-- GLOGIN FILE
----------------------------------------------------------------

PATH: $ORACLE_HOME/sqlplus/admin/sqlfile.sql

Sirve para cargar variables de entorno cuando iniciemos SQLPLUS

Dejar un comentario »

Aún no hay comentarios.

RSS feed for comments on this post. TrackBack URI

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

Blog de WordPress.com.

A %d blogueros les gusta esto: