Conoce SQL – Lenguaje procedimental 3 – Procedimientos

La definición sencilla de procedimiento es que es una función pero que no devuelve nada. Bueno,  realmente si se pueden obtener datos usando parámetros OUT pero vuelvo a recomendar no usarlos. El motivo no es que funcione mal sino que la idea de un procedimiento no es obtener datos, para eso usa una función o una vista.

El caso típico de un procedimiento es por ejemplo insertar un usuario. A la hora de insertar un usuario quizá necesitemos hacer alguna comprobación (algo más complejo que lo que nos pueda dar una restricción UNIQUE) o insertar filas en otras tablas , etc. Vamos a ponernos en situación y con un ejemplo real. En Cutrenti la inserción de usuarios la hace un procedimiento al que se le pasa el email, el nombre, los apellidos y la constraseña. Entonces cuando se inserta un usuario genera actividad de ese usuario (insertando una fila en ultimos cambios) y además se añanden los emoticonos predeterminados a la lista de emoticonos del usuario.

Como ves, estamos trasladando toda la lógica del registro del usuario a mysql en vez de hacerlo con php.

Primero definimos el procedimiento:

CREATE  PROCEDURE insertar_usuario (in v_nombre varchar(25), in v_apellido1 varchar(25), in v_apellido2 varchar(25), in v_email varchar(25), in v_password varchar(25))

Ya está, aquí no hace falta poner returns ni nada. Es sencillo y sin complicaciones. Ahora se inicia un bloque de código (con BEGIN) y se escribe lo que quieres que haga el procedimiento

BEGIN
INSERT INTO usuarios (nombre,apellido1,apellido2,email,md5_pass) VALUES (v_nombre,v_apellido1,v_apellido2,v_email,MD5(v_password));

INSERT INTO ultimos_cambios(id_usuario) values(LAST_INSERT_ID());

INSERT INTO usuarios_emoticonos (archivo,id_usuario) VALUES (’11c14bd1496afd0e21df115d25b68e96′,LAST_INSERT_ID());
INSERT INTO usuarios_emoticonos (archivo,id_usuario) VALUES (’19bb8ebfe3c2f5ef3ffb9aa4a027900d’,LAST_INSERT_ID());
INSERT INTO usuarios_emoticonos (archivo,id_usuario) VALUES
END

Simplemente lo que hemos hecho ha sido meter consultas INSERT dentro procedimiento. Primero inserta el usuario y si no se crea (por reesctricciones o cualquier cosa) tampoco se insertarán últimos cambios ni emoticonos (aquí solo hay dos, esque no iba a poner los 20 o así que son). No hay condiciones que controlen eso pero sí restricciones. Si el usuario no se ha podido insertar, LAST_INSERT_ID() devuelve el numero ‘0’ que no coincide con el de ningún usuario entonces salta el foreign key diciendo “ey, que no hay usuario con el id ‘0’” y no se insertan. Aquí las restricciones nos han ayudado a hacer un procedimiento más sencillo.

Bien, este procedimiento es muy chorra así que vamos con un mas completito. Este procedimiento también es de Cutrenti y sirve para comentar fotos. Cuando se comenta una foto, además de insertar la foto hay que avisar de ese comentario a todos los usuarios etiquetados. Como has adivinado, aquí usamos un cursor también.

CREATE  PROCEDURE comentar_foto (v_id_comentador INT, v_id_usuario_foto INT, v_n_foto INT, v_comentario VARCHAR(255), v_audio INT)
BEGIN
DECLARE ultimo INT;
DECLARE v_id_aparicion int;
DECLARE v_audio_fin int;
DECLARE terminado INT DEFAULT 0;
DECLARE c_apariciones CURSOR FOR SELECT id_aparicion FROM apariciones where id_usuario=v_id_usuario_foto and n_foto=v_n_foto and id_aparicion<>v_id_comentador;
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET terminado = 1;

SET ultimo = (select n_comentario from comentarios WHERE id_usuario=v_id_usuario_foto and n_foto=v_n_foto ORDER BY n_comentario DESC LIMIT 1)+1;

SET v_audio_fin = v_audio;

IF v_audio_fin < 0 THEN
SET v_audio_fin=null;
END IF;

IF ultimo IS null THEN
SET ultimo=0;
END IF;
INSERT INTO comentarios (id_usuario,n_foto,id_comentador,comentario,n_comentario,id_multimedia) VALUES (v_id_usuario_foto,v_n_foto,v_id_comentador,v_comentario,ultimo,v_audio_fin);

OPEN c_apariciones;
REPEAT
FETCH c_apariciones into v_id_aparicion;
IF terminado = 0 THEN
UPDATE usuarios SET nuevos_comentarios=1 WHERE id_usuario=v_id_aparicion;
INSERT INTO nuevos_comentarios (id_usuario,id_usuario_foto,n_foto) VALUES (v_id_aparicion,v_id_usuario_foto,v_n_foto);
END IF;
UNTIL terminado END REPEAT;

CLOSE c_apariciones;

END

Primero obtenemos el último id para sumarle uno y así tener el nuevo id. No podemos usar AUTO INCREMENT porque la clave primaria está formada por varios elementos y por desgracia MySQL no lo permite. Después comprobamos si se ha insertado un audio. No comprobamos si el audio existe o no ya que la restricción foreign key lo hará por nosotros (si no existe, no deja que se cree). Después se inserta el comentario y por último se avisa a todos los usuarios etiquetados a los que llamo “apariciones”. Eso se hace un con cursor y un bucle. Este bucle sería el equivalente a un “do while”. La característica de ese bucle es que siempre se entra por lo menos una vez a diferencia de while donde sólo se entra si se cumple la condición (y puede que ni se entre al bucle).

Ese cursor lo que hace es actualizar el estado de cada usuario que aparece en la foto e insertar el tipo de actividad. Esto se repetirá mientras haya “apariciones”.

En este procedimiento hemos usado INSERT, SELECT y UPDATE con cursor de regalo. Se podría decir que es un ejemplo bastante completito. Luego ya te puedes líar aninando cursores y todo lo que se te ocurra.

Creo que con estos ejemplos queda más o menos claro el uso que se le puede dar a un procedimiento y esque no es más que una función que podamos hacer en PHP pero beneficiándonos de las ventajas que nos da el gestor de bases de datos, como por ejemplo la independencia que tenemos al estar insertado en la base de datos y poder usarlo desde cualquier programa.

Y por último, para usar un procedimiento la consulta es la siguiente:

call nombre_procedimiento (parametro1,parametro2)

Como último consejo, es recomendable que este tipo de funciones se hagan en el sistema gestor de bases de datos y no en la propia aplicación. Lo siguiente ya son los triggers, muy interesantes no te lo puedes perder!

You may also like...

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos necesarios están marcados *