MySQL Tutorials - Herong's Tutorial Examples - Version 4.20, by Dr. Herong Yang
Execution Flow Control Statements
This section describes execution flow control statements used in stored procedures: IF...THEN, LOOP, REPEAT, WHILE statements.
To selectively executing a group of statements, you can use the IF statement:
IF condition THEN statement_list ELSE IF condition THEN statement_list ELSE IF condition THEN statement_list ...... ELSE statement_list END IF
To repeatedly executing a group of statements without any conditions, you can use the LOOP statement:
LOOP statement_list END LOOP
To repeatedly executing a group of statements with an ending condition, you can use the REPEAT statement:
REPEAT statement_list UNTIL condition END REPEAT
To repeatedly executing a group of statements with an ending condition, you can use the WHILE statement:
WHILE condition statement_list END WHILE
Below is a sample code that uses a while loop to insert multiple rows into a table:
-- ProcedureLoop.sql -- Copyright (c) 2015, HerongYang.com, All Rights Reserved. -- DROP DATABASE IF EXISTS HyTest; CREATE DATABASE HyTest; USE HyTest; -- DROP PROCEDURE IF EXISTS InitTable; DELIMITER '/'; CREATE PROCEDURE InitTable(IN N INTEGER) BEGIN DECLARE I INTEGER; SET I = 0; WHILE I < N DO INSERT INTO MyTable VALUES (I, RAND()*N); SET I = I + 1; END WHILE; END/ DELIMITER ';'/ -- DROP TABLE IF EXISTS MyTable; CREATE TABLE MyTable (ID INTEGER, Value INTEGER); CALL InitTable(100); -- SELECT 'My table:' AS '---'; SELECT * FROM MyTable WHERE ID < 10;
Output:
My table: ID Value 0 3 1 63 2 8 3 52 4 35 5 18 6 86 7 75 8 18 9 63
Last update: 2015.
Table of Contents
MySQL Introduction and Installation
Introduction of MySQL Programs
Perl Programs and MySQL Servers
PHP Programs and MySQL Servers
Java Programs and MySQL Servers
Character Strings and Bit Strings
Table Column Types for Different Types of Values
Using DDL to Create Tables and Indexes
Using DML to Insert, Update and Delete Records
Using SELECT to Query Database
Transaction Management and Isolation Levels
Defining and Calling Stored Procedures
►Variables, Loops and Cursors Used in Stored Procedures
Local Variables in Stored Procedures
►Execution Flow Control Statements
ITERATE and LEAVE - Statements to Break Loops