Presentación de PowerPoint - catedras.facet.unt.edu.ar · - Un procedimiento almacenado es un...
-
Upload
phamnguyet -
Category
Documents
-
view
246 -
download
0
Transcript of Presentación de PowerPoint - catedras.facet.unt.edu.ar · - Un procedimiento almacenado es un...
Catedra de Base de Datos
Facultad de Ciencias Exactas y Tecnología
Universidad Nacional de Tucumán
Ciclo Lectivo 2016
Cronograma
25-Oct Martes SQL. Transacciones. Procedimientos Almacenados. Triggers. Vistas. U6
25-Oct Martes TP6. SQL TP6
27-Oct Jueves TP6. SQL TP6
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
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`()
BEGINSELECT * 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
VARIABLESLos procedimientos almacenados pueden crear y usar variables locales, las cuales solo existen mientrasexista el procedimiento. Las variables no las puede usar otro proceso.
SENTENCIAS VALIDASUn procedimiento almacenado puede:- Seleccionar y modificar datos;- Crear tablas temporales y permanentes.- Llamar a otros procedimientos almacenados.- Referenciar objetos de base de datos.
SENTENCIA INVALIDASUn 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 disparan los 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 – TriggerSintaxis simplificada:CREATE TRIGGER nombre_triggerON nombre_tablaFOR {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 localesBEGIN--Sentencias[EXCEPTION]--Sentenciascontrol de excepcionEND <nombre_trigger>;
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
SQL – Trigger
SENTENCIA SQL OLD NEW
InsertNo definido; todos los campos toman valor NULL
Valores que serán insertados cuando se complete la orden.
UpdateValores originales de la fila, antes de la actualización
Nuevos valores que serán escritos cuando se complete la orden.
DeleteValores 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
EjemploCREATE 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 clientesFOR EACH ROWINSERT 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 clientesFOR EACH ROWINSERT 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 clientesFOR EACH ROWINSERT 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 ZaballaPérez.--Rev. Tca. Alfredo GoñiSarriguren , 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