系统变量分为全局系统变量（需要添加 GLOBAL 关键字）以及会话系统变量（需要添加 SESSION 关键字），有时也把全局系统变量简称为全局变量，有时也把会话系统变量称为local变量。如果不写，默认会话级别。静态变量（在 MySQL 服务实例运行期间它们的值不能使用 set 动态修改）属于特殊的全局系统变量。
DELIMITER $ CREATEPROCEDURE set_value() BEGIN DECLARE emp_name VARCHAR(25); DECLARE sal DOUBLE(10,2); SELECT last_name,salary INTO emp_name,sal FROM employees WHERE employee_id =102; SELECT emp_name,sal; END $ DELIMITER ;
CALL set_value();
声明两个变量，求和并打印 （分别使用会话用户变量、局部变量的方式实现）
用户变量
1 2 3 4
SET@a=10; SET@b=3.14; SET@sum=@a+@b; SELECT@sum;
局部变量
1 2 3 4 5 6 7 8 9 10
DELIMITER $ CREATEPROCEDURE add_value() BEGIN DECLARE m INTDEFAULT1; DECLARE n INTDEFAULT3; DECLARE SUM INT; SET SUM = m+n; SELECT SUM; END $ DELIMITER ;
DELIMITER $ CREATEPROCEDURE different_salary(IN emp_id INT,OUT dif_salary DOUBLE(10,2)) BEGIN DECLARE emp_sal DOUBLEDEFAULT0.0; DECLARE mgr_sal DOUBLEDEFAULT0.0; DECLARE mgr_id INT; SELECT manager_id INTO mgr_id FROM employees WHERE employee_id = emp_id; SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id; SELECT salary INTO mgr_sal FROM employees WHERE employee_id = mgr_id; SET dif_salary = mgr_sal - emp_sal; END $ DELIMITER ;
DELIMITER $ CREATEPROCEDURE update_salary_by_eid1(IN emp_id INT) BEGIN DECLARE sal DOUBLE(10,2); DECLARE hire_year DOUBLE; -- 查询工资 SELECT salary INTO sal FROM employees WHERE employee_id = emp_id; -- 查询入职时间 SELECT DATEDIFF(NOW(),hire_date)/365INTO hire_year FROM employees WHERE employee_id = emp_id; -- 判断是否满足条件 IF sal <8000AND hire_year >5 THENUPDATE employees SET salary = salary +500 WHERE employee_id = emp_id; END IF; END $ DELIMITER ;
104号员工在职超过5年，且工资低于8000，就拿它试试
原工资6000，调用完存储过程之后再查询，工资6500，成功
1 2 3 4 5
CALL update_salary_by_eid1(104);
SELECT salary FROM employees WHERE employee_id =104;
DELIMITER $ CREATEPROCEDURE update_salary_by_eid2(IN emp_id INT) BEGIN DECLARE sal DOUBLE(10,2); DECLARE hire_year DOUBLE; -- 查询工资 SELECT salary INTO sal FROM employees WHERE employee_id = emp_id; -- 查询入职时间 SELECT DATEDIFF(NOW(),hire_date)/365INTO hire_year FROM employees WHERE employee_id = emp_id; -- 判断是否满足条件 IF sal <9000AND hire_year >5 THENUPDATE employees SET salary = salary +500 WHERE employee_id = emp_id; ELSE UPDATE employees SET salary = salary +100 WHERE employee_id = emp_id; END IF; END $ DELIMITER ;
100号员工的工资高于9000，调用存储过程之后，涨薪100
1 2 3 4 5
CALL update_salary_by_eid2(100);
SELECT salary FROM employees WHERE employee_id =100;
DELIMITER $ CREATEPROCEDURE update_salary_by_eid3(IN emp_id INT) BEGIN DECLARE sal DOUBLE; DECLARE bonus DECIMAL(3,2); -- 查询工资 SELECT salary INTO sal FROM employees WHERE employee_id = emp_id; -- 查询奖金比例 SELECT commission_pct INTO bonus FROM employees WHERE employee_id = emp_id; -- 判断是否满足条件 IF sal <9000 THENUPDATE employees SET salary =9000 WHERE employee_id = emp_id; ELSEIF sal <10000AND bonus ISNULL THENUPDATE employees SET commission_pct =0.01 WHERE employee_id = emp_id; ELSE UPDATE employees SET salary = salary +100 WHERE employee_id = emp_id; END IF; END $ DELIMITER ;
分支结构之CASE
CASE 语句的语法结构：
类似于Java中的switch case
1 2 3 4 5 6
CASE 表达式 WHEN 值1THEN 结果1或语句1(如果是语句，需要加分号) WHEN 值2THEN 结果2或语句2(如果是语句，需要加分号) ... ELSE 结果n或语句n(如果是语句，需要加分号) END [case]（如果是放在beginend中需要加上case，如果放在select后面不需要）
类似于多重if else
1 2 3 4 5 6
CASE WHEN 条件1THEN 结果1或语句1(如果是语句，需要加分号) WHEN 条件2THEN 结果2或语句2(如果是语句，需要加分号) ... ELSE 结果n或语句n(如果是语句，需要加分号) END [case]（如果是放在beginend中需要加上case，如果放在select后面不需要）
举例：使用CASE流程控制语句的第1种格式，判断val值等于1、等于2，或者两者都不等。
1 2 3 4 5
CASE val WHEN1THENSELECT'val is 1'; WHEN2THENSELECT'val is 2'; ELSESELECT'val is not 1 or 2'; ENDCASE;
举例：使用CASE流程控制语句的第2种格式，判断val是否为空、小于0、大于0或者等于0。
1 2 3 4 5 6
CASE WHEN val ISNULLTHENSELECT'val is null'; WHEN val <0THENSELECT'val is less than 0'; WHEN val >0THENSELECT'val is greater than 0'; ELSESELECT'val is 0'; ENDCASE;
DELIMITER $ CREATEPROCEDURE update_salary_while(OUT num INT) BEGIN DECLARE count_while INTDEFAULT0; DECLARE avg_sal DOUBLEDEFAULT0.0; SELECTAVG(salary) INTO avg_sal FROM employees; WHILE avg_sal >5000 DO UPDATE employees SET salary = salary *0.9; SELECTAVG(salary) INTO avg_sal FROM employees; SET count_while = count_while +1; END WHILE; SET num = count_while; END $ DELIMITER ;
1 2
CALL update_salary_while(@num); SELECT@num;
循环结构之REPEAT
REPEAT语句创建一个带条件判断的循环过程。与WHILE循环不同的是，REPEAT 循环首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。
REPEAT语句的基本格式如下：
1 2 3 4
[repeat_label:] REPEAT 循环体的语句 UNTIL 结束循环的条件表达式 END REPEAT [repeat_label]
DELIMITER // CREATEPROCEDURE update_salary_repeat(OUT num INT) BEGIN DECLARE count_repeat INTDEFAULT0; DECLARE avg_sal DOUBLEDEFAULT0.0; SELECTAVG(salary) INTO avg_sal FROM employees; REPEAT UPDATE employees SET salary = salary *1.15; SET count_repeat = count_repeat +1; SELECTAVG(salary) INTO avg_sal FROM employees; UNTIL avg_sal >=13000 END REPEAT; SET num = count_repeat; END//
DELIMITER $ CREATEPROCEDURE leave_begin(IN num INT) leave_label:BEGIN IF num <=0 THEN LEAVE leave_label; ELSEIF num =1 THENSELECTAVG(salary) FROM employees; ELSEIF num =2 THENSELECTMIN(salary) FROM employees; ELSEIF num >2 THENSELECTMAX(salary) FROM employees; END IF; SELECTCOUNT(*) FROM employees; END $ DELIMITER ;
DELIMITER $ CREATEPROCEDURE leave_while(OUT num INT) BEGIN DECLARE count_while INTDEFAULT0; DECLARE avg_sal DOUBLEDEFAULT0.0; SELECTAVG(salary) INTO avg_sal FROM employees; leave_label:WHILE TRUE DO IF avg_sal <=10000THEN LEAVE leave_label; END IF; SET count_while = count_while +1; UPDATE employees SET salary = salary *0.9; SELECTAVG(salary) INTO avg_sal FROM employees; END WHILE; SELECT count_while; SET num = count_while; END $ DELIMITER ;
DELIMITER $ CREATEPROCEDURE test_iterate() BEGIN DECLARE num INTDEFAULT0; loop_label:LOOP SET num = num +1; IF num <10 THEN ITERATE loop_label; ELSEIF num >15 THEN LEAVE loop_label; END IF; SELECT CONCAT('输出测试',num); END LOOP loop_label; END $ DELIMITER ; -- 输出6个结果，从10到15
游标
什么是游标(光标)
虽然我们也可以通过筛选条件 WHERE 和 HAVING，或者是限定返回记录的关键字 LIMIT 返回一条记录，但是，却无法在结果集中像指针一样，向前定位一条记录、向后定位一条记录，或者是随意定位到某一条记录，并对记录的数据进行处理。
CREATE DATABASE test16_var_cur; use test16_var_cur; CREATETABLE employees AS SELECT*FROM atguigudb.`employees`; CREATETABLE departments AS SELECT*FROM atguigudb.`departments`;
1. 创建函数get_count()，返回公司的员工个数
1 2 3 4 5 6 7 8 9 10
DELIMITER $ CREATEFUNCTION get_count() RETURNSINT BEGIN DECLARE emp_cnt INTDEFAULT0; SELECTCOUNT(*) INTO emp_cnt FROM employees; RETURN emp_cnt; END $ DELIMITER ;
1
SELECT get_count();
2. 创建函数ename_salary()，根据员工姓名，返回它的工资
1 2 3 4 5 6 7 8 9 10 11
DELIMITER $ CREATEFUNCTION ename_salary(ename VARCHAR(25)) RETURNSDOUBLE BEGIN DECLARE emp_sal DOUBLEDEFAULT0.0; SELECT salary INTO emp_sal FROM employees WHERE last_name = ename; RETURN emp_sal; END $ DELIMITER ;
1
SELECT ename_salary('Abel');
3. 创建函数dept_sal() ，根据部门名，返回该部门的平均工资
1 2 3 4 5 6 7 8 9 10 11 12
DELIMITER $ CREATEFUNCTION dept_sal(dept_name VARCHAR(25)) RETURNSDOUBLE BEGIN DECLARE dept_avg_sal DOUBLEDEFAULT0.0; SELECTAVG(salary) INTO dept_avg_sal FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE department_name = dept_name; RETURN dept_avg_sal; END $ DELIMITER ;
1
SELECT dept_sal('IT');
4. 创建函数add_float()，实现传入两个float，返回二者之和
1 2 3 4 5 6 7 8 9
DELIMITER $ CREATEFUNCTION add_float(f1 FLOAT,f2 FLOAT) RETURNSFLOAT BEGIN DECLARE res FLOATDEFAULT0.0; SET res = f1 + f2; RETURN res; END $ DELIMITER ;
DELIMITER $ CREATEPROCEDURE test_if_pro(sal DOUBLE) BEGIN IF sal <3000 THENDELETEFROM employees WHERE salary = sal; ELSEIF sal BETWEEN3000AND5000 THENUPDATE employees SET salary = salary +1000WHERE salary = sal; ELSE UPDATE employees SET salary = salary +500WHERE salary = sal; END IF; END $ DELIMITER ;
3. 创建存储过程insert_data()，传入参数为 IN 的 INT 类型变量 insert_count，实现向admin表中批量插入insert_count条记录
1 2 3 4 5 6 7 8 9 10 11
DELIMITER $ CREATEPROCEDURE insert_data(INT insert_count INT) BEGIN DECLARE i INTDEFAULT1; WHILE i <= insert_count DO INSERT admin(user_name,user_pwd) VALUES (CONCAT('Rose-',i),ROUND(RAND() *100000)); SET i = i +1; END WHILE; END $ DELIMITER ;
1
CALL insert_data(100);
游标的使用
创建存储过程update_salary()，参数1为 IN 的INT型变量dept_id，表示部门id；参数2为 IN的INT型变量change_sal_count，表示要调整薪资的员工个数。查询指定id部门的员工信息，按照salary升序排列，根据hire_date的情况，调整前change_sal_count个员工的薪资，详情如下。
