Diferència entre revisions de la pàgina «Procediments i funcions a MySQL»

De wikijoan
Salta a la navegació Salta a la cerca
Línia 298: Línia 298:
 
</pre>
 
</pre>
  
−
==Exercici 5==
+
==Exercici 5: funció==
 +
Implementar el següent exemple de funció que es mostra en aquest enllaç:
 
*https://linuxhint.com/mysql-cursor/
 
*https://linuxhint.com/mysql-cursor/
  
 +
Creació de les taules i inserció de les dades:
 +
<pre>
 +
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');
 +
</pre>
 +
Creació de la funció:
 +
<pre>
 +
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 ;
 +
</pre>
 +
Execució de la funció:
 +
<pre>
 +
mysql> SELECT Calculate_Bonus (1);
 +
The bonus of Kamal Hasan is TK.1260000.
 +
</pre>
 
{{Autor}}, abril 2022
 
{{Autor}}, abril 2022

Revisió del 16:51, 18 abr 2022

Introducció

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

Tasques a realitzar

Exercici 1

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(). 

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

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.

I la 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 ;

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

Creació de la funció: 

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.

creat per Joan Quintana Compte, abril 2022

Obtingut de «http://wiki.joanillo.org/index.php?title=Procediments_i_funcions_a_MySQL&oldid=298940»