15-综合案例-统计雇员表

9.5 综合案例——统计雇员表

本节视频教学录像：13分钟

通过本章的学习，应该掌握了存储过程和自定义函数的定义、调用、修改和删除操作。在这两种子程序中，可能要涉及变量的声明和使用，当查询结果有多条记录时，还要应用光标来进行处理，同时要用到分支和循环这些控制语句，如果遇到错误还要进行处理。本节通过实例来掌握存过过程和自定义函数的定义和使用。

该案例使用前面定义的雇员表emp,表中的字段分别为empno(雇员编号）、empname（雇员姓名）、empsex(雇员性别)、empage（雇员年龄）、dno(雇员所在的部门编号)。

mysql> select * from emp; +-------+---------+--------+--------+------+ |empno|empname|empsex|empage|dno | +-------+---------+--------+--------+------+ | 1|张丽 |女 | 28| 1| | 2|李小军 |男 | 30| 1| | 3|王芳 |女 | 35| 2| | 4|周新 |男 | 45| 1| | 5|张北 |男 | 37| 2| +-------+---------+--------+--------+------+ 5 rows in set (0.07 sec)

【范例9-28】

创建存储过程，查看某个年龄段的雇员人数,并统计年龄的和。

mysql> delimiter // mysql> CREATE PROCEDURE emp_age_count(IN age1 int,IN age2 int, -> OUT count INT ) -> BEGIN -> DECLARE temp FLOAT; -> DECLARE emp_age CURSOR FOR SELECT empage FROM emp; -> DECLARE EXIT HANDLER FOR NOT FOUND -> CLOSE emp_age; -> SET@sum=0; -> SELECT COUNT(*) INTO count FROM emp -> WHERE empage>age1 AND empage<age2; -> OPEN emp_age; -> REPEAT -> FETCH emp_age INTO temp; -> IF temp>age1 AND temp<age2 -> THEN SET@sum=@sum+temp; -> END IF; -> UNTIL 0 END REPEAT; -> CLOSE emp_age; -> END// Query OK, 0 rows affected (0.16 sec)

存储过程分析如下。

(1) 创建的存储过程名称为emp_age_count。

(2) 存储过程两个输入参数age1和age2,分别存储用户输入的年龄下界和年龄上界；参数count为输出参数，存储符合年龄范围的雇员人数。

(3) 定义局部变量temp，存储每个符合条件的雇员的年龄。

(4) DECLARE emp_age CURSOR FOR SELECT empage FROM emp;定义光标，对应的查询为雇员表中雇员的年龄。

(5) DECLARE EXIT HANDLER FOR NOT FOUND CLOSE emp_age；定义条件处理。如果没有遇到关闭光标，就退出存储过程。

(6) sum是会话变量，前面必须加@,sum中的值为满足条件的年龄的总和。

(7) SELECT COUNT(*) INTO count FROM emp WHERE empage>age1 AND empage<age2 ;用SELECT…INOT语句来为输出变量count赋值。

【范例9-29】

调用范例9-28创建的存储过程如下。

mysql> call emp_age_count(20,31,@cc)// Query OK, 0 rows affected (0.00 sec)

通过CALL语句来调用该存储过程，20赋值给age1，31赋值给age2,变量cc接收输出参数count的值。

mysql> select @cc,@sum// +------+------+ |@cc |@sum| +------+------+ | 2| 58| +------+------+ 1 row in set (0.00 sec)

通过SELECT语句查看两个变量的值，cc为符合年龄条件的变量，sum为符合年龄条件的年龄的总和。

【范例9-30】

删除范例9-28创建的存储过程。

mysql> delimiter ; mysql> drop procedure emp_age_count; Query OK, 0 rows affected (0.00 sec)

删除的结果可以通过SHOW CREATE PROCEDURE来查看，代码如下。

mysql> show create procedure emp_age_count\G; ERROR 1305 (42000): PROCEDURE emp_age_count does not exist

通过结果可以看到emp_age_count存储过程已经被删除。

【范例9-31】

可以通过自定义函数来实现查看某个年龄段的雇员人数。

mysql> delimiter // mysql> CREATE FUNCTION emp_age_count(age1 int,age2 int) -> Returns int -> Begin -> Return(select count(*)from emp where empage>age1 and empage<age2); -> End // Query OK, 0 rows affected (0.00 sec)

由于自定义函数只能够返回一个值，所以该函数返回结果中的符合年龄条件的雇员人数，而符合条件的雇员年龄的总和在该函数中不能返回。

【范例9-32】

调用范例9-28创建的函数emp_age_count，代码如下。

mysql> select emp_age_count(20,32); +----------------------+ | emp_age_count(20,32) | +----------------------+ | 2| +----------------------+ 1 row in set (0.00 sec)

【范例9-33】

删除自定义函数emp_age_count的语句如下。

mysql> drop function emp_age_count; Query OK, 0 rows affected (0.00 sec)