Procediments emmagatzemats. Continuació

IF. Condicionals

• https://www.mysqltutorial.org/mysql-if-statement/

Ú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

• https://www.mysqltutorial.org/mysql-case-statement/

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

• https://www.mysqltutorial.org/stored-procedures-loop.aspx

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

• https://www.mysqltutorial.org/mysql-stored-procedure/mysql-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

• https://www.mysqltutorial.org/mysql-stored-procedure/mysql-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

• https://www.mysqltutorial.org/mysql-stored-procedure/mysql-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