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_varINTDEFAULT0; IF p_type =1THEN SET m_var =1; ELSEIF p_type =2THEN SET m_var =2; ELSE SET m_var =3; ENDIF; SELECT m_var; END;
2、CASE WHEN
DROP PROCEDUREIFEXISTS case_when_example; CREATE PROCEDURE case_when_example( IN p_type TINYINT ) COMMENT 'case_when_example' BEGIN DECLARE m_varINTDEFAULT0; CASE p_type WHEN 1THEN SET m_var= m_var+1; SET m_var= m_var+1; WHEN 2THEN SET m_var= m_var+2; SET m_var= m_var+2; WHEN 3THEN SET m_var= m_var+3; SET m_var= m_var+3; ENDCASE; SELECT m_var; END;
3、WHILE DO
DECLARE iINTDEFAULT0; WHILE i <100 DO SET i = i +1; ENDWHILE;
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*/