Procediments emmagatzemats. Continuació
Contingut
IF. Condicionals
Ús del IF-ELSEIF-ELSE:
DELIMITER $$
CREATE PROCEDURE GetCustomerLevel(
IN pCustomerNumber INT,
OUT pCustomerLevel VARCHAR(20))
BEGIN
DECLARE credit DECIMAL DEFAULT 0;
SELECT creditLimit
INTO credit
FROM customers
WHERE customerNumber = pCustomerNumber;
IF credit > 50000 THEN
SET pCustomerLevel = 'PLATINUM';
ELSEIF credit <= 50000 AND credit > 10000 THEN
SET pCustomerLevel = 'GOLD';
ELSE
SET pCustomerLevel = 'SILVER';
END IF;
END $$
DELIMITER ;
I per provar el procediment:
CALL GetCustomerLevel(447, @level); SELECT @level; CALL GetCustomerLevel(447, @level); SELECT @level;
sentència CASE
La sintaxi de la clàusula CASE és com es mostra a continuació:
DELIMITER $$
CREATE PROCEDURE GetCustomerShipping(
IN pCustomerNUmber INT,
OUT pShipping VARCHAR(50)
)
BEGIN
DECLARE customerCountry VARCHAR(100);
SELECT
country
INTO customerCountry FROM
customers
WHERE
customerNumber = pCustomerNUmber;
CASE customerCountry
WHEN 'USA' THEN
SET pShipping = '2-day Shipping';
WHEN 'Canada' THEN
SET pShipping = '3-day Shipping';
ELSE
SET pShipping = '5-day Shipping';
END CASE;
END$$
DELIMITER ;
Per provar-ho:
CALL GetCustomerShipping(112,@shipping); SELECT @shipping;
Mirem també aquest altre exemple:
DELIMITER $$
CREATE PROCEDURE GetDeliveryStatus(
IN pOrderNumber INT,
OUT pDeliveryStatus VARCHAR(100)
)
BEGIN
DECLARE waitingDay INT DEFAULT 0;
SELECT
DATEDIFF(requiredDate, shippedDate)
INTO waitingDay
FROM orders
WHERE orderNumber = pOrderNumber;
CASE
WHEN waitingDay = 0 THEN
SET pDeliveryStatus = 'On Time';
WHEN waitingDay >= 1 AND waitingDay < 5 THEN
SET pDeliveryStatus = 'Late';
WHEN waitingDay >= 5 THEN
SET pDeliveryStatus = 'Very Late';
ELSE
SET pDeliveryStatus = 'No Information';
END CASE;
END$$
DELIMITER ;
CALL GetDeliveryStatus(10100,@delivery);
Bulces LOOP
Exemple bàsic:
DROP PROCEDURE LoopDemo;
DELIMITER $$
CREATE PROCEDURE LoopDemo()
BEGIN
DECLARE x INT;
DECLARE str VARCHAR(255);
SET x = 1;
SET str = '';
loop_label: LOOP
IF x > 10 THEN
LEAVE loop_label;
END IF;
SET x = x + 1;
IF (x mod 2) THEN
ITERATE loop_label;
ELSE
SET str = CONCAT(str,x,',');
END IF;
END LOOP;
SELECT str;
END$$
DELIMITER ;
Quan executem aquest bucle, la resposta que obtenim és:
CALL LoopDemo(); +-------------+ | str | +-------------+ | 2,4,6,8,10, | +-------------+
Fixem-nos què fa aquest codi:
- Declarem dues variables. x és un comptador que comença per 1 i es va incrementant d'un en un; str és una cadena que anirà acumulant el resultat que volem visualitzar com a resposta.
- loop_label és una etiqueta, per donar un nom al bucle.
- Entrem al bucle, i hi ha un condicional que ens diu que si x>10 sortim del bucle.
- Dins del bucle hi ha un altre condicional que diu que si x és un nombre parell, aleshores concatenem el valor a la cadena str, afegint una coma (,). I si és senar ens saltem una iteració del bucle.
- Finalment fem SELECT str, és a dir, visualitzar la cadena.
La funció MOD, que significa mòdul, és el residu de la divisió. Per exemple:
25 MOD 7 = 4
doncs al dividir 25 entre 7 dóna 3, i la resta (residu) val 4, és a dir, en sobren 4. Per tant, fent el mòdul 2 d'un número ens serveix per saber si és parell o senar.
6 MOD 2 = 0 7 MOD 2 = 1
I si el resultat (0 o 1) el posem dins del IF, recordem que 0 és equivalent a FALSE, i 1 és equivalent a TRUE.
Bucles WHILE LOOP
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
Per exemple, creem la taula calendars:
CREATE TABLE calendars(
id INT AUTO_INCREMENT,
fulldate DATE UNIQUE,
day TINYINT NOT NULL,
month TINYINT NOT NULL,
quarter TINYINT NOT NULL,
year INT NOT NULL,
PRIMARY KEY(id)
);
I creem dos procediments:
DELIMITER $$
CREATE PROCEDURE InsertCalendar(dt DATE)
BEGIN
INSERT INTO calendars(
fulldate,
day,
month,
quarter,
year
)
VALUES(
dt,
EXTRACT(DAY FROM dt),
EXTRACT(MONTH FROM dt),
EXTRACT(QUARTER FROM dt),
EXTRACT(YEAR FROM dt)
);
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE LoadCalendars(
startDate DATE,
day INT
)
BEGIN
DECLARE counter INT DEFAULT 1;
DECLARE dt DATE DEFAULT startDate;
WHILE counter <= day DO
CALL InsertCalendar(dt);
SET counter = counter + 1;
SET dt = DATE_ADD(dt,INTERVAL 1 day);
END WHILE;
END$$
DELIMITER ;
Amb aquest procediment fem insercions a la taula calendars:
CALL LoadCalendars('2019-01-01',31);
Si et fixes bé en el codi, el procediment LoadCalendars() executa els InsertCalendar() a dins dels bucle.
Bucles REPEAT LOOP
DELIMITER $$
CREATE PROCEDURE RepeatDemo()
BEGIN
DECLARE counter INT DEFAULT 1;
DECLARE result VARCHAR(100) DEFAULT '';
REPEAT
SET result = CONCAT(result,counter,',');
SET counter = counter + 1;
UNTIL counter >= 10
END REPEAT;
-- display result
SELECT result;
END$$
DELIMITER ;
CALL RepeatDemo();
Comanda LEAVE
Podem fer servir LEAVE per sortir d'un procediment:
CREATE PROCEDURE sp_name()
sp: BEGIN
IF condition THEN
LEAVE sp;
END IF;
-- other statement
END$$
Per exemple:
DELIMITER $$
CREATE PROCEDURE CheckCredit(
inCustomerNumber int
)
sp: BEGIN
DECLARE customerCount INT;
-- check if the customer exists
SELECT
COUNT(*)
INTO customerCount
FROM
customers
WHERE
customerNumber = inCustomerNumber;
-- if the customer does not exist, terminate
-- the stored procedure
IF customerCount = 0 THEN
LEAVE sp;
END IF;
-- other logic
-- ...
END$$
DELIMITER ;
I també podem fer servir LEAVE per sortir d'un bucle:
[label]: LOOP
IF condition THEN
LEAVE [label];
END IF;
-- statements
END LOOP [label];
o bé:
[label:] REPEAT
IF condition THEN
LEAVE [label];
END IF;
-- statements
UNTIL search_condition
END REPEAT [label];
o bé:
[label:] WHILE search_condition DO
IF condition THEN
LEAVE [label];
END IF;
-- statements
END WHILE [label];
Exemple de LEAVE
DELIMITER $$
CREATE PROCEDURE LeaveDemo(OUT result VARCHAR(100))
BEGIN
DECLARE counter INT DEFAULT 1;
DECLARE times INT;
-- generate a random integer between 4 and 10
SET times = FLOOR(RAND()*(10-4+1)+4);
SET result = '';
disp: LOOP
-- concatenate counters into the result
SET result = concat(result,counter,',');
-- exit the loop if counter equals times
IF counter = times THEN
LEAVE disp;
END IF;
SET counter = counter + 1;
END LOOP;
END$$
DELIMITER ;
CALL LeaveDemo(@result); SELECT @result;
La sortida:
+------------------+ | @result | +------------------+ | 1,2,3,4,5,6,7,8, | +------------------+ 1 row in set (0.00 sec)
creat per Joan Quintana Compte, març 2022
