Capa_datos_Con_procedimientos_almacenados

Capa datos – Procedimientos

almacenadosMySql

Jesús Alejandro Flores Hernández

Introducción

Descripción. Presentación que muestra el uso de los procedimientos

almacenados y parámetros en MySql para programar la capa de datos.

Objetivo. Construir una capa de datos utilizando procedimientos

almacenados en MySql.

Requerimientos

Tener instalado MySql y HeidiSql (ver:

http://es.slideshare.net/ltidesdaci/instalaciondmysqlwamp-y-heidisql)

Tener la Base de datos ProvPar en MySql con las tablas: proveedores,

partes y pedidos(se describen a continuación)

http://es.slideshare.net/ltidesdaci/instalaciondmysqlwamp-y-heidisql





Base de datos provPar1. Agregue una base de datos llamada

provPar en MySql

2. Agregue una tabla llamada partes a

la base de datos provPar con los

campos

3. Agregue a la tabla partes los datos

Procedimiento almacenados (SP)

Un SP es un conjunto de instrucciones en SQL que permiten procesar los datos

de un base de datos, nos permiten un mejor control de la información sobre

todo cuando se tiene múltiples aplicaciones para una base de datos, como

una referencia a los SP vea:

http://dev.mysql.com/doc/refman/5.0/es/stored-procedure-syntax.html

http://dev.mysql.com/doc/refman/5.0/es/stored-procedures.html

http://es.slideshare.net/ltidesdaci/procedimientosalmacenados

http://fcocastan.wordpress.com/2010/12/29/procedimiento-almacenado-y-

estructuras-de-control-en-mysql-while-if-then-else-switch/












http://fcocastan.wordpress.com/2010/12/29/procedimiento-almacenado-y-estructuras-de-control-en-mysql-while-if-then-else-switch/

























Procedimiento almacenado (SP) para insertar datos

CREATE DEFINER=`root`@`localhost` PROCEDURE `spAltaParte`(IN ̀ miID` VARCHAR(10), IN `miDescripcion` VARCHAR(50), IN `miPeso` INT, IN `miColor` VARCHAR(10), IN `miCU` DOUBLE, IN `miExistencia` INT, OUT `miSalida` VARCHAR(50))

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT ''

BEGIN

if not EXISTS( select (1) from partes where partes.id=miID) THEN

#insertarlo

insert into partes values(miID,miDescripcion,miPeso,miColor,miCU,miExistencia);

set miSalida='inserción correcta';

else

set miSalida='Clave existente';

end if;

END

NOTA

Este es el código del procedimiento almacenado para dar de alta una

parte, sin embargo es mas fácil crearlo desde Heidi (ver

http://es.slideshare.net/ltidesdaci/procedimientosalmacenados), en las

diapositivas siguientes se asume que creará el SP usando Heidi y en ellas se

indica como hacerlo.


Crear el procedimiento almacenadoPara un ejemplo de como crear el SP en Heidi vea(http://es.slideshare.net/ltidesdaci/procedimientosalmacenados)

1 Cree un procedimiento llamado spAltaParte

2 En la pestaña parámetros; Agregue los parámetros que se muestran con los tipos que se muestran (note que 6 son IN y el séptimo es OUT):



Codificar el procedimiento almacenado3 Agregue donde muestra la línea roja el código del recuadro azul:

if not EXISTS( select id from partes where partes.id=miID) THEN

#insertarlo insert into partes values(miID,miDescripcion,miPeso,miColor,miCU,miExistencia);


else


end if;

Explicación parámetros

El SP tiene 7 parámetros 6 de entrada (IN):

miID

miDesxcripcion

miPeso

miColor

miCU

miExistencia

Y uno de salida (OUT)

miSalida

Llamado

La forma de llamar al SP desde MySql es: En una ventana de consulta de Heidi tecle:

call spAltaParte('09','tee cobre',60,'natural',85,20,@miSalida);

select @miSalida

Donde los parámetros de entrada (los primeros 6) corresponden a los datos que se

desean insertar y el parámetro de salida (se declara con @ en MySql por que es una

variable) recibirá una respuesta del SP.

En este llamado se esta tratando de insertar una parte con los datos que se dan en

los primero 6 parámetros y a continuación se muestra el contenido del parámetro de

salida devuelto por el SP.

Explicación del código

La sentencia:

select id from partes where partes.id=‘miID' ;

Muestra el campo id de la tabla parte donde el id sea igual al parámetro miID,

básicamente busca un registro cuyo valor en su campo id sea el de nuestro

parámetro. Es decir revisa si esta clave ya existe.

La sentencia:

if not EXISTS( select id from partes where partes.id=miID) THEN

Pregunta ¿si el registro buscado no existe entonces?

Pues entonces se inserta la parte y se almacena un letrero en el parámetro de salida miSalida:

insert into partes values(miID,miDescripcion,miPeso,miColor,miCU,miExistencia);


En caso contrario quiere decir que la parte ya existe y no se inserta por que no debe

duplicarse y solo se pone en el parámetro de salida el letrero:

else


Explicación

Llamado – En una ventana de consulta tecle y ejecute

call spAltaParte('09','tee cobre',60,'natural',85,20,@miSalida);

select @miSalida

Verá el resultado en la ventana de resultados de HeidiSql

SP usosEn un SP podemos realizar comprobaciones de datos antes de insertarlos, por ejemplo supongaque deseamos asegurarnos de que el valor en el campo existencia no sea negativo y tambiénqueremos que la descripción de la parte este en mayúsculas sin importar como la tecleo elusuario.

El código en nuestro spAltaParte sería:

BEGINif not EXISTS( select id from partes where partes.id=miID) THEN

#convertir descripción a amysuculasset miDescripcion=UPPER(miDescripcion);#verificar que existencia no sea negativoif miExistencia<0 then

set miExistencia=0;end if;#insertar datoinsert into partes values(miID,miDescripcion,miPeso,miColor,miCU,miExistencia);set miSalida='inserción correcta';

elseset miSalida='Clave existente';

end if;END

SP para consulta de datos

A continuación crearemos un SP para consulta de datos

Agregue la tabla proveedores a la base de datos ProvPar

Agregue los datos

Agregue la tabla pedidos

Agregue a la base de datos provPar la tabla pedidos con los campos:

Inserte en la tabla pedidos los datos:

Cree un SP llamado spPedidos con un

parámetro de entrada llamado opcion (sin acento) y

el código:

Pruebe el SP

Pruebe el sp con las sentencia (una sentencia por cada consulta):

call spPedidos(1)

call spPedidos(2)

call spPedidos(3)

call spPedidos(4)

)

ExplicaciónEs Sp recibe el parámetro opocion (no debe llevar acento) en el llamado. Y según sea 1,2 3 o

4 ejecuta una cosnsulta, en el código del SP la sentencia:

case opcion

when 1 then

#todos

when 2 then

#nombre proveedor nombre partes fecha y cantidad

when 3 then

#los que ya se surtieron

when 4 then

#los que no se han surtido

end case;

Permite ejecutar una consulta en dependencia del contenido del parámetro

Por ejemplo cuando el parámetro tiene el valor 1

when 1 then

#todos

select * from pedidos;

Recupera los datos de todos los

pedidos

Por ejemplo cuando el parámetro tiene el valor 2

when 2 then

#nombre proveedor nombre partes fecha y cantidad

select proveedores.nombre as 'Proveedor', partes.descripcion, pedidos.fecha, pedidos.cantidad

from pedidos inner join partes on pedidos.idparte=partes.id inner join proveedores on

pedidos.idproveedor=proveedores.id;

Recupera los datos de todos los pedidos con nombre de

proveedor y descripción de la parte

Cuando el parámetro tiene valor…

Con valor 3 devuelve los que ya se surtieron

Con valor 4 los que no se han surtido.

Actividad

A partir del ejemplo, agregue el código necesario para dar de alta pedidos y

proveedores. También las siguiente consultas:

1. Todas las partes pedidas a un proveedor dado.

2. Todos los pedidos que aun no entrega un proveedor dado.

3. El total de una parte dada pedidas a un proveedor dado.

La palabra dado se refiere a un dato que dará el usuario y debemos enviarlo

como parámetro al SP.

Fin




http://fcocastan.wordpress.com/2010/12/29/procedimiento-almacenado-y-

estructuras-de-control-en-mysql-while-if-then-else-switch/










































Capa_datos_Con_procedimientos_almacenados

Documents

Transcript of Capa_datos_Con_procedimientos_almacenados