Cerrar

Enviar mensaje

twitter FaceBook You Tube Git Hub Docs.com Enviar email

Manual de SQL (Oracle SQL)

[11]
Consultas avanzadas

Publicidad

[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:

SELECT edad, nombre FROM clientes ORDER BY edad DESC;

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:

Empleado

Jefe

Antonio

Ángel

Ángel

Eva

Ángel

Carmen

Eva

Andrés

Eva

Carmelo

Andrés

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:

Nombre

Andrés

Eva

Ángel

Sin embargo este código:

SELECT nombre FROM empleados
START WITH nombre=’Andrés’
CONNECT BY n_jefe= PRIOR n_empleado;

Devuelve como resultado:

Nombre

Andrés

Carmelo

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

Nombre

Ángel

Antonio

Eva

Carmen

Andrés

Carmelo

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:

Level

Nombre

1

Ángel

2

Antonio

2

Eva

3

Carmen

3

Andrés

4

Carmelo

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:

Tipo

Modelo

Sum(cantidad)

AR

6

10530

AR

9

4928

AR

15

5667

AR

20

43

AR

 

21168

BI

10

363

BI

38

1740

BI

57

1638

BI

 

3741

CL

12

7000

CL

15

3068

CL

18

6612

CL

 

16680

EM

21

257

EM

42

534

EM

 

791

PU

5

12420

PU

9

7682

PU

 

20102

TO

6

464

TO

9

756

TO

10

987

TO

12

7740

TO

16

356

TO

 

10303

TU

6

277

TU

9

876

TU

10

1023

TU

12

234

TU

16

654

TU

 

3064

 

 

75849

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:

Tipo

Modelo

SUM(cantidad)

 

 

75849

 

5

12420

 

6

11271

 

9

14242

 

10

2373

 

12

14974

 

15

8735

 

16

1010

 

18

6612

 

20

43

 

21

257

 

38

1740

 

42

534

 

57

1638

AR

 

21168

AR

6

10530

AR

9

4928

AR

15

5667

AR

20

43

BI

 

3741

BI

10

363

BI

38

1740

BI

57

1638

CL

 

16680

CL

12

7000

CL

15

3068

CL

18

6612

EM

 

791

EM

21

257

EM

42

534

PU

 

20102

PU

5

12420

PU

9

7682

TO

 

10303

TO

6

464

TO

9

756

TO

10

987

TO

12

7740

TO

16

356

TU

 

3064

TU

6

277

TU

9

876

TU

10

1023

TU

12

234

TU

16

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:

Tipo

Modelo

SUM(cantidad)

GROUPING(tipo)

GROUPING(modelo)

 

 

75849

1

1

 

5

12420

1

0

 

6

11271

1

0

 

9

14242

1

0

 

10

2373

1

0

 

12

14974

1

0

 

15

8735

1

0

 

16

1010

1

0

 

18

6612

1

0

 

20

43

1

0

 

21

257

1

0

 

38

1740

1

0

 

42

534

1

0

 

57

1638

1

0

AR

 

21168

0

1

AR

6

10530

0

0

AR

9

4928

0

0

AR

15

5667

0

0

AR

20

43

0

0

BI

 

3741

0

1

BI

10

363

0

0

BI

38

1740

0

0

BI

57

1638

0

0

CL

 

16680

0

1

CL

12

7000

0

0

CL

15

3068

0

0

CL

18

6612

0

0

EM

 

791

0

1

EM

21

257

0

0

EM

42

534

0

0

PU

 

20102

0

1

PU

5

12420

0

0

PU

9

7682

0

0

TO

 

10303

0

1

TO

6

464

0

0

TO

9

756

0

0

TO

10

987

0

0

TO

12

7740

0

0

TO

16

356

0

0

TU

 

3064

0

1

TU

6

277

0

0

TU

9

876

0

0

TU

10

1023

0

0

TU

12

234

0

0

TU

16

654

0

0

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:

Tipo

Modelo

N_almacen

SUM(cantidad)

AR

6

 

10530

AR

9

 

4928

AR

15

 

5667

AR

20

 

43

BI

10

 

363

BI

38

 

1740

BI

57

 

1638

CL

12

 

7000

CL

15

 

3068

CL

18

 

6612

EM

21

 

257

EM

42

 

534

PU

5

 

12420

PU

9

 

7682

TO

6

 

464

TO

9

 

756

TO

10

 

987

TO

12

 

7740

TO

16

 

356

TU

6

 

277

TU

9

 

876

TU

10

 

1023

TU

12

 

234

TU

16

 

654

 

 

1

30256

 

 

2

40112

 

 

3

5481

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)