Procediments i funcions a MySQL
Contingut
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().
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.
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
