Recordatorio y ejemplos del lenguage SQL basado en el SQLPlus de Oracle.

Select con operaciones aritméticas:

select first_name, salary, 12*(salary) from employees;

Select con concatenaciones:

select 'Nombre : ' ||first_name || 'Apellido :' ||last_name, salary, 'Salario Anual :'|| 12*(salary) from employees;

Select ordenado:

select FIRST_NAME, LAST_NAME, EMPLOYEE_ID, SALARY 
from employees where SALARY > 100 
order by LAST_NAME, FIRST_NAME;

SELECT [DISTINCT] columna
FROM tabla
WHERE condicion
ORDER BY  c coluna [ASC | DESC]

select LOWER(FIRST_NAME), UPPER(FIRST_NAME), INITCAP(FIRST_NAME), SUBSTR(FIRST_NAME, 5) 
from employees;

select LENGTH(FIRST_NAME), INSTR(FIRST_NAME,'a'), LPAD(FIRST_NAME,10,'.·.') 
from employees;

select first_name, last_name, concat(first_name, last_name), length(first_name), instr(first_name, 'A') 
from employees 
where substr(first_name, -1,1) = 'n';

select SALARY, ROUND(SALARY), ROUND(23.873,88),ROUND(23.873,18)
from employees;

select SALARY, ROUND(SALARY), ROUND(23.873,88),ROUND(23.873,18)  
from employees;

select  FIRST_NAME , SALARY,  COMMISSION_PCT, MOD(SALARY,  COMMISSION_PCT)
from employees;

select  FIRST_NAME , SALARY,  COMMISSION_PCT, MOD(SALARY,  NVL(COMMISSION_PCT,0))
from employees;

select  FIRST_NAME ,  HIRE_DATE , ( SYSDATE - HIRE_DATE) / 7 as SEMANAS, TRUNC( ( SYSDATE - HIRE_DATE) / 7) as SEMANAS_COMPLETAS
from employees
where  DEPARTMENT_ID  = 50;

select  FIRST_NAME ,  HIRE_DATE , 
MONTHS_BETWEEN( SYSDATE, HIRE_DATE), 
ADD_MONTHS( HIRE_DATE, 6)  as REVISION_DEL_SALARIO,
NEXT_DAY( HIRE_DATE, 'FRIDAY'),
LAST_DAY(HIRE_DATE)
from employees
where  DEPARTMENT_ID  = 50;

select  FIRST_NAME ,  HIRE_DATE , SYSDATE,
ROUND(SYSDATE, 'MONTH'), 
ROUND(SYSDATE, 'YEAR'), 
TRUNC(SYSDATE, 'MONTH'), 
TRUNC(SYSDATE, 'YEAR'),
ROUND(HIRE_DATE, 'MONTH'), 
ROUND(HIRE_DATE, 'YEAR'), 
TRUNC(HIRE_DATE, 'MONTH'), 
TRUNC(HIRE_DATE, 'YEAR')
from employees
where  DEPARTMENT_ID  = 50;

select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Current Time"
from dual;

select sysdate from sys.dual;

Conversión de una cadena o variable en una fecha

Algunos ejemplos:

to_date('10-10-04','MM-DD-YY')

to_date('jan 2009','MON YYYY')

to_date('2007/04/28','YYYY/MM/DD')

to_date('062004','mmyyyy')

to_date('12312000','MMDDYYYY')

to_date('10-31-2008 12:15','MM-DD-YYYY HH:MI')

to_date('2007,094,00:00:00' , 'YYYY,DDD,HH24:MI:SS')

to_date('15-apr-2007 06:00:01','dd-mon-yyyy hh24:mi:ss')

to_date(substr( collection_started,1,12),'DD-MON-YY HH24')

to_date('2008/11/14 21', 'yyyy/mm/dd hh24')

TO_DATE(MI_VARIABLE, 'yyyy-mm-dd/hh24:mi:ss'))*24*60)

El formato es to_date(cadena, 'especificación de formato')

Al hacer una selección de más de una tabla lo que se hace es el productio cartesiano de las tablas. Es decir, se convinan todos los registros con todos los registros.

select FIRST_NAME  ,  DEPARTMENT_NAME   
from EMPLOYEES, DEPARTMENTS;

Pero normalmente esto no es lo que nos interesa Tipos de Joins

• Equijoins : Está en las dos tablas

select EMPLOYEES.EMPLOYEE_ID, EMPLOYEES.FIRST_NAME,  DEPARTMENT_NAME   
from EMPLOYEES, DEPARTMENTS
where EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID;

select EMPLOYEES.EMPLOYEE_ID, EMPLOYEES.FIRST_NAME,  DEPARTMENT_NAME, LOCATIONS.STREET_ADDRESS || ', ' || LOCATIONS.CITY 
from EMPLOYEES, DEPARTMENTS, LOCATIONS
where EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
and DEPARTMENTS.LOCATION_ID = LOCATIONS.LOCATION_ID; 

• Non-Equijoins : Se puede hacer una compararación entre varias tablas siempre que se ajuste el tipo de datos. Fijate en la siguiente consulta. Se busca una coincidencia entre un valor y un rango de minimo y maximo. Osea, cuando el valor a esté entre el valor b y el valor c

select A.FIRST_NAME as Nombre, A.SALARY, B.JOB_TITLE
from EMPLOYEES  A, JOBS  B
where A.SALARY between B.MIN_SALARY and B.MAX_SALARY;   

• Outer Joins : Con un join recuperamos las filas que cumplen la condiciones establecidas. Pero quizás nos puede interesar recuperar TODOS los registros. Eso se hace colocando un simbolo (+) en el lado que quieras rellenar con nulos para que la otra tabla salga completa.

select EMPLOYEES.EMPLOYEE_ID ID, EMPLOYEES.FIRST_NAME Nombre,  DEPARTMENTS.DEPARTMENT_NAME   Departamento
from EMPLOYEES, DEPARTMENTS
where EMPLOYEES.DEPARTMENT_ID(+) = DEPARTMENTS.DEPARTMENT_ID;

• Self Joins : Un Join consigo mismo

select currante.FIRST_NAME || ' trabaja para ' ||  jefe.FIRST_NAME 
from EMPLOYEES  currante, EMPLOYEES jefe
where currante.MANAGER_ID = jefe.EMPLOYEE_ID;

• AVG : calcula el valormedio
• COUNT : cuenta el numero de valores
• MAX : busca el valor máximo
• MIN : busca el valor minimo
• STDDEV : busca la desviación estandard
• SUM : suma los valores
• VARIANCE : busca la variacion

SELECT columna, columna FUNCION_DE_GRUPO(columna)
FROM tabla
[ WHERE condicion ]
[ GROUP BY columna ]
[ ORDER BY columna] ;

Ejemplos:

select AVG(SALARY), MAX(SALARY), MIN(SALARY), SUM(SALARY)
from EMPLOYEES;

Ten en cuenta que al hacer agrupaciones no pues meter cualquier cosa. esta consulta te devolverá sólo una linea y se quejará si intentas meter algo mas .

Para contar el número de registros de una tabla se hace así:

select count(*) from table;

cont(*) es cualquier columna…

Para hacer agrupaciones se hace de esta manera:

select DEPARTMENT_ID , AVG(SALARY)
from EMPLOYEES
group by DEPARTMENT_ID
order by AVG(SALARY);

Se puede especificar tambien cuales grupos se muestran…

select  JOB_ID , SUM(SALARY) Gasto_del_departamento
from EMPLOYEES
group by JOB_ID
having SUM(SALARY)>60000
order by SUM(SALARY);

De esta manera se mostrarán sólo aquellos departamentos que la suma de su gasto sea mayor que 600000

Las subconsultas sirven para responder a preguntas del estilo ¿Que empleados tienen un salario mayor al de Pepe?

Estructura:

SELECT  colmna, columna
FROM  tabla
WHERE expresion del operador
                   (SELECT columna
                    FROM tabla);

Y ahora… vamos a responder a la pregunta:

select  FIRST_NAME , LAST_NAME
from EMPLOYEES
where SALARY > 
	(select SALARY 
		 from EMPLOYEES
		 where  EMPLOYEE_ID  = 200 );

Pero quienes son los que cobran menos de cada departamento. incluso menos que los del depatamento 20?

select  DEPARTMENT_ID, MIN(SALARY)
from EMPLOYEES
group by DEPARTMENT_ID
having MIN(SALARY) > 
	(select MIN(SALARY) 
		 from EMPLOYEES
		 where  DEPARTMENT_ID  = 20 );

Y… puedes hacer cualquier cosa. Como por ejemplo. Saber quien cobra mas que nadie… select FIRST_NAME , LAST_NAME, JOB_TITLE

from EMPLOYEES, JOBS
where EMPLOYEES.JOB_ID = JOBS.JOB_ID
and   SALARY > ALL
	(select AVG(SALARY)
		 from EMPLOYEES
		 group by DEPARTMENT_ID );

aunque para eso no hace falta una consulta… el jefe!

select  A.FIRST_NAME ||', '|| A.LAST_NAME, A.SALARY, A.DEPARTMENT_ID,  B.MEDIA 
from EMPLOYEES A, (SELECT DEPARTMENT_ID, AVG(SALARY) MEDIA 
		FROM EMPLOYEES
		GROUP BY DEPARTMENT_ID) B
where A.DEPARTMENT_ID = B.DEPARTMENT_ID
and   A.SALARY > B.MEDIA;

… añadir nuevos registros a la tabla. Estructura

INSERT INTO tabla [ columna, columna, columna]
VALUES (valor, valor, valor)

ejemplo:

INSERT INTO EMPLOYEES ( EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, EMAIL,COMMISSION_PCT, DEPARTMENT_ID )
               VALUES (        1196,      'Pepe',   'Ruiz', 'SH_CLERK',    124,   SYSDATE,   2000, 'pepe@a.es',    NULL,            50 );

Insert mediante subconsultas. Estructura

INSERT INTO tabla [ columna, columna, columna]
          SELECT columna, columna clumna;

create table jefes
(id number(6),
nombre varchar2(25),
cobra number(6),
cumpleaños date);

INSERT INTO jefes ( id, nombre, cobra, cumpleaños )
             select EMPLOYEE_ID , FIRST_NAME, SALARY, HIRE_DATE
             from employees
             where upper(job_id) like '%MAN%';

/

y tendremos…

SQL> @1

Table created.
12 rows created.
12 rows created.

Estructura

DELETE FROM tabla
[ WHERE condicion]]

ejemplo:

 delete from jefes
 where upper(job_id) like '%MAN%';

Vamos a librarnos de los jefes que cobran demasiado…

DELETE FROM jefes
WHERE cobra > 3500;

y si omitimos la clausula where borramos todos los registros de la tabla

DELETE FROM jefes;

Tras realizar operaciones de modificación de datos se deben confirmar los cambios mediante el comando commit. Atención :¡Se realiza un commit automático al realizar la siguente orden de modificación de datos!

commit

En caso de querer anular los cambios hechos en la base de datos se puede volver a un estado anterior mediante el comando rollback

rollback

sql>create table NOMBRE_DE_TABLA(
columna tipo_de_dato expresión,
columna tipo_de_dato expresión,

• varchar2(tamaño): Carácteres de longitud variable.
• char(tamaño): Carácteres de longitud fija.
• number(precisión, escala): Datos numéricos con una precisión numérica entre 1 y 38 decimales y una escala.
• date: Fecha y hora.
• long: Carácteres de longitud variable, hasta un máximo de 2GB.
• clow: Carácteres “single-byte” hasta 4 GB.
• raw y long raw: Datos binarios.
• blob: Datos binarios hasta 4 GB.
• bfile: Datos binarios hasta 4 GB almacenados en un fichero externo.

create table dept
 (depno number(2),
  dname varchar2(15),
  loc varchar2(10))

create table nombre_de_tabla as
select columna, columna, columna
from tabla
where condicion

Sirve para modificar datos dentro de una tabla

UPDATE tabla 
SET  coluna = valor [, columna = valor, ...]
[ WHERE condicion ]

ejemplo

Vamos a bajarle el sueldo a los jefes que cobran demasiado:

UPDATE jefes 
SET  cobra = 0
WHERE cobra > 4000;

Update con subconsulta: Vamos a dejarselo un poquito mejor….

UPDATE jefes 
SET  cobra = (select salary from employees WHERE employee_id= 186)
WHERE cobra = 0;

• Para añadir una nueva columna en una tabla
• Para modificar una columna que ya existe
• Para dar un valor por defecto a una nueva columna

ALTER TABLE tabla
ADD (columna tipo_de_datos [ DEFAULT expresion ]
    [, columna tipo_de_datos [ DEFAULT expresion ], ... ]

o bien

ALTER TABLE tabla
MODIFY (columna tipo_de_datos [ DEFAULT expresion ]
    [, columna tipo_de_datos [ DEFAULT expresion ], ... ]

o bien

ALTER TABLE tabla
DROP COLUMN columna 

ejemplo:

ALTER TABLE jefes
ADD malevolo varchar2(5) DEFAULT 'no';

o

ALTER TABLE jefes
MODIFY malevolo varchar2(10) DEFAULT 'no';

o

ALTER TABLE jefes
DROP COLUMN malevolo;

Borrar una tabla…

DROP TABLE tabla;

ejemplo:

DROP TABLE jefes;

Borrar todos los registros de una tabla…

TRUNCATE TABLE tabla;

ejemplo:

TRUNCATE TABLE jefes;

Al crear una tabla nos puede interesar crear cierto tipo de restricciones. Como por ejemplo que los datos no sean únicos, que los datos deben ser iguales a los que hay en otra tabla, etc. Estas son las restricciones posibles en Oracle ( Para MySql mira en su página ):

• NOT NULL Especifica que una columna no puede contener valores nulos.
• UNIQUE Especifica que una columna o combinación de ellas no pueden tener valores repetidos.
• PRIMARY KEY Identifica unívocamente a cada fila de la tabla.
• FOREIGN KEY Establece y garantiza una una relación de clave externa entre la columna y una columna de una tabla exteriror. Es decir, establece que los vallores de una columna deven exixtir previamente en otra de otra tabla.
• CHECK Especifica una condición que debe ser verdadera

columna CONSTRAINT restriccion 

Ejemplo:

CREATE TABLE  jefes(
id number(4),
nombre varchar2(10) NOT NULL,
apellido varchar2(10) NOT NULL,
cobra number(6) );

Ejemplo:

CREATE TABLE  jefes(
id number(4),
nombre varchar2(10),
apellido varchar2(10),
cobra number(6),
CONSTRAINT identificador UNIQUE(id) );

y ahora cuando intentemos meter una fila con un valor repetido para id nos dirá tal que así:

ORA-00001: unique constraint (HR.IDENTIFICADOR) violated

Ejemplos:

CREATE TABLE  jefes(
id number(4),
nombre varchar2(10),
apellido varchar2(10),
cobra number(6),
CONSTRAINT identificador PRIMARY KEY(id) );

Una Clave Foranea establece la relacion y la dependencia entre una columna y otra de otra tabla.

• FOREIGN KEY Nombre del tipo de restriccion
• REFERENCES Identifica a que tabla y columna referencia
• ON DELETE CASDACE Establece que cuando se borren registros de la tabla padre se borarán tambien de la tabla dependiente

Ejemplos:

CREATE TABLE  jefes(
id number(4),
nombre varchar2(10),
apellido varchar2(10),
cobra number(6),
CONSTRAINT identificador PRIMARY KEY(id) );

ALTER TABLE  tabla
DROP CONSTRAINT  nombre_de_la_restricción

Ejemplo:

alter table jefes
drop constraint identificador;

• TABLA Unidad básica de almacenamiento compuesta de registros y columnas
• VISTA Representación lógica de un subconjunto de una o mas tablas
• SECUENCIA Genera valores para claves primarias
• INDICE Mejora el rendimiento de algunas consultas
• SINONIMO Da nombres alternativos a los objetos
• DICCIONARIO DE DATOS Tablas mantenidas por Oracle para almacenar metainformación.
• • USER_TABLES Tablas propiedad del usuario.
  • USER_OBJECTS Objetos propiedad del usuario.
  • USER_CATALOG Tablas, vistas, sinonimos y secuencias propiedad del usuario.

Una vista es un subconjunto de una o n tablas. Sirve para restringir el acceso a los datos, para facilitar consultas complejas o para cualquier propósito que se te ocurra.

CREATE  [ O REPLACE ] [FORCE | NOFORCE] VIEW nombre_de_la_vista
AS consulta(*)
[ WITH CHECK OPTION [RESTRICCION]
[ WITH READ ONLY ]

(*) la consulta puede ser todo lo compleja que quieras pero NO puede estar ordenada (ORDER BY)

• REPLACE Usar replace en vez de create si es que la vista ya exixtía y queremos volver a generarla
• FORCE Crea la vista independientemente que el resultado de la consulta sea nulo
• NOFORCE Crea la vista si la tabla base exixte. Es la opción por defecto.
• WITH READ ONLY Pues eso, que no se puede hacer nada mas que leer
• WITH CHECK OPTION Especifica que solo las filas accesibles a la vista se puedan insertar o modificar.

Ejemplo:

CREATE VIEW  currantes_del_50  
AS select employee_id, first_name, last_name 
from employees where
department_id = 50;

View created.

Y ahora ya puedes usarlo como si fuera una tabla:

select * from currantes_del_50;

Para listar las vistas que hay en la base de datos:

select * from user_views;

Tambien puedes especificarle un nombre a los campos de la vista:

  CREATE VIEW  currantes_del_50 (id_pringao, nombre_del_currela, apellido)
  AS select employee_id, first_name, last_name 
	from employees where
	department_id = 50;

entonces para recuperar los datos de la vista podrás hacer:

select id_pringao, nombre_del_currela, apellido
from currantes_del_50;

Cuando creamos una clave primaria, a veces nos interesa tener una secuencia para asegurarnos que no habrá valores repetidos.Podemos pedirle a oracle (Y a MySql también pero de otro modo) que cree una secuencia para nosotros. y podemos definirla como queramos.

CREATE SEQUENCE nombre_de_la_secuencia
[ INCREMENT BY n ]
[ START WITH n]
[ ( MAXVALUE n | NOMAXVALUE ) ]
[ ( MINVALUE n | NOMINVALUE ) ]
[ ( CYCLE | NOCYCLE) ]
[ ( CACHE | NOCACHE ) ];

• INCREMENT BY n El intervalo de crecimiento. Por defecto 1
• START WITH n Establece el número de inicio. Si se omite, el valor por defecto es 1
• MAXVALUE n | NOMAXVALUE Especifica el valor máximo que la secuencia puede generar
• MINVALUE n | NOMINVALUE Especifica el valor minimo que la secuencia puede generar
• CYCLE | NOCYCLE En caso de llegar al valor máximo especifica si vuelve a comenzar por el principio o no. Por defecto es NOCYCLE
• CACHE | NOCACHE Especifica la cantidad de números asignados y mantenidos en memoria por Oracle. Por defecto 20

Ejemplo:

CREATE SEQUENCE SECUENCIA
INCREMENT BY 2
START WITH 94
MAXVALUE 1000
NOCACHE
NOCYCLE;

vale… y ahora como la manipulo? Para ver que es de ella, que ha hecho y que tal está puedes hacerlo a traves de la tabla user_sequences

select sequence_name, min_value, max_value, increment_by, last_number
from user_sequences;

y obtendrás algo tal que así:

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY LAST_NUMBER
------------------------------ ---------- ---------- ------------ -----------
LOCATIONS_SEQ                           1       9900          100        3300
DEPARTMENTS_SEQ                         1       9990           10         280
EMPLOYEES_SEQ                           1 1.0000E+27            1         207  
SECUENCIA                               1       1000            2          94

Por otro lado si quieres usar la secuencia que has creado tienes las pseudocolumnas NEXTVAL y CURRVAL que te generará un valor o te dirá en que valor te encuentras en este momento.

Ejemplo:

insert into jefes ( id, nombre, apellido, cobra)
values (secuencia.nextval, 'jefe' || secuencia.currval, 'apellido'||secuencia.currval, 100);

y esto nos meterá en la tabla jefes un jefe distinto cada vez….

        ID NOMBRE     APELLIDO        COBRA
---------- ---------- ---------- ----------
        94 jefe94     apellido94        100
        96 jefe96     apellido96        100

Un indice es un objeto de la base de datos usado por Oracle para acelerar la recuperación de filas mediante el uso de punteros (Lo siento pero no se como va este tema en MySql. Posiblemente tambien lo tenga, posiblemente no, porque MySql es el más rápido del oeste y puede que no lo necesite. Igualmente mirate su web. Un indice reduce el tiempo de acceso a disco generando una estructura de punteros para encontrar la ubicación de los datos. Además. Es un objeto independiente de la tabla que indexa por lo que es usado y mantenido automáticamente por el servidor de Oracle. Los índices se pueden crear automáticamente al definir que una columna es una PRIMARY KEY o es UNIQUE o bien e pueden definir manualmente por parte del usuario.

CREATE INDEX indice
ON tabla ( columna, columna, ...);

Ejemplo:

create index indice_de_jefes
on jefes (id, nombre);

respuesta:

Index created.

Es aconsejable crear un indice cuando:

• La columna es usada con frecuencia en una clausula where o en una condición de un join
• La columna tiene un rango de valores muy amplio
• La columna tiene muchos valores nulos
• La tabla es muy grande y se espera que las consultas hagan peticiones de menos del 3% o 4% de las filas

NO es aconsejable cuando:

• La tabla es pequeña
• Las columnas no se usan con frecuencia en condiciones de consultas
• Se espera que las consultas recuperen mas del 5% de las filas
• La tabla se actualiza frecuentemente.

La vista user_indexes del diccionario de datos tiene toda la información sobre los índices

Sinonimos y no alias. Un alias es llamar a una columna com otro nombre. Un sinonimo es llamar a una tabla, o vista como otra. Es útil para por ejemplo referenciar tablas de otra base de datos.

select * from user_synonyms;

RENAME objeto TO nuevo_nombre;

Se puede establecer una variable de substitución para almacenar valores temporales. En el caso de que no tenga un valor asignado se le pedirá al usuario en tiempo de ejecución.

tiene este aspecto:

select  FIRST_NAME ||', '|| LAST_NAME, SALARY
from EMPLOYEES
where DEPARTMENT_ID = &numero_de_departamento;

y el resultado es algo así como esto:

Enter value for numero_de_departamento: 10
old   3:   where DEPARTMENT_ID = &numero_de_departamento
new   3:   where DEPARTMENT_ID = 10

FIRST_NAME||','||LAST_NAME                          SALARY
----------------------------------------------- ----------
Jennifer, Whalen                                      4400

Y en el caso de de valores cadenas de carácteres:

select  FIRST_NAME ||', '|| LAST_NAME, SALARY
from EMPLOYEES
where FIRST_NAME = '&Nombre';

En el caso de que quieras reutilizar la variable debes establecerla con &&variable y reutilizarla con &variable

select  FIRST_NAME ||', '|| LAST_NAME, &&columna
from EMPLOYEES
order by &columna;

Si no quieres que te salga el prompt de old y new deverás establecer set verify a off

set verify off

* DEFINE variable = valor Establece una variable de tipo CHAR * DEFINE muestra las variables que hay establecidas * DEFINE variable muestra la variable, su valor y el tipo de datos que contiene. * ACCEPT variable PROMPT 'pregunta al usuario para que establezca el valor'

FORMATO

ACCEPT variable [tipo de dato] [FORMAT formato] [PROMPT 'pregunta a formular']{HIDE}

ejemplo:

ACCEPT departamento  PROMPT 'ponga un id de departamento :'
select  FIRST_NAME ||', '|| LAST_NAME
from EMPLOYEES
where DEPARTMENT_ID= &departamento;

y tenemos algo de este estilo:

ponga un id de departamento :20
old   3:   where DEPARTMENT_ID= &departamento
new   3:   where DEPARTMENT_ID= 20

FIRST_NAME||','||LAST_NAME
-----------------------------------------------
Michael, Hartstein
Pat, Fay

ejemplo:

DEFINE departamento  = 20
DEFINE departamento
select  FIRST_NAME ||', '|| LAST_NAME
from EMPLOYEES
where DEPARTMENT_ID= &departamento;

y tenemos algo de este estilo:

DEFINE DEPARTAMENTO    = "20" (CHAR)
old   3:   where DEPARTMENT_ID= &departamento
new   3:   where DEPARTMENT_ID= 20

FIRST_NAME||','||LAST_NAME
-----------------------------------------------
Michael, Hartstein
Pat, Fay

Con set se pueden definir toda una serie de variables de entorno

• ARRAYSIZE{20|n} Define el tamaño del Fetch de la BB.DD.
• COLSEP{_|text} Define el texto a imprimir entre columnas. Por defecto un espacio en blanco
• FEEDBACK{6|N|OFF|ON} Define el número de registros devueltos por una consulta
• HEADING{OFF|ON} Define si se pone o no la cabecera de las columnas
• LINESIZE{80|n} Define el núnero de carácteres por linea para informes. (El ancho de la página)
• LONG{80|n} Define el ancho máximo para visualizar valores LONG
• PAGESIZE{24|n} Define el tamaño de la página. (A cada nueva página pone las caveceras)
• PAUSE{OFF|ON|text} Permite controlar el desplazamiento entre pantallas. Presionando intro se continua
• TERMOUT{OFF|ON} Determina si resutado se muestra en pantalla o no.

En el fichero login.sql se encuentran todas las variables de entorno Para personalizar tu entorno modifica ese fichero

Se puede formatear la forma en que SQL*Plus de devuelve los datos.

• COLUMN [opciones] Controla el formato de la columna
• TITLE [texto|OFF|ON] Especifica una cabecera al principio de cada página
• BTITLE [texto|OFF|ON] Especifica una texto al pie de cada página
• BREAK [ON elemento de informe] Suprime la visualización de duplicados

• CLEAR Limpia cualquier formato
• FORMAT formato Define la visualizacion de una columna
• HEADING texto Define la cebecera de la columna
• JUSTIFY alineación justifica LA CABECERA de la columna
• NOPRINT Oculta la columna
• PRINT muestra la columna
• TRUNCATE Trunca la cadena de caracteres al final de la primera linea
• WRAPPED Continua escribiendo en la linea siguiente

• An Establece el ancho de columna en número de caracteres
• 9 Digitos con supresion de ceros
• 0 Digitos dejando los ceros
• $ Simbolo de dolar $
• L Moneda local
• . Posicion del punto decimal
• , Separador de miles
• | Inserta un salto de linea

ejemplo:

COLUMN FIRST_NAME HEADING 'Empleado | Nombre' FORMAT A8
COLUMN SALARY JUSTIFY LEFT FORMAT $99,999.00
COLUMN MANAGER_ID FORMAT 99999999 NULL 'No Format'

select EMPLOYEE_ID, FIRST_NAME, SALARY,  MANAGER_ID from EMPLOYEES;

resultado:

  Empleado  Empleado
    Nombre  Nombre  SALARY      MANAGER_ID
---------- -------- ----------- ----------
       198 Donald     $2,600.00        124
       199 Douglas    $2,600.00        124
       200 Jennifer   $4,400.00        101
       201 Michael   $13,000.00        100
       202 Pat        $6,000.00        201
       203 Susan      $6,500.00        101
       204 Hermann   $10,000.00        101
       205 Shelley   $12,000.00        101
       206 William    $8,300.00        205

Si quieres guardar una consulta en un fichero lo puedes hacer. Deberás guardarla con la extensión .sql para que te la reconozca sin problemas. Después con START archivo o simplemente con @archivo desde la linea de comandos de sqlPlus se ejecuta

ejemplo:

NOMBRE DE ARCIVO: 1.sql

SET PAGESIZE 40
SET LINESIZE 80
SET FEEDBACK OFF
TITLE 'EMPLEADOS  | INFORME'
BTITLE 'CONFIDENCIAL'
COLUMN FIRST_NAME HEADING 'Nombre' FORMAT A8
COLUMN SALARY JUSTIFY LEFT FORMAT $99,999.00
COLUMN MANAGER_ID FORMAT 99999999 NULL 'No Format'
REM ** AQUI EMPIEZA EL SELECT
select EMPLOYEE_ID, FIRST_NAME, SALARY,  MANAGER_ID 
from EMPLOYEES
where SALARY > 1000
order by JOB_ID, FIRST_NAME
/
SET FEEDBACK ON
REM LIMPIANDO TODA LA INFORMACION.....

y por fin… desde la linea de comandos de sql… y contando que estemos en el directorio en el que creamos el archivo…

SQL> @1.sql

otra utilidad es la de almacenar procesos recursivos.

ACCEPT id PROMPT ' introduzca el id del departamento :'
ACCEPT nombre PROMPT ' introduzca el nombre del departamento :'

INSERT INTO DEPARTMENTS ( DEPARTMENT_ID, DEPARTMENT_NAME )
               VALUES (          &id,      '&nombre'       );

y obtendremos:

 introduzca el id del departamento :99
 introduzca el nombre del departamento :nuevo
old   2:                  VALUES (          &id,      '&nombre'       )
new   2:                  VALUES (          99,      'nuevo'       )

1 row created.