PDF de programación - El Diseo de una Base de Datos - Tema 7. El estandar SQL

Bases de Datos



3º Informática de Sistemas

TEMA 7.- EL ESTANDAR SQL.
El Sublenguaje de Manipulación.
El Sublenguaje de Definición.
El Sublenguaje de Control.
El Sublenguaje Huésped.


1. El Sublenguaje de Manipulación.

(Se verá en la clase de problemas)


2. El Sublenguaje de Definición.


Permite definir cada relación que pertenece a la base de datos
relacional, incluyendo:

• El esquema de cada relación.
• El dominio de valores permitido a cada atributo.
• Restricciones de Integridad.
• El conjunto de índices que se crean para cada relación.
• Información de seguridad y autorización de cada relación.
• La estructura de almacenamiento físico de cada relación en el

disco.



2.1.

Definir una relación.


Para crear una relación mediante SQL, se utiliza la sentencia create

table:


CREATE TABLE [Esquema.]NombreTabla (

{NombreColumna TipoColumna [NOT NULL],}+,
{CONSTRAINT NombreRestricción
{UNIQUE ([NombreColumna,]+) |
CHECK (Condicion) |
PRIMARY KEY ([NombreColumna,]+) |
FOREIGN KEY (NombreColumna) REFERENCES
NombreTabla(NombreColumna)},}*)

El Estándar SQL



Página 1 de 15

Bases de Datos



3º Informática de Sistemas


Principales tipos en SQL de ORACLE (TipoColumna).



varchar2 (n). Cadena de caracteres de longitud variable, con

un máximo de n caracteres.

number. Números sin restricción en la precisión.

number (l, d). Números, donde “l” es la longitud y “d” el

número de dígitos decimales.

date. Fecha.

Blob. Objeto binario de hasta 4 Gb.

2.2.

Definir las Restricciones de Integridad en SQL de ORACLE

(CONSTRAINT NombreRestricción).

NOT NULL. Establece la obligatoriedad de que la columna tenga
un valor no nulo. Se debe especificar junto a la columna a la que
afecta.



UNIQUE. Evita valores repetidos en una o varias columnas,

permitiendo valores nulos.

CHECK. Comprueba que se cumpla una condición determinada al
rellenar esa o esas columnas. La condición sólo puede estar
construida con columnas de esta misma tabla.

El Estándar SQL



Página 2 de 15

Bases de Datos



3º Informática de Sistemas

PRIMARY KEY. Establece el conjunto de columnas que forman la
clave primaria de esta tabla. La restricción garantiza la unicidad y
la obligatoriedad. Sólo puede existir una clave primaria por tabla.

FOREIGN KEY. Establece que los valores válidos de la o las
columnas son o bien valores presentes en la o las columnas
referenciadas o bien valores nulos. No hay límite para el número
de claves ajenas en una tabla.



Se puede forzar que cuando una fila de la tabla referenciada sea
borrada, todas las filas de esta tabla cuya clave ajena coincida con
la clave borrada se borren también. Esto se consigue añadiendo la
coletilla ON DELETE CASCADE en la definición de la clave
ajena.



Ejemplo de Sentencia Create Table:


REM
REM Tabla Departamento con un código de departamento, un nombre y
REM una localización.
REM
create table departamento (

codigo_dep varchar2 (3),
nombre varchar2 (15) not null,
localidad varchar2 (15),
constraint dep_pk primary key (codigo_dep),
constraint dep_local check

(localidad in (‘Valladolid’, ‘Soria’, ‘Palencia’))

);

El Estándar SQL



Página 3 de 15

Bases de Datos



3º Informática de Sistemas

REM
REM Tabla Empleado
REM
create table empleado (

codigo_emp varchar2 (3),
nombre varchar2(10) not null,
oficio varchar2(11),
fecha_alta date,
salario number(10,2),
codigo_dep varchar2(3)
constraint emp_pk primary key (codigo_emp),
constraint emp_fk foreign key (codigo_dep)

references departamento (codigo_dep) on delete cascade,

constraint emp_ck check (salario > 0)
);



2.3. Modificar o Borrar una Relación.

Para borrar una relación con SQL, se utiliza la sentencia drop table:


DROP TABLE [Esquema.]NombreTabla;
La sentencia borra toda la información de la tabla: el esquema y
todas las tuplas.


Para modificar el esquema de una relación con SQL, se utiliza la

sentencia alter table:


ALTER TABLE [Esquema.]NombreTabla {ADD | MODIFY}
({NombreColumna TipoColumna [NOT NULL],}+);



Esta sentencia permite añadir o modificar atributos de una relación.

ALTER TABLE [Esquema.]NombreTabla {ADD | DROP}
CONSTRAINT NombreRestricción DefiniciónRestricción;

Esta sentencia permite añadir o borrar una restricción.

El Estándar SQL



Página 4 de 15

Bases de Datos



3º Informática de Sistemas

2.4. Más Restricciones de Integridad con el SQL de ORACLE.


Triggers



• Un trigger (disparador) define una acción que la base de datos
tipo de

realizar cuando ocurre algún

siempre debería
acontecimiento que la afecta.


• Se utilizan para

mantener la integridad referencial,
asegurar reglas de negocio complejas y
auditar cambios en los datos.

• Para definir un trigger se debe:

Especificar las condiciones bajo las que el trigger será

Especificar las acciones que se realizarán cuando el trigger

ejecutado.

se ejecute.


• Para crear un trigger mediante SQL, se utiliza la sentencia

create trigger:



CREATE TRIGGER NombreTrigger


{before | after | instead of}
{delete | insert | update [of NombreColumna

[, NombreColumna] ...] }
[or {delete | insert | update [of NombreColumna

[, NombreColumna] ...] } ] ...

on {NombreTabla | Nombre-Vista}

[ [referencing { old [as] NombreViejo | new [as] NombreNuevo} ...]
for each {row | statement} [when (Condición)] ] Bloque pl/sql

El Estándar SQL



Página 5 de 15

Bases de Datos



3º Informática de Sistemas

Ejemplo de Trigger.



• Supongamos que un banco decide trabajar con los descubiertos

de las cuentas como sigue:

Pone el saldo de la cuenta a cero.
Crea una tupla en la tabla Préstamo con la cantidad del
descubierto. Da a este préstamo un número de préstamo
idéntico al número de cuenta que tiene el descubierto.

Crea una tupla en la tabla de Tomadores de préstamo con

la información del cliente que ha hecho el descubierto.

• La condición para ejecutar el trigger es una modificación en la
relación Cuenta que produzca un valor del atributo Saldo
negativo.

• La sentencia para la creación del trigger es:

create trigger Descubierto

instead of update on Cuenta
for each row when (new.Saldo < 0)

begin

insert into Prestamos values
(old.Id_Sucursal, old.Id_Cuenta, −new.Saldo);
insert into Tomador
(select Nombre_Cliente, Id_Cuenta
from Cliente
where Cliente.Id_Cuenta = old.Id_Cuenta);
update Cuenta
set new.Saldo = 0
where new.Id_Cuenta = old.Id_Cuenta;

end;

El Estándar SQL



Página 6 de 15

Bases de Datos



3º Informática de Sistemas

2.5.

Crear una vista.


Una vista es la implementación de un esquema externo, según la
Arquitectura ANSI/SPARC, en el Modelo Relacional.

Una vista

• es una tabla virtual,
• no hay ninguna tupla que pertenezca a ella,
• el SGBD sólo conoce su definición.



Una vista se define utilizando al instrucción create view, se le da un
nombre a la vista y se indica la consulta que la va a calcular.


CREATE VIEW NombreVista [(Nombre de Atributos)] AS

<Sentencia de Consulta>;



Cuando se utiliza la vista (quizás como parte de alguna sentencia
SELECT) es cuando el SGBD realiza la consulta que define la vista y
presenta aquellas tuplas que forman parte de la vista.


Las actualizaciones en las vistas están muy restringidas por los
problemas que pueden originar en la consistencia de la información de la
base de datos.

El Estándar SQL



Página 7 de 15

Bases de Datos



3º Informática de Sistemas


3. El Sublenguaje de Control.

Los usuarios que han creado objetos en la base de datos (tablas,
vistas, ...) pueden conceder privilegios sobre ellos a otros usuarios por
medio de la orden GRANT.

También pueden conceder la capacidad de hacer concesiones sobre
sus objetos con la opción with grant option.

La sintáxis es:

GRANT Privilegio [, Privilegio ...]

ON Objeto
TO { Usuario | Papel | PUBLIC } [, {Usuario | Papel | PUBLIC}
...]}
[WITH GRANT OPTION]


Los privilegios pueden ser:

SELECT
INSERT
DELETE
UPDATE [(Lista de Columnas)]
ALL


Para denegar los privilegios otorgados, se utiliza la orden REVOKE:

REVOKE Privilegio [, Privilegio ...]

ON Objeto
FROM { Usuario | Papel | PUBLIC } [, {Usuario | Papel |
PUBLIC} ...]}



El Estándar SQL



Página 8 de 15

Bases de Datos



3º Informática de Sistemas

4. El Sublenguaje Huésped.


4.1. El lenguaje de programación de Oracle, PL/SQL.


Es un lenguaje estructurado en bloques. Un bloque agrupa declaraciones
y sentencias lógicamente relacionadas.

Un bloque PL/SQL puede tener tres partes:


• La declarativa, [DECLARE <Declaraciones>],

se declaran variables y objetos. Las variables tienen un nombre y son de
un tipo (o son del mismo tipo que un atributo definido en la base de
datos o son de un tipo del SQL):

v_nombre empleado.nombre%TYPE;
v_apellidos VARCHAR2(30);



• La ejecutiva, BEGIN <Sentencias>,

se manipulan esas variables y se procesan los datos. Se pueden utilizar
sentencias condicionales, iterativas o secuenciales, IF-THEN-ELSE,
FOR-LOOP, WHILE-LOOP, ...


• La de excepciones, [EXCEPTION <Excepciones>],

se procesan las excepciones y errores que se producen durante la
ejecución. Las excepciones pueden ser cualquier error definido por el
usuario o bien errores o excepciones de la