Bases-de-Datos-Unidad-VI-2017-SQL-Parte-2

Vista previa del material en texto

Catedra de 
Base de Datos 
Facultad de Ciencias Exactas y Tecnología 
Universidad Nacional de Tucumán 
Ciclo Lectivo 2017 
Unidad 6: SQL. Transacciones. Procedimientos Almacenados. Triggers. Vistas. 
 
Programa Analítico de la Materia 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Transacciones 
Definición 
- Las transacciones, como los batches, son un grupo de ordenes que son tratadas como una 
unidad de trabajo que son admitidas si se ejecutan todas(COMMITED TRANSACTION) o 
rechazadas si falla alguna de ellas (ROLLBACK TRANSACTION). 
 
Tipos de Transacciones 
- Implícitas : Son ordenes naturales manejadas internamente por el SQL. Por ejemplo los 
UPDATE. 
- Explicitas: son las ordenes definidas por el usuario y delimitadas por START TRANSACTION 
y COMMIT. 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Transacciones 
Sintaxis 
 START TRANSACTION <nombre de la transaccion>; 
 <ordenes sql>; 
 COMMIT o ROLLBACK; 
 
Ejemplo 
START TRANSACTION; 
SELECT @A := presupuesto 
FROM departamentos_externos WHERE codigo =11; 
INSERT INTO departamentos( codigo, nombre, presupuesto ) 
VALUES ( 11, ‘Department test’, @A ); 
COMMIT; 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Transacciones 
Al realizar una transacción SQL hay que tener en cuenta que apenas se realice un INSERT, 
UPDATE o DELETE se genera un bloqueo sobre la tabla y que otros clientes no pueden acceder 
para escribir esta tabla. Otros clientes podrán realizar SELECTs sobre la tabla, pero no podrán 
ver los datos del primer cliente hasta que los mismos sean confirmados.- 
 
Consideraciones: 
- Un grupo de ordenes puede incrementar la performance de las operaciones, ya que todas de 
una vez realizan la recuperación. Pero si es una lista larga en una misma transacción podría 
causar problemas de concurrencia. 
- No se necesitan permisos para definir transacciones. 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Procedimientos Almacenados 
Definición 
- Un procedimiento almacenado es un conjunto de sentencias SQL y de control de flujo. 
- Un procedimiento almacenado es una colección de instrucciones guardadas en la Base de 
Datos. 
- La ventaja de un procedimiento almacenado es que al ser ejecutado , este se ejecuta 
directamente en el motor de base de datos, el cual usualmente corre en un servidor por 
separado. 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Procedimientos Almacenados 
Ventajas 
- Simplifican la ejecución de tareas repetitivas. 
- Corren mas rápido que las mismas instrucciones ejecutadas en forma interactiva. 
- Reducen el trafico a través de la red. 
- Pueden capturar errores antes que ellos puedan entrar a la base de datos. 
- Establece consistencia porque ejecuta las tareas de la misma forma. 
- Permite el desarrollo modular de aplicaciones. 
- Ayuda a proveer seguridad. 
- Puede forzar reglas y defaults complejos de los negocios. 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Procedimientos Almacenados 
Tipificación 
- Procedimientos almacenados definidos por el usuario: son procedimientos definidos por el 
usuario que se debe llamar explícitamente. 
- Triggers: Son procedimientos definidos por el usuario que se ejecutan automáticamente 
cuando se modifica un dato en un tabla. 
- Procedimientos del sistema: Procedimientos suministrados por el sistema. 
- Procedimientos Extendidos: Procedimientos que se hacen llamadas al sistema operativo y 
ejecutan tareas a ese nivel. 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Procedimientos Almacenados 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Procedimientos Almacenados 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Procedimientos Almacenados 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Procedimientos Almacenados 
Ventajas en el rendimiento 
- Un procedimiento almacenado se ejecuta más rápido que un batch porque: 
- El procedimiento almacenado ya ha sido analizado. 
- Ya se han resuelto las referencias a los objetos referenciados en el procedimiento 
almacenado. 
- No se necesita construir el árbol de búsqueda, el usa el que se hace en el momento de 
compilarlo. 
- No se necesita crear un plan de búsqueda, porque ya el procedimiento tiene uno. 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Procedimientos Almacenados 
Crear y borrar procedimientos almacenados 
- Sintaxis de creación: 
CREATE PROCEDURE nombre_procedimiento (IN parametros) 
BEGIN 
 Sentencias. 
END 
 
Ejemplo: 
CREATE PROCEDURE `prueba`.`ps_verpais`() 
 BEGIN 
 SELECT * FROM pais; 
END$$ 
 
 
- Sintaxis de borrado: 
DROP PROCEDURE nombre_procedimiento 
 
 Ejemplo: 
 DROP PROCEDURE ps_verpais 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Procedimientos Almacenados 
Ejemplo: 
- mysql> delimiter // 
 
- mysql> CREATE PROCEDURE simpleproc (OUT param1 INT) 
- -> BEGIN 
- -> SELECT COUNT(*) INTO param1 FROM t; 
- -> END 
- -> // 
- Query OK, 0 rows affected (0.00 sec) 
 
- mysql> delimiter ; 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Procedimientos Almacenados 
VARIABLES 
Los procedimientos almacenados pueden crear y usar variables locales, las cuales solo existen mientras 
exista el procedimiento. Las variables no las puede usar otro proceso. 
 
SENTENCIAS VALIDAS 
Un procedimiento almacenado puede: 
- Seleccionar y modificar datos; 
- Crear tablas temporales y permanentes. 
- Llamar a otros procedimientos almacenados. 
- Referenciar objetos de base de datos. 
 
SENTENCIA INVALIDAS 
Un procedimiento no puede ejecutar: 
- Use database. 
- Create view. 
- Create default. 
- Create rule. 
- Create procedure. 
- Create trigger. 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Trigger 
Un trigger es un procedimiento almacenado asociado con una tabla, el cual se ejecuta automáticamente 
cuando se modifica un dato de esa tabla. 
Un trigger se define asociado con una tabla para una o mas sentencias de manipulación de datos. Se puede 
definir para insert, update, o delete o cualquier combinación de ellos. 
 
APLICACIONES TÍPICAS 
- Hace modificaciones en cascada sobre tablas relacionadas. 
- Deshacer cambios que violan la integridad de los datos. 
- Forzar restricciones que son muy complejas para reglas y restricciones. 
- Mantener datos duplicados. 
- Mantener columnas con datos derivados. 
- Hacer ajustes de registros. 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Trigger 
Cuando se modifica un dato en una tabla que tiene declarado un trigger para esa sentencia, el trigger se 
“dispara”. El trigger se dispara una vez, independiente del numero de filas afectadas. 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Trigger 
Un trigger es parte de la transacción que causa el disparo. 
El trigger puede deshacer: 
- Asi mismo solamente. 
- Asi mismo y la sentencia que causa el disparo 
- La transacción total. 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Trigger 
Un trigger pueden: 
- Declarar variables locales. 
- Invocar procedimientos almacenados. 
 
Un trigger no puede: 
- Llamarse directamente. 
- Usar parámetros. 
- Definirse sobre tablas temporales o vistas. 
- Crear objetos permanentes de la base de datos. 
 
Las operaciones con registro minimo (como select into) no disparanlos triggers. 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Trigger 
Consideraciones 
Los triggers se pueden ejecutar antes (BEFORE) y/o después (AFTER) de que sean 
modificados los datos. 
Los triggers tienen dos palabras clave, OLD y NEW que se refieren a los valores que tienen las 
columnas antes y después de la modificación. 
Los INSERT permiten NEW, los DELETE solo OLD y los UPDATE ambas.- 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Trigger 
Sintaxis simplificada: 
CREATE TRIGGER nombre_trigger 
ON nombre_tabla 
FOR {INSERT | UPDATE | DELETE} 
AS 
Sentencias; 
 
Sintaxis completa: 
CREATE [OR REPLACE] TRIGGER <nombre_trigger> 
{BEFORE|AFTER} 
{DELETE|INSERT|UPDATE [OF col1, col2, ..., colN] 
[OR {DELETE|INSERT|UPDATE [OF col1, col2, ..., colN]...]} 
ON <nombre_tabla> 
[FOR EACH ROW [WHEN (<condición>)]] 
DECLARE 
--variables locales 
BEGIN 
--Sentencias 
[EXCEPTION] 
--Sentenciascontrol de excepcion 
END <nombre_trigger>; 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Trigger 
SENTENCIA SQL OLD NEW 
Insert 
No definido; todos 
los campos toman 
valor NULL 
Valores que serán 
insertados cuando se 
complete la orden. 
Update 
Valores originales de 
la fila, antes de la 
actualización 
Nuevos valores que 
serán escritos cuando 
se complete la orden. 
Delete 
Valores antes del 
borrado de la fila. 
No definido; todos 
los campos toman el 
valor NULL. 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Trigger 
Ejemplo 
CREATE TABLE clientes( 
id_cliente int not null auto_increment, 
Nombre varchar(20), 
Seccion varchar(20), 
Accion varchar(10) default ‘insertado’, 
PRIMARY KEY(id_cliente)) ENGINE = innoDB; 
 
CREATE TABLE auditoria_clientes ( 
Id int not null auto_increment, 
Nombre_anterior varhcar(20), 
Seccion_anterior varchar(20), 
Nombre_nuevo varchar(20), 
Seccion_nuevo varchar(20), 
Usuario varchar(20), 
Modificado datetime, 
Prceso varchar(10), 
Id_cliente int(4), 
PRIMARY KEY (Id)) ENGINE = innoDB; 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Trigger 
CREATE TRIGGER inserta_auditoria_clientes AFTER INSERT ON clientes 
FOR EACH ROW 
INSERT INTO auditoria_clientes(nombre_nuevo, sección_nuevo. Usuario, modificado, proceso, id_cliente) 
VALUES (NEW.nombre, NEW.sección, CURRENT_USER(), NOW(), NEW.acción, NEW.acción, NEW.id_clente); 
 
 
CREATE TRIGGER modifica_auditoria_clientes BEFORE UPDATE ON clientes 
FOR EACH ROW 
INSERT INTO auditoria_clientes(nombre_anterior, sección_anterior, nombre_nuevo, sección_nuevo. 
Usuario, modificado, proceso, id_cliente) 
VALUES (OLD.nombre, OLD.sección, NEW.nombre, NEW.sección, CURRENT_USER(), NOW(), NEW.acción, 
NEW.acción, NEW.id_clente); 
 
 
CREATE TRIGGER elimina_auditoria_clientes AFTER DELETE ON clientes 
FOR EACH ROW 
INSERT INTO auditoria_clientes(nombre_anterior, sección_anterior. Usuario, modificado, id_cliente) 
VALUES (OLD.nombre, OLDsección, CURRENT_USER(), NOW(), NEW.acción, OLD.id_clente); 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Vistas 
Una vista es un SELECT almacenado. El motor distingue dos tipos de tabla, las base y las 
derivadas. Una tabla base es una tabla existente en el motor de almacenamiento. Una tabla 
derivada, es la tabla que surge de cualquier combinación de tablas base, literales y/o 
funciones. 
 
Funciones de una vista 
- Proporcionar un nivel de seguridad, ya que permiten excluir datos apra que ciertos usuarios 
no los vean.- 
- Proporcionan un mecanismo para que los usuarios vean los datos en el formato que 
deseen.- 
- Representan una imagen consistente y permanente de la base de datos, incluso si la base de 
datos cambia su estructura. 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
SQL – Vistas 
Sintaxis 
CREATE VIEW nombre_vista [(column_list)] 
 AS <sentencias_sql>; 
 
Ejemplo 
CREATE VIEW ‘vista_guerra’ as 
SELECT * FROM guerras ORDER BY nombre; 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
Fundamentos de bases de datos / 
Abraham Silberschatz, Henry F. 
Korth /y/ S. Sudarshan.—(Tra. 
Fernándo Sáenz Pérez, Antonio 
García Cordero /y/ Jesús Correas 
Fernández.-- Rev. Tca. Luis Grau 
Fernández). McGraw Hill. Madrid 
/c.2008/5a. Edic. 
 
Introducción al SQL para Usuarios y 
Programadores / Cornelio et al / 
THOMSON / 2003 / 2da. Edic 
 
Fundamentos de sistemas de 
bases de datos / Ramez Elmasri 
/y/ Shamkant B. Navathe.—(Tra. 
Verónica Canivell Castillo, Beatriz 
Galán Espiga /y/ Gloria Zaballa 
Pérez.--Rev. Tca. Alfredo Goñi 
Sarriguren , Arturo Jaime Elizondo 
/y/ Tomás A. Pérez Fernández) 
Pearson Educación. Madrid 
/c.2002/3a. ed. 
 
Bibliográficas Principal 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
Bibliografía 
• Paper “A relational model for a large shared data banks”, E. F. Codd. ” – ACM – 1970. 
• “Procesamiento de bases de datos. fundamentos, diseño e implementación” - David M. Kroenke — (Tra. 
Ana Elizabeth García Hernández.--Rev. Tca. Juan Raúl Esparza Martínez). Pearson Educación – México - 
c.2003 – 8ª Edic. 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
 Sitio Web de la Cátedra http://catedras.facet.unt.edu.ar/bd 
BASES DE DATOS 
Bases de Datos Ing. Franco D. Menendez 
http://catedras.facet.unt.edu.ar/bd