一、IF條件語句

語法

IF condition THENstatements;
ELSEIF condition THENstatements;
ELSEstatements;
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,myCnamefrom v4 where sid=stuIdorder by score desclimit 1;-- 根據局部變量做判斷if myScore>80 thenselect concat(myCname,'A') 課程情況;elseif myScore<80 and myScore>60 thenselect concat(myCname,'B') 課程情況;elseselect 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 '成績等級';

二、CASE條件語句

CASE XWHEN condition1 THEN statements1WHEN condition2 THEN statements2...ELSE statements
END CASE;

根據性別返回稱呼

特性	DECLARE	SET
用途	聲明變量并定義類型	為已聲明的變量賦值
位置	BEGIN...END塊開始，其他語句之前	變量聲明后的任意位置
語法	DECLARE 變量名 數據類型 [DEFAULT 默認值];	SET 變量名 = 值;
作用域	局部（當前塊內）	可操作局部變量和會話變量
功能限制	只能聲明變量	只能賦值，不能創建變量

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,genderfrom t_student where sid=stuId;-- 使用判斷case genderwhen '男' thenset gender = '小伙子';when '女' thenset gender = '小姑娘';elseset 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 '稱呼';

三、LOOP循環語句

[loop_label:] LOOPstatements;IF condition THENLEAVE [loop_label];END IF;
END LOOP [loop_label];

計算階乘

DELIMITER //
CREATE PROCEDURE a3(IN p_num INT, OUT p_result INT)
BEGINDECLARE i INT DEFAULT 1;SET p_result = 1;my_loop: LOOPSET p_result = p_result * i;SET i = i + 1;IF i > p_num THENLEAVE 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的階乘';

四、WHILE循環語句

[while_label:] WHILE condition DOstatements;
END WHILE [while_label];

打印數字

DELIMITER //
CREATE PROCEDURE a4(IN p_max INT)
BEGINDECLARE i INT DEFAULT 1;WHILE i <= p_max DOSELECT i AS '當前數字';SET i = i + 1;END WHILE;
END //
DELIMITER ;
調用CALL a4(3);

計算平均分

drop procedure if exists p6;
delimiter $$
create procedure p6()
begindeclare 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_lengthdoset 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 '平均分';