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 263: Línia 263:
  
 
==Exercici 4==
 
==Exercici 4==
−
Partint de l'exercici anterior, crear la funció DisputedGlobal() que ens doni el total de les comandes que encara estan disputades.
+
Crear la funció '''DisputedGlobal()''' que ens doni el total d'euros que representen les comandes que encara estan disputades.
 +
 
 +
La select que necessites és:
 +
<pre>
 +
SELECT SUM(priceEach*quantityOrdered) FROM orders o INNER JOIN orderdetails od USING(orderNumber )WHERE status = 'Disputed';
 +
61158.78
 +
</pre>
 +
 
 +
'''solució''':
 +
<pre>
 +
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 ;
 +
</pre>
 +
Per executar la funció i trobar el valor ho podem fer de dues maneres (equivalents):
 +
<pre>
 +
mysql> SELECT DisputedGlobal();
 +
61158.78
 +
 
 +
mysql> SET @vtotal = DisputedGlobal();
 +
mysql> select @vtotal;
 +
61158.78
 +
</pre>
  
 
==Exercici 5==
 
==Exercici 5==

Revisió del 16:41, 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

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

creat per Joan Quintana Compte, abril 2022

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