Procediments i funcions a MySQL

De wikijoan
Salta a la navegació Salta a la cerca

Introducció

Realitzarem uns quants exercicis per consolidar els conceptes de procediments, cursors i funcions.

Tasques a realitzar

Exercici 1: procediment amb cursor

Ens basem en el següent exercici de Oracle (PL/SQL):

però de fet és molt senzill. Es tracta de definir un cursor i mostrar la llista de tots els productes (codi del producte, nom del producte i preu, amb el format separat per comes). Ho farem amb el procediment llistarProductes().

L'equivalent a l'exemple és utilitzar la base de dades classicmodels.

SOLUCIÓ:

DROP PROCEDURE IF EXISTS llistarProductes;

DELIMITER $$
CREATE PROCEDURE llistarProductes ()
BEGIN
   DECLARE finished INT DEFAULT 0;
   DECLARE vproductCode varchar(15);
   DECLARE vproductName varchar(70);
   DECLARE vbuyPrice decimal(10,2);
   DECLARE curProductes CURSOR FOR SELECT productCode, productName, buyPrice FROM products;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

   OPEN curProductes;

   getProductes: LOOP
      FETCH curProductes INTO vproductCode,vproductName,vbuyPrice;
         IF finished = 1 THEN 
            LEAVE getProductes;
         END IF;

         SELECT CONCAT(vproductCode,';',vproductName,';',vbuyPrice);

   END LOOP getProductes;
   CLOSE curProductes;

END$$

DELIMITER ;
CALL llistarProductes();

S10_1678;1969 Harley Davidson Ultimate Chopper;48.81
S10_1949;1952 Alpine Renault 1300;98.58
S10_2016;1996 Moto Guzzi 1100i;68.99
S10_4698;2003 Harley-Davidson Eagle Drag Bike;91.02
S10_4757;1972 Alfa Romeo GTA;85.68
S10_4962;1962 LanciaA Delta 16V;103.42
S12_1099;1968 Ford Mustang;95.34
S12_1108;2001 Ferrari Enzo;95.59
S12_1666;1958 Setra Bus;77.90
...

NOTA: per tal de què no surtin totes les línies de separació farem servir l'opció silent:

$ mysql -u alumne -pkeiL2lai -ss classicmodels

Si volem executar directament el procediment des de la línia de comandes de Linux, i generar un fitxer CSV, podem fer:

$ mysql -u alumne -pkeiL2lai -ss classicmodels -e 'CALL llistarProductes()' > llistaProductes.csv

$ cat llistaProductes.csv 
S10_1678;1969 Harley Davidson Ultimate Chopper;48.81
S10_1949;1952 Alpine Renault 1300;98.58
S10_2016;1996 Moto Guzzi 1100i;68.99
...

Exercici 2: procediment assignarLimitCredit()

Ens basem en el següent exemple, que està basat en Oracle i el llenguatge PL/SQL:

L'equivalent a l'exemple és utilitzar la base de dades classicmodels. Tenim la taula orders i orderdetails.

mysql> desc orders;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber    | int         | NO   | PRI | NULL    |       |
| orderDate      | date        | NO   |     | NULL    |       |
| requiredDate   | date        | NO   |     | NULL    |       |
| shippedDate    | date        | YES  |     | NULL    |       |
| status         | varchar(15) | NO   |     | NULL    |       |
| comments       | text        | YES  |     | NULL    |       |
| customerNumber | int         | NO   | MUL | NULL    |       |
+----------------+-------------+------+-----+---------+-------+

mysql> desc orderdetails;
+-----------------+---------------+------+-----+---------+-------+
| Field           | Type          | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| orderNumber     | int           | NO   | PRI | NULL    |       |
| productCode     | varchar(15)   | NO   | PRI | NULL    |       |
| quantityOrdered | int           | NO   |     | NULL    |       |
| priceEach       | decimal(10,2) | NO   |     | NULL    |       |
| orderLineNumber | smallint      | NO   |     | NULL    |       |
+-----------------+---------------+------+-----+---------+-------+

Podem crear la vista sales:

CREATE VIEW sales AS
SELECT customerNumber,
       SUM(priceEach * quantityOrdered) total,
       ROUND(SUM(priceEach * quantityOrdered) * 0.05) credit
FROM orderdetails
INNER JOIN orders USING (orderNumber)
WHERE status = 'Shipped'
GROUP BY customerNumber;

La idea és que com més bo és un client (com més comandes hagi fet), el seu crèdit serà més bo. Assignarem als clients un crèdit del 5% del total que han facturat.

L'objectiu del procediment assignarLimitCredit() serà:

1. Fer un reset del crèdit dels clients (camp customers.creditLimit). Ho farem amb un udpate creditLimit=0 ....

2. Tenim un pressupost de 1 milió (1000000) per assignar crèdit als clients. Recorrem (cursor) els clients amb la vista sales, i anirem assignant el nou crèdit als clients fins mentre no ens passem del pressupost que tenim.

La solució amb PL/SQL la tens a l'enllaç. Ho hem de fer amb MySQL.

SOLUCIÓ amb MySQL:

DROP PROCEDURE IF EXISTS assignarLimitCredit;

DELIMITER $$
CREATE PROCEDURE assignarLimitCredit ()
BEGIN
   DECLARE finished INT DEFAULT 0;
   DECLARE vbudget INT DEFAULT 1000000;
   DECLARE vcustomerNumber INT;
   DECLARE vcredit decimal(42,0);
   DECLARE curSales CURSOR FOR SELECT customerNumber, credit FROM sales;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

   UPDATE customers SET creditLimit = 0;

   OPEN curSales;

   getSales: LOOP
      FETCH curSales INTO vcustomerNumber, vcredit;
         IF finished = 1 THEN 
            LEAVE getSales;
         END IF;
         IF vbudget > vcredit THEN
            UPDATE customers SET creditLimit=vcredit WHERE customerNumber=vcustomerNumber;
         END IF;

         SET vbudget = vbudget - vcredit;
         
         IF vbudget <= 0 THEN 
            LEAVE getSales;
         END IF;

   END LOOP getSales;
   CLOSE curSales;

END$$

DELIMITER ;

Abans d'executar el procediment (les dades originals):

mysql> select customerNumber,customerName,creditLimit from customers limit 10;
+----------------+------------------------------+-------------+
| customerNumber | customerName                 | creditLimit |
+----------------+------------------------------+-------------+
|            103 | Atelier graphique            |    21000.00 |
|            112 | Signal Gift Stores           |    71800.00 |
|            114 | Australian Collectors, Co.   |   117300.00 |
|            119 | La Rochelle Gifts            |   118200.00 |
|            121 | Baane Mini Imports           |    81700.00 |
|            124 | Mini Gifts Distributors Ltd. |   210500.00 |
|            125 | Havel & Zbyszek Co           |        0.00 |
|            128 | Blauer See Auto, Co.         |    59700.00 |
|            129 | Mini Wheels Co.              |    64600.00 |
|            131 | Land of Toys Inc.            |   114900.00 |
+----------------+------------------------------+-------------+

Executem el procediment i assignem nous valors de límit de crèdit als clients:

CALL assignarLimitCredit();

Després d'executar el procediment:

mysql> select customerNumber,customerName,creditLimit from customers limit 10;
+----------------+------------------------------+-------------+
| customerNumber | customerName                 | creditLimit |
+----------------+------------------------------+-------------+
|            103 | Atelier graphique            |     1116.00 |
|            112 | Signal Gift Stores           |     4009.00 |
|            114 | Australian Collectors, Co.   |     9029.00 |
|            119 | La Rochelle Gifts            |     5847.00 |
|            121 | Baane Mini Imports           |     5211.00 |
|            124 | Mini Gifts Distributors Ltd. |    29209.00 |
|            125 | Havel & Zbyszek Co           |        0.00 |
|            128 | Blauer See Auto, Co.         |     3797.00 |
|            129 | Mini Wheels Co.              |     3336.00 |
|            131 | Land of Toys Inc.            |     5382.00 |
+----------------+------------------------------+-------------+

Exercici 3: cursor amb paràmetres

Ens fixem en el següent exemple, com a punt de partida:

Sobre la base de dades classicmodels i la taula orders, crear el procediment llistarComandes(vstatus) per saber el número de comanda, dia de la comanda i número del client, filtrant per un valor vàlid del camp status. Crearem un cursor per a les comandes, filtrant pel valor de status.

SOLUCIÓ:

DROP PROCEDURE IF EXISTS llistarComandes;

DELIMITER $$
CREATE PROCEDURE llistarComandes(IN vstatus varchar(15))
BEGIN
    DECLARE vorderNumber INT;
    DECLARE vorderDate DATE;
    DECLARE vcustomerNumber INT;
    DECLARE done INT DEFAULT FALSE;

    DECLARE curLlistaComandes CURSOR FOR
    	SELECT orderNumber, orderDate, customerNumber FROM orders WHERE status =  vstatus;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN curLlistaComandes;
        read_loop: LOOP
        FETCH curLlistaComandes INTO vorderNumber, vorderDate, vcustomerNumber;
        IF done THEN LEAVE read_loop; END IF;
		select vorderNumber, vorderDate, vcustomerNumber;
        END LOOP;
    CLOSE curLlistaComandes;
END$$

DELIMITER ;

Execució:

CALL llistarComandes('Shipped');

Les diferents opcions que tenim per al camp status són:

mysql> select distinct status from orders;

Shipped
Resolved
Cancelled
On Hold
Disputed
In Process
mysql> CALL llistarComandes('Resolved');
10164	2003-10-21	452
10327	2004-11-10	145
10367	2005-01-12	205
10386	2005-03-01	141

mysql> CALL llistarComandes('Cancelled');
10167	2003-10-23	448
10179	2003-11-11	496
10248	2004-05-07	131
10253	2004-06-01	201
10260	2004-06-16	357
10262	2004-06-24	141

Exercici 4: funció

Crear la funció DisputedGlobal() que ens doni el total d'euros que representen les comandes que encara estan disputades.

La select que necessites és:

SELECT SUM(priceEach*quantityOrdered) FROM orders o INNER JOIN orderdetails od USING(orderNumber )WHERE status = 'Disputed';
61158.78

SOLUCIÓ:

DROP FUNCTION IF EXISTS DisputedGlobal;

DELIMITER $$

CREATE FUNCTION DisputedGlobal(
) 
RETURNS DECIMAL(8,2)
DETERMINISTIC
BEGIN
    DECLARE total DECIMAL(8,2);
    SELECT SUM(priceEach*quantityOrdered) INTO total FROM orders o INNER JOIN orderdetails od USING(orderNumber )WHERE status = 'Disputed';
	RETURN (total);
END$$
DELIMITER ;

Per executar la funció i trobar el valor ho podem fer de dues maneres (equivalents):

mysql> SELECT DisputedGlobal();
61158.78

mysql> SET @vtotal = DisputedGlobal();
mysql> select @vtotal;
61158.78

Exercici 5: funció

Implementar el següent exemple de funció que es mostra en aquest enllaç:

Creació de les taules i inserció de les dades:

CREATE TABLE sales_persons(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
email VARCHAR(50),
contact_no VARCHAR(30));

INSERT INTO `sales_persons` (`id`, `name`, `email`, `contact_no`) VALUES
(NULL, 'Kamal Hasan', 'kamal@gmail.com', '0191275634'),
(NULL, 'Nila Hossain', 'nila@gmail.com', '01855342357'),
(NULL, 'Abir Hossain', 'abir@yahoo.com', '01634235698');

CREATE TABLE sales(
id INT NOT NULL PRIMARY KEY,
sales_date DATE NOT NULL,
amount INT,
sp_id int,
CONSTRAINT fk_sp FOREIGN KEY (sp_id)
REFERENCES sales_persons(id)
ON DELETE CASCADE ON UPDATE CASCADE);

INSERT INTO `sales` (`id`, `sales_date`, `amount`, `sp_id`) VALUES
('90', '2021-11-09', '800000', '1'),
('34', '2020-12-15', '5634555', '3'),
('67', '2021-12-23', '900000', '1'),
('56', '2020-12-31', '6700000', '1');

SOLUCIÓ:

DROP FUNCTION IF EXISTS `Calculate_Bonus`;

DELIMITER //

CREATE FUNCTION `Calculate_Bonus`(`spid` INT)
RETURNS VARCHAR(200)
READS SQL DATA
DETERMINISTIC
BEGIN
	DECLARE done INT DEFAULT FALSE;
	DECLARE total INT DEFAULT 0;
	DECLARE person VARCHAR(50) DEFAULT "";
	DECLARE bonus INT DEFAULT 0;
	DECLARE output VARCHAR(200);

	DECLARE cur CURSOR FOR
	SELECT name, SUM(amount)
	FROM sales, sales_persons
	WHERE sales_persons.id=sales.sp_id and sp_id = spid
	GROUP By sp_id;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	
	OPEN cur;

	FETCH cur INTO person, total;
	IF total > 10000 THEN
		SET bonus = total*0.15;
	ELSE
		SET bonus = total*0.10;
	END IF;

	SET output = CONCAT("The bonus of ", person," is TK.", bonus, ".");
	
	CLOSE cur;
	RETURN output;
END;
//

DELIMITER ;

Execució de la funció:

mysql> SELECT Calculate_Bonus (1);
The bonus of Kamal Hasan is TK.1260000.

Entrega

Entregaràs un pdf amb captures de pantalla conforme que has executat els exercicis proposats, dins del termini previst. Un cop passat el termini es publicaran les solucions i encara es podrà entregar la pràctica on es demostren que s'executen correctament els exercicis (amb penalització).


creat per Joan Quintana Compte, abril 2022