mysql - 流程控制语句

-- 清除原先的函数
DROP FUNCTION IF EXISTS `fun_test`;

-- 创建函数
CREATE FUNCTION `fun_test`
    -- 设置函数参数
    (a DOUBLE, b DOUBLE)
    -- 设置返回值类型
    RETURNS VARCHAR(500)
BEGIN
    -- 设置成员变量
    DECLARE res VARCHAR(500);
    SET res = '';

    -- If...else...语句 
    IF a > 0 THEN
        SET res = CONCAT(res, 'a > 0; ');
    ELSEIF a < 0 THEN
        SET res = CONCAT(res, 'a < 0; ');
    ELSE
        SET res = CONCAT(res, 'a = 0; ');
    END IF;

    -- case...when...语句 类似于switch 
    CASE b
        WHEN 0 THEN SET res = CONCAT(res, 'b == 0; ');
        ELSE SET res = CONCAT(res, 'b != 0; ');
        END CASE;

    -- loop语句 类似于for循环，使用LEAVE跳出循环 
    SET @count = 0;
    loop_eg:
    LOOP
        SET @count = @count + 1;
        IF @count < 2 THEN
            SET res = CONCAT(res, 'LOOP循环测试:');
        ELSE
            LEAVE loop_eg;
        END IF;
    END LOOP loop_eg;

    -- loop语句 类似于for循环，使用ITERATE跳出本次循环 
    SET @count = 0;
    loop_eg:
    LOOP
        SET @count = @count + 1;
        IF @count = 1 THEN
            SET res = CONCAT(res, '1');
        ELSEIF @count = 2 THEN
            ITERATE loop_eg; SET res = CONCAT(res, '2');
        ELSEIF @count = 3 THEN
            SET res = CONCAT(res, '3;');
        ELSE
            LEAVE loop_eg;
        END IF;
    END LOOP loop_eg;

    -- repeat...util...语句 类似于do...WHILE...语句 
    SET @count = 0;
    REPEAT
        SET @count = @count + 1;
        SET res = CONCAT(res, @count);
    UNTIL @count = 5
        END REPEAT;

    -- while...语句 
    WHILE @count < 10
        DO
            SET @count = @count + 1;
            SET res = CONCAT(res, @count);
        END WHILE;

    RETURN res;
END;

-- 测试函数
SELECT fun_test(1, 2)