A nested cursor example

• The following function will walk through the warehouse and the seller table.
• It uses two cursors and two different continue handlers.

• CREATE PROCEDURE doubleWalk()
  BEGIN
     DECLARE finished BOOL DEFAULT FALSE;
     DECLARE managerName VARCHAR(100);
  
     DECLARE  warehouseWalker CURSOR FOR SELECT manager FROM WAREHOUSE;
  
     DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET finished = TRUE;
  
     OPEN warehouseWalker;
  
     FETCH  warehouseWalker INTO managerName;
  
     WHILE finished IS NOT TRUE DO
        BEGIN
            DECLARE done BOOL DEFAULT FALSE;
            DECLARE newName VARCHAR(100);
  
            DECLARE salesWalker CURSOR FOR SELECT SellerName FROM SELLER;
  
            DECLARE CONTINUE HANDLER FOR NOT FOUND
               SET done = TRUE;
  
            OPEN salesWalker;
  
            FETCH  salesWalker INTO newName;
            WHILE done IS NOT TRUE DO
                SELECT newName, managerName;
                FETCH  salesWalker INTO newName;
            END WHILE;
            CLOSE salesWalker;
        END;
        FETCH  warehouseWalker INTO managerName;
     END WHILE;
  
     CLOSE warehouseWalker;
  END;
      

Functions

• Created with CREATE FUNCTION function_name (params) RETURNS type body
• DROP FUNCTION function_name
• Allow the user to return a value
• And can be used in select statements.
• Much like a procedure otherwise.

• CREATE FUNCTION HasSixLetters(name varchar(40)) 
     RETURNS BOOL
     BEGIN
        RETURN LENGTH(name) = 6;
     END
  
  mysql> @x=HasSixLetters("fred");
  mysql> SELECT * FROM WAREHOUSE WHERE HasSixLetters(WarehouseCity);