Advance Store Procedure

Store Procedure Parameters

Let’s examine how you can define parameters within a stored procedure.

  • CREATE PROCEDURE procedure_name() : Parameter list is empty
  • CREATE PROCEDURE procedure_name(IN varname DATA-TYPE) : One input parameter. The word IN is optional because parameters are IN (input) by default.
  • CREATE PROCEDURE procedure_name(OUT varname DATA-TYPE) : One output parameter.
  • CREATE PROCEDURE procedure_name(INOUT varname DATA-TYPE) : One parameter which is both input and output.

IN parameter example

The IN parameter in the GetOfficeByCountry stored procedure that selects offices located in a specified country.

DELIMITER //
CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))
    BEGIN
        SELECT * 
         FROM offices
         WHERE country = countryName;
    END //
DELIMITER ;

OUT parameter example

The following stored procedure returns the number of orders by order status. It has two parameters:

  • orderStatus: IN parameter that is the order status which you want to count the orders.
  • total: OUT parameter that stores the number of orders for a specific order status.
DELIMITER $$
CREATE PROCEDURE CountOrderByStatus(
         IN orderStatus VARCHAR(25),
         OUT total INT)
BEGIN
    SELECT count(orderNumber)
    INTO total
    FROM orders
    WHERE status = orderStatus;
END$$
DELIMITER ;

Query Output

CALL CountOrderByStatus('Shipped',@total);
SELECT @total;
CALL CountOrderByStatus('in process',@total);
 SELECT @total AS  total_in_process;

Simple CASE statement

MySQL CASE statements to construct complex conditionals.

CASE  case_expression
   WHEN when_expression_1 THEN commands
   WHEN when_expression_2 THEN commands
   ...
   ELSE commands
END CASE;
DELIMITER $$
 
CREATE PROCEDURE GetCustomerShipping(
        in  p_customerNumber int(11), 
        out p_shiping        varchar(50))
BEGIN
    DECLARE customerCountry varchar(50);
 
    SELECT country INTO customerCountry
    FROM customers
    WHERE customerNumber = p_customerNumber;
 
    CASE customerCountry
        WHEN  'USA' THEN
           SET p_shiping = '2-day Shipping';
        WHEN 'Canada' THEN
           SET p_shiping = '3-day Shipping';
        ELSE
           SET p_shiping = '5-day Shipping';
    END CASE;
 
END$$
DELIMITER;
The following is the test script for the stored procedure above:

SET @customerNo = 112;
 SELECT country into @country
FROM customers
WHERE customernumber = @customerNo;
CALL GetCustomerShipping(@customerNo,@shipping);
 SELECT @customerNo AS Customer,
       @country    AS Country,
       @shipping   AS Shipping;

Searched CASE statement

The simple CASE statement only allows you match a value of an expression against a set of distinct values. In order to perform more complex matches such as ranges you use the searched CASE statement. The searched CASE statement is equivalent to the IF statement, however its construct is much more readable.

The following illustrates the syntax of the searched CASE statement:

CASE
    WHEN condition_1 THEN commands
    WHEN condition_2 THEN commands
    ...
    ELSE commands
END CASE;
Example

DELIMITER $$
 
CREATE PROCEDURE GetCustomerLevel142(
    in  p_customerNumber int(11), 
    out p_customerLevel  varchar(10))
BEGIN
    DECLARE creditlim double;
 
    SELECT creditlimit INTO creditlim
    FROM customers
    WHERE customerNumber = p_customerNumber;
 
    CASE  
        WHEN creditlim > 50000 THEN 
           SET p_customerLevel = 'PLATINUM';
        WHEN (creditlim <= 50000 AND creditlim >= 10000) THEN
           SET p_customerLevel = 'GOLD';
        WHEN creditlim < 10000 THEN
           SET p_customerLevel = 'SILVER';
    END CASE;
 
END$$
DELIMITER;

Output

CALL GetCustomerLevel142(112,@level);
SELECT @level AS 'Customer Level';

Loop in Stored Procedures

MySQL provides loop statements that allow you to execute a block of SQL code repeatedly based on a condition. There are three loop statements in MySQL: WHILE, REPEAT and LOOP.

Syntax

WHILE expression DO
   Statements
END WHILE
DELIMITER $$
 DROP PROCEDURE IF EXISTS WhileLoopProc$$
 CREATE PROCEDURE WhileLoopProc()
       BEGIN
               DECLARE x  INT;
               DECLARE str  VARCHAR(255);
               SET x = 1;
               SET str =  '';
               WHILE x  <= 5 DO
                           SET  str = CONCAT(str,x,',');
                           SET  x = x + 1; 
               END WHILE;
               SELECT str;
       END$$
   DELIMITER ;

REPEAT loop

First MySQL executes the statements, and then it evaluates the expression. If the expression evaluates to TRUE, MySQL executes the statements repeatedly until the expression evaluates to FALSE.
Because the REPEAT loop statement checks the expression after the execution of statements therefore the REPEAT loop statement is also known as post-test loop.
We can rewrite the stored procedure that uses WHILE loop statement above using the REPEAT loop statement:

Syntax

REPEAT
Statements;
UNTIL expression
END REPEAT
DELIMITER $$
 DROP PROCEDURE IF EXISTS RepeatLoopProc$$
 CREATE PROCEDURE RepeatLoopProc()
       BEGIN
               DECLARE x  INT;
               DECLARE str  VARCHAR(255);
               SET x = 1;
               SET str =  '';
               REPEAT
                           SET  str = CONCAT(str,x,',');
                           SET  x = x + 1; 
               UNTIL x  > 5
               END REPEAT;
               SELECT str;
       END$$
 DELIMITER ;

LOOP Statement

MySQL also gives you a LOOP statement that allows you to execute a block of code repeatedly with an additional flexibility of using a loop label.

Syntax

Loop
Statements;
UNTIL expression
END Loop
DELIMITER $$
 DROP PROCEDURE IF EXISTS LOOPLoopProc$$
 CREATE PROCEDURE LOOPLoopProc()
       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 ;

Admin has written 171 articles