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*/