数据库原理及应用

报告的结构是何老师的，课程使用软件HeidiSQL。语句仅供参考，感谢指出错误。

---

GROUP1.Mysql基本代码

问题1：根据输入变量deptcode，编写代码块来获得雇员的数量

use example;
set @deptcode =1;
set @personnum=0;
select count(*) into @personnum from emp where deptno=@deptcode;
select @personnum;

问题2：编写一个包含部分定义、执行的代码块:根据员工的empno，输出员工姓名、工资和部门名称

USE EXAMPLE;
SET @empno=1;
SET @ename=null;
SET @sal=null;
SET @dname=null;
select emp.ename,emp.Sal,dept.Dname INTO @ename,@sal,@dname from emp,dept WHERE emp.empno=@empno AND emp.deptno=dept.Deptno;
SELECT @ename,@sal,@dname;

运行结果

问题3：编写一个代码块,根据员工数量的变量(empno),得到他的年收入和并输出

USE EXAMPLE;
SET @empno=2,@ename=NULL,@sal_year=0;
SELECT emp.ename,emp.Sal*12 INTO @ename,@sal_year from emp WHERE emp.empno=@empno;
SELECT @ename,@sal_year;

运行结果

问题4：编写一个包含定义、执行的代码块，实现以下功能:根据部门编号变量的值输出本部门所有员工的平均工资

USE EXAMPLE;
SET @deptno=1,@dname=NULL,@avgsal=0;
SELECT dept.Dname,AVG(emp.Sal) INTO @dname,@avgsal from emp,dept WHERE emp.deptno=@deptno AND emp.deptno=dept.Deptno;
SELECT @dname,@avgsal;

运行结果

GROUP2.存储过程的编写和应用

问题1:请写一个存储过程

根据输入的参数员工编号显示该员工的姓名和工资。

//创建存储过程
CREATE PROCEDURE `select_salary`(IN `empcode` int)
BEGIN
declare empsalary float;
declare empname varchar(30);
select ename,sal into empname , empsalary from example.emp where empno= empcode;
select empname, empsalary
END

//查询
set @empcode=5;
SET @empname="";
SET @empsalary=0.0;
call select_salary(@empcode,@empname,@empsalary);
SELECT @empname,@empsalary;

问题2:编写一个存储过程完成以下功能

根据输入参数部门编号，输出该部门所有员工的平均工资。

//查询
SET @deptno=1;
SET @deptname="";
SET @avgsalary=0.0;
CALL  select_salary(@avgsalary,@deptname,@deptno);
SELECT @deptname,@avgsalary;

问题3:编写一个存储过程，根据输入的参数员工编号empno更改员工的工资sal

如果员工的工资大于100，则在原值的基础上增加100;如果工资为0或null，则将其设置为200。

BEGIN
select sal into salary from emp where empno=empcode;
if salary>100 then update example.emp set sal=sal+100 where empno=empcode;
end if;
if salary=0 or salary is null then update example.emp set sal=200 where empno=empcode;
end if;
select sal into empsal from example.emp where empno=empcode;
END
//查询
SET @empcode=2;
SET @salary=0.0;
SET @empsal=0.0;
CALL update_sal(@empcode,@empsal,@salary);
SELECT @empcode,@empsal;

问题4:编写一个存储过程，根据输入的部门编号更改该部门的工资

如果员工的工资大于1000，则在原值的基础上增加10%;如果工资大于1500，则提高8%。(需要使用E5中的游标)

//存储过程代码

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_deptsalary`(
IN `deptcode` INT,
OUT `perno` INT,
OUT `salary` FLOAT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE done INT DEFAULT 0;
declare se_sal cursor for select empno,sal from emp where deptno=deptcode and Sal>1000;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
open se_sal;
repeat
fetch se_sal into perno,salary;
IF (NOT done and salary<=1500) THEN
update emp set sal=sal*1.1 where empno=perno;
select perno,salary;
ELSEIF (NOT done and salary>1500) THEN
update emp set sal=sal*1.08 where empno=perno;
select perno,salary;
END IF;
until done end repeat;
CLOSE se_sal;
END