运维实施18-数据库流程控制语句

1、IF条件语句

-IF条件语句

语法

IF condition THEN
    statements;
ELSEIF condition THEN
    statements;
ELSE
    statements;
END IF;

判断成绩等级

# 判断成绩等级
# 输入学生的编号,取出学生的第一门课，然后判断当前的课程的等级
drop procedure if exists p2;
delimiter $$
create procedure p2(in stuId int)
begin
    -- 定义局部变量
    declare myScore double default 0.0;
    declare myCname varchar(20);
    -- 查询学生的成绩
    select score,cname into myScore,myCname
         from v4 where sid=stuId
    order by score desc
    limit 1;
    -- 根据局部变量做判断
    if myScore>80 then
        select concat(myCname,'A') 课程情况;
    elseif myScore<80 and myScore>60 then
        select concat(myCname,'B') 课程情况;
    else
        select concat(myCname,'C') 课程情况;
    end if;
end$$
delimiter ;

-- 调用存储过程
call p2(2);

调用

SET @grade = '';
CALL a1(85.5, @grade);
SELECT @grade AS '成绩等级';

SET @grade = '';
CALL a1(59.5, @grade);
SELECT @grade AS '成绩等级';

2、CASE条件语句

语法

CASE X    WHEN condition1 THEN statements1    WHEN condition2 THEN statements2    ...    ELSE statementsEND CASE;

根据性别返回称呼

drop procedure if exists p3;
delimiter $$
create procedure p3(in stuId int)
begin
    -- 定义名字和性别
    declare name varchar(20);
    declare gender varchar(20);
    -- 查询
    select sname,ssex into name,gender
    from t_student where sid=stuId;
    -- 使用判断
    case gender
        when '男' then
            set gender = '小伙子';
        when '女' then
            set gender = '小姑娘';
        else
            set gender = '妖怪';
    end case;
    -- 输出
    select name,gender;
end$$
delimiter ;

调用

SET @title = '';
CALL a2(1001, @title);
SELECT @title AS '称呼';
​
SET @title = '';
CALL a2(1003, @title);
SELECT @title AS '称呼';

3、LOOP循环语句

语法

[loop_label:] LOOP
    statements;
    IF condition THEN
        LEAVE [loop_label];
    END IF;
END LOOP [loop_label];

计算阶乘

DELIMITER //
CREATE PROCEDURE a3(IN p_num INT, OUT p_result INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    SET p_result = 1;
    
    my_loop: LOOP
        SET p_result = p_result * i;
        SET i = i + 1;
        
        IF i > p_num THEN
            LEAVE my_loop;
        END IF;
    END LOOP my_loop;
END //
DELIMITER ;

调用

SET @result = 0;
CALL a3(5, @result);
SELECT @result AS '5的阶乘';
​
SET @result = 0;
CALL a3(3, @result);
SELECT @result AS '3的阶乘';

4、WHILE循环语句

语法

[while_label:] WHILE condition DO
    statements;
END WHILE [while_label];

打印数字

DELIMITER //
CREATE PROCEDURE a4(IN p_max INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    
    WHILE i <= p_max DO
        SELECT i AS '当前数字';
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

调用

CALL a4(3);

计算平均分

drop procedure if exists p6;
delimiter $$
create procedure p6()
begin
    declare ns varchar(10) default '一二三四五六七八九十';
    declare name_length int default 2;
    declare name varchar(10) default '';
    declare idx int default 0;
    while char_length(name) < name_length
        do
            set idx = floor(char_length(ns) * rand());
            set name = concat(name, substring(ns, idx, idx + 1));
        end while;
    select max(cid) + 1 into idx from t_course;
    insert into t_course(cid, cname, tid)
    values (idx, name, 1);
end$$
delimiter ;
​
call p6();

调用

SET @avg_score = 0;
CALL a5(1001, @avg_score);
SELECT @avg_score AS '平均分';