SQL*Plus

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.

Oracle te deja redirigir la salida a un fichero de texto. esto se consigue mediante el comando spool .

SQL> spool /tmp/mi_fichero_de_texto.lst

NOTA

• Los datos no se materializarán en el fichero de texto hasta que no hagas spool off
• Si vas a escribir mucho tendrás que modificar el serveroutput y darle un tamaño mayor

Este sería un ejemplo de utilización donde se desea un listado en un fichero de texto:

SQL> set serveroutput on size 10000
SQL> spool /tmp/listado.lst
SQL> prompt test
SQL> spool off;

Y si ahora lees el contenido de /tmp/listado.lst verás que hay lo mismo que te ha salido por pantalla

Cuando intentas insertar una cadena con un caracter especial o reservado por Oracle como puede ser & o ? probablemente tendremos problemas si no escapamos el caracter.

Con sqlplus se puede hacer:

SQL> set escape '\'
SQL> insert into MI_TABLA(candena)  values('mi cadena con un caracter especial \&');

Otra opción es usar la función ASCII chr()

SQL> insert into MI_TABLA values (chr(39) || 'hola' || chr(39));