MySQL查询和存储过程练习题

-- ---------------1.练习题分割线开始---------------
-- 1、创建一个存储过程，通过接收员工工号，能够计算出员工每个月工资
create procedure pro_8(in emp_empno int,out emp_ename varchar(20),out wage float)
begin
select emp.ename,job.sal+job.comm into emp_ename,wage from emp inner join job on emp.jobno=job.jobno where emp_empno=empno;
end;
-- 验证
call pro_8(20164001,@a,@b);
select @a,@b;
-- 2、创建一个存储过程，通过接收员工工号，能够计算出员工的年薪
--     （董事长为15薪，总经理为14薪，部门经理为13薪，职员为12薪）
create procedure pro_10(in emp_empno int,out emp_ename varchar(20),out wage float)
begin 
declare a int ;
set a=(select jobno from emp where empno=emp_empno);
if a=(select jobno from job where jobname="董事长") then
select emp.ename,15*(job.sal+job.comm) into emp_ename,wage from emp inner join job on emp.jobno=job.jobno where emp_empno=empno and job.jobname="董事长";
elseif a=(select jobno from job where jobname="总经理") then
select emp.ename,14*(job.sal+job.comm) into emp_ename,wage from emp inner join job on emp.jobno=job.jobno where emp_empno=empno and job.jobname="总经理";
elseif a=(select jobno from job where jobname="部门经理") then
select emp.ename,14*(job.sal+job.comm) into emp_ename,wage from emp inner join job on emp.jobno=job.jobno where emp_empno=empno and job.jobname="部门经理";
elseif a=(select jobno from job where jobname="职员") then
select emp.ename,12*(job.sal+job.comm) into emp_ename,wage from emp inner join job on emp.jobno=job.jobno where emp_empno=empno and job.jobname="职员";
end if;
end
-- 验证董事长年薪
call pro_10(20161001,@a,@b);
select @a,@b;
-- 验证总经理年薪
call pro_10(20162001,@a,@b);
select @a,@b;
-- 验证部门经理年薪
call pro_10(20163001,@a,@b);
select @a,@b;
-- 验证职员年薪
call pro_10(20164001,@a,@b);
select @a,@b;
-- 3、创建一个存储过程，向表job表中插入10条数据，要求职位编号为101至110，职位名称为‘职位101’至‘职位110’，工资和奖金都设置为1000
create procedure pro_insert_job()
begin
declare a int ;
declare b varchar(20);
declare c float(7,2);
declare d float(7,2);
set a=101;
set b=concat('职位',a);
set c=1000;
set d=1000;
while a<=110 do
insert into job values (a,b,c,d);
set a=a+1;
end while;
end
-- 验证
call pro_insert_job();
select * from job;
-- ---------------1.练习题分割线结束---------------