Procedimientos de almacenados [STORED PROCEDURES]

Noté que muchos compañeros querían ahondar en este tema, la verdad es que yo vengo manejando base de datos ya más de un año y es mi pan de cada día. Y eh tocado este y otros temas, pero en SQL Server, y aunque son muy similares tienen sus diferencias.

Bueno antes que nada ya se debe tener en cuenta que es un DML y un DDL. Los procedures, triggers, vistas, entre otros pertenecen al *DDL y los comandos propios de un DDL son:

• CREATE
• ALTER
• DROP
• Entre otros

Teniendo esto en mente, es fácil deducir cual va a ser el comando a usar para crear un PROCEDURE.

Y cito la documentación de MySQL:
MySQL Documentation Stored Procedures

Bien lo primero a escribir es la creación del Procedure Ej:

CREATE PROCEDURE SP_INS_AUTHORS

Donde CREATE PROCEDURE es la sentencia de creación, y SP_INS_AUTHORS es un nombre cualquiera, pero es buena practica escribir las iniciales de a que tipo de objeto hace referencia SP [STORED PROCEDURES], seguida de la acción que realizará INS [INSERT] y al final la tabla a la que afectará AUTHORS.

Pero ahí no termina todo, seguido de ello, vienen los parámetros, es decir los valores que recibirá el INSERT y aprovecho para decirles que aunque un procedure pertenezca al lenguaje DDL en su interior contendrá (por lo general) lenguaje DML. Pero vamos por paso, primero los parámetros.

CREATE PROCEDURE SP_INS_AUTHORS(p_name VARCHAR(100), p_nationality VARCHAR(100))

Ahora ya tenemos la primera linea. Si has trabajado antes con lenguajes de programación como python, php o java (por mencionar alguno) es lo mismo que cuando creas una función, entre paréntesis, deben ir los parámetros con su respectivo tipo de dato, idéntico a como lo definiste en la creación de la tabla para evitar conflictos. Además si te das cuenta antepuse una p_ a los nombres de mis campos, tan solo para hacerlos mas legible, y dar a notar mas adelante que me refiero a parámetros.

Ahora bien, pudiéramos dejar nuestro código de la siguiente manera:

CREATE PROCEDURE SP_INS_AUTHORS(p_name VARCHAR(100), p_nationality VARCHAR(100))
INSERT INTO authors(name, nationality) VALUES(p_name, p_nationality);

Donde ya definimos nuestro DML INSERT (va a funcionar correctamente), pero es una buena práctica comenzar con un BEGIN y finalizar con un END. Quedando de la siguiente manera:

CREATE PROCEDURE SP_INS_AUTHORS(p_name VARCHAR(100), p_nationality VARCHAR(100))
BEGIN
  INSERT INTO authors(name, nationality) VALUES(p_name, p_nationality);
END;

Y como vemos así quedaría nuestro Procedure. Y si nos damos cuenta en VALUE van los valores que pasamos como parámetros.

• El Insert ya no lo explico puesto eso ya se vio en el curso 😄

Ahora si queremos hacer uso de nuestro Procedure hacemos lo siguiente.

CALL SP_INS_AUTHORS('LUIS ARCE', 'ECU');

Y esto es como si se le pasara los valores directamente al INSERT

Aquí un ejemplo de como eliminar un procedure:

DROP PROCEDURE SP_INS_AUTHORS;

Algo importante es que en MySQL no se puede modificar los parametros y/o el comportamiento del procedure (ALgo que en SQL Server si se puede 😄)

Alter Procedures

UPDATE con procedures

CREATE PROCEDURE SP_UPD_AUTHORS(p_name VARCHAR(100), p_nationality VARCHAR(100), p_id INTEGER)
BEGIN
  UPDATE authors SET name = p_name, nationality = p_nationality WHERE author_id = p_id;
END;

CALL SP_UPD_AUTHORS('CARLOS VILLAGRAN', 'ECU', 193);

DELETE con procedures

CREATE PROCEDURE SP_DEL_AUTHORS(p_id INTEGER)
BEGIN
  DELETE FROM authors WHERE author_id = p_id;
END;

CALL SP_DEL_AUTHORS(193);

Espero les haya servido de mucho 😄