Diferentes tipos de procedimientos en MySQL

Un procedimiento es una subrutina (como un subprograma) en un lenguaje de programación normal, almacenado en una base de datos. En el caso de MySQL, los procedimientos se escriben en MySQL y se almacenan en la base de datos/servidor MySQL. Un procedimiento MySQL tiene un nombre, una lista de parámetros y declaraciones SQL.

Hay cuatro tipos diferentes de procedimientos MySQL:

1. Procedimiento sin parámetros:
un procedimiento sin parámetros no toma ninguna entrada ni proyecta una salida indirectamente. Simplemente se llama con su nombre de procedimiento seguido de() (sin ningún parámetro). Se utiliza para consultas simples.

Ejemplo:
Considere dos tablas autor y libro:

create table author (author_id integer primary key, 
                            authorName varchar(30), 
                            email varchar (25), gender varchar (6));

create table book (BookId integer not null unique, 
                        ISBN integer primary key, 
                       book_name varchar (30) not null, 
                        author integer, ed_num integer, 
                      price integer, pages integer, 
         foreign key (author) references author (author_id) on delete cascade);

Insertando valores en ellos:

insert into author values 
              (1, "Kraig Muller", "Wordnewton@gmail.com", "Male");
insert into author values
              (2, "Karrie Nicolette", "karrie23@gmail.com", "Female");
insert into book values
              (1, 001, "Glimpses of the past", 1, 1, 650, 396);
insert into book values
              (2, 002, "Beyond The Horizons of Venus", 1, 1, 650, 396);
insert into book values
              (3, 003, "Ultrasonic Aquaculture", 2, 1, 799, 500);
insert into book values
              (4, 004, "Cyrogenic Engines", 2, 1, 499, 330); 

Procedimiento (sin parámetros) para visualizar todos los libros:

delimiter //
create procedure display_book() 
                      -> begin 
                      -> select *from book; 
                      -> end //
call display_book(); //

Producción:

+--------+------+------------------------------+--------+--------+-------+-------+
| BookId | ISBN | book_name                    | author | ed_num | price | pages |
+--------+------+------------------------------+--------+--------+-------+-------+
|      1 |    1 | Glimpses of the past         |      1 |      1 |   650 |   396 |
|      2 |    2 | Beyond The Horizons of Venus |      1 |      1 |   650 |   396 |
|      3 |    3 | Ultrasonic Aquaculture       |      2 |      1 |   799 |   500 |
|      4 |    4 | Cyrogenic Engines            |      2 |      1 |   499 |   330 |
+--------+------+------------------------------+--------+--------+-------+-------+
4 rows in set (0.0012 sec)

2. Procedimiento con parámetro IN:
Un parámetro IN se utiliza para tomar como entrada un parámetro como un atributo. Cuando definimos un parámetro IN en un procedimiento, el programa que llama tiene que pasar un argumento al procedimiento almacenado. Además, el valor de un parámetro IN está protegido. Significa que incluso si el valor del parámetro IN se cambia dentro del procedimiento, su valor original se conserva después de que finaliza el procedimiento (como pasar por valor). En otras palabras, el procedimiento solo funciona en la copia del parámetro IN.

Ejemplo:
Procedimiento para actualizar el precio de un libro tomando como input el ISBN del libro y su nuevo precio: (considerando las tablas anteriores)

delimiter //
create procedure update_price (IN temp_ISBN varchar(10), IN new_price integer)
               -> begin
               -> update book set price=new_price where ISBN=temp_ISBN;
               -> end; //
call update_price(001, 600); //

Cambiamos el precio del libro con ISBN ‘001’ (Glimpses of the past) a 600 (desde su precio por defecto 650).

Producción:

delimiter ;
select *from book;
+--------+------+------------------------------+--------+--------+-------+-------+
| BookId | ISBN | book_name                    | author | ed_num | price | pages |
+--------+------+------------------------------+--------+--------+-------+-------+
|      1 |    1 | Glimpses of the past         |      1 |      1 |   600 |   396 |
|      2 |    2 | Beyond The Horizons of Venus |      1 |      1 |   650 |   396 |
|      3 |    3 | Ultrasonic Aquaculture       |      2 |      1 |   799 |   500 |
|      4 |    4 | Cyrogenic Engines            |      2 |      1 |   499 |   330 |
+--------+------+------------------------------+--------+--------+-------+-------+
4 rows in set (0.0013 sec)

3. Procedimiento con parámetro OUT:
Un parámetro OUT se utiliza para pasar un parámetro como salida o visualización como el operador de selección, pero de forma implícita (a través de un valor establecido). El valor de un parámetro OUT se puede cambiar dentro del procedimiento y su nuevo valor se devuelve al programa de llamada. Un procedimiento no puede acceder al valor inicial del parámetro OUT cuando se inicia.

Ejemplo:
Procedimiento para mostrar el precio más alto entre todos los libros con un parámetro de salida:

delimiter //
create procedure disp_max(OUT highestprice integer)
                 -> begin
                 -> select max(price) into highestprice from book;
                 -> end; //
call disp_max(@M); //
select @M; 

Salida:
El precio más alto de nuestra base de datos de libros es el del libro con ISBN 003 (Ultrasonic Aquaculture) con un precio de 799, que se muestra.

+-----+
| @M  |
+-----+
| 799 |
+-----+
1 row in set (0.0005 sec) 

4. Procedimiento con parámetro IN-OUT:
Un parámetro INOUT es una combinación de parámetros IN y OUT. Significa que el programa que llama puede pasar el argumento, y el procedimiento almacenado puede modificar el parámetro INOUT y devolver el nuevo valor al programa que llama.

Ejemplo:
Procedimiento para tomar la entrada de tipo de género (‘Masculino’/’Femenino’ aquí) con un parámetro de entrada-salida que refleja el número de autores que caen en esa categoría/tipo de género:

delimiter //
create procedure disp_gender(INOUT mfgender integer, IN emp_gender varchar(6))  
                     -> begin 
                     -> select COUNT(gender) 
                         INTO mfgender FROM author where gender = emp_gender;   
                     -> end; //
delimiter ;
call disp_gender(@M, "Male");
select @M;
call disp_gender(@F, "Female");
select @F; 

Salida:
Tenemos dos autores, uno masculino y otro femenino según las inserciones en la tabla autor. Por lo tanto, la salida es 1 para un autor masculino y 1 para una autora femenina, respectivamente.

+----+
| @M |
+----+
|  1 |
+----+
1 row in set (0.0004 sec)
+----+
| @F |
+----+
|  1 |
+----+
1 row in set (0.0005 sec)