PDF de programación - Bases de Datos Relacionales: SQL y el PL/SQL de Oracle - Administración de Bases de Datos

Departamento de Lenguajes y Ciencias de la
Computación Universidad de Málaga

Administración de

Bases de Datos

(Ingeniería Técnica en Informática de Gestión)

Bases de Datos Relacionales:

SQL y el PL/SQL de Oracle

E.T.S.I. Informática

J. Galindo Gómez

SQL (Structured Query Language)

SQL

• SQL está en continua evolución: Es una evolución del lenguaje
SEQUEL de D.D. Chamberlin y R.F. Boyce (1974) y fue implementado
por primera vez por IBM en su BDR llamado SYSTEM R.
– ISO (International Standards Organization) y ANSI (American National

Standards Institute) desarrollaron una versión estándar en 1986,
llamada SQL86 o SQL1. Posteriormente, se desarrolló SQL92 o
SQL2. Actualmente se desarrolla SQL3, que incluye conceptos de BD
orientadas a objetos.

• SQL es un lenguaje estándar para GESTIÓN de BDR:

– Está incluido en muchos SGBD (DBMS), como DB2 (de IBM),

Oracle, Ingres, Informix, Sybase, Access, SQL Server...

• Las mismas sentencias sirven en distintos SGBD.
• Si se usan sólo las características estándares facilita la tarea de

migrar de SGBD fi Hay funciones no estándar en algunos SGBD.

– Fácil de usar y aprender: Tiene similitudes con el Álgebra Relacional,

aunque se parece más al Cálculo y es más fácil e intuitivo que ambos
lenguajes formales.

– Aquí se incluye una introducción a SQL estándar con algunos

comentarios sobre el SGBD Oracle.

2

1

SQL (Structured Query Language)

SQL

• SQL es un lenguaje COMPLETO: Incluye sentencias para
– DDL y DML: Permite definir esquemas, consultar, borrar, actualizar...
– Definición de vistas: Para ver la BD de distintas formas.
– Seguridad: Permisos de acceso distintos para cada usuario.
– Definir restricciones de integridad: Integridad referencial...
– Especificar control de transacciones: Para grandes empresas,

recuperación de errores, archivos históricos...

– Puede incrustarse en lenguajes de alto nivel (C, C++, Pascal, COBOL...).
– Permite operaciones tan complejas que su total definición es complicada:

Sólo veremos un subconjunto de las operaciones posibles.

• ESQUEMA (schema): Conjunto de elementos (tablas, vistas,

permisos...) que pertenecen a la misma BD. Cada esquema tiene un
nombre y un usuario propietario del esquema:

CREATE SCHEMA <Nombre> AUTHORIZATION <Usuario>;
• CATÁLOGO (catalog): Conjunto de esquemas. Tiene un esquema es-
pecial llamado INFORMATION_SCHEMA que provee información sobre los
demás esquemas, usuarios autorizados, definiciones de dominio, restric-
ciones de integridad referencial (sólo entre tablas del mismo catálogo)...

SQL (Structured Query Language)

SQL

• TIPOS de DATOS de los atributos de las relaciones:

– Enteros de distintos tamaños: INTEGER o INT y SMALLINT.
– Reales de distinta precisión: FLOAT(p), REAL, DOUBLE PRECISION, o el más

genérico DECIMAL(precisión, escala) (o NUMBER en Oracle), donde precisión=“número
total de dígitos” (de 1 a 38 en Oracle) y escala=“número de decimales” (de -84 a 127 en
Oracle, con valor 0 por defecto). También admite DEC(p,e) o NUMERIC(p,e).

– Caracteres: CHAR(n) o CHARACTER(n), n=longitud fija (por defecto n=1). También
VARCHAR(n), n=longitud máxima (Oracle aconseja usar VARCHAR2 con 4000 de máximo).
Para cadenas muy largas usar LONG (máximo 2GB) o CLOB (Character Large OBject , máx. 4GB).
• NCHAR y NVARCHAR2 usan el juego de caracteres Nacional definido al crear la BD.
– Cadenas de bits (para gráficos, sonidos, ficheros binarios...): BIT(n), n=longitud fija o

BIT VARYING(n), con n=longitud máxima. Por defecto n=1. En Oracle se prefiere
usar RAW(tamaño_fijo_máx_2000bytes) o LONG RAW (sin argumento y con un tamaño
máximo de 2GB). Últimamente Oracle aconseja usar LOB o BLOB (Binary Large OBject,
máximo 4GB), o también BFILE para almacenar la localización de un fichero binario.

– Fecha y Hora: DATE (año, mes, día: YYYY-MM-DD). TIME (horas, minutos, segundos:

HH:MM:SS). TIMESTAMP incluye ambos (sinónimo a DATE en Oracle).

• Se usan las funciones TO_CHAR y TO_DATE para convertir un dato de tipo fecha a

texto y viceversa.

• Definiciones de DOMINIOS: Crear nuevos tipos de datos:
CREATE DOMAIN <Nombre> AS <Tipo>;

– Ejemplo: CREATE DOMAIN NIF_TYPE AS CHAR(12);
– Utilidades: Hacer las definiciones más legibles y hacer más fáciles los cambios de

dominio de ciertos atributos. En Oracle se traduce como CREATE TYPE.

3

4

2

DDL de SQL: CREATE TABLE

DDL de SQL

• Comando CREATE TABLE: Crea una nueva relación/tabla base

con su nombre, atributos (nombres y dominios) y restricciones:

CREATE TABLE <NombreTabla> (

<NombreA1> <TipoA1> <Valor por defecto>

<Restricciones para A1>,

<NombreA2> <TipoA2> <Valor por defecto>

<Restricciones para A2>,

...
<RestriccionesTabla>);


• Valor por defecto: Se asigna al atributo si no se especifica otro valor.

– DEFAULT <Valor>

• Restricciones para un Atributo: Son restricciones que afectan sólo a un

atributo particular.

• Restricciones para la Tabla: Pueden afectar a uno o más atributos.

– Si una restricción involucra un único atributo, esta restricción puede escribirse

como restricción para ese atributo o al final, como restricción para la tabla.

– Si una restricción involucra varios atributos debe escribirse como restricción

para la tabla.

DDL de SQL: CREATE TABLE

DDL de SQL

• Restricciones: Pueden tener un Nombre, que se escribe al principio, con el
formato: CONSTRAINT <nombreR> y un <Estado> que se verá después.
– El nombre es útil para referirse a la restricción (para borrarla, cambiar su estado...).
– Cuando se incumple una restricción, el SGBD debe dar un error indicando el nombre de

dicha restricción: Escoger un nombre adecuado es útil para saber el motivo del error.
• Restricciones de Atributos (puede haber varias por cada uno):

– NOT NULL

• Atributo que no admite valores NULL.
• Es la única restricción que SÓLO puede ponerse como restricción de

atributo. Las demás pueden también ponerse como restricción de tabla.

– PRIMARY KEY

• Clave o llave primaria (no admite NULL)

– UNIQUE

• Llave candidata.

– CHECK (<Condición>)

• Comprueba que se cumple esa condición (si el atributo es no nulo).

– REFERENCES...

• Llave externa (o foránea): Debe indicarse la tabla referenciada.

5

6

3

DDL de SQL: CREATE TABLE

DDL de SQL

• Restricciones de Tabla:

– PRIMARY KEY (<Atributos de la Llave Primaria>)

• La llave primaria puede estar formada por varios atributos.

– UNIQUE (<Llave Candidata o Secundaria>)
– CHECK (<Condición>)

• La condición puede estar formada por varios atributos, pero se interpretan
Tabla de Restricción

como los atributos de la misma fila.

– FOREIGN KEY (<Llave Externa>) REFERENCES <Tabla>(<Atributos>)

[ON DELETE {CASCADE | SET NULL | SET DEFAULT}]
[ON UPDATE {CASCADE | SET NULL | SET DEFAULT}]

Si se borra la llave
referenciada, se
borran las tuplas que
la referencian
Si se actualiza la
llave referenciada, se
actualizan las tuplas
que la referencian

Si se borra/actualiza
la llave referenciada,
se ponen a NULL los
valores que la
referencian (llave
externa).

Si se borra/actualiza
la llave referenciada,
se ponen los valores
que la referencian a su
valor por defecto.

Las Restricciones en Oracle

DDL de SQL

• Propiamente, Oracle no distingue entre restricciones de tabla

y restricciones de Atributo (o de Columna):
– Oracle las almacena todas en la vista USER_CONSTRAINTS del

Diccionario de Datos, con atributos como:

• CONSTRAINT_NAME: Nombre de la restricción. Si no se le ha dado uno, Oracle le

asigna uno con un código.

• TABLE_NAME: Nombre de la tabla con dicha restricción.
• CONSTRAINT_TYPE: Es un carácter (P para PRIMARY KEY, U para UNIQUE, R

para una restricción de integridad referencial, C para una restricción de tipo CHECK
(o NOT NULL) con la condición almacenada en el atributo SEARCH_CONDITION...)

• STATUS: Estado de la restricción (ENABLE o DISABLE).

– La diferencia entre restricciones de tabla y de atributo es sólo a nivel

sintáctico sobre dónde y cómo deben escribirse:

• Las restricciones que involucren varios atributos deben ser consideradas

forzosamente como restricciones de tabla.

• Las restricciones NOT NULL y DEFAULT son forzosamente restricciones de atributo,

aunque estrictamente hablando DEFAULT no es una restricción.

• Oracle 8 no implementa las opciones de ON UPDATE ni la opción ON

DELETE SET DEFAULT.
– Por defecto, Oracle no permite borrar una tupla si existe una o varias
tuplas que estén haciendo referencia a algún valor de la tupla que se
intenta borrar (ORA-2292).

• Por defecto, las restricciones de tipo CHECK sólo se exigen si los

atributos involucrados tienen valores distintos de NULL.

7

8

4

DDL de SQL estándar: DROP y ALTER

DDL de SQL

• Borrar Esquemas y Tablas: DROP

– DROP SQUEMA <NombreEsquema [CASCADE | RESTRICT]

• CASCADE borra el esquema totalmente y RESTRICT sólo si está vacío.

– DROP TABLE <NombreTabla> [CASCADE | RESTRICT]

• CASCADE: Borra la tabla (y su contenido). Si hay referencias sobre ella

(llaves externas en otras tablas), dichas restricciones son borradas.

• RESTRICT: Borra la tabla si no hay referencias sobre ella.

– En Oracle RESTRICT no existe y es la opción por defecto. Para borrar las

restricciones que hacen referencia a la tabla se usa CASCADE CONSTRAINTS.

• Modificar Tablas: ALTER TABLE <NombreTabla> <ACCIÓN>
– Añadir columna: ADD (<NombreA, Tipo, Restric_de_Columna>);

• En Oracle, para modificar un atributo se usa MODIFY en vez de ADD.

– Borrar columna: DROP <NombreA> [CASCADE|RESTRICT];
– Añadir restric. de Atributo: ALTER <NombreA> SET <RestricA>;
– Borrar restric.: ALTER <NombreA> DROP <TipoRA: DEFAULT...>;
– Borrar restric. de tabla (debe tener un nombre):

– Añadir restric. de tabla:

DROP CONSTRAINT <NombreC> CASCADE;
ADD (<Restric_de_Tabla>);

9

ALTER TABLE en Oracle 8

DDL de SQL

• Modificar Tablas: ALTER TABLE <NombreTabla> <ACCIÓN>

Añadir Columna: ADD (<NombreA> <Tipo> [<Restric_Columna>]);
Añadir Restricción de Tabla: ADD (<Restric_de_Tabla>);
Modif. Col.: MODIFY (<Nom