[11.1] consultas avanzadas

[11.1.1]cláusula FOR UPDATE

Es una cláusula no estándar que posee Oracle y que permite bloquear las filas que aparecen en una instrucción SELECT de modo que esta instrucción genera una transacción (si no estuviéramos ya en una). La razón es que nadie pueda modificar esas filas a fin de trabajar sin problemas con ellas.

Esta cláusula se coloca antes de la cláusula ORDER BY. Ejemplo:

SELECT cif, nombre, localidad, direccion  FROM empresas

WHERE provincia=’Palencia’

FOR UPDATE;

Por supuesto sólo podemos bloquear filas que otros usuarios no estén bloqueando. Y, puesto que esta instrucción en realidad es de tipo DML, para finalizar el bloqueo deberemos finalizar la transacción (con ROLLBACK o COMMIT).

Podemos especificar que se bloquee sólo una o más columnas:

SELECT cif, nombre, localidad, direccion  FROM empresas

WHERE provincia=’Palencia’

FOR UPDATE OF direccion;

En el código anterior se bloquea solo la columna dirección.

Incluso podemos indicar un tiempo máximo de bloqueo:

SELECT cif, nombre, localidad, direccion  FROM empresas

WHERE provincia=’Palencia’

FOR UPDATE WAIT 10; 

--Bloquea las filas durante 10 segundos

[11.1.2]consultas con ROWNUM

La función ROWNUM devuelve el número de la fila de una consulta. Por ejemplo en:

SELECT ROWNUM, edad, nombre FROM clientes

Aparece el número de cada fila en la posición de la tabla. Esa función actualiza sus valores usando subconsultas. De modo que la consulta:

SELECT ROWNUM AS ranking, edad, nombre 

FROM (

   SELECT edad, nombre FROM clientes ORDER BY edad DESC

);

Puesto que:

obtiene una lista de los clientes ordenada por edad, el SELECT superior obtendrá esa lista pero mostrando el orden de las filas en esa consulta. Eso permite hacer consultas de tipo top-n, (los n más....).

Por ejemplo para sacar el top-10 de la edad de los clientes (los 10 clientes más mayores)´, haríamos:

SELECT ROWNUM AS ranking, edad, nombre FROM clientes

FROM (

	SELECT edad, nombre FROM clientes ORDER BY edad DESC

)

WHERE ROWNUM<=10;

[11.1.3]consultas sobre estructuras jerárquicas

Imaginemos una tabla de empleados definida por un código de empleado, nombre del mismo y el código del jefe. Este último código está relacionado con el código de empleado que posee el jefe en cuestión. Así definido, una consulta que muestre el nombre de un empleado y el nombre de su jefe directo, sería:

SELECT e.nombre AS empleado, j.nombre AS jefe 

FROM empleados e

JOIN  empleados j ON (e.cod_jefe=j.cod_empleado);

Saldría por ejemplo:

En el ejemplo anterior se observa como un jefe puede tener otro jefe o jefa (Eva es la jefa de Andrés, pero tiene como jefe a Ángel), generando una estructura jerárquica de tipo recursivo.

En este tipo de estructuras, a veces se requieren consultas que muestren todos los empleados de un jefe, mostrando los mandos intermedios. Se trata de una consulta que recorre ese árbol hacia su lado superior. Este tipo de consultas posee esta sintaxis:

SELECT [LEVEL,] listaDeColumnasYExpresiones

FROM tabla(s)...

[WHERE condiciones...]

[START WITH condiciones]

CONNECT BY [NOCYCLE] [PRIOR] expresion1=[PRIOR] expresion2 

El apartado CONNECT permite indicar qué relación hay que seguir para recorrer el árbol. La palabra PRIOR indica hacia dónde se dirige el recorrido. Finalmente el apartado START indica la condición de inicio del recorrido (normalmente la condición que permita buscar el nodo del árbol por el que comenzamos el recorrido, es decir sirve para indicar desde donde comenzamos. Ejemplo:

SELECT nombre FROM empleados

START WITH nombre=’Andrés’

CONNECT BY PRIOR n_jefe=n_empleado;

Resultado:

Sin embargo este código:

SELECT nombre FROM empleados

START WITH nombre=’Andrés’

CONNECT BY n_jefe= PRIOR n_empleado;

Devuelve como resultado:

Si en lugar de Andrés en esa consulta buscáramos desde Ángel, saldría:

El modificador LEVEL permite mostrar el nivel en el árbol jerárquico de cada elemento:

SELECT LEVEL, nombre FROM empleados

START WITH nombre=’Ángel’

CONNECT BY n_jefe= PRIOR n_empleado;

Resultado:

La columna Level indica el grado de distancia respecto al inicio de la búsqueda. Así el nivel 4 de Carmelo indica que es un empleado de Ángel, pero porque lo es de un sub-sub-jefe suyo.

Para eliminar recorridos, se utilizan condiciones en la cláusula WHERE o en el propio CONNECT. De modo que :

SELECT LEVEL, nombre FROM empleados

WHERE nombre!=’Eva’

START WITH nombre=’Ángel’

CONNECT BY n_jefe= PRIOR n_empleado;

En ese ejemplo, Eva no sale en los resultados.

En este otro código:

SELECT LEVEL, nombre FROM empleados

START WITH nombre=’Ángel’

CONNECT BY n_jefe= PRIOR n_empleado AND nombre!=’Eva’;

No sale ni Eva ni sus empleados (se corta la rama entera).

Cuando los datos forman un árbol jerárquico (como ocurre con los ejemplos) no suele haber ningún problema. Pero hay diseños en los que hay padres de hijos que a su vez pueden ser sus padres. Con los padres e hijos no ocurre esta situación, evidentemente. Pero por ejemplo si tuviéramos un diseño de redes sociales donde se apunta el nombre del usuario y el nombre de sus amigos, entonces resulta que yo estaré apuntado como amigo de una persona que, a su vez, es mi amigo.

En ese caso resultaría un bucle infinito cuando se hace esta consulta:

SELECT amigo FROM contactos

START WITH nombre=’Ángel’

CONNECT BY PRIOR amigo=nombre; 

-- Ocurre un bucle imposible de solucionar

Para esos casos disponemos de la cláusula NOCYCLE que controla los resultados repetidos y evita los caminos que ya hemos recorrido.

En cualquier caso consultas en datos no jerarquizados en forma de árbol sino en forma de grafo (como el ya comentado ejemplo de las redes sociales), pueden ocupar muchísimo tiempo a Oracle por lo que no es mala idea pensar en otra solución con ayuda de PL/SQL (el lenguaje procedimental que se incluye en Oracle).

SELECT DISTINCT amigo FROM contactos

START WITH nombre=’Ángel’

CONNECT BY NOCYCLE PRIOR amigo=nombre; -- Ahora sí

Por cierto, el DISTINCT impide que aparezcan muchas veces las mismas personas. Ya que pueden ser amigos de muchos de mis amigos (e incluso de muchos de los amigos de mis amigos).

[11.2] consultas de agrupación avanzada

[11.2.1]ROLLUP

Esta expresión en una consulta de agrupación (GROUP BY) permite obtener los totales de la función utilizada para calcular en esa consulta. Ejemplo:

SELECT tipo, modelo, SUM(cantidad)

FROM existencias

GROUP BY tipo,modelo;

Esta consulta suma las cantidades de la tabla existencias por cada tipo y modelo distinto. Si añadimos:

SELECT tipo, modelo, SUM(cantidad)

FROM existencias

GROUP BY ROLLUP (tipo,modelo);

Entonces nos añade un registro para cada tipo en el que aparece la suma del total para ese tipo. Al final mostrará un registro con el total absoluto. Es decir el resultado de esa consulta es:

Se pueden unir varias columnas entre paréntesis para tratarlas como si fueran una unidad:

SELECT tipo, modelo, n_almacen, SUM(cantidad)

FROM existencias

GROUP BY ROLLUP ((tipo,modelo), (n_almacen));

La diferencia respecto a la anterior es que el total mostado por ROLLUP se referirá al tipo y al modelo.

[11.2.2]CUBE

Es muy similar al anterior, sólo que en este caso, se calculan todos los subtotales relativos a la consulta. Ejemplo:

SELECT tipo, modelo, SUM(cantidad)

FROM existencias

GROUP BY CUBE (tipo,modelo);

Resultado:

Es decir, calcula totales por tipo, por modelo y el total absoluto.

[11.2.3]GROUPING

Se trata de una función que funciona con ROLLUP y CUBE y que recibe uno o más campos e indica si la fila muestra un subtotal referido a los campos en cuestión. Si la fila es un subtotal de esos campos pone 1, sino lo marca con 0. Ejemplo:

SELECT tipo, modelo, SUM(cantidad), 

	GROUPING(tipo), GROUPING(modelo)

FROM existencias

GROUP BY CUBE (tipo,modelo);

Sale:

Se utiliza sobre todo para preparar un consulta para la creación de informes.

[11.2.4]GROUPING SETS

Se trata de una mejora aparecida en Oracle 9i que permite realizar varias agrupaciones para la misma consulta. Sintaxis:

SELECT...

...

GROUP BY GROUPING SETS (listaDeCampos1) [,(lista2)...]

Las listas indican los campos por los que se realiza la agrupación. Ejemplo:

SELECT tipo, modelo, n_almacen, SUM(cantidad)

FROM existencias

GROUP BY GROUPING SETS ((tipo,modelo), (n_almacen));

De esa consulta se obtiene:

Se trata de dos consultas de totales unidades

[11.2.5]conjuntos de agrupaciones combinadas

Se pueden combinar agrupaciones de diversas formas creando consultas como:

SELECT tipo, modelo, n_almacen, SUM(cantidad)

FROM existencias

GROUP BY tipo, ROLLUP(modelo), CUBE(n_almacen)

Que mostraría un informe espectacular sobre las tablas anteriores. Así como:

SELECT tipo, modelo,n_almacen, SUM(cantidad)

FROM existencias

GROUP BY GROUPING SETS(tipo,modelo), GROUPING SETS(tipo,n_almacen)