MySQL-5.7 High-order grammar and Process Control

1. Label statements

[begin_label:] BEGIN    [statement_list]END [end_label][begin_label:] LOOP    statement_listEND LOOP [end_label][begin_label:] REPEAT    statement_listUNTIL search_conditionEND REPEAT [end_label][begin_label:] WHILE search_condition DO    statement_listEND WHILE [end_label]

• Label labels can be added to begin...end statements as well as loop,repeat and while statements
• Statements are controlled by iterate and leave, iterate means that the specified label position is returned, leave indicates a jump out of the label

Instance:

mysql> delimiter //mysql> create procedure doiterate(in p1 int,out p2 int)    -> begin    -> label1:loop    -> set p1 = p1 + 1;    -> if p1 < 10 then iterate label1;end if;    -> leave label1;    -> end loop label1;    -> set p2=p1;    -> end//Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;mysql> call doiterate(1,@a);Query OK, 0 rows affected (0.00 sec)mysql> select @a;+------+| @a   |+------+|   10 |+------+1 row in set (0.00 sec)mysql> call doiterate(5,@a);Query OK, 0 rows affected (0.00 sec)mysql> select @a;+------+| @a   |+------+|   10 |+------+1 row in set (0.00 sec)

2.Declare statements

Grammar:

DECLARE var_name [, var_name] ...  type [DEFAULT value]

• Declare statements typically declare local variables, cursors, conditions, or handler
• The Declare statement is allowed only in the Begin...end statement and must appear in the first row
• The order of the declare is also required, usually by declaring the local variable first, then the cursor, then the condition and the handler
• Specifies the default value of the variable using default, or null if no default value is specified
• The declared variables and the fields in the referenced data table are separated by the

Variables in stored procedures

• Local variables can be declared by declare statements
• The declared variable can be assigned by Select...into Var_list or by a set statement, or by defining a cursor and using Fetch...into var_list to assign a value

mysql> delimiter //mysql> create procedure sp1(v_sid int)    -> begin    -> declare xname varchar(64) default ‘bob‘;    -> declare xgender int;    -> select sname,gender into xname,xgender from students where sid=v_sid;    -> select xname,xgender;    -> end//Query OK, 0 rows affected (0.01 sec)mysql> delimiter ;mysql> mysql> call sp1(1);+--------+---------+| xname  | xgender |+--------+---------+| Andrew |       1 |+--------+---------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)

3. Process Control Statements

(1) Case statement
A complex conditional selection statement is indicated in a stored procedure or function.
Grammar:

CASE case_value    WHEN when_value THEN statement_list    [WHEN when_value THEN statement_list] ...    [ELSE statement_list]END CASEOr:CASE    WHEN search_condition THEN statement_list    [WHEN search_condition THEN statement_list] ...    [ELSE statement_list]END CASE

Description

• Case_value and When_value do equal comparisons sequentially, and if they do, the corresponding subsequent SQL statements are executed, otherwise the contrast is followed;
• When the search_condition satisfies the true/1 result, the corresponding SQL statement is executed, otherwise the SQL statement of else is executed;

Instance:

Mysql> delimiter//mysql> CREATE PROCEDURE exp_case (v_sid int)--Declare v int default 1;    Select Gender to V from students where sid=v_sid;    Case V-If 0 then update students set gender=1 where Sid=v_sid;    -If 1 then update students set gender=0 where Sid=v_sid;    , else, update students set gender=-1 where Sid=v_sid;    -End case; End//query OK, 0 rows Affected (0.00 sec) mysql> delimiter;mysql> mysql> SELECT * from students where sid=1 ;+-----+--------+--------+---------+| Sid | sname | Gender |   dept_id |+-----+--------+--------+---------+| 1 | Andrew |       1 | 1 |+-----+--------+--------+---------+1 row in Set (0.00 sec) Mysql> Call Exp_case (1); Query OK, 1 row affected (0.03 sec) mysql> SELECT * from students where sid=1;+-----+--------+--------+---------+| Sid | sname | Gender |   dept_id |+-----+--------+--------+---------+| 1 | Andrew |       0 | 1 |+-----+--------+--------+---------+1 row in Set (0.00 sec) Another notation:mysql> delimiter//mysql> CREATE PROCEDURE exp_case2 (v_sid int    ), begin, declare v int default 1;    Select Gender to V from students where sid=v_sid;    Case-I v=0 then update students set gender=1 where Sid=v_sid;    -If V=1 then update students set gender=0 where Sid=v_sid;    , else, update students set gender=-1 where Sid=v_sid;    -End case; End//query OK, 0 rows Affected (0.00 sec) mysql> delimiter;mysql> call EXP_CASE2 (1); Query OK, 1 row affected (0.03 sec) mysql> SELECT * from students where sid=1;+-----+--------+--------+---------+| Sid | sname | Gender |   dept_id |+-----+--------+--------+---------+| 1 | Andrew |       1 | 1 |+-----+--------+--------+---------+1 row in Set (0.00 sec)

(2) If statement
to indicate the underlying conditional selection statement in a stored procedure or function
Grammar:

IF search_condition THEN statement_list    [ELSEIF search_condition THEN statement_list] ...    [ELSE statement_list]END IF

• If the search_condition satisfies the TRUE/1 condition, the corresponding SQL statement is executed, otherwise the search_condition in ElseIf is not satisfied, then the SQL statement in else is executed;
• Statement_list can contain one or more SQL statements

Instance:

  mysql> delimiter//mysql> create function simplecompare (n int,m int) returns varchar-> ;    Begin DECLARE S varchar (20);    If n > m then set S = ' > ';    -ElseIf n = m Then set s = ' = ';    else set S = ' < ';    -End If;    Set S = concat (n, ', S, ' ', m);    return s; End//query OK, 0 rows Affected (0.00 sec) mysql> delimiter;mysql> mysql> Select Simplecompare (5,6); +------- -------------+| Simplecompare (5,6) |+--------------------+| 5<6 |+--------------------+1 row in Set (0.00 sec) mysql> Select Simplecompare (15,34); +--------------- -------+| Simplecompare (15,34) |+----------------------+| 15<34 |+----------------------+1 row in Set (0.00 sec) mysql> Select Simplecompare (78,78); +----------- -----------+| Simplecompare (78,78) |+----------------------+| 78=78 |+----------------------+1 row in Set (0.00 sec)  

If nesting:

mysql> delimiter //mysql> create function verboseCompare(n int,m int)    -> returns varchar(50)    -> begin    -> declare s varchar(50);    -> if n = m then set s = ‘equals‘;    -> else    -> if n > m then set s = ‘greater‘;    -> else set s = ‘less‘;    -> end if;    -> set s = concat(‘is‘,s,‘than‘);    -> end if;    -> set s = concat(n,‘‘,s,‘‘,m,‘.‘);    -> return s;    -> end//Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;mysql> select verboseCompare(4,5);+---------------------+| verboseCompare(4,5) |+---------------------+| 4islessthan5.       |+---------------------+1 row in set (0.00 sec)

(3) Iterate statement
Appears only in the Loop,repeat,while loop statement, indicating that the loop is restarted.
Grammar:

ITERATE label

(4) Leave statement
A Process Control statement block that indicates the specified label, usually used in Begin...end, and loop,repeat,while loop summary;

LEAVE label

Instance:

mysql> delimiter //mysql> create procedure doiterate2(in p1 int,out p2 int)    -> begin    -> label1:loop    -> set p1=p1+1;    -> if p1 < 10 then iterate label1;end if;    -> leave label1;    -> end loop label1;    -> set p2=p1;    -> end//Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;

(5) Loop statement
A way of expressing a loop execution in a stored procedure or function;
Grammar:

[begin_label:] LOOP    statement_listEND LOOP [end_label]

Instance:

mysql> delimiter //mysql> create procedure doiterate3(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//Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;

(6) Repeat statement
A way of expressing a loop execution in a stored procedure or function;
Grammar:

[begin_label:] REPEAT    statement_listUNTIL search_conditionEND REPEAT [end_label]

Instance:

mysql> delimiter //mysql> create procedure doiterate4(p1 int)    -> begin    -> set @x=0;    -> repeat    -> set @[email protected]+1;    -> until @x>p1 end repeat;    -> end//Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;mysql> call doiterate4(1000);Query OK, 0 rows affected (0.02 sec)mysql> select @x;+------+| @x   |+------+| 1001 |+------+1 row in set (0.00 sec)

(7) While statement
A way of expressing a loop execution in a stored procedure or function;
Grammar:

[begin_label:] WHILE search_condition DO    statement_listEND WHILE [end_label]

• When Search_condition returns True, the loop executes the SQL statement until search_condition is false;

Instance:

mysql> delimiter //mysql> create procedure dowhile()    -> begin    -> declare v1 int default 5;    -> while v1>0 do    -> update students set gender=-1 where sid=v1;    -> set v1=v1-1;    -> end while;    -> end//Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;mysql> call dowhile();Query OK, 1 row affected (0.63 sec)mysql> select * from students;+-----+--------+--------+---------+| sid | sname  | gender | dept_id |+-----+--------+--------+---------+|   1 | Andrew | -1     |       1 ||   2 | Andy   | -1     |       1 ||   3 | Bob    | -1     |       1 ||   4 | Ruth   | -1     |       2 ||   5 | Mike   | -1     |       2 ||   6 | John   | 0      |       3 ||   7 | Cindy  | 1      |       3 ||   8 | Susan  | 1      |       3 |+-----+--------+--------+---------+8 rows in set (0.00 sec)

(8) Return statement
In the function, used to terminate the execution of the function and return the specified value to the caller;
Grammar:

RETURN expr

• There must be at least one return statement in the function, and when there is more than one return statement, there are multiple exit methods for the function;

Instance:

mysql> delimiter //mysql> create function doreturn()    -> returns int    -> begin    -> select gender into @a from students where sid = 1;    -> if @a=1 then return 1;    -> elseif @a=0 then return 0;    -> else return 999;    -> end if;    -> end//Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;mysql> select doreturn();+------------+| doreturn() |+------------+|        999 |+------------+1 row in set (0.00 sec)mysql> select * from students where sid = 1;+-----+--------+--------+---------+| sid | sname  | gender | dept_id |+-----+--------+--------+---------+|   1 | Andrew | -1     |       1 |+-----+--------+--------+---------+1 row in set (0.00 sec)

MySQL-5.7 High-order grammar and Process Control