存储过程（2）

-- if语句的使用
delimiter $ -- 声明结束符号
CREATE PROCEDURE pro_test4()
    BEGIN
        DECLARE height int DEFAULT 175;
        DECLARE description varchar(50) DEFAULT '';
        -- if语句的使用         
        if height >= 180 then
            set description = '身材高挑';
        elseif height >= 170 and height < 180 then
            set description = '标准身材';
        else
            set description = '一般身材';
        end if;
        SELECT CONCAT('身高', height, '对应的身材类型为', description);
    end$
delimiter ; -- 声明结束符号

CALL pro_test4();



-- 传递参数 输入参数 in
delimiter $ -- 声明结束符号
CREATE PROCEDURE pro_test5(in height int)
    BEGIN
        DECLARE description varchar(50) DEFAULT '';        
        if height >= 180 then
            set description = '身材高挑';
        elseif height >= 170 and height < 180 then
            set description = '标准身材';
        else
            set description = '一般身材';
        end if;
        SELECT CONCAT('身高', height, '对应的身材类型为', description);
    end$
delimiter ; -- 声明结束符号

CALL pro_test5(20);


-- 传递参数 in 输入参数  out输出参数 inout 输入输出参数
delimiter $ -- 声明结束符号
CREATE PROCEDURE pro_test6(in height int, out description varchar(100))
    BEGIN    
        if height >= 180 then
            set description = '身材高挑';
        elseif height >= 170 and height < 180 then
            set description = '标准身材';
        else
            set description = '一般身材';
        end if;
    end$
delimiter ; -- 声明结束符号


-- 定义一个用户会话变量接收返回值   @@叫做系统变量
CALL pro_test6(20, @description);

SELECT @description;


-- case 
delimiter $ -- 声明结束符号
CREATE PROCEDURE pro_test7(in mon int)
    BEGIN
        DECLARE result varchar(50);        
        CASE 
            WHEN mon >= 1 and mon <= 3 THEN
                set result = '一季度';
            WHEN mon >= 4 and mon <= 6 THEN
                set result = '二季度';
            WHEN mon >= 7 and mon <= 9 THEN
                set result = '三季度';
            ELSE
                set result = '四季度';
        END CASE;

        SELECT CONCAT('传递的月份', mon, '计算的结果', result) as content;
    end$
delimiter ; -- 声明结束符号

CALL pro_test7(10);


-- while循环
delimiter $ -- 声明结束符号
CREATE PROCEDURE pro_test8(n int)
    BEGIN
        DECLARE total int DEFAULT 0;
        DECLARE num int DEFAULT 1;
        while num <= n do
            set total = total + num;
            set num = num + 1;
        end while;
        select total;
    end$
delimiter ; -- 声明结束符号

CALL pro_test8(100);


-- repeat 循环  满足条件退出循环
delimiter $ -- 声明结束符号
CREATE PROCEDURE pro_test9(n int)
    BEGIN
        DECLARE total int DEFAULT 0;
        
        repeat
            set total = total + n;
            set n = n - 1;
            -- 结束循环条件 until 后的语句不加 ;
            until n = 0
        end repeat;
        
        select total;
    end$
delimiter ; -- 声明结束符号

CALL pro_test9(100);


-- loop 循环 需要配合 leave
delimiter $ -- 声明结束符号
CREATE PROCEDURE pro_test10(n int)
    BEGIN
        DECLARE total int DEFAULT 0;
        
        c:loop
            
            set total = total + n;
            set n = n - 1;
            
            if n <= 0 then 
                leave c;
            end if;
        end loop c;
        
        select total;
    end$
delimiter ; -- 声明结束符号

CALL pro_test10(100);


-- 游标  查询emp表数据并且逐行展示

delimiter $ -- 声明结束符号
CREATE PROCEDURE pro_test11()
    BEGIN
    
        DECLARE e_id int(11);
        DECLARE e_name varchar(50);
        DECLARE e_age int(11);
        DECLARE e_salary int(11);
        DECLARE has_data int DEFAULT 1;
        
        -- 声明游标
        DECLARE emp_result CURSOR for SELECT * FROM emp;
        -- 声明拿不到数据的句柄
        DECLARE EXIT HANDLER FOR NOT FOUND SET has_data = 0;
        
        -- 开启游标
        OPEN emp_result;·
        
        repeat
            -- 抓取游标中的数据  调一次 fetch 游标往下移动一行
            FETCH emp_result into e_id, e_name, e_age, e_salary;
            SELECT CONCAT('id=',e_id,',name=',e_name,',age=',e_age,',e_salary=', e_salary);
            
            until has_data = 0
        end repeat;

        -- 关闭游标
        CLOSE emp_result;
        
    end$
delimiter ; -- 声明结束符号

CALL pro_test11();


-- 存储函数  获取满足条件的 city 总记录数

delimiter $ -- 声明结束符号
CREATE FUNCTION fun1(countryId int)
RETURNS int
BEGIN
    DECLARE cnum int;
    SELECT COUNT(*) into cnum FROM city where country_id = countryId;
    
    return cnum;
END$
delimiter ; -- 声明结束符号

-- 函数调用使用 select 语句
SELECT fun1(1);