mysql存储过程之条件语句（四）

1.存储过程的条件语句

需求：编写存储过程，传入ID如果是偶数，返回其url，否则返回ID

DELIMITER $$
CREATE PROCEDURE test3(IN BOOK_ID BIGINT(11))
BEGIN
    -- 定义url
    DECLARE URL VARCHAR(255) DEFAULT '';
    -- 判断语句
    IF(BOOK_ID %2 = 0)
    -- 满足if表达式
    THEN
        SELECT MARK_URL INTO URL FROM book_mark WHERE id = BOOK_ID;
        SELECT URL;
    -- 不满足if表达式
    ELSE
        SELECT BOOK_ID;
    -- 结束判断
    END IF;
END;
$$
DELIMITER ;

总结

1.条件语句的最基本结构：if() then ...  else ... end if;
2.if判断返回逻辑真或假，表达式可以是任意表示真或假的表达式

 

2.存储过程的条件语句应用示例

需求：用户输入id
（1）如果status = 1, 则score + 10;
（2）如果status = 2, 则score + 20;
（3）其他情况, 则score + 50;


DELIMITER $$
CREATE PROCEDURE test5(IN uid BIGINT(11))
BEGIN
	DECLARE ustatus TINYINT(2);
	SELECT STATUS INTO ustatus FROM test_score WHERE id = uid;
	IF(ustatus = 1)
	THEN
		UPDATE test_score SET score = score + 10 WHERE id = uid;
	ELSEIF (ustatus = 2)
	THEN
		UPDATE test_score SET score = score + 20 WHERE id = uid;
	ELSE
		UPDATE test_score SET score = score + 50 WHERE id = uid;
	END IF;
END;
$$
DELIMITER ;

CALL test5(4)