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)