procedimientos almacenados activadores...

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


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”



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


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%.



Evita

sobrecarga de

transmisión de

datos

salientes y

entrantes.

Almacenado en

La BD

Servidor

DELIMITER $$


BEGIN

SET @precio = (SELECT precio FROM Libro WHERE id_libro=id);

IF @precio < 50000 THEN




END IF;

END $$

DELIMITER ;

- Reducen el tráfico 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


- Corren más rápido que las mismas instrucciones ejecutadas en

forma interactiva


Arbol de consulta

Cache

Plan consulta Datos

Batch

-----

-----

-----

Ejecución interactiva


Procedimiento Almacenado

Arbol de consulta

Creación de un procedimiento almacenado

Create PROC p_nombre

--------

--------

--------

Sysprocedures


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 $$


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);


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


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

…


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

procedimientos almacenados activadores...

Documents