mysql 流程控制语句全接触

 

Flow Control Statements

MySQL supports the IFCASEITERATELEAVE LOOPWHILE, and REPEAT constructs for flow control within stored programs. It also supports RETURN within stored functions.

Many of these constructs contain other statements, as indicated by the grammar specifications in the following sections. Such constructs may be nested. For example, an IF statement might contain a WHILE loop, which itself contains a CASE statement.

MySQL does not support FOR loops.

 

CASE Syntax

Or:

 

 

Example:

 

IF Syntax

Example:

 

 

 

 

 

 LOOP Syntax

 

 

 

REPEAT Syntax

 

Example:

 

 

RETURN 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.

 

 

WHILE Syntax

 

 

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

WHILE statement can be labeled. For the rules regarding label use, see Section 13.6.2, “Statement Label Syntax”.

Example:

 

LEAVE 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 (LOOPREPEATWHILE).

For an example, see Section 13.6.5.5, “LOOP Syntax”.

 

ITERATE Syntax

ITERATE label

 

ITERATE can appear only within LOOPREPEAT, and WHILE statements. ITERATE means “start the loop again.”

For an example, see Section 13.6.5.5, “LOOP Syntax”.

 

 

 

Statement Label Syntax

 

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

  • begin_labelmust be followed by a colon.
  • begin_labelcan be given without end_label. If end_label is present, it must be the same as begin_label.
  • end_labelcannot be given without begin_label.
  • Labels at the same nesting level must be distinct.
  • 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:

 

 

More info on mysql:

13.6.5.1 CASE Syntax

13.6.5.2 IF Syntax

13.6.5.3 ITERATE Syntax

13.6.5.4 LEAVE Syntax

13.6.5.5 LOOP Syntax

13.6.5.6 REPEAT Syntax

13.6.5.7 RETURN Syntax

13.6.5.8 WHILE Syntax

 

 

 

打赏作者

您的支持将鼓励我们继续创作!

[微信] 扫描二维码打赏

[支付宝] 扫描二维码打赏

发表评论

电子邮件地址不会被公开。 必填项已用*标注