Procediments emmagatzemats. Bàsic

Introducció

Ja hem acabat d'estudiar el llenguatge SQL. Ara toca ampliar el SQL amb nova funcionalitat: programació dins de la base de dades. Veurem declaració de variables, condicionals, bucles, paràmetres d'entrada,...

Introducció als procediments emmagatzemats a MySQL

Utilitzarem la base de dades classicmodels amb MySQL.

Per crear un stored procedure:

DELIMITER $$

CREATE PROCEDURE GetCustomers()
BEGIN
	SELECT customerName, city, state, postalCode, country
	FROM customers
	ORDER BY customerName;
END$$

DELIMITER ;

Fixa't que necessitem canviar el delimitador.

Un cop està emmagatzemat, podem cridar el procediment fent:

mysql> CALL GetCustomers();

En aquest cas el procediment GetCustomers() retorna el mateix que una select. Però com veurem, a un procediment li afegirem instruccions de programació (declaracions de variables, condicionals, bucles,...) que el farà molt més potent.

Avantatges d'utilitzar procediments

• Reduce network traffic
• Centralize business logic in the database
• Make database more secure

Desavantatges d'utilitzar procediments

• Resource usages
• Troubleshooting: It's difficult to debug stored procedures. Unfortunately, MySQL does not provide any facilities to debug stored procedures like other enterprise database products such as Oracle and SQL Server.
• Maintenances

Canviar el delimitador per defecte

A MySQL, el delimitador per defecte és el punt i coma (;). Però ho podem caniviar:

DELIMITER //

SELECT * FROM customers //

SELECT * FROM products //

I en els procediments és necessari canviar-ho, doncs el punt i coma es fa servir dins la definició del procediment, i per tant hem de distingir entre el fi d'una setència dins del procediment, i la fi del procediment.

DELIMITER $$

CREATE PROCEDURE sp_name()
BEGIN
  -- statements
END $$

DELIMITER ;

Crear nous procediments emmagatzemats

DELIMITER //

CREATE PROCEDURE GetAllProducts()
BEGIN
	SELECT *  FROM products;
END //

DELIMITER ;

mysql> CALL GetAllProducts();

Des del MySQL Workbench també podem crear i executar els procediments. I també podem veure la llista de procediments emmagatzemats que tenim.

Eliminar procediments emmagatzemats

És molt fàcil:

DROP PROCEDURE [IF EXISTS] stored_procedure_name;

DROP PROCEDURE  GetAllProducts()

També ho podem fer des del workbench.

Parametres

Aquí comença la part interessant. Tenim paràmetres d'entrada (IN), paràmetres de sortida (OUT), i paràmetres d'entrada i de sortida (INOUT).

Sintaxi bàsica per definir un paràmetre:

[IN | OUT | INOUT] parameter_name datatype[(length)]

Paràmetres d'entrada

DELIMITER //

CREATE PROCEDURE GetOfficeByCountry(
	IN countryName VARCHAR(255)
)
BEGIN
	SELECT * 
 	FROM offices
	WHERE country = countryName;
END //

DELIMITER ;

CALL GetOfficeByCountry('USA');

Si no passem el paràmetre d'entrada dóna un error:

mysql> CALL GetOfficeByCountry();
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE classicmodels.GetOfficeByCountry; expected 1, got 0

Paràmetres de sortida

DELIMITER $$

CREATE PROCEDURE GetOrderCountByStatus (
	IN  orderStatus VARCHAR(25),
	OUT total INT
)
BEGIN
	SELECT COUNT(orderNumber)
	INTO total
	FROM orders
	WHERE status = orderStatus;
END$$

DELIMITER ;

mysql> CALL GetOrderCountByStatus('Shipped',@total);

mysql> SELECT @total;
+--------+
| @total |
+--------+
|    303 |
+--------+

mysql> CALL GetOrderCountByStatus('in process',@total);

mysql> SELECT @total AS  total_in_process;
+------------------+
| total_in_process |
+------------------+
|                6 |
+------------------+

Veiem com la variable total està disponible després d'haver cridar el procediment.

Paràmetres d'entrada i de sortida

Un paràmetre pot ser d'entrada i de sortida al mateix temps:

DELIMITER $$

CREATE PROCEDURE SetCounter(INOUT counter INT,IN inc INT)
BEGIN
	SET counter = counter + inc;
END$$

DELIMITER ;

SET @counter = 1;

CALL SetCounter(@counter,1); -- 2

CALL SetCounter(@counter,1); -- 3

CALL SetCounter(@counter,5); -- 8

SELECT @counter; -- 8
+----------+
| @counter |
+----------+
|        8 |
+----------+

Llistar els procediments emmagatzemats

La manera més fàcil:

SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]
SHOW PROCEDURE STATUS WHERE db = 'classicmodels';
SHOW PROCEDURE STATUS LIKE '%pattern%'

Llistar els procediments utilitzant el diccionari de dades:

SELECT 
    routine_name
FROM
    information_schema.routines
WHERE
    routine_type = 'PROCEDURE'
        AND routine_schema = 'classicmodels';

També podem veure fàcilment els procediments emmagatzemats amb el MySQL Workbench.

---

creat per Joan Quintana Compte, març 2022