分支结构IF

#举例:

CREATE PROCEDURE test_if()
BEGIN
			#声明变量
			DECLARE stu_name VARCHAR(15);
			if stu_name is null
							THEN SELECT 'stu_name is null';
				end if;
end; 
#调用
CALL test_if();

例子

#情况二:"(二选一)
declare email VARCHAR(25) DEFAULT 'aaa';
if email is null
						then select 'email is null';
			else
						SELECT 'email is not null'
				end if;
#情况三:
DECLARE age INT DEFAULT 20;
	if age > 40 
				THEN SELECT '老';
	ELSEIF age>18
				THEN SELECT '青年';
	ELSEIF  age>8
					THEN SELECT '少';
			ELSE 
					SELECT '婴儿';
		end if;

分支结构case

#举例

CREATE PROCEDURE test_case()
BEGIN
			#演示case....when....then......case
			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 values';
		END CASE;
		#演示 case when .... then..
		CASE 
		WHEN var>10 THEN
		SELECT '两位数';
		WHEN var>9 THEN
		SELECT '两位数';
		ELSE
		SELECT '个位数';
		END CASE;

end;
#调用
CALL test_case();

#举例3：声明存储过程“update_salary_by_eid4”，定义IN参数emp_id，输入员工编号。判断该员工 #薪资如果低于9000元，就更新薪资为9000元；薪资大于等于9000元且低于10000的，但是奖金比例 #为NULL的，就更新奖金比例为0.01；其他的涨薪100元。CREATE CREATE PROCEDURE 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;
			
			#判断
			CASE 
						WHEN emp_sal < 9000 THEN
						UPDATE employees SET salary = 9000 WHERE employee_id = emp_id;
						WHEN emp_sal BETWEEN 9000 AND 10000 AND bonus	<=> NULL THEN
							UPDATE employees SET commission_pct = 0.01 WHERE employee_id = emp_id;
						ELSE
								UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
					END CASE;

END;