Transcript procedimientos almacenados y triggers

Procedimientos Almacenados y
Disparadores
M.C. Pedro Bello López
Procedimientos
almacenados
• Un store procedure
(procedimiento almacenado)
es un pequeño programa que
se encuentra almacenado en
la base de datos.
Ventajas de los Procedimientos
almacenados
• Realización de
cambios de
código sin necesidad de afectar
a la aplicación.
• Minimización del tráfico en la
red ya que en lugar de mandar
una sentencia larga, solo se
manda a ejecutar el nombre
corto del store procedure, por lo
que su ejecución se vuelve
mucho más rápida la ejecución.
Requerimientos
• Contar con la versión
5.0 de MySQL.
• Comprobación
de
versión:
• Una vez comprobado
que se cuenta con la
versión
5.0.x,
se
puede asegurar que
el store procedure va
a funcionar.
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.18 |
+-----------+
1 row in set (0.20 sec)
Sintaxis
• Para crear o eliminar un procedimiento
almacenado, se emplean las sentencias
• CREATE PROCEDURE
• DROP PROCEDURE
Sintaxis CREATE
PROCEDURE
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE nombre_sp
([proc_parametros[,...]])
[caracteristicas...] cuerpo_rutina
Sintaxis CREATE
PROCEDURE…
proc_parametros:
[ IN | OUT | INOUT ] nombre_parametro tipo
caracteristicas:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL
DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
Cuerpo_rutina:
Sentencia SQL válida
Sintaxis DROP
PROCEDURE
DROP {PROCEDURE | FUNCTION} [IF EXISTS]
sp_name;
Sintaxis para la sentencia
compuesta Begin … End
Los procedimientos almacenados pueden contener varios comandos, usando
un comando compuesto BEGIN ... END .
[begin_label:] BEGIN
[lista_de_sentencias]
END [end_label]
Usar múltiples comandos requiere que el cliente sea capaz de enviar cadenas
de consultas con el delimitador de comando ;
Esto se trata en el cliente de línea de comandos mysql con el comando
delimiter.
Cambiar el delimitador de final de consulta ; end-of-query (por ejemplo, al
simbolo //)
Mysql> delimiter //
Mysql> select * from pelicula//
Pasos para crear un procedimiento
almacenado desde Mysql
1. Crear la base de datos sobre la que va
actuar el procedimiento almacenado.
2. Crear las tablas donde se realizarán las
operaciones y llenarlas con registros.
3. Declarar el procedimiento almacenado.
4. Llamar con el comando call al
procedimiento
Ejemplo 1 de procedimiento
almacenado Paso 1
• Crear la base de Datos:
mysql> create database dbprueba;
Query OK, 1 row affected (0.08 sec)
mysql> use dbprueba;
Database changed
Ejemplo 1 de procedimiento
almacenado Paso 2
• Crear las tablas
mysql> create table t (c1 int);
Query OK, 0 rows affected (0.22 sec)
mysql> insert into t (c1) values (20);
Query OK, 1 row affected (0.06 sec)
Ejemplo 1 de procedimiento
almacenado Paso 3
• Declarar el procedimiento almacenado:
mysql> CREATE PROCEDURE p1 ()
SELECT * FROM t;
Query OK, 0 rows affected (0.00 sec)
Ejemplo 1 de procedimiento
almacenado Paso 4
• Llamar con el comando call al procedimiento:
mysql> call p1();
+------+
| c1 |
+------+
|1 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Ejemplo de procedimientos
almacenados con parámetros de
entrada
mysql> CREATE PROCEDURE p2(IN p INT) SET @x = p;
Query OK, 0 rows affected (0.00 sec)
mysql> CALL p2(12345);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x;
+-------+
| @x |
+-------+
| 12345 |
+-------+
1 row in set (0.00 sec)
Ejemplo de procedimientos
almacenados con parámetros de salida
mysql> CREATE PROCEDURE p3 (OUT p INT)
SET p = -5 ;
mysql> CALL p3(@y);
mysql> SELECT @y;
+------+
| @y |
+------+
| -5 |
+------+
Ejemplo de procedimientos
almacenados con sentencias
compuestas
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 ;
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;
+------+
| @a |
+------+
|1 |
+------+
Pasos para crear un procedimiento
almacenado desde PHP
• Crear la base de datos sobre la que va actuar el
procedimiento almacenado
• Crear las tablas donde se realizarán las
operaciones.
• Declarar el procedimiento almacenado desde
mysql
• Llamar con el comando call al procedimiento
con el comando mysql_query();
Ejemplo en PHP
• Dar de alta los siguientes comandos en
php:
mysql> create database Pruebas;
mysql>use Pruebas;
myql> create table Persona(id int, Nombres
varchar(50), Apellidos varchar(50));
mysql>delimiter //
Ejemplo en PHP
mysql>create Procedure sp_InsertarPersona(IN
Id INTEGER,IN Nombres VARCHAR(50),IN
Apellidos VARCHAR(50))
BEGIN
INSERT INTO Persona
VALUES(Id,Nombres,Apellidos);
END;
//
sp.php
<?php
$link = mysql_connect('localhost', 'root', '');
if (!$link) {
die('Not connected : ' . mysql_error());
}
$db_selected = mysql_select_db('Pruebas', $link);
if (!$db_selected) {
die ('No se puede utilizar la base de datos dbprueba : ' . mysql_error());
}
$query='CALL sp_InsertarPersona(1,"Johny Perez","Moreno")';
$result = mysql_query($query);
echo "la consulta fue exitosa";
if (!$result) {
die('Invalid query: ' . mysql_error());
}
?>
Disparadores(Triggers)
• Son objetos relacionados a tablas que son
ejecutados o mostrados cuando sucede
algún evento en contra de sus tablas
asociadas.
• Estos eventos son aquellas sentencias
(INSERT, DELETE, UPDATE) que modifican
los datos dentro de la tabla a la que está
asociado el trigger y pueden ser disparados
antes (BEFORE) y/o después (AFTER) de
que la fila es modificada.
Ventajas de los
Disparadores
• Los triggers son muy parecidos a los
procedimientos almacenados, de tal forma que si
deseamos ejecutar múltiples acciones cuando un
trigger es disparado, podemos encapsular estas
acciones dentro de una construcción BEGIN, END.
•
Permite llevar un registros de las actividades de
los usuarios y las operaciones que realizan en la
Base de Datos
Sintaxis
• Para crear o eliminar un disparador, se
emplean las sentencias
• CREATE TRIGGER
• DROP TRIGGER
Sintaxis Create Trigger
CREATE TRIGGER nombre_disparador
momento_disparo evento_disparado
ON nombre_tabla FOR EACH ROW
sentencia_disparada
Sintaxis Create Trigger
Donde:
momento_disparo es el momento en que el disparador entra en
acción. Puede ser BEFORE (antes) o AFTER (después), para
indicar que el disparador se ejecute antes o después de la
sentencia que lo activa.
evento_disparado indica la clase de sentencia que activa al
disparador. Puede ser INSERT, UPDATE, o DELETE. Por ejemplo,
un disparador BEFORE para sentencias INSERT podría utilizarse
para validar los valores a insertar.
sentencia_disparada es la sentencia que se ejecuta cuando se activa
el disparador
Sintaxis Drop Trigger
DROP TRIGGER
[nombre_esquema.]nombre_disp
Donde:
Anteriormente a la versión 5.0.10 de
MySQL, se requería el nombre de tabla en
lugar del nombre de esquema.
(nom_tabla.nom_disp).
Funcionalidades
• Los triggers tienen un par de palabras
clave extra
• OLD : valores de las columnas antes del
cambio
• NEW :valores de las columnas después de
que la sentencia fue procesada.
• Las
sentencias
INSERT
únicamente
permiten NEW, las sentencias UPDATE
permiten ambos, NEW y OLD, y las
sentencias DELETE permiten sólo OLD.
Pasos para crear un trigger desde
mysql
1. Crear la base de datos y las tablas sobre la
que va actuar el disparador
2. Crear los procedimientos almacenados si se
necesitan.
3. Declarar el trigger desde mysql
4. Ejecutar la sentencia mysql que provoca la
ejecución del trigger
Ejemplo de Disparador
Creación de la tabla Paso 1
Mysql> create database Vn;
Mysql>use Vn;
mysql> DELIMITER //
mysql> CREATE TABLE ventas (id INT NOT NULL
AUTO_INCREMENT,
item VARCHAR(10),
valor DECIMAL(9,2),
ejecutivo_cuenta VARCHAR(10),
comision DECIMAL(7,2),
PRIMARY KEY(id)) //
Creación de un
procedimiento almacenado
mysql> CREATE PROCEDURE
comision(valor DECIMAL(9,2))
BEGIN
SET @comm := valor / 10;
END;
//
Creación del trigger
mysql> CREATE TRIGGER ventas_comm
BEFORE INSERT ON ventas
FOR EACH ROW
BEGIN
CALL comision(NEW.valor);
SET NEW.comision = @comm;
END;
//
Prueba de ejecución del
trigger
mysql> DELIMITER ;
mysql> INSERT INTO ventas(item, valor, ejecutivo_cuenta)
VALUES('Queso',300.00,'Mario');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO ventas(item, valor, ejecutivo_cuenta)
VALUES('Papas',400.00,'Mario');
Query OK, 1 row affected (0.01 sec)
Vista de la acción del
trigger
•
•
•
•
•
•
•
•
mysql> SELECT * FROM ventas;
+----+-------+--------+------------------+----------+
| id | item | valor | ejecutivo_cuenta | comision |
+----+-------+--------+------------------+----------+
| 1 | Queso | 300.00 | Mario
| 30.00 |
| 2 | Papas | 400.00 | Mario
| 40.00 |
+----+-------+--------+------------------+----------+
2 rows in set (0.00 sec)
Ejemplo 2 Datos
preliminares
Mysql> use videoteca
Mysql> describe pelicula;
Mysql> select * from pelicula;
Verificamos que Robert de Niro no se encuentra
en ninguna pelicula.
Ejemplo 2 Creación de
trigger
Mysql> delimiter //
Mysql> Create trigger before_insert_pelicula
before insert on pelicula
For each row
Begin
Set New.actor=’Robert de Niro’;
End;
//
Disparo de la acción
Mysql> delimiter ;
mysql> insert into pelicula (titulo, director)
values (‘taxi driver’,’Coppola’);
Ahora verificamos la inserción y la acción de
disparo
Mysql>select * from pelicula;
Eliminamos el trigger
Mysql> drop trigger before_insert_pelicula;