MYSQL简单控制流程

DELIMITER $CREATE PROCEDURE proc(i INT) BEGIN DECLARE j INT DEFAULT 100 ; IF i=10 THEN SELECT j ; SELECT '等于10' ; ELSE SET j = i + i ; SELECT j ; SELECT '不等于10' ; END IF ; END$CREATEPROCEDUREproc(iINT)BEGINDECLAREjINTDEFAULT100;IFi=10THENSELECTj;SELECT′等于10′;ELSESETj=i+i;SELECTj;SELECT′不等于10′;ENDIF;END

CALL proc(11)

DROP PROCEDURE IF EXISTS proc

– 等额判断
DELIMITER $CREATE PROCEDURE proc( w INT) BEGIN CASE w WHEN 1 THEN SELECT '星期一' ; WHEN 2 THEN SELECT '星期二' ; WHEN 3 THEN SELECT '星期三' ; WHEN 4 THEN SELECT '星期四' ; WHEN 5 THEN SELECT '星期五' ; WHEN 6 THEN SELECT '星期六' ; WHEN 7 THEN SELECT '星期七' ; ELSE SELECT '输入有误' ; END CASE ; END$CREATEPROCEDUREproc(wINT)BEGINCASEwWHEN1THENSELECT′星期一′;WHEN2THENSELECT′星期二′;WHEN3THENSELECT′星期三′;WHEN4THENSELECT′星期四′;WHEN5THENSELECT′星期五′;WHEN6THENSELECT′星期六′;WHEN7THENSELECT′星期七′;ELSESELECT′输入有误′;ENDCASE;END

CALL proc(10)

DROP PROCEDURE IF EXISTS proc
– 范围判断
DELIMITER $CREATE PROCEDURE proc( score INT) BEGIN CASE WHEN score&gt;90 THEN SELECT &#x27;优&#x27; ; WHEN score&gt;60 AND score&lt;=90 THEN SELECT &#x27;及格&#x27; ; ELSE SELECT &#x27;不及格&#x27; ; END CASE ; END$CREATEPROCEDUREproc(scoreINT)BEGINCASEWHENscore>90THENSELECT′优′;WHENscore>60ANDscore<=90THENSELECT′及格′;ELSESELECT′不及格′;ENDCASE;END

CALL proc(80)

DROP PROCEDURE IF EXISTS proc
– 实现循环累加等功能

DELIMITER $$
CREATE PROCEDURE proc( m INT )
BEGIN
DECLARE i INT DEFAULT 0 ;
DECLARE result INT DEFAULT 0 ;

WHILE  i < m  DO
	SET result = result + i ;
	SET i = i + 1 ;
END WHILE ;
SELECT result ;

END $$

CALL proc(100)

DROP PROCEDURE IF EXISTS proc
DELIMITER $$
CREATE PROCEDURE proc( m INT)
BEGIN
DECLARE result INT DEFAULT 0 ;
DECLARE i INT DEFAULT 1 ;
calc : LOOP
SET result = result + i ;
SET i = i + 1 ;
IF i > m THEN
LEAVE calc ;
END IF ;
END LOOP ;
SELECT result ;
END

CALL proc(10)

DROP PROCEDURE IF EXISTS proc
– repeat until
DELIMITER $CREATE PROCEDURE proc(m INT) BEGIN DECLARE i INT DEFAULT 0 ; DECLARE result INT DEFAULT 0 ; REPEAT SET result = result + i ; SET i = i + 1 ; UNTIL i &gt; m END REPEAT ; SELECT result ; END$CREATEPROCEDUREproc(mINT)BEGINDECLAREiINTDEFAULT0;DECLAREresultINTDEFAULT0;REPEATSETresult=result+i;SETi=i+1;UNTILi>mENDREPEAT;SELECTresult;END

CALL proc(10)