Variables en SQL*Plus (parte 2)

Hay tres tipos de variables en SQL*Plus y suelen causar confusión.
Estas son: variables de usuario, variables de sustitución y bind variables.
En la primera parte repasamos las variables de usuario, aquí van las variables de sustitución.

VARIABLES DE SUSTITUCION

Las variables de sustitución suelen confundirse con las variables de usuario, más que nada porque ambas aparecen precedidas con un símbolo ampersand (&). Sin embargo, hay diferencias en la definición, asignación y uso de las mismas. Como vimos en la parte 1, las variables de usuario se declaran explícitamente con el comando DEFINE, tienen asociado un tipo y se les pueden asignar valores en el script como lo hacemos en cualquier lenguaje de programación.
Las variables de sustitución en cambio cuando están indefinidas, solicitan el valor al usuario cada vez que aparecen, y el resultado es exactamente el mismo que si escribiéramos el texto directamente.

Por ejemplo: en la siguiente consulta en cada aparición de variable será solicitado el valor al usuario. Notar que aunque la variable COLUMNA aparece repetida, se trata como si fueran diferentes y el valor es solicitado dos veces.

SELECT &COLUMNA, SUM(MONTO)
FROM cuentas
GROUP BY &COLUMNA;

Enter value for columna: ID_REGION
old 1: SELECT &COLUMNA,
new 1: SELECT ID_REGION,
Enter value for columna: ID_REGION
old 1: GROUP BY &COLUMNA
new 1: GROUP BY ID_REGION

Mientras que el símbolo ampersand (&) declara una variable sustitución temporal (vale una única vez), dos ampersand consecutivos (&&) definen una variable permanente evitando que el usuario deba ingresar nuevamente su valor.

Scripts parametrizados

Las variables de sustitución se utilizan típicamente para pasar parámetros a un script a través del comando START. Dentro del script las variables deben enumerarse secuencialmente &1, &2, etc, y éstas serán asignadas en orden con los parámetros pasados en la llamada.

Ejemplo: Un script al que le pasamos dos parámetros, un nombre de columna y la tabla

programa.sql
SET VERIFY OFF
SELECT &&1, SUM(MONTO)
FROM &2
GROUP BY &1;

La llamada se realiza de la siguiente manera:

SQL> START programa.sql ID_REGION cuentas

• La variable 1 tiene dos ampersand por lo que queda definida a la primera vez que aparece.
• SET VERIFY OFF elimina el feedback de old y new values.
• Las variables de sustitución 1 y 2, al igual que las variables de usuario también se pueden borrar de memoria, por ejemplo incluyendo la línea UNDEFINE 1 en el script.

Ver también:
Variables en SQLPlus (parte 1) - Sobre variables de usuario
Variables en SQLPlus (parte 3) - Sobre variables bind