Los Stored Procedures son bloques de código SQL que son almacenados en la base de datos y se utilizan para realizar una serie de acciones que van más allá de los query salvados como boorkmarks, para llamarlos lo hacemos con la instrucción CALL, inclusive podemos pasar parámetros a dichos Stored Procedures.
Creando un Stored Procedure
Para poder proceder con la creación de un Stored Procedure (SP) debemos inicialmente tener los permisos de usuario de CREATE ROUTINE y ALTER ROUTINE, para el usuario que estaremos utilizando para crearlos en la Base de Datos, también se necesitará el permiso EXECUTE, aunque este es asignado automáticamente al creador de un SP.
Para crear un SP, lo primero que debemos hacer es ir al Query Box, usualmente para finalizar una sentencia SQL utilizamos el punto y coma (
en este caso vamos a cambiar este delimitador, por lo siguiente (//) , esto es debido a que el SP es una gran sentencia SQL y dentro tiene varias, por lo que no podemos terminar el contenido con el punto y coma (
¿Una vez hecho esto, vamos a ingresar nuestro código en la query box, veamos un ejemplo de código:
Como vemos no es muy difícil enterarnos que hace este SP, la primera línea es bastante sencilla, simplemente definimos los parámetros de entrada y salida del SP, luego iniciamos con BEGIN nuestro bloque, colocamos un condicional IF ELSE donde evaluamos cada uno de los parámetros de entrada, de forma que con cada condición ejecutamos una acción, luego cerramos con ENDIF y END los respectivos bloques de nuestro SP y terminamos con // que es nuestro delimitador personalizado.
Probando el Stored Procedure
Una vez definido, vamos a realizar las pruebas de nuestro SP, para ello veremos un par de elementos que no habíamos utilizado, primero veamos el código y luego la explicación:
call add_page('1-234567-22-0', 4, @message);
SELECT @message;
Veamos, utilizamos el CALL para llamar a nuestro SP, luego pasamos dos parámetros que el SP estaba esperando y de último utilizamos una variable MySQL para recibir la respuesta, la segunda línea simplemente consultamos esta variable para obtener su valor; como vemos es bastante fácil trabajar con esto.
Manipulando el Stored Procedure
El SP está almacenado en la Base de Datos, sin embargo no está atado a ninguna tabla en específico, por lo que para acceder a ellos primero vamos a la vista de base de datos en phpMyAdmin, en la página estructura tendremos un apartado llamado Routines.
Cuando hacemos click sobre el icono de edición vemos que el código del SP que nos muestra es algo diferente al nuestro, veamos que nos aparece:
Los SP no los podemos alterar, por lo que al momento de tratar de editarlo, la primera instrucción es el DROP del SP, de esta forma lo eliminamos para volverlo a crear, en las líneas subsiguientes, también nos genera una línea donde se especifica el usuario que es el creador de dicho SP; una vez hechos los cambios al hacer click en Go creamos nuestro SP modificado y se salva en la Base de Datos.
Con esto finalizamos el tutorial, como vemos los Stored Procedures son bastante útiles para almacenar bloques de instrucciones que podemos llamar en cualquier momento, lo que nos brinda el poder de las subrutinas dentro de la Base de Datos.
Creando un Stored Procedure
Para poder proceder con la creación de un Stored Procedure (SP) debemos inicialmente tener los permisos de usuario de CREATE ROUTINE y ALTER ROUTINE, para el usuario que estaremos utilizando para crearlos en la Base de Datos, también se necesitará el permiso EXECUTE, aunque este es asignado automáticamente al creador de un SP.
Para crear un SP, lo primero que debemos hacer es ir al Query Box, usualmente para finalizar una sentencia SQL utilizamos el punto y coma (
en este caso vamos a cambiar este delimitador, por lo siguiente (//) , esto es debido a que el SP es una gran sentencia SQL y dentro tiene varias, por lo que no podemos terminar el contenido con el punto y coma (
¿Una vez hecho esto, vamos a ingresar nuestro código en la query box, veamos un ejemplo de código:
CREATE PROCEDURE `add_page`(IN param_isbn VARCHAR(25),
IN param_pages INT, OUT param_message VARCHAR(100))
BEGIN
IF param_pages > 100 THEN
SET param_message = 'the number of pages is too big';
ELSE
UPDATE book SET page_count = page_count + param_pages WHERE
isbn=param_isbn;
SET param_message = 'success';
END IF;
END
//
Como vemos no es muy difícil enterarnos que hace este SP, la primera línea es bastante sencilla, simplemente definimos los parámetros de entrada y salida del SP, luego iniciamos con BEGIN nuestro bloque, colocamos un condicional IF ELSE donde evaluamos cada uno de los parámetros de entrada, de forma que con cada condición ejecutamos una acción, luego cerramos con ENDIF y END los respectivos bloques de nuestro SP y terminamos con // que es nuestro delimitador personalizado.
Probando el Stored Procedure
Una vez definido, vamos a realizar las pruebas de nuestro SP, para ello veremos un par de elementos que no habíamos utilizado, primero veamos el código y luego la explicación:
call add_page('1-234567-22-0', 4, @message);
SELECT @message;
Veamos, utilizamos el CALL para llamar a nuestro SP, luego pasamos dos parámetros que el SP estaba esperando y de último utilizamos una variable MySQL para recibir la respuesta, la segunda línea simplemente consultamos esta variable para obtener su valor; como vemos es bastante fácil trabajar con esto.
Manipulando el Stored Procedure
El SP está almacenado en la Base de Datos, sin embargo no está atado a ninguna tabla en específico, por lo que para acceder a ellos primero vamos a la vista de base de datos en phpMyAdmin, en la página estructura tendremos un apartado llamado Routines.
Cuando hacemos click sobre el icono de edición vemos que el código del SP que nos muestra es algo diferente al nuestro, veamos que nos aparece:
DROP PROCEDURE `add_page`//
CREATE DEFINER=`marc`@`%` PROCEDURE `add_page`(IN param_isbn
VARCHAR(25), IN param_pages INT, OUT param_message VARCHAR(100))
BEGIN
IF param_pages > 100 THEN
SET param_message = 'the number of pages is too big';
ELSE
UPDATE book SET page_count = page_count + param_pages WHERE
isbn=param_isbn;
SET param_message = 'success';
END IF;
Los SP no los podemos alterar, por lo que al momento de tratar de editarlo, la primera instrucción es el DROP del SP, de esta forma lo eliminamos para volverlo a crear, en las líneas subsiguientes, también nos genera una línea donde se especifica el usuario que es el creador de dicho SP; una vez hechos los cambios al hacer click en Go creamos nuestro SP modificado y se salva en la Base de Datos.
Con esto finalizamos el tutorial, como vemos los Stored Procedures son bastante útiles para almacenar bloques de instrucciones que podemos llamar en cualquier momento, lo que nos brinda el poder de las subrutinas dentro de la Base de Datos.
