MySQL流程控制

1、IF ELSE

DROP PROCEDURE IF EXISTS if_else_example;
CREATE PROCEDURE if_else_example(

IN p_type TINYINT 

)
COMMENT 'if_else_example'
BEGIN

DECLARE m_var INT DEFAULT 0;

IF p_type =1 THEN
SET m_var =1;

ELSEIF p_type =2 THEN
SET m_var =2;

ELSE
SET m_var =3;

END IF;

SELECT m_var;

END;

 

2、CASE WHEN

DROP PROCEDURE IF EXISTS case_when_example;
CREATE PROCEDURE case_when_example(

IN p_type TINYINT 

)
COMMENT 'case_when_example'

BEGIN

DECLARE m_var INT DEFAULT 0;

CASE p_type

WHEN 1 THEN
  SET m_var = m_var + 1;

WHEN 2 THEN
  SET m_var = m_var + 2;

WHEN 3 THEN
  SET m_var = m_var +3;

END CASE;

SELECT m_var;

END;

 

3、WHILE DO

DECLARE i INT DEFAULT 0;
WHILE i <100 DO
SET i = i +1;
ENDW HILE;

 

4、使用LEAVE语句强制退出存储过程

DROPPROCEDUREIFEXISTS leave_example;
CREATEPROCEDURE leave_example(
OUT p_out INT
)
begin_label:BEGIN

DECLARE m INTDEFAULT5;

IF m >1THEN
SET p_out =1;
LEAVE begin_label;
ENDIF;

IF m >2THEN
SET p_out =2;
LEAVE begin_label;
ENDIF;

IF m >3THEN
SET p_out =3;
LEAVE begin_label;
ENDIF;

END;

/* CALL leave_example(@s); 结果：1 */