请在“答题”菜单下选择相应命令,并按照题目要求完成下面的简单应用题,具体要求如下: 注意:下面出现的“考生文件夹”均为%USER% ...
注意:下面出现的“考生文件夹”均为%USER%
给定的企业员工管理数据库db_yggl中包括部门表tb_dept和职工表tb_emp,tb_dept包含的字段有deptno(部门号)、deptname(部门名称)、manager(部门负责人)、office(办公地点)、telephone(电话),tb_emp包含的字段有eno(员工号)、name(姓名)、sex(性别)、birth(出生年份)、education(学历)、worktime(参加工作时间)、title(职务)、salary(工资)、telephone(电话)和deptno(部门号)。
请按要求完成以下操作:
1. 创建一个名称为fn_yggl的存储函数,要求根据输入的员工号判断其工龄是否大于等于30,如果大于等于30,则将其工资增加5%作为奖励。(说明:计算工龄时只计年份)
注意:在考生文件夹中的sj21.txt文件已给出部分程序,但程序不完整,请考生删除下划线,并在下划线处填上适当的内容,使程序补充完整,不能增加或删除行,并按原文件名保存在考生文件夹下,否则没有成绩。
2.创建一个名称为sp_emp的存储过程,要求逐行显示“市场部”员工的姓名、学历、工资和该部门的平均工资。
注意:在考生文件夹中的sj22.txt文件已给出部分程序,但程序不完整,请考生删除下划线,并在下划线处填上适当的内容,使程序补充完整,不能增加或删除行,并按原文件名保存在考生文件夹下,否则没有成绩。
参考解析: 1.DELIMITER //
CREATE FUNCTION fn_yggl(eid CHAR(4))
(1)CHAR(20)
DETERMINISTIC
BEGIN
DECLARE workage INT;
DECLARE newsalary FLOAT;
SELECT YEAR(CURDATE())-SUBSTRING(worktime,1,4) INTO workage FROM tb_emp WHERE eno=eid;
IF (2)THEN
UPDATE tb_emp SET salary=salary*1.05 WHERE eno=eid;
RETURN('工资已经更新');
ELSE
RETURN('工作年限不够,工资不能更新');
END IF;
END //
DELIMITER ;
SELECT (3)('1118');
答案:(1) RETURNS (2) workage >=30 (3) fn_fggl
解析:根据不同的mysql用户,需要查看创建函数的功能是否开启:Mysql> show variables like ‘%func%’; 如果Value处值为off,则需将其开启,使用如下语句开启set global log_bin_trust_function_creators=1;接下来就是创建存储函数的过程,其中”DELEMITER $$”语句是将MYSQL的结束符设置为$$,因为MYSQL默认的语句结束符是’;’,这是为了避免存储函数中SQL语句结束符相冲突,就使用了DELEMITER,在定义完毕后再使用”DELEMITER ;”回复默认。创建函数的是create function func_name(func_paramter)
Returns type
[characteristic…] routine body
func_name表示存储函数的名称;func_parameter为存储函数的参数列表,有如下形式:[in|out|inout] param_name type,其中 in表示输入,out表示输出,inout既表示输出也可以输入;returns type 表示函数返回数据的类型,因此(1)处填的是RETRUNS;character指存储函数的特性 ;begin 和end 语句用来限定存储过程体;(2)处是if判断语句,判断工龄是否大于等于30,因此(2)处填的是workage >=30;第三处使用select语句进行调用函数fn_fggl(),因此(3)处填的函数名fn_fggl。
CREATE (1) sp_emp (OUT avgsalary FLOAT)
BEGIN
DECLARE ename VARCHAR(12);
DECLARE edu VARCHAR(10);
DECLARE esalary FLOAT;
DECLARE totalsalary FLOAT;
DECLARE count SMALLINT;
DECLARE done INT DEFAULT -1;
DECLARE cur_emp CURSOR FOR
SELECT name, education, salary
FROM tb_emp INNER JOIN tb_dept
ON tb_emp.deptno=tb_dept.deptno AND deptname= '市场部';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
SET totalsalary=0;
SET count=0;
OPEN cur_emp;
FETCH cur_emp INTO ename, edu, esalary;
WHILE (done!=1) DO
SELECT ename, edu, esalary;
SET totalsalary=totalsalary+(2);
SET count=count+1;
FETCH cur_emp INTO ename, edu, esalary;
END WHILE;
IF (count>0) THEN
SET avgsalary= totalsalary/count;
ELSE
SET avgsalary=0;
END IF;
CLOSE (3);
END $$
DELIMITER ;
答案:(1)PROCEDURE (2) esalary (3) cur_emp
解析:创建存储过程,其中”DELEMITER $$”语句是将MYSQL的结束符设置为$$,因为MYSQL默认的语句结束符是’;’,这是为了避免存储过程中SQL语句结束符相冲突,就使用了DELEMITER,在定义完毕后再使用”DELEMITER ;”回复默认。创建存储过程的是create procedure sp_name([proc_paramter])
[characteristics….]routine_body
sp_name表示存储函数的名称,因此(1)处填的是PROCEDURE;proc_parameter为存储过程的参数列表,有如下形式:[in|out|inout] param_name type,其中 in表示输入,out表示输出,inout既表示输出也可以输入。character指存储函数的特性 ;begin 和end 语句用来限定存储过程体,dec cur_cid CURSOR FOR select是使用declare关键字来声明光标,open语句是打开光标,fetch语句是使用光标,close语句是关闭光标,注意MYSQL中光标只能在存储过程和函数中使用,本题使用了while流程控制语句,而(2)处表示的是的员工的总工资,因此(2)处填的是esalary,在整个流程结束的时候,需要关闭之前定义的光标,因此(3)处填的是cur_emp。