Super

Bases de Datos /
Elementos de
Bases de Datos 2011

Que es un Stored Procedure?
• Un stored procedure (S.P.) es un procedimiento
(como una subrutina en cualquier lenguaje de
programación) almacenado en una B.D. que
puede ser invocado externamente.
• Esta formado por un encabezado y un cuerpo.
• El encabezado contiene el nombre del
procedimiento y una lista de parámetros.
• El cuerpopuede contener sentencias SQL y
sentencias propias de un lenguaje de
programación imperativo: declaración y asignación
de variables, condicionales, repetición, etc.

Stored Procedures, Triggers y
Transacciones en MySQL
Departamento de Ciencias e
Ingeniería de la Computación
Universidad Nacional del Sur
1

Stored Procedures: Ventajas
•
•

•
•

Creando un Stored Procedure en MySQL• Se utiliza la sentencia create procedure.

Mejora la performance: Reduce la comunicación por
red (se ejecuta en el servidor).
Las operaciones comunes y frecuentes pueden
almacenarse en la B.D. como un S.P. y ser
invocadas desde cualquier aplicación.
–
–
–

2

create procedure ([ in | out | inout ] , …)
begin
;
encabezado
.
.
cuerpo
.

;
end;

Reduce la complejidad de lasaplicaciones que usan la B.D.
Las aplicaciones pueden compartir código.
Separamos la interfase de la lógica del sistema.

• In, out e inout definen parámetros de entrada, salida y
entrada-salida respectivamente.
• : cualquier tipo válido de MySQL: int,char,date,etc.
• : sentencia SQL, condicionales, repetición, …
• El S.P. es validado sintácticamente y optimizado en el
momento de lacreación.

Mejora la portabilidad del sistema. Los S.P. son
portables, se pueden ejecutar en cualquier
plataforma donde se ejecuta MySQL.
Permite asociar los datos con las operaciones para
manejar los datos.
3

4

Stored Procedures: ejemplos

Creando un Stored Procedure: ejemplo

• Consideremos una B.D. de un banco que
mantiene el saldo de cada cuenta.

use bank;
delimiter ! #define “!” como delimitador
create procedure p()
begin
SELECT * FROM cuentas;
end; !
delimiter ; # reestablece el “;” como delimitador

CREATE DATABASE bank;
USE bank;
CREATE TABLE cuentas(
numero INT UNSIGNED NOT NULL,
saldo DECIMAL(7,2) NOT NULL,
PRIMARY KEY(numero)
) ENGINE=InnoDB;

• Antes de crear un S.P. debemos seleccionar la B.D.
asociada. En este ejemplo: use bank;
• Dado que“;” se usa para separar las sentencias dentro del
S.P. y también se usa para separar las sentencias sql,
debemos cambiar el delimitador a “!” para que el cliente
sepa donde termina la sentencia create procedure.
Atención: no dejar espacios después de “!”.

INSERT INTO cuentas VALUES (1, 1000);
INSERT INTO cuentas VALUES (2, 2000);
INSERT INTO cuentas VALUES (3, 3000);
5

Invocando unStored Procedure: ejemplo

6

Pasaje de Parámetros: ejemplo (1)
delimiter !
create procedure q(IN c CHAR(10), IN d DATE)
begin
SELECT c as cadena, d as fecha;
end; !
delimiter ;

• Una vez creado en S.P. puede invocarse a través de la
sentencia call.
mysql> call p();
+--------+----------+
| numero | saldo
|
+--------+----------+
|
1 | 1000.00 |
|
2 | 2000.00 |
|
3 | 3000.00|
+--------+----------+

• Los usuarios deben tener un privilegio especial para poder
ejecutar un S.P.
grant execute on procedure bank.p to ;
• Para eliminar un S.P. utilizamos la sentencia drop procedure.

mysql> call q(‘Viernes', '2010-10-08');
+---------+------------+
| cadena | fecha
|
+---------+------------+
| Viernes | 2010-10-08 |
+---------+------------+

mysql> dropprocedure p;
7

8

Declaración y uso de variables: ejemplo

Pasaje de Parámetros: ejemplo (2)
delimiter !
create procedure r(IN i INT, OUT o INT)
begin
if i < 0 then
set o=i; # asignación: SET =;
else
set o=i+10;
end
end if;
end; !
delimiter ;
mysql> call r(10, @A); #@A es una variable local a la conexión
Query OK, 0 rows affected (0.00 sec)
mysql> select @A as resultado;...