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 |
+----------+

Modificar procediments emmagatzemats

A Oracle tenim la sentència CREATE OR REPLACE PROCEDURE.... Però a MySQL no es fa així. El que s'ha de fer és esborrar el procediment i tornar-lo a crear:

DROP PROCEDURE IF EXISTS SetCounter;

DELIMITER $$

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

DELIMITER ;

SET @counter = 1;

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

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

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

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

Variables

Com en qualsevol llenguatge de programació, podem declarar variables:

DECLARE variable_name datatype(size) [DEFAULT default_value];

DECLARE totalSale DEC(10,2) DEFAULT 0.0;
DECLARE x, y INT DEFAULT 0;

Per assignar valors a una variable:

SET variable_name = value;

Per exemple:

DECLARE total INT DEFAULT 0;
SET total = 10;

Un exemple:

DELIMITER $$

CREATE PROCEDURE GetTotalOrder()
BEGIN
	DECLARE totalOrder INT DEFAULT 0;
    
    SELECT COUNT(*) INTO totalOrder FROM orders;
    SELECT totalOrder;
END$$

DELIMITER ;

CALL GetTotalOrder();

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';

+-----------------------+
| ROUTINE_NAME          |
+-----------------------+
| GetCustomers          |
| GetOfficeByCountry    |
| GetOrderCountByStatus |
| GetTotalOrder         |
| SetCounter            |
+-----------------------+

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

mysql> SHOW PROCEDURE STATUS WHERE db = 'classicmodels';
+---------------+-----------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db            | Name                  | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+---------------+-----------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| classicmodels | GetCustomers          | PROCEDURE | root@localhost | 2022-03-16 09:08:27 | 2022-03-16 09:08:27 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | latin1_swedish_ci  |
| classicmodels | GetOfficeByCountry    | PROCEDURE | root@localhost | 2022-03-16 09:29:50 | 2022-03-16 09:29:50 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | latin1_swedish_ci  |
| classicmodels | GetOrderCountByStatus | PROCEDURE | root@localhost | 2022-03-16 09:32:18 | 2022-03-16 09:32:18 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | latin1_swedish_ci  |
| classicmodels | GetTotalOrder         | PROCEDURE | root@localhost | 2022-03-16 10:42:32 | 2022-03-16 10:42:32 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | latin1_swedish_ci  |
| classicmodels | SetCounter            | PROCEDURE | root@localhost | 2022-03-16 10:35:30 | 2022-03-16 10:35:30 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | latin1_swedish_ci  |
+---------------+-----------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

---

creat per Joan Quintana Compte, març 2022