procedimientos almacenados y funciones en mysql
TRANSCRIPT
-
7/22/2019 Procedimientos Almacenados y Funciones en MySQL
1/13
Procedimientos almacenados y funciones en MySQL
Una de las grandes novedades de la versin 5 de MySQL es sin dudas la inclusin de soporte para
procesos almacenados. A continuacin veremos los fundamentos tericos y este tema ms
algunos ejemplos bsicos.
Si ya usamos bases de datos como Oracle, Interbase / Firebird, PostgreSQL, seguro escuchamos
hablar de procedimientos almacenados. Sin embargo, en MySQL esto es toda una novedad y un
paso enorme para que esta base de datos se convierta en un verdadero sistema gestor de bases
de datos.
Ahora bien, qu son en realidad los procedimientos almacenados? Luego de sumergirnos en este
tema veremos que el nombre es plenamente identificatorio y casi explica lo que es un
procedimiento almacenado.
Los procedimientos almacenados son un conjunto de instrucciones SQL ms una serie de
estructuras de control que nos permiten dotar de cierta lgica al procedimiento. Estos
procedimientos estn guardados en el servidor y pueden ser accedidos a travs de llamadas, como
veremos ms adelante.
Para crear un procedimiento, MySQL nos ofrece la directiva CREATE PROCEDURE. Al crearlo
ste es ligado o relacionado con la base de datos que se est usando, tal como cuando creamos
una tabla, por ejemplo.
Para llamar a un procedimiento lo hacemos mediante la instruccin CALL. Desde un procedimiento
podemos invocar a su vez a otros procedimientos o funciones.
Un procedimiento almacenado, al igual cualquiera de los procedimientos que podamos programar
en nuestras aplicaciones utilizando cualquier lenguaje, tiene:
http://4.bp.blogspot.com/-AJTQeW541YA/TcIUnARX3rI/AAAAAAAAAQ0/J9nFlKUOmVM/s1600/mysql_codigo_cerrado_billytec_com.jpg -
7/22/2019 Procedimientos Almacenados y Funciones en MySQL
2/13
Un nombre.
Puede tener una lista de parmetros.
Tiene un contenido (seccin tambin llamada definicin del procedimiento: aqu
se especifica qu es lo que va a hacer y cmo).
Ese contenido puede estar compuesto por instrucciones sql, estructuras de control,
declaracin de variables locales, control de errores, etctera.
MySQL sigue la sintaxis SQL:2003 para procedimientos almacenados, que tambin usa IBM DB2.
En resumen, la sintaxis de un procedimiento almacenado es la siguiente:view plainprint?
1. CREATEPROCEDUREnombre (parmetro)2. [caractersticas] definicin
Puede haber ms de un parmetro (se separan con comas) o puede no haber ninguno (en este
caso deben seguir presentes los parntesis, aunque no haya nada dentro).
Los parmetros tienen la siguiente estructura: modo nombre tipo
Donde:
modo: es opcional y puede ser IN (el valor por defecto, son los parmetros que elprocedimiento recibir), OUT (son los parmetros que el procedimiento podr modificar) INOUT(mezcla de los dos anteriores).
nombre: es el nombre del parmetro.
tipo: es cualquier tipo de dato de los provistos por MySQL.
Dentro de caractersticas es posible incluir comentarios o definir si el procedimientoobtendr los mismos resultados ante entradas iguales, entre otras cosas.
definicin: es el cuerpo del procedimiento y est compuesto por el procedimiento en s:aqu se define qu hace, cmo lo hace y bajo qu circunstancias lo hace.
As como existen los procedimientos, tambin existen las funciones. Para crear una funcin,
MySQL nos ofrece la directiva CREATE FUNCTION.
La diferencia entre una funcin y un procedimiento es que la funcin devuelve valores. Estos
valores pueden ser utilizados como argumentos para instrucciones SQL, tal como lo hacemos
normalmente con otras funciones como son, por ejemplo, MAX() o COUNT().
Utilizar la clusula RETURNS es obligatorio al momento de definir una funcin y sirve para
especificar el tipo de dato que ser devuelto (slo el tipo de dato, no el dato).
Su sintaxis es:view plainprint?
1. CREATEFUNCTIONnombre (parmetro)2. RETURNStipo3. [caractersticas] definicin
http://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.html -
7/22/2019 Procedimientos Almacenados y Funciones en MySQL
3/13
Puede haber ms de un parmetro (se separan con comas) o puede no haber ninguno (en este
caso deben seguir presentes los parntesis, aunque no haya nada dentro). Los parmetros tienen
la siguiente estructura:nombre tipo
Donde:
nombre: es el nombre del parmetro.
tipo: es cualquier tipo de dato de los provistos por MySQL.
Dentro de caractersticas es posible incluir comentarios o definir si la funcin devolver losmismos resultados ante entradas iguales, entre otras cosas.
definicin: es el cuerpo del procedimiento y est compuesto por el procedimiento en s:aqu se define qu hace, cmo lo hace y cundo lo hace.
Para llamar a una funcin lo hacemos simplemente invocando su nombre, como se hace en
muchos lenguajes de programacin.
Desde una funcin podemos invocar a su vez a otras funciones o procedimientos.
view plainprint?
1. mysql> delimiter //2. mysql> CREATEPROCEDUREprocedimiento (INcod INT)3. -> BEGIN4. -> SELECT* FROMtabla WHEREcod_t = cod;5. -> END6. -> //7. Query OK, 0 rowsaffected (0.00 sec)8. mysql> delimiter ;9. mysql> CALL procedimento(4);
En el cdigo anterior lo primero que hacemos es fijar un delimitador. Al utilizar la lnea de
comandos de MySQL vimos que el delimitador por defecto es el punto y coma (;): en los
procedimientos almacenados podemos definirlo nosotros.
Lo interesante de esto es que podemos escribir el delimitador anterior; sin que el procedimiento
termine. Ms adelante, en este mismo cdigo volveremos al delimitador clsico. Luego creamos el
procedimiento con la sintaxis vista anteriormente y ubicamos el contenido entre las palabras
reservadas BEGIN y END.
El procedimiento recibe un parmetro para luego trabajar con l, por eso ese parmetro es de tipoIN. Definimos el parmetro como OUT cuando en l se va aguardar la salida del procedimiento. Si
el parmetro hubiera sido de entrada y salida a la vez, sera de tipo denominado INOUT.
El procedimiento termina y es llamado luego mediante la siguiente instruccin:
view plainprint?
1. mysql> CALL procedimento(4);
http://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.html -
7/22/2019 Procedimientos Almacenados y Funciones en MySQL
4/13
Otro ejemplo:
view plainprint?
1. CREATEPROCEDUREprocedimiento2 (INa INTEGER)2. BEGIN3. DECLAREvariable CHAR(20);4. IF a > 10 THEN5. SETvariable = mayora 10;6. ELSE7. SETvariable = menoro igual a 10;8. ENDIF;9. INSERTINTOtabla VALUES(variable);10. END
El procedimiento recibe un parmetro llamado a que es de tipo entero.
Se declara una variable para uso interno que se llama variable y es de tipo char. Se implementa una estructura de control y si a es mayor a 10 se asigna a variable un valor. Si no
lo es se le asigna otro.
Se utiliza el valor final de variable en una instruccin SQL.
Recordemos que para implementar el ultimo ejemplo se debern usar nuevos delimitadores, como
se vio anteriormente.
Observemos ahora un ejemplo de funciones:view plainprint?
1. mysql> delimiter //
2. mysql>CREATE
FUNCTION
cuadrado (sSMALLINT
)RETURNS
SMALLINT
3. -> RETURNs*s;4. -> //5. Query OK, 0 rowsaffected (0.00 sec)6. mysql> delimiter ;7. mysql> SELECTcuadrado(2);
Otras bases de datos como PostgreSQL implementan procedimientos almacenados y brindan la
posibilidad de programarlos utilizando lenguajes como PHP o Java.
En MySQL hay intenciones de implementar lo mismo y seguramente en las prximas versiones lo
veremos, pero ms importante que utilizar un lenguaje u otro es entender para qu podran
servirnos los procedimientos almacenados.
En definitiva hemos dado un recorrido por el mundo de la programacin de procedimientos
almacenados en MySQL. Es importante que se trata de un mundo que est en pleno desarrollo y
que promete evolucionar.
http://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.htmlhttp://emanuelpeg.blogspot.com/2011/05/procedimientos-almacenados-y-funciones.html -
7/22/2019 Procedimientos Almacenados y Funciones en MySQL
5/13
Procedimientos almacenados en MySQL
Un procedimiento almacenado es un programa que se guarda fsicamente en unabase de datos. Su implementacin vara de un gestor de bases de datos a otro. Esteprograma esta hecho con un lenguaje propio de cada Gestor de BD y esta compilado,por lo que la velocidad de ejecucin es muy rpida.
Las principales ventajas son: El S.G.B.D. es capaz de trabajar ms rpidocon los datos que cualquierprograma externo, ya que posee acceso directo a los datos a manipular y slonecesita enviar el resultado final al usuario.
Slo realizamos una conexinal servidor y este ya es capaz de realizartodas las comprobaciones sin tener que volver a establecer una conexin.
Podemos reutilizar el procedimiento y este puede ser llamado desdediferentes aplicaciones y lenguajes. Slo lo programaremos una vez.
La desventaja principal es que se guarda en la B.D., por o que si se corrompepodemos perder los procedimientos. La solucin como siempre en las BB.DD. estener una buena poltica de copias de seguridad.
Bueno dejemos la teora y vayamos a la prctica este es el ejemplo de unprocedimiento almacenado:> DELIMITER //> CREATE PROCEDURE addAutomovil(IN nombre VARCHAR(50),IN plazas INT)
> BEGIN> IF plazas < 6 THEN> INSERT INTO coche VALUES(nombre,plazas);> ELSE> INSERT INTO monovolumen VALUES(nombre,plazas);> END IF;> END;> //
Este procedimiento almacenado recibe los parmatros de nombre y nmero de plazasy en funcin de las plazas del vehculo inserta los datos en la tabla coche omonovolumen. Es una funcin bien tonta, lo s, incluso podra ser ms lenta comoprocedimiento almacenado que como cdigo en nuestro programa. Pero es
simplemente un ejemplo.Para hacer una llamada al procedimiento almacenado basta con usar la sentenciacall:> CALL addAutomovil("Nissan Serena",8);
De ahora en adelante, usaremos siempre este procedimiento para introducirautomviles en la B.D. de forma que si un da se debe modificar slo deberemoscorregir el procedimiento.
http://3.bp.blogspot.com/_KOuvviIUpS8/TTinYYy8dwI/AAAAAAAABvI/XX-XVMxbTFo/s1600/mysql.jpg -
7/22/2019 Procedimientos Almacenados y Funciones en MySQL
6/13
Bien como ya hemos visto los procedimientos almacenados son muy interesantes, aqu no
hemos visto estructuras de control, como lo son IF, SWITCH, WHILE,VARIABLES, etc y
creo que es bueno comenzar con esta ultima.
VARIABLES
1 DECLAREedad INT;
Esta tendr un mbito local y cuando se acabe el procedimiento no podr ser accedida. Una
vez la variable es declarada, para cambiar su valor usaremos la sentencia SET como en el
siguiente ejemplo:
1 SETedad = 56 ;
Para poder acceder a una variable a la finalizacin de un procedimiento se tiene que
usar parmetros de salida como en el siguiente Cdigo:
IF THEN ELSE1
2
3
4
5
6
7
8
9
10
11
12
delimiter //CREATEproceduremiProc(INp1 int) /* Parmetro de entrada */begindeclaremiVar int; /* se declara variable local*/SETmiVar = p1 +1 ; /* se establece la variable */IF miVar = 12 thenINSERTINTOlista VALUES(55555);elseINSERTINTOlista VALUES(7665);endIF;end;
//
SWITCH1
2
3
4
5
6
7
8
9
10
11
12
delimiter //CREATEproceduremiProc (INp1 int)begindeclarevar int;SETvar = p1 +2 ;casevarwhen2 thenINSERTINTOlista VALUES(66666);when3 thenINSERTINTOlista VALUES(4545665);
elseINSERTINTOlista VALUES(77777777);endcase;end;//
Creo que no hacen falta explicaciones.
COMPARACIN DE CADENAS
-
7/22/2019 Procedimientos Almacenados y Funciones en MySQL
7/13
1
2
3
4
5
6
7
8
9
10
delimiter //CREATEprocedurecompara(INcadena varchar(25), INcadena2 varchar(25))beginIF strcmp(cadena, cadena2) = 0 thenSELECT"son iguales!";elseSELECT"son diferentes!!";endIF;end;//
La funcin strcmp devuelve 0 si las cadenas son iguales, si no devuelve 0 es que son
diferentes.
USO DE WHILE1
2
3
4
5
6
7
8
9
10
11
delimiter //CREATEprocedurep14()begindeclarev int;SETv = 0;while v < 5 doINSERTINTOlista VALUES(v);SETv = v +1 ;endwhile;end;//
Un while de toda la vida.
USO DEL REPEAT1
2
3
4
5
6
7
8
9
10
11
12
delimiter //CREATEprocedurep15()begindeclarev int;SETv = 20;repeatINSERTINTOlista VALUES(v);SETv = v + 1;until v
-
7/22/2019 Procedimientos Almacenados y Funciones en MySQL
8/13
Como primer post le dejare y enseare a como crear un procedimiento Almacenado
en MySQL puesto que un Amigo me molesto mucho para que me creara este Blog
para compartir Conocimiento. Yo lo encontr super bueno porque como dice la
bienvenida de este blog OPEN SOURCE para los desarrolladores de Hoy :).-
Bueno Comencemos con el Apunte de Store Procedure en MySQL
En Primera Instancia en MySQL no es llegar y ponerse a programar la rutina con
un CREATE PROCEDURE nombre_sp, ac debemos comenzar con las palabras
DELIMITER // que no se olvide los / o si algn otro apunte en otra pgina
aparece con signo $. entonces la estructura del cuerpo del SP seria as
1
2
3
4
5
6
7
8
9
10
11
DELIMITER //
CREATEPROCEDUREel_nombre_del_sp()
AS
BEGIN
END;
DELIMITER//
Ese seria el primer paso de como crear un sp, ahora procedemos a realizar una
consulta SQL dentro del SP que seria algo as
-
7/22/2019 Procedimientos Almacenados y Funciones en MySQL
9/13
1
2
3
4
5
6
7
8
9
10
11
12
13
DELIMITER //
CREATEPROCEDUREel_nombre_del_sp()
AS
BEGIN
SELECT* FROMtu_tabla;
END;
DELIMITER//
Con est tipo de forma sera para realizar una consulta a toda una tabla con filtro
pero les quiero pedir mucho OJO para finalizar la consulta SQL porque ac la
consulta termina con ;, lo que es muy diferente a los otros motores de Base de
Datos como por Ejemplo SQL SERVER, SYBASE, ORACLE.
Ahora si queremos un procedimiento que requiera parametros de entradas debes
declararlos de la siguiente forma.
1
2
DELIMITER //
-
7/22/2019 Procedimientos Almacenados y Funciones en MySQL
10/13
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATEPROCEDUREel_nombre_del_sp
(
INtu_atributo1 varchar(50),
INtu_atributo2 varchar(50)
)
AS
BEGIN
INSERTINTOtu_tabla values(tu_atributo1,tu_atributo2);
END;
DELIMITER//
-
7/22/2019 Procedimientos Almacenados y Funciones en MySQL
11/13
Ac se reciben parmetro de Entradas para realizar una insercin simple directo a
una tabla pero a esto surge una pregunta y si existe el
atributo identificatorio como lo es el tu_atributo1?
La Respuesta est pregunta es super simple solo debemos validar el procedimientoalmacenado que seria una cosa as
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DELIMITER //
CREATEPROCEDUREel_nombre_del_sp
(
INtu_atributo1 varchar(50),
INtu_atributo2 varchar(50)
)
AS
BEGIN
-
7/22/2019 Procedimientos Almacenados y Funciones en MySQL
12/13
17
18
19
20
21
22
23
24
25
IF NOTEXISTS selecttu_atributo1 fromtu_tabla wheretu_atributo1 = tu_atri
INSERTINTOtu_tabla values(tu_atributo1,tu_atributo2);
ENDIF;
END;
DELIMITER//
Para ejecutar y probar cada sp tienes que poner CALL nombre_sp asi el motor te lo
ejecutara ejemplo:
1 CALL el_nombre_del_sp();
Y Eso seria por el momento.para validar la existencia de registros en rutinas SP
de MySQL
Esperando que el apunte le all gustado y lo compartan con mucha mas Gente, y
pronto estar subiendo nuevos apuntes relacionados con est linda carrera como lo
es la Informtica.
http://elpoli.delphiaccess.com/procedimientos-almacenados-de-mysql-desde-excell-con-vba-y-
ado-macros/
Accesar procedimientos desde access
Const DB_CONNECT As String = "Driver={MySQL ODBC 5.1Driver};Server=localhost;Port=3306;Database=nombre_de_la_bd;User=root;Password=la_contrasea;Option=3;"
http://elpoli.delphiaccess.com/procedimientos-almacenados-de-mysql-desde-excell-con-vba-y-ado-macros/http://elpoli.delphiaccess.com/procedimientos-almacenados-de-mysql-desde-excell-con-vba-y-ado-macros/http://elpoli.delphiaccess.com/procedimientos-almacenados-de-mysql-desde-excell-con-vba-y-ado-macros/http://elpoli.delphiaccess.com/procedimientos-almacenados-de-mysql-desde-excell-con-vba-y-ado-macros/http://elpoli.delphiaccess.com/procedimientos-almacenados-de-mysql-desde-excell-con-vba-y-ado-macros/ -
7/22/2019 Procedimientos Almacenados y Funciones en MySQL
13/13
'Definicin de ObjetosDim cn As ADODB.ConnectionDim cmd As ADODB.CommandDim rst As ADODB.Recordset
Set cn = New ADODB.Connection
With cn.ConnectionString = DB_CONNECT.OpenEnd With
Set cmd = New ADODB.CommandWith cmd.ActiveConnection = cn 'Activo la Conexin.NamedParameters = True.CommandType = adCmdStoredProc 'Defino el tipo de comando.CommandText = "proc_guardar_editar_usuario()" 'Defino los parmetros a enviarSet rst = .Execute 'Ejecuto el envio del procedimiento.End With
If IsNull(Me.id_usuario) Then 'Pregunto si esta vacio el cuadro de textoid_usuario, si esta vacio entonces cargamos_el dato devuelto por el procedimiento el id_usuario o se 1.
Me.id_usuario = rst(0)rst.Close ' Cierro el recordset, para poder usarlo eventualmente en otros
procedimientos.MsgBox "Se ha guardado con exito", vbInformation, "Guardar"
Else ' De lo contrario el registro se esta editando.
MsgBox "Se ha editado con xito", vbInformation, "Editar"
End If
cn.Close 'Cierro la conexinSet cmd = NothingSet cn = Nothing