Procediments emmagatzemats. Bàsic

De wikijoan
Salta a la navegació Salta a la cerca

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,...

Seguirem el tutorial:

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

Explicat:

  • Los stored procedures típicamente ayudan a aumentar el rendimiento de las aplicaciones. Una vez creados, los stored procedures se compilan y almacenan en la base de datos. Sin embargo, MariaDB implementa los stored procedures ligeramente diferentes. Los stored procedures de MariaDB se compilan bajo demanda. Después de compilar un stored procedure, MariaDB lo coloca en un caché. Y MariaDB mantiene su propio caché de stored procedures para cada conexión. Si una aplicación utiliza un stored procedure varias veces en una sola conexión, se utiliza la versión compilada, de lo contrario, el stored procedure funciona como una consulta.
  • Los stored procedures ayudan a reducir el tráfico entre la aplicación y el servidor de bases de datos, ya que en lugar de enviar varias sentencias SQL largas, la aplicación sólo tiene que enviar nombre y parámetros del stored procedure.
  • Los stored procedures son reutilizables y transparentes para cualquier aplicación. Los stored procedures exponen la interfaz de la base de datos a todas las aplicaciones para que los desarrolladores no tengan que desarrollar funciones que ya están soportadas en stored procedure.
  • Los stored proceedures son seguros. El administrador de bases de datos puede conceder permisos adecuados a las aplicaciones que acceden a stored procedures en la base de datos sin conceder permisos sobre las tablas de base de datos subyacentes.

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

Explicat:

  • Si utiliza una gran cantidad de stored procedures, el uso de memoria de cada conexión que utiliza estos stored procedures aumentará sustancialmente. Además, si sobreutiliza un gran número de operaciones lógicas dentro de stored procedure, el uso de CPU también aumentará porque el servidor de base de datos no está bien diseñado para operaciones lógicas.
  • Las construcciones de stored procedures dificultan el desarrollo de stored procedures que tienen una lógica de negocio complicada.
  • Es difícil depurar stored procedures. Sólo unos pocos sistemas de gestión de bases de datos permiten depurar stored procedures. Desafortunadamente, MariaDB no proporciona facilidades para depurar stored procedures.
  • No es fácil desarrollar y mantener stored procedures. Desarrollar y mantener stored procedures a menudo se requiere un conjunto de habilidades especializadas que no todos los desarrolladores de aplicaciones poseen. Esto puede dar lugar a problemas tanto en el desarrollo de aplicaciones como en las fases de mantenimiento.

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