¿Cómo trasladar valores entre procedimientos almacenados en MySQL?

Para esta demostración estamos usando la base de datos de ejemplo de Northwind en Mysql que consiste en un sistema de pedidos, cuenta con las tablas Customers, Orders, OrderDetails, Products, Categories, Suppliers, Employees, entre otras.

En principio queremos crear un procedimiento que llame a otro procedimiento almacenado, así que vamos a crear dos procedimientos el primero solicita como parametro el código del cliente y contendra la instrucción para eliminar el cliente.

El segundo procedimiento también solicita el codigo del cliente y usa una condición para evaluar si el cliente tiene ordenes y si tiene indicara que no se puede eliminar el cliente pero si no tiene invocará al primer procedimiento almacenado, el código SQL debe de ser:

Procedimiento 1:
Delimiter $$
Create procedure Proc_DeleteCustomer (In Codigo varchar(5))
Begin
Delete from Customers where Customerid=Codigo;
End
$$
Delimiter ;

Procedimiento 2:

Delimiter $$
Create procedure Proc_QuitarCliente (In IDcliente varchar(5))
Begin
If (Select Count(*) from Orders where Customerid=IDCliente)>0 then
Select 'No se puede eliminar a este cliente';
else
Call Proc_DeleteCustomer (IDcliente);
end if;
End
$$
Delimiter ;

Ahora en un segundo ejercicio se necesita crear un procedimiento almacenado que inserte un nuevo registro en la tabla OrderDetails, dentro de los campos que se deben de insetar esta el Precio por Unidad al que se vende el producto pero este precio en vez de ingresarlo se debe de buscar del catalogo de productos, que es la tabla productos, a través de un procedimiento almacenado:

En resumen el primer procedimiento debe buscar el precio del producto y luego trasladarselo al segundo procedimiento, esto se puede hacer de dos formas, la primera forma es creando una tabla temporal que usen los dos procedimientos y que sirva para compartir el dato buscado. La segunda forma es usar los parametros de salida de los que disponemos en los procedimientos.

Forma con tabla temporal:

Procedimiento 1:

Delimiter $$
Create procedure Proc_ConsultarPrecio (In Codigo int)
Begin
drop temporary table if exists TablaTemporal;
create temporary table TablaTemporal as Select Unitprice from Products where ProductID=Codigo;
End
$$
Delimiter ;

Procedimiento 2:

Delimiter $$
Create procedure Proc_InsertarDetalle (In NumOrden Int, In NumProd int, In Cantidad int, In Descuento decimal(7,2))
Begin
Declare VarPrecio decimal(7,2);
Call Proc_ConsultarPrecio(NumProd) ;
Set VarPrecio= (Select UnitPrice from TablaTemporal);
Insert into Orderdetails (OrderID, ProductID, UnitPrice, Quantity, Discount)
Values (NumOrden, NumProd, VarPrecio, Cantidad, Descuento );
drop temporary table if exists TablaTemporal;
End
$$
Delimiter ;

Forma utilizando parametros de salida del procedimiento:

Procedimiento 1:

Delimiter $$
Create Procedure proc_consultar_precio (inout precio int,In CodProd int)
Begin
select unitprice into precio from products where productId = CodProd;
End
$$
Delimiter ;

Procedimiento 2:

Delimiter $$
Create Procedure Insert_Orderdetail(IN POrderId Int(11), IN PProductID int(11), PQuantity Int(6), PDiscount double)
Begin
Declare Variable1 decimal(7,2);
Call proc_consultar_precio(Variable1, PProductID);
Insert into Orderdetails(OrderId, ProductID, UnitPrice, Quantity, Discount)
Values(POrderId,PProductID,Variable1,PQuantity,Discount);
End
$$
Delimiter ;

Para esta demostración estamos usando la base de datos de ejemplo de Northwind en Mysql que consiste en un sistema de pedidos, cuenta con las tablas Customers, Orders, OrderDetails, Products, Categories, Suppliers, Employees, entre otras.

En principio queremos crear un procedimiento que llame a otro procedimiento almacenado, así que vamos a crear dos procedimientos el primero solicita como parametro el código del cliente y contendra la instrucción para eliminar el cliente.

El segundo procedimiento también solicita el codigo del cliente y usa una condición para evaluar si el cliente tiene ordenes y si tiene indicara que no se puede eliminar el cliente pero si no tiene invocará al primer procedimiento almacenado, el código SQL debe de ser:

Procedimiento 1:
Delimiter $$
Create procedure Proc_DeleteCustomer (In Codigo varchar(5))
Begin
Delete from Customers where Customerid=Codigo;
End
$$
Delimiter ;

Procedimiento 2:

Delimiter $$
Create procedure Proc_QuitarCliente (In IDcliente varchar(5))
Begin
If (Select Count(*) from Orders where Customerid=IDCliente)>0 then
Select 'No se puede eliminar a este cliente';
else
Call Proc_DeleteCustomer (IDcliente);
end if;
End
$$
Delimiter ;

Ahora en un segundo ejercicio se necesita crear un procedimiento almacenado que inserte un nuevo registro en la tabla OrderDetails, dentro de los campos que se deben de insetar esta el Precio por Unidad al que se vende el producto pero este precio en vez de ingresarlo se debe de buscar del catalogo de productos, que es la tabla productos, a través de un procedimiento almacenado:

En resumen el primer procedimiento debe buscar el precio del producto y luego trasladarselo al segundo procedimiento, esto se puede hacer de dos formas, la primera forma es creando una tabla temporal que usen los dos procedimientos y que sirva para compartir el dato buscado. La segunda forma es usar los parametros de salida de los que disponemos en los procedimientos.

Forma con tabla temporal:

Procedimiento 1:

Delimiter $$
Create procedure Proc_ConsultarPrecio (In Codigo int)
Begin
drop temporary table if exists TablaTemporal;
create temporary table TablaTemporal as Select Unitprice from Products where ProductID=Codigo;
End
$$
Delimiter ;

Procedimiento 2:

Delimiter $$
Create procedure Proc_InsertarDetalle (In NumOrden Int, In NumProd int, In Cantidad int, In Descuento decimal(7,2))
Begin
Declare VarPrecio decimal(7,2);
Call Proc_ConsultarPrecio(NumProd) ;
Set VarPrecio= (Select UnitPrice from TablaTemporal);
Insert into Orderdetails (OrderID, ProductID, UnitPrice, Quantity, Discount)
Values (NumOrden, NumProd, VarPrecio, Cantidad, Descuento );
drop temporary table if exists TablaTemporal;
End
$$
Delimiter ;

Forma utilizando parametros de salida del procedimiento:

Procedimiento 1:

Delimiter $$
Create Procedure proc_consultar_precio (inout precio int,In CodProd int)
Begin
select unitprice into precio from products where productId = CodProd;
End
$$
Delimiter ;

Procedimiento 2:

Delimiter $$
Create Procedure Insert_Orderdetail(IN POrderId Int(11), IN PProductID int(11), PQuantity Int(6), PDiscount double)
Begin
Declare Variable1 decimal(7,2);
Call proc_consultar_precio(Variable1, PProductID);
Insert into Orderdetails(OrderId, ProductID, UnitPrice, Quantity, Discount)
Values(POrderId,PProductID,Variable1,PQuantity,Discount);
End
$$
Delimiter ;

Revisar planes de ejecución en MySql

La sentencia EXPLAIN se usa en Mysql para proporciona información sobre cómo su base de datos ejecuta una consulta. En MySQL, EXPLAINse puede usar delante de una consulta que comienza con SELECT, INSERT, DELETE, REPLACEy UPDATE. 

En lugar de la salida de resultados habitual, MySQL mostraría su plan de ejecución explicando qué procesos tienen lugar y en qué orden se ejecutan las sentencias.

La información más importante de la salida del Explain es una columna TYPE que explica el tipo de recorrido que realiza para obtener los datos, si esta consultando indices o montones de datos.

eq_ref, const

Realiza un recorrido de un B-tree para encontrar una fila (como INDEX UNIQUE SCAN) y trae las columnas adicionales desde la tabla si se necesitan (TABLE ACCESS BY INDEX ROWID). La base de datos emplea esta operación si una clave primaria o una restricción de unicidad aseguran que el criterio de búsqueda coincide sólo con una entrada. Cuando la columna “Extra” muestra “Using Index” (Usando un índice), significa que no se produce acceso a la tabla porque el índice tiene todos los datos requeridos.

ref, range

Realiza un recorrido del B-tree, lee los nodos hoja para encontrar todas las entradas del índice. (similar a INDEX RANGE SCAN) y obtiene las columnas adicionales desde la primera tabla almacenada si es necesario (TABLE ACCESS BY INDEX ROWID).

index

Lee el índice entero (todos las filas) en el orden del mismo (similar a INDEX FULL SCAN).

all

Lee la tabla entera (todas las filas y las columnas) tal y como se almacena en disco. Además de la tasa alta de I/O, un escaneo de tabla debe leer todas las filas desde la tabla así que también puede agregar una carga considerable para la CPU.

Usando índice (en la columna “Extra”)

Cuando la columna “Extra” muestra “Using Index” (Usando un índice), significa que no se produce acceso a la tabla porque el índice tiene todos los datos requeridos. En este caso se puede pensar en utilizar solamente el índice. Sin embargo, si se usa una agrupación de índice (por ejemplo, el índice PRIMARY cuando se utiliza InnoDB), “Using Index” no aparece en la columna “Extra” aunque técnicamente se trata sólo de un escaneo del índice.