7/25/2019 Bases_asir_cap8 Construccin de Guiones Luis Hueso Ibez
1/18
Captulo 8
Construccin de
guiones
7/25/2019 Bases_asir_cap8 Construccin de Guiones Luis Hueso Ibez
2/18
NDICE CAPTULO 8
Lenguajes de programacin y bases de datos
Procedimientos y funciones almacenados en MySQL
Sintaxis y ejemplos
Parmetros y variables Instrucciones condicionales Instrucciones repetitivas o loops SQL en rutinas: Cursores Gestin de rutinas almacenadas
Manejo de errores
7/25/2019 Bases_asir_cap8 Construccin de Guiones Luis Hueso Ibez
3/18
Lenguajes de programacin y bases
de datos
Aumentan la funcionalidad de lossistemas gestoresLa mayora incorporan lenguajespropiosSuelen incorporar APIs de otros
lenguajes (Java, C++, perl, Ruby,php, etc.)
7/25/2019 Bases_asir_cap8 Construccin de Guiones Luis Hueso Ibez
4/18
Procedimientos y funciones
almacenados en MySQL
Esquema general
7/25/2019 Bases_asir_cap8 Construccin de Guiones Luis Hueso Ibez
5/18
Procedimientos y funciones
almacenados en MySQL
Ejemplo hola mundo con Workbench
7/25/2019 Bases_asir_cap8 Construccin de Guiones Luis Hueso Ibez
6/18
Sintaxis y ejemplos de rutinasalmacenadas
Sintaxis general
par amet er :[ I N | OUT | I NOUT ] param_name t ypet ype:
Any val i d MySQL dat a t ypechar act er i st i c:LANGUAGE SQL| [ NOT] DETERMI NI STI C| { CONTAI NS SQL | NO SQL | READS SQL DATA | MODI FI ES SQL DATA } | SQLSECURI TY { DEFI NER | I NVOKER }
FuncinCREATE FUNCTI ON sp_name( [ par amet er [ , . . . ] ] )RETURNS t ype
[ char act er i st i c . . . ]r out i ne_body
ProcedimientoCREATE PROCEDURE sp_name( [ par amet er [ , . . . ] ] )
[ char act er i st i c . . . ]r out i ne_body
7/25/2019 Bases_asir_cap8 Construccin de Guiones Luis Hueso Ibez
7/18
PARMETROS Y VARIABLES I
Parmetros de una rutina
IN: entrada OUT: salida
INOUT: entrada/salida
Variables en una rutina Tipos: de datos
Alcance
variables: determinado porbloque BEGIN/END
7/25/2019 Bases_asir_cap8 Construccin de Guiones Luis Hueso Ibez
8/18
PARMETROS Y VARIABLES II
Ejemplo
CREATE PROCEDURE pr oc3( OUT p I NT) SET p = - 5 $$CALL proc3( @y) $$SELECT @y$$+- - - - - - +| @y |+- - - - - - +| - 5 |+- - - - - - +
En este caso hemos creado una nueva variable @y al
llamar al procedimiento cuyo valor se actualiza dentrodel mismo por ser sta de tipo OUT.
7/25/2019 Bases_asir_cap8 Construccin de Guiones Luis Hueso Ibez
9/18
INSTRUCCIONES CONDICIONALES I
If-then-elseI F expr 1 THEN. . .ELSEI F expr 2 THEN
. . .ELSE. . .END I F
CASECASE expr essi onWHEN val ue THEN[ WHEN val ue THEN
. . . ][ ELSE]END CASE;
7/25/2019 Bases_asir_cap8 Construccin de Guiones Luis Hueso Ibez
10/18
Instrucciones condicionales II
Ejemplo IF-THEN-ELSE
En el siguiente ejemplo insertamos o actualizamos la tabla de prueba t en la base de datostest segn el valor de entrada:
DELI MI TER $$
CREATE PROCEDURE pr oc7 ( I N par1 I NT)BEGI NDECLARE var1 I NT;SET var 1 = par1 + 1;I F var 1 = 0 THENI NSERT I NTO t VALUES ( 17) ;END I F;
I F par1 = 0 THENUPDATE t SET s1 = s1 + 1;ELSEUPDATE t SET s1 = s1 + 2;END I F;END; $$
Cuando el valor de la variable1 es 0 entonces hacemos una insercin, en caso de que sea 0el parmetro de entrada actualizamos sumando 1 al valor actual y si no sumamos 2.
7/25/2019 Bases_asir_cap8 Construccin de Guiones Luis Hueso Ibez
11/18
INSTRUCCIONES REPETITIVAS IWhile loop
[etiqueta:] WHILE expression DOinstrucciones
END WHILE [etiqueta]
Repeat until loop
[etiqueta:] REPEAT
instrucciones
UNTIL expresionEND REPEAT [etiqueta]
Simple loop
[etiqueta:] LOOPinstrucciones
END LOOP
[etiqueta];
7/25/2019 Bases_asir_cap8 Construccin de Guiones Luis Hueso Ibez
12/18
INSTRUCCIONES REPETITIVAS IIEjemplo
En el siguiente ejemplo se muestran los nmerosimpares desde 0 a 10
DELI MI TER $$CREATE PROCEDURE pr oc10( )
BEGI NDECLARE i i nt ;SET i =0;l oop1: REPEAT
SET i =i +1;I F MOD( i , 2) 0 THEN / *nmer o i mpar */
SELECT CONCAT( i , " es i mpar " ) ;END I F;UNTI L i >= 10
END REPEAT;END; $$
7/25/2019 Bases_asir_cap8 Construccin de Guiones Luis Hueso Ibez
13/18
SQL en rutinas: Cursores
Definicin/comandos ICursor
Objeto que hace referencia a un conjunto de datos obtenidos de una consulta
Comandos de cursores
DECLARE: define un nuevo cursor
DECLARE cur sor _name CURSOR FOR SELECT_st at ement ;
OPEN: Abre el cursor o sus filas asociadas
OPEN cur sor _name
FETCH: Extrae la siguiente fila de un cursor
FETCH cur sor _name I NTO var i abl e l i st ;
CLOSE: Cierra el cursor
CLOSE cursor _name ;
7/25/2019 Bases_asir_cap8 Construccin de Guiones Luis Hueso Ibez
14/18
SQL en rutinas: Cursores
Definicin/comandos IIEjemplo para contar el nmero de noticias mediante un cursorCREATE PROCEDURE cur sor _demo3( )BEGI N
DECLARE t mp VARCHAR( 200) ;
DECLARE l r f BOOL;DECLARE nn I NT;DECLARE cur sor 2 CURSOR FOR SELECT t i t ul o FROM not i ci as;DECLARE CONTI NUE HANDLER FOR NOT FOUND SET l r f =1;SET l r f =0, nn=0;
OPEN cur sor 2;l _cur sor : LOOPFETCH cur sor 2 I NTO t mp;SET nn=nn+1;I F l r f =1 THENLEAVE l _cur sor ;
END I F;END LOOP l _cur sor ;
CLOSE cur sor 2;SELECT nn;END; $$
7/25/2019 Bases_asir_cap8 Construccin de Guiones Luis Hueso Ibez
15/18
GESTION DE RUTINAS ALMACENADAS
Creacin (ya visto)
Eliminacin rutinas
Para eliminar procedimientos o funciones usamos el comando SQL DROP con lasiguiente sintaxis:
DROP {PROCEDURE | FUNCTI ON} [ I F EXI STS] sp_name
Consulta rutinas
SHOW CREATE {PROCEDURE | FUNCTI ON} sp_nameSHOW {PROCEDURE | FUNCTI ON} STATUS [ LI KE ' pat t er n' ]
7/25/2019 Bases_asir_cap8 Construccin de Guiones Luis Hueso Ibez
16/18
MANEJO DE ERRORES
Sintaxis
DECLARE {CONTI NUE | EXI T} HANDLER FOR
{SQLSTATE sql st at e_code| MySQL er r or code| condi t i on_name}handl er _act i ons
Tipo de manejador: EXIT o CONTINUE
Condicin del manejador: Estado SQL (SQLSTATE), errorpropio de MySQL o cdigo de errordefinido por el usuario
Acciones del manejador: Acciones a tomar cuando se activeel manejador
7/25/2019 Bases_asir_cap8 Construccin de Guiones Luis Hueso Ibez
17/18
EJEMPLO COMPLETOEjemplo completo en el que se obtienen y muestran el nmero de noticias publicadas decada autor para lo cual se precisan dos cursores:
CREATE PROCEDURE not i ci as_aut or ( ) READS SQL DATABEGI NDECLARE vaut or , na_count I NT;DECLARE f i n BOOL;DECLARE autor_cur sor cur sor FOR SELECT i d_aut or FROM aut or ;DECLARE not i ci a_cursor cur sor FOR SELECT aut or FROM not i ci as WHERE autor=vaut or ;
DECLARE CONTI NUE HANDLER FOR NOT FOUND SET f i n=1;SET na_count=0;OPEN aut or _cur sos;
aut or_l oop: LOOPFETCH ac i nt o vaut or ;
I F f i n=1 THEN LEAVE aut or_l oop;END I F;
OPEN not i ci a_cursor ;SET na_count =0;not i ci as_l oop: LOOP
FETCH nc I NTO vaut or ;I F f i n=1 THEN LEAVE aut or_l oop;END I F;SET na_count =na_count +1;
END LOOP not i ci as_l oop;CLOSE not i ci a_cursor ;SET f i n=0;SELECT CONCAT( ' El aut or ' , vaut or , ' t i ene' , na_count , ' not i ci as' ) ;
END LOOP aut or _l oop;CLOSE aut or_cursor ;END; $$
7/25/2019 Bases_asir_cap8 Construccin de Guiones Luis Hueso Ibez
18/18
FIN CAPTULO 8