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

Bucles WHILE LOOP

• https://www.mysqltutorial.org/mysql-stored-procedure/mysql-while-loop/

Bucles REPEAT LOOP

• https://www.mysqltutorial.org/mysql-stored-procedure/mysql-repeat-loop/

Sentència LEAVE

• https://www.mysqltutorial.org/mysql-stored-procedure/mysql-leave/

---

creat per Joan Quintana Compte, març 2022