Sublenguajes especializados

Muchas veces querremos acceder a la base de datos desde una aplicación hecha en un lenguaje de programación cualquiera. Para utilizar el SQL desde un lenguaje de programación, podemos utilizar el SQL hospedado, y para trabajar con éste necesitamos un precompilador que separe las sentencias del lenguaje de programación de las del lenguaje de bases de datos. Una alternativa muy interesante a esta forma de trabajar son las rutinas SQL/CLI.

El objetivo de este apartado no es explicar con detalle ni el SQL hospedado ni, aún menos, las rutinas SQL/CLI. Sólo introduciremos las ideas básicas del funcionamiento de ambos.

Sentencias de control - Las autorizaciones y desautorizaciones - La opción RESTRICT

La opción RESTRICT no nos permite desautorizar a un usuario si éste ha autorizado a otros.

Sentencias de control - Las autorizaciones y desautorizaciones - Desautorizaciones - Si un usuario

Si un usuario al que hemos autorizado ha autorizado a su vez a otros, que al mismo tiempo pueden haber hecho más autorizaciones, la opción CASCADE hace que queden desautorizados todos a la vez.

Sentencias de control - Las autorizaciones y desautorizaciones - Desautorizaciones - La opción GRANT OPTION FOR

La opción GRANT OPTION FOR se utilizaría en el caso de que quisiéramos eliminar el derecho a autorizar (WITH GRANT OPTION).

Sentencias de control - Las autorizaciones y desautorizaciones - Desautorizaciones - privilegios, objeto y usuarios

privilegios, objeto y usuarios son los mismos que para la sentencia GRANT.

Sentencias de control - Las autorizaciones y desautorizaciones - Desautorizaciones

Para desautorizar, el SQL dispone de la siguiente sentencia:

REVOKE [GRANT OPTION FOR] privilegios ON objeto FROM
usuarios [RESTRICT|CASCADE];

Sentencias de control - Las autorizaciones y desautorizaciones - Autorizaciones - La opción WITH GRANT OPTION

La opción WITH GRANT OPTION permite que el usuario que autoricemos pueda, a su vez, autorizar a otros usuarios a acceder al objeto con los mismos privilegios con los que ha sido autorizado.

Sentencias de control - Las autorizaciones y desautorizaciones - Autorizaciones - Usuarios

c) Usuarios puede ser todo el mundo: PUBLIC, o bien una lista de los identificadores de los usuarios que queremos autorizar.

Sentencias de control - Las autorizaciones y desautorizaciones - Autorizaciones - Objeto debe ser:

• DOMAIN: dominio
• TABLE: tabla.
• Vista.

Sentencias de control - Las autorizaciones y desautorizaciones - Autorizaciones - privilegios puede ser:

ALL PRIVILEGES: todos los privilegios sobre el objeto especificado.
• USAGE: utilización del objeto especificado; en este caso el dominio.
• SELECT: consultas.
• INSERT [(columnas)]: inserciones. Se puede concretar de qué columnas.
• UPDATE [(columnas)]: modificaciones. Se puede concretar de qué columnas.
• DELETE: borrados.
• REFERENCES [(columna)]: referencia del objeto en restricciones de integridad.
Se puede concretar de qué columnas.

Sentencias de control - Las autorizaciones y desautorizaciones - Autorizaciones

Para autorizar, el SQL dispone de la siguiente sentencia:

GRANT privilegios ON objeto TO usuarios
[WITH GRANT OPTION];

Donde tenemos que:

Sentencias de control - Las autorizaciones y desautorizaciones

Todos los privilegios sobre la base de datos los tiene su propietario, pero no es el único que accede a ésta. Por este motivo, el SQL nos ofrece sentencias para autorizar y desautorizar a otros usuarios.

Sentencias de control - Las transacciones - Ejemplo de transacción

A continuación proponemos un ejemplo de transacción en el que se quiere disminuir el sueldo de los empleados que han trabajado en el proyecto 3 en 1.000 euros. y aumentar el sueldo
de los empleados que han trabajado en el proyecto 1 también en 1.000 euros.

SET TRANSACTION READ WRITE;
UPDATE empleados SET sueldo = sueldo – 1000 WHERE num_proyec = 3;
UPDATE empleados SET sueldo = sueldo + 1000 WHERE num_proyec = 1;
COMMIT;

Sentencias de control - Las transacciones (II)

Si queremos actualizar la base de datos utilizaremos la opción READ WRITE, y si no la queremos actualizar, elegiremos la opción READ ONLY.
Sin embargo, en cambio, una transacción siempre debe acabar explícitamente con alguna de las sentencias siguientes:

{COMMIT|ROLLBACK} [WORK];

La diferencia entre COMMIT y ROLLBACK es que mientras la sentencia COMMIT confirma todos los cambios producidos contra la BD durante la ejecución de la transacción, la sentencia ROLLBACK deshace todos los cambios que se hayan producido en la base de datos y la deja como estaba antes del inicio de nuestra transacción.
La palabra reservada WORK sólo sirve para aclarar lo que hace la sentencia, y es totalmente opcional.

Sentencias de control - Las transacciones (I)

Una transacción es una unidad lógica de trabajo. O informalmente, y trabajando con SQL, un conjunto de sentencias que se ejecutan como si fuesen una sola. En general, las sentencias que forman parte de una transacción se interrelacionan entre sí, y no tiene sentido que se ejecute una sin que se ejecuten las demás.

La mayoría de las transacciones se inician de forma implícita al utilizar alguna sentencia que empieza con CREATE, ALTER, DROP, SET, DECLARE, GRANT o REVOKE, aunque existe la sentencia SQL para iniciar transacciones, que es la siguiente:

SET TRANSACTION {READ ONLY|READ WRITE};

Sentencias de control

Además de definir y manipular una base de datos relacional, es importante que se establezcan mecanismos de control para resolver problemas de concurrencia de usuarios y garantizar la seguridad de los datos. Para la concurrencia de usuarios utilizaremos el concepto de transacción, y para la seguridad veremos cómo se puede autorizar y desautorizar a usuarios a acceder a la base de datos.

SQL Sentencias de manipulación - La diferencia - Utilización de la diferencia en BDUOC - Ejemplo anterior expresado con NOT IN y con NOT EXISTS

El ejemplo que hemos hecho antes se podría expresar con NOT IN:

SELECT c.codigo_cli
FROM clientes c
WHERE c.codigo_cli NOT IN (SELECT p.codigo_cliente
FROM proyectos p);

o también con NOT EXISTS

SELECT c.codigo_cli
FROM clientes c
WHERE NOT EXISTS (SELECT *
FROM proyectos p
WHERE c.codigo_cli = p.codigo_cliente);

SQL Sentencias de manipulación - La diferencia - Utilización de la diferencia en BDUOC - Diferencia utilizando NOT EXISTS:

SELECT columnas
FROM tabla
WHERE NOT EXISTS (SELECT *
                                        FROM tabla
                                         WHERE condiciones);

SQL Sentencias de manipulación - La diferencia - Utilización de la diferencia en BDUOC - Diferencia utilizando NOT IN:

SELECT columnas
FROM tabla
WHERE columna NOT IN (SELECT columna
                                               FROM tabla
                                                 [WHERE condiciones]);

SQL Sentencias de manipulación - La diferencia - Utilización de la diferencia en BDUOC

Si queremos saber los clientes que no nos han contratado ningún proyecto, podríamos hacer:

El resultado de esta consulta sería el que se ve en el margen.
La diferencia es, junto con la intersección, una de las operaciones del SQL que se puede realizar de más formas diferentes. También podríamos encontrar la diferencia utilizando NOT IN o NOT EXISTS:

SQL Sentencias de manipulación - La diferencia

Para encontrar la diferencia entre dos o más sentencias SELECT FROM podemos utilizar la cláusula EXCEPT, que tiene este formato:

SELECT columnas
FROM tabla
[WHERE condiciones]
EXCEPT [ALL]
SELECT columnas
FROM tabla
[WHERE condiciones];

Si ponemos la opción ALL aparecerán todas las filas que da la diferencia. No la pondremos si queremos eliminar las filas repetidas.
Lo más importante de la diferencia es que somos nosotros quienes tenemos que vigilar que se haga entre columnas definidas sobre dominios compatibles.

SQL Sentencias de manipulación - La intersección - Ejemplo anterior expresado con IN y con EXISTS

El ejemplo que hemos propuesto antes se podría expresar con IN:

SELECT c.ciudad
FROM clientes c
WHERE c.ciudad IN (SELECT d.ciudad_dep
                                      FROM departamentos d);

o también con EXISTS:

SELECT c.ciudad
FROM clientes c
WHERE EXISTS (SELECT *
                              FROM departamentos d
                                  HERE c.ciudad = d.ciudad_dep;

SQL Sentencias de manipulación - La intersección - Intersección utilizando EXISTS

SELECT columnas
FROM tabla
WHERE EXISTS (SELECT *
                                 FROM tabla
                                   WHERE condiciones);

SQL Sentencias de manipulación - La intersección - Intersección utilizando IN

SELECT columnas
FROM tabla
WHERE columna IN (SELECT columna
                                    FROM tabla
                                       [WHERE condiciones]);

SQL Sentencias de manipulación - La intersección - Utilización de la intersección en BDUOC

Si queremos saber todas las ciudades donde tenemos departamentos en los que podamos encontrar algún cliente, podríamos hacer:

Sin embargo, la intersección es una de las operaciones del SQL que se puede hacer de más formas diferentes. También podríamos encontrar la intersección con IN o EXISTS:

SQL Sentencias de manipulación - La intersección

Para hacer la intersección entre dos o más sentencias SELECT FROM, podemos utilizar la cláusula INTERSECT, cuyo formato es:

SELECT columnas

FROM tabla

[WHERE condiciones]

INTERSECT [ALL]

SELECT columnas

FROM tabla

[WHERE condiciones];

Si indicamos la opción ALL, aparecerán todas las filas obtenidas a partir de la intersección. No la pondremos si queremos eliminar las filas repetidas.

Lo más importante de la intersección es que somos nosotros quienes tenemos que vigilar que se haga entre columnas definidas sobre dominios compatibles; es decir, que tengan la misma interpretación semántica.

SQL Sentencias de manipulación - La unión - Utilización de la unión en BDUOC

Si queremos saber todas las ciudades que hay en nuestra base de datos, podríamos hacer: