Variables de sustitución

En todas las publicaciones anteriores hemos ejecutado consultas con columnas, valores y condiciones fijos, vamos a hacer nuestras consultas dinámicas, donde siempre se ejecute la misma consulta con valores diferentes.

El uso de las variables de sustitución está limitado SQL Plus o SQL Developer.

Veamos un ejemplo de las variables de sustitución:

SELECT

  *

FROM hr.employees

WHERE employee_id=&id_del_empleado;

Al momento de ejecutar la consulta, se despliega una ventana solicitando el valor que va a ir en la variable de sustitución &id_del_empleado:

Para este caso se buscó el empleado con id 204:

Se debe tener en cuenta los tipos de datos, realmente lo que hace la variable de sustitución es pedir un valor y ese valor es remplazado textualmente sin alguna modificación, si consultamos un valor de tipo carácter o cadena de caracteres, de deben poner las comillas simples en la ventana donde pide el valor, veamos un ejemplo donde consultamos por el nombre del empleado:

SELECT

   employee_id

  ,last_name

  ,hire_date

  ,job_id

FROM hr.employees

WHERE last_name=&apellido_del_empleado;

Veríamos un error por tipo de dato

Si el al  ejecutar la consulta lo encerramos en comillas simples, de estamos diciendo que es una cadena de caracteres y se ejecuta sin algún error:

Así se pueden manejar los caracteres, las cadenas de caracteres y fechas.

Las variables de sustitución pueden ser usadas en las clausulas SELECT, WHERE y ORDER BY.

Veamos un ejemplo:

SELECT

   employee_id

  ,last_name

  ,hire_date

  ,&columna_a_mostrar

FROM hr.employees

WHERE last_name=&apellido_del_empleado

ORDER BY &ordenado_por;

El resultado seria:

Usando doble ampersand en variables de sustitución

Se usa doble ampersand (&&) para definir las variables de sustitución que se están reusando:

SELECT

   employee_id

  ,last_name

  ,hire_date

  ,&&columna

FROM hr.employees

WHERE last_name='Whalen'

ORDER BY &&columna;

Observemos que la variable de sustitución es solo una en las dos cláusulas que se usa, se muestra solo una ventana pidiendo una sola vez el valor y la función del doble ampersand es reusarese valor en los dos lados donde se usa, el resultado seria:

Recuerde que usted puede definir sus variables a usar

Siguiente tema: comandos  DEFINE y VERIFY

ORDER BY Usando la cláusula para ordenar registros

En ocasiones es necesario ordenar los registros devueltos o recuperados de la consulta.

Con la cláusula ORDER BY se puede ordenar los registros recuperados de forma

                ASC        : Ascendente “Esta forma es la que por defecto usa ORACLE” y

                DESC      : Orden descendente.

La cláusula ORDER BY es la última que se ubica en una consulta SQL.

Si no se usa la cláusula ORDER BY, el orden de los registros recuperados es indefinido.

Se puede usar la palabra clave NULLS FIRST O NULLS LAST para indicar que los valores nulos de la columna que se están ordenando, se muestren al principio o al final.

La estructura de una consulta con la cláusula ORDER BY es:

SELECT                  expresión

FROM                   tabla

[WHERE condición o condiciones]            

[ORDER BY {columnas, expresiones, posición numérica} [ASC|DESC]]

Miremos algunos ejemplos:

SELECT

    last_name

   ,job_id

   ,salary

FROM hr.employees

WHERE  salary < 3000

ORDER BY last_name;

Por defecto se ordena en orden ascendente, si usamos la palabra clave ASC obtendríamos el mismo resultado.

SELECT

    last_name

   ,job_id

   ,salary

FROM hr.employees

WHERE  salary < 3000

ORDER BY last_name ASC;

Si usamos  la palabra clave DESC ordena de mayor a menor.

SELECT

    last_name

   ,job_id

   ,salary

FROM hr.employees

WHERE  salary < 3000

ORDER BY last_name DESC;

Los valores ordenados para las columnas de números es en orden ascendente desde el 0 hasta Infinito, para los valores de cadena de caracteres es de la A a la Z, para los valores de fechas van primero las fechas anteriores, es decir: va primero 13-JAN-01 que 21-APR-08

Veamos un ejemplo de cómo se usa NULLS FIRST o NULLS LAST

SELECT

     last_name

    ,hire_date

    ,department_id

FROM hr.employees

ORDER BY department_id DESC NULLS FIRST;

Si usamos NULLS FIRST  dejamos los nulos al inicio del resultado o de los registros recuperados.

Por el contrario  NULLS LAST los deja al final de los registros recuperados.

SELECT

     last_name

    ,hire_date

    ,department_id

FROM hr.employees

ORDER BY department_id DESC NULLS LAST;

Ordenar por posición numérica

Así como podemos ordenar por el nombre de la columna, también podemos ordenar por orden numérico,

En la siguiente consulta vamos a analizar el posicionamiento o como está definido el orden numérico:

SELECT

     last_name

    ,hire_date

    ,department_id

FROM hr.employees

ORDER BY 1;

Cuando hablamos de ordenamiento numérico definimos que las columnas que se van a consultar o mostrar, tienen o se les asigna un valor numérico, para el caso de nuestra consulta:

last_name          : tiene el numero 1

hire_date            : tiene el numero 2

hire_date            : tiene el numero 3

department_id : tiene el numero 4

Si deseamos ordenar por la columna hire_date debemos usar ORDER BY 2 así:

SELECT

     last_name

    ,hire_date

    ,department_id

FROM hr.employees

ORDER BY 2;

El resultado de esta consulta es:

La proxima publicacion : Variables de sustitución (Substitution variables )

Reglas de precedencia

Se pueden usar paréntesis “()” para modificar o sobrescribir las reglas de precedencia.

Las reglas de precedencia determina el orden con que se evalúan y calculan las expresiones.

En la tabla de precedencia se indica el orden con que se ejecutan por defectos las reglas, sin embargo de ser necesario, se pueden modificar encerrando en paréntesis la expresión que quiera que se evalué primero.

La consulta

SELECT

    last_name

   ,job_id

   ,salary

FROM hr.employees

WHERE job_id = 'SA_REP'

  OR job_id = 'AD_PRES'

  AND salary > 15000;

Evalúa primero las expresiones relacionadas en el AND

   job_id = 'AD_PRES'

AND salary > 15000

El resultado del AND  de esas expresiones lo evalúa luego con el OR

job_id = 'SA_REP'

  OR resultado del AND

En esta misma consulta  podemos sobrescribir las reglas de precedencia con paréntesis.

SELECT

    last_name

   ,job_id

   ,salary

FROM hr.employees

WHERE (job_id = 'SA_REP'

  OR job_id = 'AD_PRES')

  AND salary > 15000;

Primero evalúa lo que está en paréntesis,

        (job_id = 'SA_REP'

  OR job_id = 'AD_PRES')

 el resultado de esa evaluación lo evalúa con la expresión AND

Resultado del parrentesis

AND salary > 15000

Operadores lógicos AND, OR o NOT, definiendo condiciones

Una condición lógica es evalúa el resultado de dos condiciones que devuelven un solo resultado, basado en esas condiciones evaluadas.

Un registro es devuelto o mostrado solo si el resultado  general es verdadero.

Todos los ejemplos vistos hasta este tema solo habían tenido una sola condición verdadera en su cláusula WHERE.

Usando el operador lógico AND

El operador lógico AND requiere que las dos componentes de las condiciones evaluadas sean verdaderas.

Es decir, si necesito todos los empleados que ganen $2600 y en su job_id contengan  el prefijo “SH”, es necesario usar el operador lógico AND así:

SELECT

   last_name

  ,job_id

  ,salary

FROM hr.employees

WHERE salary=2600

  AND job_id LIKE 'SH%';

La tabla de la verdad indica el comportamiento de la evolución de los componentes  

Usando el operador OR

El operador OR requiere que alguna delas dos componentes de la condición sean verdaderas.

Si es necesitamos  mostrar todos los empleados que ganen $2600 o en su job_id contengan el prefijo “SH”, es necesario usar el operador OR asi:

SELECT

   last_name

  ,job_id

  ,salary

FROM hr.employees

WHERE salary=2600

  OR job_id LIKE '%SH%';

El operador OR busca todos los registros que en su salario “salary” sean igual a 2600 sin importar el “job_id” que tengan, pero también busca todos los id de trabajo “job_id” que contengan el prefijo “SH” sim importa el “salary” que tengan.

Imegn operador 4

Operador lógico NOT

El operador lógico NOT indica todo lo que no sea, es decir si necesitamos todos los registros a excepción de algunos cuantos, podemos usar el operador NOT.

Pongamos el caso hipotético que necesitamos todos los empleados  que su “job_id” no sean AC_ACCOUNT, FI_ACCOUNT, SH_CLERK, ST_CLERK, usaríamos una consulta asi:

SELECT

    last_name

   ,job_id

   ,salary

FROM hr.employees

WHERE job_id NOT IN ('AC_ACCOUNT','FI_ACCOUNT','SH_CLERK','ST_CLERK');

Su tabla de la verdad es:

Operadores de comparación

Los operadores de comparación son usados en condiciones que compara una expresión con otro valor o expresión, son usados en la cláusula WHERE.

Usando operadores de comparación

Digamos que se desea extraer el nombre y el salario de todos los empleados que ganen menos o  $3000.

SELECT

  last_name,

  salary

FROM hr.employees

WHERE salary <= 3000;

El resultado seria:

Si queremos buscar un valor en un rango de valores debemos usar el operador BETWEEN

SELECT

  last_name,

  salary

FROM hr.employees

WHERE salary BETWEEN 2900 AND 3000;

Donde 2900 es el valor límite inferior y 3000 es el valor límite superior

El resultado de la consulta seria:

Operador IN

Se usa el operador IN para evaluar los valores que están en la columna evaluada, si el valor que está en la columna evaluada esta en los valores o lista  que contiene el operador IN, el registro es mostrado.

Es decir, si deseamos que se muestre solo los salarios de los empleados que ganan  ‘3000’,’2500’ y ‘2300’, podemos ejecutarla consulta:

SELECT

  last_name,

  salary

FROM hr.employees

WHERE salary IN ('3000','2500','2300');

Cabe agregar que si la columna evaluada no contiene uno de los valores relacionados en la lista que recibe el operador IN, no será mostrado.

Operador LIKE

Quizás no se conozca el valor exacto que desea buscar, con el operador LIKE, puede usar un comodín o parte de la palabra o valor a buscar.  

El operador LIKE, busca en una cadena de caracteres que se especifique, si existe un carácter o expresión en el valore de la columna que contenga la cadena o carácter, retorna true y devuelve el valor encontrado.

Si deseamos traer todos los nombres de los empleados que inicien con la letra “S”, deberías consultalos asi:

SELECT

   last_name

  ,salary

FROM hr.employees

WHERE last_name LIKE 'S%';

El resultadode ejecutar esta consulta es:

También se puede usar el símbolo “_” para representar un solo carácter, si queremos traer todos los empleados  que en su segunda letra tenga la letra “a” ejecutaríamos la consulta:

SELECT

   last_name

  ,salary

FROM hr.employees

WHERE last_name LIKE '_a%';

Es necesario usar el identificador de escape “\” en caso de tener en el campo a filtrar, en nuestro caso el job_id contenga o haga parte de la cadena el carácter “_”, consultemos los empleados con su nombre y id de trabajo “job_id”:

SELECT

   last_name

  ,job_id

FROM hr.employees;  

Si deseáramos consultar todos los job_id  que iniciaran con la cadena de caracteres “SA_”  sería necesario escapar  el carácter “_”, debido a que Oracle toma el carácter como un solo carácter después de la cadena “SA”, en este caso usaríamos la consulta:

SELECT

   last_name

  ,job_id

FROM hr.employees

WHERE job_id LIKE '%SA\_%' ESCAPE '\';

Donde ESCAPE ‘\’ es como le indicamos al operador de comparación LIKE que el carácter “_” hace parte de la cadena que vamos a buscar.

Operador IS NULL

Este operador identifica busca las columnas que contengan un nulo, si fuera necesario identificar cuales empleados tiene una comisión pct nula, podemos ejecutar la consulta:

SELECT

   last_name

  ,job_id

  ,commission_pct

FROM hr.employees

WHERE commission_pct IS NULL;

De igual forma los para el operador de comparación IS NOT NULL  indica todos los que no tengan una comisión pct nula.

Cadena de caracteres y fechas

Las cadenas de caracteres y fechas deben ser encerrados en comillas simples (‘).

Los valores carácter son CASE-SENSITIVE.

Los Valores fecha son FORMAT-SENSITIVE.

Las fechas por defecto se muestran en un formato DD-MON-RR.

Digamos que deseamos mostrar todos los empleados que su apellido sea “Grant”

SELECT

  last_name,

  department_id

FROM hr.employees

WHERE last_name ='Grant';

El resultado de la consulta será:

Si ejecutamos la misma consulta solo cambiando el valor que estamos buscando de “Grant” a “grant”

SELECT

  last_name,

  department_id

FROM hr.employees

WHERE last_name ='grant';

El resultado será:

Para las fechas ocurre lo mismo con el formato, como se mencionó anteriormente, el formato de la fecha por defecto es DD-MON-RR, esto hace que al consultar una fecha, deber ser consultada con elformato requerido, asi:

SELECT

  last_name,

  hire_date,

  department_id

FROM hr.employees

WHERE hire_date='07-JUN-02';

Pero si cambiamos el formato

SELECT

  last_name,

  hire_date,

  department_id

FROM hr.employees

WHERE hire_date='07-06-02';

El resultado sería diferente:

Las bases de datos Oracle, almacena la información internamente en un formato numérico, representando Siglos, años, meses, días, horas  y segundos.