#分支结构IF的使用#举例DELIMITER//CREATEPROCEDURE test_if()BEGIN#情况1#声明局部变量#DECLARE stu_name VARCHAR(15);#IF stu_name IS NULL#THEN SELECT 'stu_name is null';#END IF;#情况2:二选一#DECLARE email VARCHAR(25);#IF email is NULL # THEN SELECT 'email is null';#ELSE # SELECT 'email is not null';#END IF ;#情况3:多选一DECLARE age INTDEFAULT20;IF age >40THENSELECT'中老年';ELSEIF age >18THENSELECT'青壮年';ELSEIF age >8THENSELECT'青少年';ELSESELECT'婴幼儿';ENDIF;END//DELIMITER;#调用CALL test_if();DROPPROCEDURE test_if;
分支结构之CASE
#分支结构之CASEDELIMITER//CREATEPROCEDURE test_case()BEGIN#演示1:case ... when ...then.../*
DECLARE var INT DEFAULT 2;
CASE var
WHEN 1 THEN SELECT 'var = 1';
WHEN 2 THEN SELECT 'var = 2';
WHEN 3 THEN SELECT 'var = 3';
ELSE SELECT 'other value';
END CASE;
*/#演示2:case when...then....DECLARE var1 INTDEFAULT10;CASEWHEN var1>=100thenSELECT'三位数';WHEN var1>=10THENSELECT'两位数';ELSESELECT'个位数';ENDCASE;END//DELIMITER;#调用CALL test_case();DROPPROCEDURE test_case;#举例:声明存储过程“update_salary_by_eid4”,定义IN参数emp_id,输入员工编号。#判断该员工薪资如果低于9000,就更新薪资为9000;薪资大于9000且低于10000的;#但是奖金比例为null的,就更新奖金比例为0.01,其他的涨薪100;DELIMITER//CREATEPROCEDURE update_salary_by_eid4(IN emp_id INT)BEGIN#声明局部变量DECLARE emp_sal DOUBLE;#记录员工工资DECLARE bonus DOUBLE;#记录奖金率#局部变量的赋值SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;SELECT commission_pct INTO bonus FROM employees WHERE employee_id = emp_id;CASEWHEN emp_sal <9000THENUPDATE employees SET salary =9000WHERE employee_id = emp_id;WHEN emp_sal <10000AND bonus ISNULLTHENUPDATE employees SET commission_pct =0.01WHERE employee_id = emp_id;ELSEUPDATE employees SET salary = salary +100WHERE employee_id = emp_id;ENDCASE;END//DELIMITER;CALL update_salary_by_eid4(103);CALL update_salary_by_eid4(104);CALL update_salary_by_eid4(105);SELECT*FROM employees
WHERE employee_id IN(103,104,105);
循环结构之LOOP
#循环结构之LOOP#举例1:DELIMITER//CREATEPROCEDURE test_loop()BEGIN#声明局部变量DECLARE num INTDEFAULT1;
loop_label:LOOP#重新赋值SET num= num+1;IF num >=10THENLEAVE loop_label;ENDIF;ENDLOOP loop_label;#查看numSELECT num;END//DELIMITER;#调用CALL test_loop();#举例2:当市场环境变好时,公司为了奖励大家,决定给大家涨工资。#声明存储过程"update_salary_loop()",声明OUT参数num,输出循环次数,存储过程中实现循环给大家涨薪,涨薪为原来的1.1倍,直到全公司的平均薪资#达到12000结束,并统计循环次数DELIMITER//CREATEPROCEDURE update_salary_loop(OUT num INT)BEGIN#声明变量DECLARE avg_sal DOUBLE;#记录员工的平均工资DECLARE loop_count INTDEFAULT0;#记录循环次数#获取员工的平均工资SELECTAVG(salary)INTO avg_sal FROM employees;
loop_lab:LOOP#结束循环条件IF avg_sal>12000THENLEAVE loop_lab;ENDIF;#如果低于12000,更新员工工资UPDATE employees SET salary = salary *1.1;#更新avg_sal 变量的值SELECTAVG(salary)INTO avg_sal FROM employees;#记录循环次数SET loop_count = loop_count +1;ENDLOOP loop_lab;#给num赋值SET num = loop_count;END//DELIMITER;SELECTAVG(salary)FROM employees;CALL update_salary_loop(@num);SELECT@num;
循环结构之WHILE
#循环结构WHILE/*
凡是循环结构,一定具备四个要素
1、初始化条件
2、循环条件
3、循环体
4、迭代条件
*/#举例1:DELIMITER//CREATEPROCEDURE test_while()BEGIN#初始化条件DECLARE num INTDEFAULT1;#循环条件WHILE num <=10DO#循环体(略)#迭代条件SET num = num +1;ENDWHILE;#查询SELECT num;END//DELIMITER;#调用CALL test_while();#举例2:市场环境不好时,公司为了渡过难关,决定暂时降低大家的薪资#声明存储过程"update_salary_while()",声明OUT参数num,输出循环次数#存储过程中实现循环给大家降薪,薪资降为原来的90%,直到全公司的平均薪资达到5000,并统计循环次数。DELIMITER//CREATEPROCEDURE update_salary_while(OUT num INT)BEGIN#声明变量DECLARE avg_sal DOUBLE;#记录平均工资DECLARE while_count INTDEFAULT0;#记录循环次数#赋值SELECTAVG(salary)INTO avg_sal FROM employees;WHILE avg_sal >5000DOUPDATE employees SET salary = salary *0.9;SET while_count = while_count +1;SELECTAVG(salary)INTO avg_sal FROM employees;ENDWHILE;#给num赋值SET num = while_count;END//DELIMITER;DROPPROCEDURE update_salary_while;#调用CALL update_salary_while(@num);SELECT@num;SELECTAVG(salary)FROM employees;
循环结构之REPEAT
#循环结构之REPEAT#举例1:DELIMITER//CREATEPROCEDURE test_repeat()BEGIN#声明变量DECLARE num INTDEFAULT1;REPEATSET num = num +1;
UNTIL num>=10ENDREPEAT;#查看SELECT num;END//DELIMITER;#调用CALL test_repeat();#举例2:当市场环境变好时,公司为了奖励大家,决定给大家涨工资#声明存储过程"update_salary_repeat()",声明OUT参数num,输出循环次数#存储过程中实现循环给大家涨薪,薪资涨为原来的1.15倍。直到全公司的平均工资为13000结束,并统计循环次数。DELIMITER//CREATEPROCEDURE update_salary_repeat(OUT num INT)BEGIN#声明变量DECLARE avg_sal DOUBLE;#记录平均工资DECLARE repeat_count INTDEFAULT0;#记录循环次数#赋值SELECTAVG(salary)INTO avg_sal FROM employees;REPEATUPDATE employees SET salary = salary *1.15;SET repeat_count = repeat_count +1;SELECTAVG(salary)INTO avg_sal FROM employees;
UNTIL avg_sal >=13000ENDREPEAT;#给num赋值SET num = repeat_count;END//DELIMITER;#调用CALL update_salary_repeat(@num);SELECT@num;SELECTAVG(salary)FROM employees;