数据库

根据输入变量deptcode，编写代码块来获得雇员人数。

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

请写一个存储过程：根据输入的参数员工编号得到该员工的姓名和工资。

drop procedure if exists select_person;
DELIMITER //
create procedure select_person(IN empcode int,OUT empname varchar(20), out empsalary DECIMAL(20,2))
begin
select ename,sal into empname , empsalary from eemp where empno= @empcode;
end;
//
DELIMITER ;
set @empcode=3;
set @empname='';
set @empsalary=0.0;
call select_person(@empcode, @empname, @empsalary);
select @empname, @empsalary;

编写一个存储过程，根据输入的参数员工编号更改员工的工资:如果员工的工资大于100，则在原值的基础上增加100;如果工资为0或null，则将其设置为200。

drop procedure if exists supdate_sal;
Delimiter //
Create procedure update_sal(in empcode int,out empsal float)
Begin
Declare salary float; 
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 emp where empno=empcode;
End //
Delimiter ;
Set @empcode=4;
Set @empsal=0;
Call update_sal(@empcode,@empsal);
Select @empcode,@empsal;

编写一个存储过程，根据输入的部门编号更改该部门所有员工的工资:如果员工的工资大于1000，则在原值的基础上增加10%;如果工资大于1500，则提高8%。(需要使用游标)
Drop procedure if exists sp_select_sal;

Drop procedure if exists sp_select_sal;
DELIMITER //
CREATE PROCEDURE sp_select_sal(IN empcode INT)
BEGIN
    DECLARE salary DOUBLE;
    DECLARE done INT DEFAULT 0;
    DECLARE cur_sal CURSOR FOR SELECT sal FROM emp WHERE empno = empcode;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN cur_sal;
    REPEAT
        FETCH cur_sal INTO salary;
        IF NOT done THEN
            IF salary > 1000 AND salary <= 1500 THEN
                UPDATE EXAMPLE.emp SET sal = sal * 1.1 WHERE empno = empcode;
            ELSEIF salary > 1500 THEN
                UPDATE EXAMPLE.emp SET sal = sal * 1.08 WHERE empno = empcode;
            END IF;
        END IF;        
    UNTIL done END REPEAT;
    CLOSE cur_sal;
END//
DELIMITER ;
SET @deptcode=2; 
Select sum(sal) from emp where deptno=@deptcode;
CALL sp_select_sal(@deptcode);
Select sum(sal) from emp where deptno=@deptcode;

请写一个存储过程，根据输入的部门编号，得到部门所有员工的姓名和工资。(用游标实现)

DELIMITER //
create procedure select_emps(in dcode int)
begin
DECLARE  empname varchar(20);
DECLARE  empsalary float;
DECLARE done INT DEFAULT 0;
DECLARE  cur_emp cursor for select ename, sal from emp where deptno= dcode;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur_emp;
REPEAT
FETCH cur_emp INTO empname, empsalary;
IF NOT done THEN
Select empname, empsalary;
END IF;
UNTIL done END REPEAT;
CLOSE cur_emp;
end;//
DELIMITER ;
call select_emps(2);

请写一个程序:根据输入的部门编号，得到这个部门所有员工的平均工资。(用游标)

drop procedure if exists sp_select_avgjob; 
DELIMITER //
create procedure sp_select_avgjob(in dcode int)
begin
declare avgsalary float default 0;
declare sum float default 0;
declare n int default 0;
declare done int default 0;
declare cur_emp cursor for select Sal from emp where deptno=dcode;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
open cur_emp;
REPEAT
fetch cur_emp into sum;
IF NOT done THEN
set n=n+1;
set avgsalary=avgsalary+sum;
end if;
UNTIL done END REPEAT;
CLOSE cur_emp;
if n>0 then
set avgsalary=avgsalary/n;
end if;
select avgsalary;
end;
//
DELIMITER ;
call sp_select_avgjob(2);

请写一个程序:更改所有员工的津贴:如果员工的原津贴小于100;则改为200;如果空值则赋100元。(用游标)

drop procedure if exists  sp_update_sal;
DELIMITER //
create procedure sp_update_sal()
begin
declare done int default 0;
declare empcomm float;
declare empname varchar(30);
declare cur_emp cursor for select Comm,ename from emp;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
open cur_emp;
REPEAT
fetch cur_emp into empcomm,empname;
IF NOT done THEN
	if empcomm<100 then 
		update example.emp set Comm=200 where ename=empname;
		elseif empcomm IS NULL then
			update example.emp set Comm=100 where ename=empname;
	end if;
	 SELECT empname, empcomm; 
end if;
UNTIL done END REPEAT;
CLOSE cur_emp;
end;
//
DELIMITER ;
call sp_update_sal();
select Comm from emp;