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

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 2

Exercici 3

Cursor amb paràmetres:

Exercici 4

  • Funcions

Exercici 5

creat per Joan Quintana Compte, abril 2022

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