JIYIK CN >

In the article "A Peek into MySQL Stored Procedure Details" , we briefly introduced the use of stored procedures. The syntax for creating stored procedures includes BEGIN...END. In addition to BEGIN...END, the following statement tags can be used in the storage body of a stored procedure:

[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]

Let's explain the above sentences one by one:

begin_label If begin_label is given, it must be followed by a colon (:). And if begin_label is given, it does not necessarily have to be followed by end_label. However, if end_label is given, it must be the same as begin_label.

end_label For this label, end_label cannot be given alone without begin_label.

Another point to note is that for these labels, the labels in each layer must be unique and cannot be the same. And the maximum length of each label string is 16 characters.

Let us take an example.

Example 1

mysql>delimiter //
mysql>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;
mysql>delimiter;

In the above example, label1 is the begin_label and end_label we mentioned above. According to our explanation above, the above example can also be changed to the following form.

Example 2

mysql>delimiter //
mysql>CREATE PROCEDURE doiterate(p1 INT)
       -->BEGIN
       --> label1: LOOP
       --> SET p1 = p1 + 1;
       --> IF p1 < 10 THEN ITERATE label1; END IF;
       --> LEAVE label1;
       --> END LOOP;
       -->END;
mysql>delimiter;

Next, let’s take a look at the application of the above-mentioned sentence tags.

1. BEGIN……END

BEGIN ... END is mainly used to write compound statements. A compound statement may contain multiple statements, which are all delimited by BEGIN ... END. Just like the example below.

Example 3

mysql> delimiter //
mysql> create procedure adddata()
         --> begin
         --> declare n int default 0;
         --> while n<100000
         --> do
         --> insert into proced(name,type) values(' Trace Memory Blog','onmpw');
         --> set n = n+1;
         --> end while;
         --> end
         -->//
mysql> delimiter ;

And BEGIN ... END blocks can be nested and can also be given names.

Example 4

mysql> delimiter //
mysql> CREATE PROCEDURE adddata()
         -->label1：BEGIN
         -->  declare n int default 0;
         -->  while n<100000
         -->  do
         -->    insert into proced(name,type) values(‘迹忆博客’,’onmpw’);
         -->  set n = n+1;
         -->  end while;
         -->END label1
         -->//
mysql> delimiter ;

2. LOOP

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

LOOP 作用是重复执行statement_list，它可能包含一条或多条执行语句。这些语句都是用分号（；）来界定的，所以之前我们需要先使用delimiter //命令改变客户端命令行的界定符。这个命令在《mysql存储过程细节窥探》中有说过。对于这个标签的使用实例如下。

例五

mysql> delimiter //
mysql>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;
       -->    SET @x = p1;
       -->END;
mysql> delimiter ;

LOOP的需要借助ITERATE 来重复，而LOOP的循环结束需要有LEAVE来完成，当然也可以使用RETURN。这里的LEAVE我们可以理解成编程语言中的break来跳出循环。RETURN可以理解成编程语言中的return，亦可以跳出循环，不过这个是整个程序的退出。

3. REPEAT

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

同样REPEAT重复执行块儿内的statement_list，直到UNTIL的条件不成立的时候结束循环。因此，我们可以看出statement_list至少被执行一次。此语句标签可以理解成编程语言中的do{}while();

使用示例如下。

例六

mysql> delimiter //
mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -->BEGIN
    -->   SET @x = 0;
    -->   REPEAT
    -->     SET @x = @x + 1;
    -->   UNTIL @x > p1
    -->END REPEAT;
    --> END
    --> //
mysql>delimiter ;
mysql> CALL dorepeat(1000);
mysql> SELECT @x;
+---------+
| @x  |
+---------+
| 1001 |
+---------+

4. WHILE

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

WHILE表示在此块儿内的statement_list被重复执行，只要search_condition为真就会执行。也就是说此WHILE语句标签相当于编程语言中的while(){}语句。

使用示例如下。

例七

mysql> delimiter //
mysql>CREATE PROCEDURE dowhile()
       -->BEGIN
       -->    DECLARE v1 INT DEFAULT 5;
       -->    label1:WHILE v1 > 0 DO
       -->             SET v1 = v1 - 1;
       -->    END WHILE label1;
       -->END//
mysql>delimiter ;

以上就是对几种语句标签的简单介绍。