Procediments i funcions a MySQL

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:

• https://www.oracletutorial.com/plsql-tutorial/plsql-cursor/

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.

Exercici 2

• https://www.oracletutorial.com/plsql-tutorial/plsql-cursor-for-loop/

Exercici 3

Cursor amb paràmetres:

• https://www.oracletutorial.com/plsql-tutorial/plsql-cursor-with-parameters/
• https://stackoverflow.com/questions/40907109/mysql-how-to-use-parameters-in-cursor

Exercici 4

• Funcions

Exercici 5

• https://linuxhint.com/mysql-cursor/

---

creat per Joan Quintana Compte, abril 2022