stored_procedure.sql

--Tutorial Link - https://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx/


--
--CREATE PROCEDURE
--

DELIMITER //
CREATE PROCEDURE procedure_name(parameter_list)
BEGIN
   statements;
END //
DELIMITER;


--
--RUN PROCEDURE
--

CALL procedure_name(args);


--
--DROP PROCEDURE
--

DROP PROCEDURE [IF EXISTS] procedure_name;


--
--DECLARE LOCAL VARIABLE
--

DECLARE variable_name datatype(size) [DEFAULT default_value];

-- Example - DECLARE totalSale DEC(10,2) DEFAULT 0.0;
-- Example - DECLARE x, y INT DEFAULT 0;


--
--ASSIGN VALUE TO VARIABLE
--

SET variable_name = value;

-- 
--Example 
-- DECLARE productCount INT DEFAULT 0;
--SELECT COUNT(*) 
--INTO productCount
--FROM products;
--


--
--PARAMETERS
--

CREATE PROCEDURE procedure_name (
  IN/OUT/INOUT param datatype(length)
)

--
--NOTES
--IN param is local; value expires out of procedure
--OUT param value can be used out of procedure
--INOUT= IN+OUT
--

--
--example
DELIMITER $$

CREATE PROCEDURE GetOrderCountByStatus (
	IN  orderStatus VARCHAR(25),
	OUT total INT
)
BEGIN
	SELECT COUNT(orderNumber)
	INTO total
	FROM orders
	WHERE status = orderStatus;
END$$

DELIMITER ;
-- CALL
CALL GetOrderCountByStatus('Shipped',@total);
SELECT @total;
--


--
--SHOW LIST OF STORED PROCEDURE
--

SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition];

--EXAMPLE
--SHOW PROCEDURE STATUS WHERE db = 'classicmodels';
--SHOW PROCEDURE STATUS LIKE '%pattern%';
--


--
--CONDITIONALS
--

IF condition THEN
   statements;
ELSEIF elseif-condition THEN
   elseif-statements;
...
ELSE
   else-statements;
END IF;

--
--EXAMPLE
--
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 ;
--CALL
CALL GetCustomerLevel(447, @level); 
SELECT @level;


--
--CASE CONDITIONAL
--

CASE case_value
    WHEN when_value1 THEN ...
    WHEN when_value2 THEN ...
    ELSE 
        BEGIN
        END;
END CASE;

--
--example
--
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 ;
--


--
--LOOP(execute a list of statements repeatedly based on a condition)
--

--
--example
--
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 ;
--


--
--WHILE LOOP(execute a loop as long as a condition is true)
--

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

--
--example
--
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 ;
--


--
--REPEAT LOOP- execute a loop until a search condition is true
--

[begin_label:] REPEAT
    statement
UNTIL search_condition
END REPEAT [end_label]

--
--example
--
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 ;
--


--