2. 创建存储过程,实现的功能是:根据输入的员工姓名删除employees表中对应的员工信息。调用这个存储过程,输入参数为:‘李丽’,查看结果。
delimiter $$ create procedure delete_name(in a char(6)) begin delete from employees where name=a; end $$ delimiter ; call delete_name('李丽'); select * from employees;
3. 创建存储过程,实现的功能是:比较两个输入的浮点参数,若前者大,输出’大于’,若前者小,输出’小于’,否则输出’等于’。调用这个存储过程,输入参数为10.2,22.8,查看结果。
delimiter $$ create procedure compar(in k1 Float, in k2 Float, out k3 char(6) ) begin if k1>k2 then set k3= '大于'; elseif k1=k2 then set k3= '等于'; else set k3= '小于'; end if; end$$ delimiter ; call compar(10.2,22.8,@k); select @k; DROP PROCEDURE compar;
4. 创建存储过程,实现的功能是:根据输入的员工姓名输出该员工所在的部门名称。调用这个存储过程,输入参数为:‘王林’,查看结果。
delimiter $$ create procedure xz(in xm char(6)) begin select departmentname from departments where departmentid=(select departmentid from employees where name=xm); end $$ delimiter ; call xz('王林'); select @部门名称; DROP PROCEDURE xz;
5. 创建存储过程,实现的功能是:根据输入的员工姓名比较两个员工的实际收入,若前者大,输出’1’,若前者小,输出’-1’,否则输出’0’。调用这个存储过程,查看结果。
delimiter $$ create procedure bj(in xm1 char(6),in xm2 char(6),out BJ int) begin declare sr1,sr2 float(8); select inCome-outCome into sr1 from salary where employeeID=(select employeeid from employees where name =xm1); select inCome-outCome into sr2 from salary where employeeID=(select employeeid from employees where name =xm2); if xm1>xm2 then set BJ=1; elseif xm1<xm2 then set BJ=-1; else set BJ=0; end if; end $$ delimiter; call bj('王林','刘明',@bjdx); DROP PROCEDURE bj;
6. 创建存储过程,要求当一个员工的工作时间大于6年时,将其转到经理办公室工作。调用这个存储过程,查看结果。
delimiter $$ create procedure cg(in id char(6)) begin declare year tinyint; select workyear into year from employees where employeeid= id; if year>6 then update employees set departmentID=(select departmentID from departments where departmentName='经理办公室') where employeeID=id; end if; end$$ delimiter ; call cg('000001'); DROP PROCEDURE cg;
7. 创建存储过程,使用游标方式计算employees表中行的数目。调用这个存储过程,查看结果。
delimiter $$ create procedure number(out pnumber int) begin declare a int; declare found bool default true; declare c cursor for select employeeid from employees; declare continue handler for not found set found=false; set pnumber=0; open c; fetch c into a; while found do set pnumber=pnumber+1; fetch c into a; end while; close c; end$$ delimiter ; call number(@pnumber); select @pnumber; select count(*) from employees; DROP PROCEDURE num;
8. 创建存储过程,使用count(*)函数计算employees表中行的数目。调用这个存储过程,查看结果。
delimiter $$ create procedure cx(out a1 int) begin declare a2 int; set a2=(select count(*) from employees); set a1=a2; end $$ delimiter ; call cx(@hangshu); select @hangshu;
9. 创建存储过程,根据输入的部门名称,输出该部门收入最高的员工姓名。
delimiter $$ create procedure max1(in a char (15),out str char (10)) begin declare deid char (3) ; declare max2 double ; declare id2 char (10); set deid = (select departmentID from departments where departmentName = a ); set max2= (select max(Income ) from employees a, salary b where a.employeeID = b.employeeID and a.departmentID = deid ); Set id2=(select employeeID from salary where Income = max2); set str=(select name from employees where employeeID =id2); end $$ delimiter ; call max1('财务部',@max); select @max; DROP PROCEDURE max1;
10. 创建存储过程,根据输入的整数,输出1到该整数的累加结果。
delimiter $$ create procedure plus(in k int(4),out sum1 int ) begin declare i int; set i=1; set sum1=0; while i<=k do set sum1=sum1+i; set i=i+1; end while; end $$ delimiter ; call plus(5,@sum1); select @sum1;
11. 创建一个存储函数,返回员工的平均实际收入。调用该存储函数。
delimiter $$ create function num() returns integer begin return (select count(*) from employees); end $$ delimiter ; select num(); drop function num;
12. 创建一个存储函数,根据输入的员工编号,若该员工收入大于3000元,则在employees表和salary表删除该员工信息,返回’大于3000元已删除’,否则返回’不大于3000元保留’。 调用该存储函数,查看结果。
delimiter $$ create function delete1(id char (10)) returns char(15) begin declare b int ; declare str char (15); set b = (select Income from salary where employeeID = id); if b>3000 then set str = '大于3000元已删除'; delete from employees where employeeID =id; delete from salary where employeeID=id; else set str ='不大于3000元保留'; Return str; end if ; end $$ delimiter ; select delete1('000001'); drop function delete1;
** 13. 创建存储函数,根据输入的员工姓名,判断员工是否在研发部工作,若是则返回其学历,若不是则返回’不是研发部员工’。**
delimiter $$ create function panduan(nm char (10)) returns char(15) begin declare b char(15) ; declare str char (15); set b=(select departmentname from departments where departmentid = (select departmentid from employees where name=nm)); if b='研发部' then set str= (select education from employees where name=nm); else set str='不是研发部员工'; end if ; return str; end $$ delimiter ; select panduan('叶凡'); drop function panduan;
14. 创建一个存储函数,将工作时间满4年的员工收入增加500元。
delimiter $$ create function sj() begin update salary set income=income+500 where salary.employeeid=employees.employeeid and workyear>4; end $$ delimiter ; select sj(); drop function panduan; delimiter $$ create function add3() returns char(10) begin declare done int default 0; declare id char (8); declare nb cursor for select employeeID from employees where workyear>=4; declare continue handler for not found set done =1; open nb ; xh:loop fetch nb into id; If done =1 then leave xh; End if ; update salary set Income= Income +500 where employeeID= id; End loop; close nb ; return '执行完成'; end$$ delimiter ; select add3(); drop function add1;
15. 创建触发器,在employees表中删除员工信息的同时将salary表中该员工的信息删除,以确保数据完整性。创建完后删除employees表中的一行数据,然后查看salary表中的变化情况。
delimiter $$ create trigger delete2 after delete on employees for each row delete from salary where employeeid=old.employeeid; end$$ delimiter ; delete from employees where employeeid='000001'; select * from salary; drop trigger delete2;
16. 创建update_employees触发器,当departments表中的部门号发生变化时,employees表中员工所属的部门号也将改变。创建完后修改departments表中的一个部门号,然后查看employees表中的变化情况。
delimiter $$ create trigger update_employees after update on departments for each row begin update employees set departmentid=new.departmentid where departmentID= old.departmentID; end$$ delimiter ; select * from employees; update departments set departmentid =8 where departmentname='研发部'; select * from employees; drop trigger update_employees;
17. 创建update_salary_outCome触发器,当salary表中的收入增加500元时,支出也增加500元。创建完后修改salary表中的收入,然后查看表的变化情况。
delimiter $$ create trigger update_salary_outCome before update on salary for each row begin declare a int ; set a=(new.Income-old.Income); set new.Outcome =old.Outcome +a ; end$$ delimiter ; select * from salary; update salary set income =income+500 where employeeid='010008'; select * from salary;