Mysql prompt-III Compound Statements

MySql Compound Statements:

   BEGIN END
   CASE STATEMENT
   CLOSE
   DECLARE CONDITION
   DECLARE CURSOR
   DECLARE HANDLER
   DECLARE VARIABLE
   FETCH
   IF STATEMENT
   ITERATE
   LABELS
   LEAVE
   LOOP   OPEN
   REPEAT LOOP
   RESIGNAL
   RETURN
   SIGNAL
   WHILE

mysql> help begin end;
Name: 'BEGIN END'
Description:
Syntax:
[begin_label:] BEGIN
    [statement_list]
END [end_label]

BEGIN ... END syntax is used for writing compound statements, which can
appear within stored programs (stored procedures and functions,
triggers, and events). A compound statement can contain multiple
statements, enclosed by the BEGIN and END keywords. statement_list
represents a list of one or more statements, each terminated by a
semicolon () statement delimiter. The statement_list itself is
optional, so the empty compound statement (BEGIN END) is legal.

BEGIN ... END blocks can be nested.

Use of multiple statements requires that a client is able to send
statement strings containing the  statement delimiter. In the mysql
command-line client, this is handled with the delimiter command.
Changing the  end-of-statement delimiter (for example, to //) permit
to be used in a program body

mysql> help case statement;
Name: 'CASE STATEMENT'
Description:
Syntax:
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

Or:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

The CASE statement for stored programs implements a complex conditional
construct.

*Note*: There is also a CASE expression, which differs from the CASE
statement. The
CASE statement cannot have an ELSE NULL clause, and it is terminated
with END CASE instead of END.

For the first syntax, case_value is an expression. This value is
compared to the when_value expression in each WHEN clause until one of
them is equal. When an equal when_value is found, the corresponding
THEN clause statement_list executes. If no when_value is equal, the
ELSE clause statement_list executes, if there is one.

This syntax cannot be used to test for equality with NULL because NULL
= NULL is false.

For the second syntax, each WHEN clause search_condition expression is
evaluated until one is true, at which point its corresponding THEN
clause statement_list executes. If no search_condition is equal, the
ELSE clause statement_list executes, if there is one.

If no when_value or search_condition matches the value tested and the
CASE statement contains no ELSE clause, a Case not found for CASE
statement error results.

Each statement_list consists of one or more SQL statements an empty
statement_list is not permitted.

To handle situations where no value is matched by any WHEN clause, use
an ELSE containing an empty BEGIN ... END block, as shown in this
example. (The indentation used here in the ELSE clause is for purposes
of clarity only, and is not otherwise significant.)

DELIMITER |

CREATE PROCEDURE p()
  BEGIN
    DECLARE v INT DEFAULT 1
    CASE v
      WHEN 2 THEN SELECT v
      WHEN 3 THEN SELECT 0
      ELSE
        BEGIN
        END
    END CASE
  END
  |

Eg:
DELIMITER $$
USE `myt` $$
DROP FUNCTION IF EXISTS `case`$$
CREATE DEFINER = `root` @`localhost` FUNCTION `case` (p1 INT) RETURNS TEXT CHARSET utf8 DETERMINISTIC
BEGIN
  DECLARE ans INT ;
  DECLARE str TEXT ;
  SELECT
    MOD(p1, 2) INTO ans ;
  CASE
    WHEN (ans = 0)    THEN 
      SET str = 'Its is even.' ;
    RETURN str ;
    WHEN (ans = 1)  THEN
     SET str = 'Its is odd' ;
    RETURN str ;
  END CASE ;
END $$
DELIMITER ;

mysql> help declare variable;
Name: 'DECLARE VARIABLE'
Description:
Syntax:
DECLARE var_name [, var_name] ... type [DEFAULT value]

This statement declares local variables within stored programs. To
provide a default value for a variable, include a DEFAULT clause. The
value can be specified as an expression it need not be a constant. If
the DEFAULT clause is missing, the initial value is NULL.

Local variables are treated like stored routine parameters with respect
to data type and overflow checking. See [HELP CREATE PROCEDURE].

Variable declarations must appear before cursor or handler
declarations.

Local variable names are not case sensitive. Permissible characters and
quoting rules are the same as for other identifiers, as described in

The scope of a local variable is the BEGIN ... END block within which
it is declared. The variable can be referred to in blocks nested within
the declaring block, except those blocks that declare a variable with
the same name.

mysql> help if statement;
Name: 'IF STATEMENT'
Description:
Syntax:
IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

The IF statement for stored programs implements a basic conditional
construct.

*Note*: There is also an IF() function, which differs from the IF
statement.The
IF statement can have THEN, ELSE, and ELSEIF clauses, and it is
terminated with END IF.

If the search_condition evaluates to true, the corresponding THEN or
ELSEIF clause statement_list executes. If no search_condition matches,
the ELSE clause statement_list executes.

Each statement_list consists of one or more SQL statements an empty
statement_list is not permitted.

DELIMITER $$
USE `myt`$$
DROP FUNCTION IF EXISTS `ifelseif1`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `ifelseif1`(p1 INT, p2 INT, p3 INT) 
RETURNS INT(11)
    DETERMINISTIC
BEGIN
    DECLARE ans INT ;
        IF((p1 < p2) && (p1 < p3))
            THEN SET ans = p1 ;
        ELSE
            IF((p2 < p3) && (p2 < p1))
                THEN SET ans = p2 ;
            ELSE
                SET ans = p3 ;
            END IF ;
        END IF;
    RETURN ans ;
END$$
DELIMITER ;

mysql> help iterate;
Name: 'ITERATE'
Description:
Syntax:
ITERATE label

ITERATE can appear only within LOOP, REPEAT, and WHILE statements.
ITERATE means "start the loop again."

mysql> help labels;
Name: 'LABELS'
Description:
Syntax:
[begin_label:] BEGIN
    [statement_list]
END [end_label]

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

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

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

Labels are permitted for BEGIN ... END blocks and for the LOOP, REPEAT,
and WHILE statements. Label use for those statements follows these
rules:

o begin_label must be followed by a colon.

o begin_label can be given without end_label. If end_label is present,
  it must be the same as begin_label.

o end_label cannot be given without begin_label.

o Labels at the same nesting level must be distinct.

o Labels can be up to 16 characters long.

To refer to a label within the labeled construct, use an ITERATE or
LEAVE statement. The following example uses those statements to
continue iterating or terminate the loop:

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1
    IF p1 < 10 THEN ITERATE label1 END IF
    LEAVE label1
  END LOOP label1
END

The scope of a block label does not include the code for handlers
mysql> help leave;
Name: 'LEAVE'
Description:
Syntax:
LEAVE label

This statement is used to exit the flow control construct that has the
given label. If the label is for the outermost stored program block,
LEAVE exits the program.

LEAVE can be used within BEGIN ... END or loop constructs (LOOP,
REPEAT, WHILE).
mysql> help loop;
Name: 'LOOP'
Description:
Syntax:
[begin_label:] LOOP
    statement_list
END LOOP [end_label]

LOOP implements a simple loop construct, enabling repeated execution of
the statement list, which consists of one or more statements, each
terminated by a semicolon () statement delimiter. The statements
within the loop are repeated until the loop is terminated. Usually,
this is accomplished with a LEAVE statement. Within a stored function,
RETURN can also be used, which exits the function entirely.

Neglecting to include a loop-termination statement results in an
infinite loop.

A LOOP statement can be labeled. For the rules regarding label use,

Eg:
DELIMITER $$
USE `myt`$$
DROP FUNCTION IF EXISTS `loop1`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `loop1`(p1 INT) RETURNS INT(11)
    DETERMINISTIC
BEGIN
  DECLARE ans INT ;
  SET ans = 1 ;
  label1 :
  LOOP
    SET ans = ans * p1 ;
    SET p1 =p1-1;
    IF p1 > 1 THEN
        ITERATE label1 ;
    END IF ;
    LEAVE label1 ;
  END LOOP label1 ;
  RETURN ans ;
END$$
DELIMITER ;
mysql> help repeat;
Name: 'REPEAT LOOP'
Description:
Syntax:
[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]
The statement list within a REPEAT statement is repeated until the
search_condition expression is true. Thus, a REPEAT always enters the
loop at least once. statement_list consists of one or more statements,
each terminated by a semicolon () statement delimiter.
A REPEAT statement can be labeled. For the rules regarding label use,
see [HELP labels].
Examples:
mysql> delimiter //

mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -> BEGIN
    ->   SET @x = 0
    ->   REPEAT
    ->     SET @x = @x + 1
    ->   UNTIL @x > p1 END REPEAT
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL dorepeat(1000)//
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)
mysql> help return;
Name: 'RETURN'
Description:
Syntax:
RETURN expr

The RETURN statement terminates execution of a stored function and
returns the value expr to the function caller. There must be at least
one RETURN statement in a stored function. There may be more than one
if the function has multiple exit points.

This statement is not used in stored procedures, triggers, or events.
The LEAVE statement can be used to exit a stored program of those
types.
mysql> help while;
Name: 'WHILE'
Description:
Syntax:
[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

The statement list within a WHILE statement is repeated as long as the
search_condition expression is true. statement_list consists of one or
more SQL statements, each terminated by a semicolon () statement
delimiter.

A WHILE statement can be labeled. For the rules regarding label use,
see [HELP labels].

URL: http://dev.mysql.com/doc/refman/5.5/en/while-statement.html

Examples:
CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5

  WHILE v1 > 0 DO
    ...
    SET v1 = v1 - 1
  END WHILE
END

Eg:

DELIMITER $$
USE `myt`$$
DROP FUNCTION IF EXISTS `while1`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `while1`(p1 INT) RETURNS INT(11)
DETERMINISTIC
BEGIN
    DECLARE ans INT ;
    SET ans = 0 ;
    WHILE   p1 > 0 DO
        SET ans = ans + p1 ;
        SET p1 = p1 - 1 ;
    END WHILE ;
    RETURN ans;
END$$
DELIMITER ;