Using Loops to Control the Flow of Execution

Simple Loops

WHILE…​END_WHILE

The WHILE statement repeats a sequence of statements so long as the boolean-expression evaluates to true. The expression is checked just before each entry to the loop body.

WHILE (boolean-expression)
   statements
 END_WHILE;

WHILE loop Example:

DROP PROCEDURE IF EXISTS prc_get_player_teams;
SET DELIMITER @
CREATE PROCEDURE prc_get_player_teams (IN iPlayer string)
RETURNS tbl_team(Year integer, Teamid string, stint integer)
 AS
    VAR vfirstnhl integer, vlastnhl integer;
    vfirstnhl,vlastnhl = (SELECT FIRSTNHL,LASTNHL FROM PLAYERS where PLAYERID = iPlayer);
    WHILE (vfirstnhl <= vlastnhl)
        INSERT into tbl_team select year,teamid,stint from SCORING
                                 where playerid = iPlayer
                                  and YEAR = vfirstnhl ORDER BY year,teamid,stint;
        vfirstnhl=vfirstnhl+1;
    END_WHILE;
END_PROCEDURE;
@
SET DELIMITER ;
CALL prc_get_player_teams('harrija01');
 YEAR  TEAMID  STINT
 ----- ------- ------
 2005    TOR     1
 2006    TOR     1
 2008    TOR     1
 2009    CAR     1
 2010    CAR     1
 2011    CAR     1

The CONTINUE statement allows the processing within a loop to ignore all SQL statements after the CONTINUE statement and continue processing the loop. The CONTINUE statement is most commonly contained inside an IF statement.

CONTINUE Example: Only rows that have position='Fan' will be updated.

DROP PROCEDURE IF EXISTS proc_continue_usage;
DROP TABLE IF EXISTS tst_hockey;
CREATE TABLE tst_hockey (tstnumber INTEGER, tstname STRING, tstposition STRING, tstteam STRING);
SET DELIMITER @
 CREATE PROCEDURE proc_continue_usage
 AS
   VAR l_team STRING;
   VAR l_name sTRING;
   VAR l_number INTEGER;
   VAR l_position STRING;
   FOR SELECT number, name, position,team FROM hockey;
       l_team = team;
       l_name = name;
       l_number = number;
       l_position = position;
       INSERT INTO tst_hockey VALUES (l_number, l_name, l_position,l_team);
       IF ( l_position != 'Fan' )
           CONTINUE;
       END_IF;
       UPDATE tsthockey SET tstteam='Crowd' WHERE tstnumber = l_number;
   END_FOR;
END_PROCEDURE;
@
SET DELIMITER ;

CALL proc_continue_usage;

SELECT  number, name, position,team FROM tst_hockey ORDER BY tstNUMBER;
 NUMBER     NAME    POSITION  TEAM
 ------- ---------- --------- -----
    1    MAX SUMMIT    Fan    Crowd

Displaying Error Messages

TRY…​CATCH

TRY
    statement
CATCH (string)
    statements
END_TRY;

TRY…​ Example:

DROP PROCEDURE IF EXISTS prc_hockey_create;
DROP TABLE tst_hockey1;
CREATE TABLE tst_hockey1 (number INTEGER UNIQUE,name STRING,position STRING,team STRING);
INSERT INTO tst_hockey1 SELECT number,name,position,team FROM hockey;
SET DELIMITER @
CREATE PROCEDURE prc_hockey_create (inNum string
  , inName string
  , inPos string
  , inTeam string)
 RETURNS trace (log string,num string,name string)
AS
  TRY
        INSERT INTO tst_hockey1 (number,name,position,team)
          values (inNum, inName, inPos, inTeam);
 CATCH(error)
        INSERT INTO trace VALUES (error, inNum,inName);
      RETURN ;
 END_TRY;
 THROW 'record valid for insert';
END_PROCEDURE
@
SET DELIMITER ;

CALL prc_hockey_create('abc','Tom Jones','Goalie','Bruins');
                        LOG                         NUM    NAME
 -------------------------------------------------- ---- ---------
 unable to convert string "abc" into type "integer" abc  Tom Jones

CALL prc_hockey_create('40','TUUKKA RASK','Goalie','Bruins');
                    LOG                     NUM     NAME
 ------------------------------------------ ---- -----------
 duplicate value in unique index PLAYER_IDX  40  TUUKKA RASK

CALL prc_hockey_create('99','Tom Jones','Goalie','Bruins');
Procedure HOCKEY.PRC_HOCKEY_CREATE, record valid for insert