procedimientos almacenados activadores...
Post on 29-Sep-2018
244 Views
Preview:
TRANSCRIPT
Bases de datos – Procedimientos AlmacenadosU. Nacional – Prof. Elizabeth León
Procedimientos Almacenados
Activadores (triggers)Curso Bases de Datos
Por
Elizabeth León Guzmán, Ph.D.
Profesora
Ingeniería de Sistemas
Grupo de Investigación MIDAS
Bases de datos – Procedimientos AlmacenadosU. Nacional – Prof. Elizabeth León
Procedimientos Almacenados(Stored Procedures)
Es un conjunto de sentencias SQL y de control de flujo, que
son definidas en bloque para que sean realizadas al mismo
tiempo, que por lo general cumplen con una tarea
específica. Estas sentencias son almacenadas físicamente
en la base de datos.
“Encapsulan un proceso grande y complejo”“Simplifican la ejecución de tareas repetitivas”
Bases de datos – Procedimientos AlmacenadosU. Nacional – Prof. Elizabeth León
Procedimientos Almacenados
DELIMITER $$
CREATE PROCEDURE promocion_libros(id INT)
BEGIN
SET @precio = (SELECT precio FROM Libro WHERE id_libro=id);
IF @precio < 50000 THEN
INSERT INTO Promocion VALUES(id,@precio*0.9);
ELSEIF @precio > 50000 AND precio < 100000 THEN
INSERT INTO Promocion VALUES(id,@precio*0.85);
END IF;
END $$
DELIMITER ;
Proceso a elaborar en un procedimiento almacenado
Adicionar una nueva promoción a la tabla de
Promociones (producto y nuevo valor): Si el
precio del libro es menor de $50.000 se le
aplicará un descuento del 10%, si
el precio del libro esta entre $50.000 y
$100.000 se le hará un descuento del 15%.
Bases de datos – Procedimientos AlmacenadosU. Nacional – Prof. Elizabeth León
Procedimientos Almacenados
Evita
sobrecarga de
transmisión de
datos
salientes y
entrantes.
Almacenado en
La BD
Servidor
DELIMITER $$
CREATE PROCEDURE promocion_libros(id INT)
BEGIN
SET @precio = (SELECT precio FROM Libro WHERE id_libro=id);
IF @precio < 50000 THEN
INSERT INTO Promocion VALUES(id,@precio*0.9);
ELSEIF @precio > 50000 AND precio < 100000 THEN
INSERT INTO Promocion VALUES(id,@precio*0.85);
END IF;
END $$
DELIMITER ;
- Reducen el tráfico a través de la red
Bases de datos – Procedimientos AlmacenadosU. Nacional – Prof. Elizabeth León
Procedimientos Almacenados
– 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 – Procedimientos AlmacenadosU. Nacional – Prof. Elizabeth León
- Corren más rápido que las mismas instrucciones ejecutadas en
forma interactiva
Procedimientos Almacenados
Arbol de consulta
Cache
Plan consulta Datos
Batch
-----
-----
-----
Ejecución interactiva
Bases de datos – Procedimientos AlmacenadosU. Nacional – Prof. Elizabeth León
Procedimiento Almacenado
Arbol de consulta
Creación de un procedimiento almacenado
Create PROC p_nombre
--------
--------
--------
Sysprocedures
Bases de datos – Procedimientos AlmacenadosU. Nacional – Prof. Elizabeth León
Cache
Procedimiento AlmacenadoEjecución de un procedimiento almacenado
Plan de consulta
Execute p_nombre
--------
--------
--------
Sysprocedures
plan de consulta
no utilizado
en caché de
procedimientos
No
Ejecución de un procedimiento almacenado
Tipos de procedimientos almacenados
• Procedimientos almacenados definidos por el usuario– Son procedimientos definidos por el usuario que se
deben llamar explícitamente
• Triggers– Son procedimientos definidos por el usuario que se
ejecutan automáticamente cuando se modifica un dato en una tabla
• Procedimientos del sistema– Procedimientos suministrados por el sistema
• Procedimientos extendidos– Procedimientos que hacen llamadas al sistema operativo
y ejecutan tareas a ese nivel
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, él 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
Crear y borrar procedimientos almacenados
• Sintaxis simplificada para create:DELIMITER $$
CREATE PROCEDURE procedure_name( arg1 <DATA_TYPE>… arg2 <DATA_TYPE>… )
BEGIN…END $$
DELIMITER ;
• Ejemplo:DELIMITER $$
CREATE PROCEDURE queryJoin1(id1 INT, id2 INT)BEGIN
SELECT * FROM table1 NATURAL JOIN table2 WHERE col1 = id AND col2 = id2;
END $$DELIMITER ;
• Sintaxis simplificada para drop:DROP PROCEDURE procedure_name
• Ejemplo:DROP PROCEDURE queryJoin1
Ejecutar procedimientos almacenados
• Sintaxis simplificada:CALL procedure_name
• Ejemplo:CALL how_do_you_turn_this_on()
Variables• Los procedimientos almacenados pueden crear y usar variables
locales
• En MySQL las variables se pueden declarar de dos formas distintas para variar el scope de las mismas.
Variables de Sesión
Variables de procedimiento.
Variables - ProcedimientoSintaxis general:
DECLARE variable_name DATATYPE DEFAULT default_value;
variable_name: Nombre de la variable
default_value : Valor por defecto al inicializar
Ejemplos:
DECLARE ventas INT DEFAULT 0
DECLARE estado VARCHAR(30) DEFAULT ‘listo’
Luego de declarar una variable se puede actualizar su valor así:
DECLARE total INT DEFAULT 0
SET total = 10;
Variables - ProcedimientoAdemás se puede asignar el valor de una consulta a una variable usando la palabra reservada INTO:
DECLARE total INT DEFAULT 0
SELECT COUNT(*) INTO total FROM productos
Variables - ProcedimientoEjemplo:DELIMITER $$
CREATE PROCEDURE promocion_libros(id INT)
BEGIN
DECLARE var_precio INT;
SELECT precio INTO var_precio FROM Libro WHERE
id_libro=id;
IF precio < 50000 THEN
INSERT INTO Promocion VALUES(id,precio*0.9);
ELSEIF @precio > 50000 AND precio < 100000 THEN
INSERT INTO deportivos VALUES(id,precio*0.85);
END IF;
END $$
DELIMITER ;
Variables – de sesiónSintaxis general una variable de sesión es aquella cuyo scope es toda la sesión en que se consulta la base de datos. Es decir que es accesible en todo momento hasta que finalice la conexión a la DB.
Para inicializar una variable de sesión se hace mediante la palabra reservada set y se utiliza un @ antes del nombre.
CREATE PROCEDURE test_var()
BEGIN
DECLARE var2 INT DEFAULT 1;
SET var2 = var2 + 1;
SET @var2 = @var2 + 1;
SELECT var2, @var2;
END;
Variables – de sesiónA continuación se ilustra que la variable de sesión @var2 conserva su valor luego de la ejecución del procedimiento y se puede acceder por fuera del mismo.
Se inicializa la variable @var2
SET @var2 = 1
CALL prc_test();
Al llamar el procedimiento se muestra lo siguiente:
var2 @var2
--- ---
2 2
CALL prc_test();
Al llamar el procedimiento se muestra lo siguiente:
var2 @var2
--- ---
2 3
Sentencias válidas e inválidas
• Un procedimiento almacenado puede:– Seleccionar y modificar datos
– Crear tablas temporales y permanentes
– Llamar otros procedimientos almacenados
– Referenciar objetos de bases de datos
• Un procedimiento almacenado no puede ejecutar:– use database
– create view
– create default
– create rule
– create procedure
– create trigger
Procedimientos almacenados y permisos
• Para permitir que otros usen un procedimiento
almacenado, el propietario debe dar los respectivos
permisos
• Sintaxis simplificada:GRANT EXECUTE ON PROCEDUREprocedure_name TO user_name
• Ejemplo:GRANT EXECUTE ON PROCEDURE promocion_libros to
‘jaime'@'localhost';
Parámetros de entrada• Un parámetro de entrada es una variable
que recibe el PA.
Definir parámetros de entrada
• Sintaxis simplificada:DELIMITER $$
CREATE PROCEDURE procedure_name( arg1 <DATA_TYPE>… arg2 <DATA_TYPE>… )
BEGIN…END $$
DELIMITER ;
Ejemplo:
DELIMITER $$
CREATE PROCEDURE queryJoin1(id1 INT, id2 INT)BEGIN
SELECT * FROM table1 NATURAL JOIN table2 WHERE col1 = id AND col2 = id2;
END $$DELIMITER ;
Paso de parámetros por posición (No
aplica en MySQL)• Sintaxis para paso por posición :
[exec | execute] procedure_name value [, value...]
• Ejemplo (sybase):exec proc_author_info "Ringer", "Albert"
au_lname au_fname title
-------- -------- -----
Ringer Albert Is Anger the Enemy?
Ringer Albert Life Without Fear
• Los parámetros se deben pasar en el mismo orden en que
ellos aparecen en la sentencia create procedure
• Como este método es más propenso a errores, se aconseja
el paso por nombre
Paso de parámetros por nombre(No
aplica en MySQL)• Sintaxis para paso por nombre:
[exec | execute] procedure procedure_nameparameter_name = value [, parameter_name = value ]
• Ejemplo (sybase):exec proc_author_info
@lname = "Ringer", @fname = "Albert"
au_lname au_fname title
-------- -------- -----
Ringer Albert Is Anger the Enemy?
Ringer Albert Life Without Fear
• Los nombres de los parámetros en la sentencia exec deben concordar con los nombres de los parámetros usados en la sentencia create procedure
• Los parámetros pueden pasar en cualquier orden
Valores por default (No aplica en
MySQL)• Se puede asignar un valor por default a un parámetro cuando
él no se indica en la sentencia exec
• Ejemplo (sybase):create proc proc_state_authors
(@state char(2) = "CA")
as
select au_lname, au_fname, state
from authors
where state = @state
return
exec proc_state_authors -- No state value passed
au_lname au_fname state
-------- -------- -----
White Johnson CA
Green Marjorie CA
...
Parámetros de entrada: errores comunes
• Los valores que se pasan no tienen el mismo tipo de datos
que los parámetros definidos
• Olvido de uno o más parámetros
• Los valores para los parámetros se pasan en un orden errado
DELIMITER $$
CREATE PROCEDURE insertar_tecnico(nombres VARCHAR(45), apellidos VARCHAR(45) )
BEGIN
INSERT INTO tecnico (nombres,apellidos) VALUES( nombres, apellidos );
SET @id_tec = (SELECT id_tecnico FROM tecnico);
SET @id_equ = (SELECT id_equipo FROM equipo WHERE nombre_equipo = equipo);
UPDATE equipo SET tecnico_id_tecnico = @id_tec WHERE id_equipo = @id_equ;
END;
$$
DELIMITER ;
Parámetros de entrada: Ejemplo
Parámetros de entrada
• Crear un procedimiento almacenado que tenga un parámetro de entrada:
CREATE PROCEDURE carros_por_color(color VARCHAR(45))
BEGIN
SELECT * FROM carros WHERE color_carros = color;
END
• Ejecutar el procedimiento con y sin un valor para el parámetro de entrada. Una sentencia fallará:
CALL carros_por_color()
CALL carros_por_color(‘rojo’)
Parámetros de entrada - IN
• Es el modo por defecto
• significa que se debe pasar un
parámetro al procedimiento
almacenado.
• es protegido por lo que la modificación
dentro del procedimiento no modificará
la variable por fuera del mismo.
• Se crea una copia del parámetro en el
procedimiento.
Parámetros de entrada - IN
• Ejemplo:
DELIMITER $$
CREATE PROCEDURE OficinaPorPais(IN nombrePais
VARCHAR(255))
BEGIN
SELECT * FROM oficina WHERE pais = nombrePais;
END $$
DELIMITER ;
Parámetros de Salida- OUT
• Si el parámetro que se pasa al
procedimiento es modificado se
modifica afuera del procedimiento.
• El valor es pasado al programa que
llamo el procedimiento al final de la
ejecución.
• El procedimiento No puede acceder al
valor inicial del parámetro.
Parámetros de entrada – OUT
• Ejemplo:
DELIMITER $$
CREATE PROCEDURE ContarEnviosPorEstado(
IN estadoEnvio VARCHAR(25),
OUT total INT)
BEGIN
SELECT count(idEnvio) INTO total FROM envios
WHERE estado = estadoEnvio;
END$$
DELIMITER ;
Parámetros de entrada – OUT
• En el ejemplo anterior se cuentan los estados
que tengan el estado igual a ‘estadoEnvio’, se
almacena en la variable ‘total’ y como esta es
de tipo out se obtiene el valor de la misma
desde afuera de la función. La función se llama
de la siguiente manera:
• CALL CountOrderByStatus(‘enviado',@total);
• Para ver el resultado: SELECT @total;
Parámetros de Entrada/Salida-
INOUT• Es una combinación de IN y OUT.
• El valor se pasa como parámetro y
puede ser accedido y modificado
dentro del procedimiento.
Parámetros de entrada/Salida –
INOUT• Ejemplo:
DELIMITER $$
CREATE PROCEDURE set_counter(INOUT count INT(4),IN inc INT(4))
BEGIN
SET count = count + inc;
END$$
DELIMITER ;
• Debido a que count es INOUT su valor inicial puede
ser accedido y modificado dentro del procedimiento,
debido a ello al incrementar su valor este cambio se
vera reflejado por fuera del procedimiento
Retorno de valores
• Un valor de retorno es una variable que
retorna una funcion luego de ser llamada.
CONCAT('FIRST ', 'SECOND');
• La función “concat”recibe dos cadenas de
caracteres y retorna el resultado al
concatenarlas.
Crear funciones que retornan valores
• Sintaxis simplificada:CREATE FUNCTION function_name
( arg1 <data_type>… arg2 <data_type>… ) RETURN <data_type>BEGIN…RETURN var_name;END
• Ejemplo:DELIMITER $$
CREATE FUNCTION contarLibrosPorNombreYPrecio(nombre CHAR(250),
precio INT ) RETURNS CHAR(250)
BEGIN
DECLARE cuenta INT DEFAULT 0;
SELECT COUNT(*) INTO cuenta FROM libros WHERE nombre_libro
LIKE CONCAT(‘%’,nombre,‘%’) AND precio_libro = precio;
RETURN cuenta;
END $$
DELIMITER ;
Usar Funciones que retornan valores
• Sintaxis simplificada:function_name( arg1, … arg2… )
• Ejemplo:SET @años_10000 =
contarLibrosPorNombreYPrecio(‘años’, 10000);
Límite de anidamiento para procedimientos
(Sybase, No aplica en MySQL)
• Los procedimientos almacenados pueden llamar otros
procedimientos almacenados– El máximo nivel de anidamiento es 16
– La variable @nestlevel contiene el nivel de anidamiento
actual
• Si se excede el nivel máximo:– Se abortan los procedimientos pendientes
– El servidor retorna un error
Planes de búsqueda• Un plan de búsqueda es un conjunto ordenado de etapas que se
requieren para acceder los datos, incluyendo información sobre:
– Si usar o no un índice
– El índice a usar
– El orden en el cual las tablas se deben encadenar
• Los planes de búsqueda son creados por el optimizador de búsquedas– El optimizador de búsquedas usa información acerca de los objetos de base de
datos para producir el plan
• Los planes de búsqueda creados para los procedimientos, se reutilizan– Cuando se ejecuta un procedimiento almacenado, el servidor chequea el caché
del procedimiento para un plan no usado
– Si hay un plan de búsqueda no utilizado, el servidor lo usa
– Si no hay un plan de búsqueda no utilizado, el servidor genera uno nuevo del árbol de búsqueda en sysprocedures
Planes de búsqueda sub-óptimos
• El plan de búsqueda creado para la una ejecución de un procedimiento
almacenado puede que no sea el plan de búsqueda óptimo para la
siguiente ejecución del procedimiento almacenado
– Las dos ejecuciones pueden usar parámetros de entrada muy diferentes
– Se pueden haber añadido nuevos índices entre las dos ejecuciones
– El tamaño de las tablas accedidas pueden haber cambiado significativamente
entre las dos ejecuciones
• Hay tres formas para forzar al servidor a generar un nuevo plan de
búsqueda (SQL Server, no aplica en MySQL)
– Usar with recompile en el procedimiento
– Usar with recompile cuando se ejecute el procedimiento
– Usar sp_recompile
• En un procedimiento, usar la opción with recompile para
forzar al servidor a crear un nuevo plan de búsqueda cada
vez que se ejecute el procedimiento
• Sintaxis simplificada:create proc procedure_namewith recompileasstatementsreturn
Crear procedimientos con recompile
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
¿Puede ser
llamado
explícitamente?
¿Puede ser ejecutado
automáticamente?
¿Puede usar
Parámetros?
Procedimientos
almacenados
definidos por el
usuario
Si No Si
Trigger No Si No
Aplicaciones Típicas de triggers
• Hacer 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
Definición de un trigger
• Un trigger se define asociado con una tabla para una o
más sentencias de manipulación de datos– Un trigger se puede definir para insert, update, o delete.
Activación de un 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, independientemente del número
de filas afectadas
– El trigger se dispara aunque no hayan filas afectadas
• Un trigger es parte de la transacción que causa el
disparo
• El trigger puede deshacer:– Así mismo solamente
– Así mismo y la sentencia que causa el disparo
– La transacción total
Triggers y transacciones
Reglas para triggers
• Los triggers pueden:– Declarar variables locales
– Invocar procedimientos almacenados
• Los triggers no pueden:– Llamarse directamente
– Usar parámetros
– Definirse sobre tablas temporales o vistas
– Crear objetos permanentes de base de datos
Crear triggers
• Sintaxis simplificada:CREATE TRIGGER name_trigger trigger_time trigger_event ON table_name
…
• Ejemplo:DELIMITER $$
CREATE TRIGGER tr_hist_equ AFTER INSERT ON equipo
FOR EACH ROW BEGIN
SET @id = NEW.id_equipo;
INSERT INTO historico_equipo (id_equipo) VALUES (id);
END;
$$
DELIMITER ;
Borrar Triggers
• Sintaxis simplificada:
drop trigger trigger_name
• Ejemplo:drop trigger trg_i_sales
Procedimientos del sistema para
Triggers
• SHOW TRIGGERS;Muestra todos los triggers de la base de datos.
• Los triggers no pueden ser renombrados en MySQL.
Triggers - ejemplo
• Se tiene la tabla autores y la tabla autores_eliminados en donde se guardan registros de los autores eliminados.
• Crear un trigger que guarde el nombre de cada autor eliminado.DELIMITER $$
CREATE TRIGGER trigger_autores_eliminado AFTER DELETE ON
autor
FOR EACH ROW BEGIN
SET @nombre = OLD.nombre_autor;
INSERT INTO autores_eliminados
(nombre_autor) VALUES (nombre);
END;
$$
DELIMITER ;
Triggers - ejemplo
• Ejecutar un delete y ver la tabla autores_eliminados:DELETE FROM autor WHERE id_autor<=20;
SELECT * FROM autores_eliminados
• Ejecutar un delete que no afecta filas y ver la tabla autores_eliminados :DELETE FROM autor WHERE 1 = 2;
SELECT * FROM autores_eliminados
• Borrar los objetos de base de datos creados:DROP TABLE autor, autores_eliminados;
OLD y NEW
• Dentro de los triggers se pueden utilizar los alias OLD y
NEW los cuales hacen referencia a las columnas
eliminadas/actualizadas o insertadas respectivamente.
• Ejemplo:
• OLD.columna_eliminada
• NEW.columna_insertada
Uso de la tabla NEW - INSERT- ejemplo
En el siguiente ejemplo el trigger se dispara cuando se inserta un equipoNuevo en la tabla ‘equipo’, al hacer esto se guarda un registro de dichoequipo en la table ‘histórico_equipo’ la cual guarda un histórico de losequipos que han pasado por la base de datos, con el fin de llevar elregistro inclusive luego de que estos desaparezcan.
DELIMITER $$
CREATE TRIGGER tr_hist_equ AFTER INSERT ON equipo
FOR EACH ROW BEGIN
SET @id = NEW.id_equipo;
INSERT INTO historico_equipo (id_equipo) VALUES (id);
END;
$$
DELIMITER ;
OLD - DELETE - ejemplo
En el siguiente ejemplo el trigger se dispara cuando se elimina un autor de la tabla ‘autor’ y elimina todos los libros asociados a dichoautor.
DELIMITER $$
CREATE TRIGGER trigger_autores_eliminado AFTER DELETE ON
autor
FOR EACH ROW BEGIN
DECLARE id_a INT;
id_a = OLD.id_autor;
DELETE FROM libros WHERE id_autor = id_a;
END;
$$
DELIMITER ;
Uso de la tabla NEW - UPDATE - ejemploDELIMITER $$
CREATE TRIGGER trigger_autores_actualizado AFTER
UPDATE ON autor
FOR EACH ROW BEGIN
SET @nombre = NEW.nombre_autor;
UPDATE libros SET nombre_autor = nombre WHERE
id_autor = OLD.id_autor;
END;
$$
DELIMITER ;
• En el ejemplo anterior se muestra como actualizar el registro de la table libros cuando el nombre del autor cambia, se haceusando el alias NEW para obtener el Nuevo nombre y OLD para obtener el id del autor que se cambio y efectuar el cambio en el update sobre libros.
Deshacer un trigger (SQL server, no aplica en MySQL)
• Para deshacer un trigger, declarar un punto de grabación y luego hacer el rollback– Un rollback sin punto de grabación deshace toda la transacción
Procedimiento almacenadoCaso A begin tran
...
insert ...
print "in sp"
...
commit tran
print "sp done"
Triggersave tran s1
....
rollback tran s1
print “tr done”
return
Procedimiento almacenadoCaso B begin tran
...(este caso insert ...ocasiona un print "in sp"error) ...
commit tranprint "sp done"
Trigger
begin tran s2
....
rollback tran s2
print “tr done”
return
• rollback trigger deshace el trigger y la sentencia que lo disparó
• Sintaxis:rollback trigger [with raiserror error_number[error_statement] ]
• Ejemplo:create trigger trg_i_publishers
on publishers
for insert
as
if @@rowcount > 1
begin
rollback trigger with raiserror 40031
"You cannot insert more than one
publisher at a time."
return
end
Deshacer un trigger(sybase, no aplica
en MySQL)
Procedimiento almacenadoCase C begin tran
...
insert ...
print "in sp"
...
commit tran
print "sp done"
Trigger
....
....
rollback trigger
print “tr done”
return
Deshacer un trigger(no aplica en
MySQL)
Triggers recursivos (no aplica en MySQL)
• Un trigger recursivo es aquel que se dispara cuando
modifica su propia tabla
• Por default, un trigger que modifica su propia tabla no
causa un disparo recursivo del trigger
Métodos para integridad de datos
• Dos métodos para implementar integridad de datos
Integridad de Domino Integridad
de
Entidad
Integridad
referencial
Constraints Chequear Constraints Constraints
de llave
primaria, y
constraints
únicos.
Constraints
referencial
es.
Objetos de la BD Reglas (Sybase, SQL
Server, No en MySQL)
Indices Triggers
Restricciones vs triggers
• Ventajas de las restricciones (constraints):– Las restricciones no requieren codificación adicional
– Es mejor para chequear datos antes de ingresarlos a la base
de datos-
• Ventajas de los triggers:– Muy flexible
• Los triggers pueden hacer cualquier cosa que se pueda codificar
– Mejor para las reglas complejas del negocio que no se pueden
expresar como restricciones referenciales tales como
actualizaciones o borrados en cascada
top related