mysql存储函数入门系列二

1.存储函数的删除

sql:drop procedure test05

2.输出多个参数

功能：查询10部门最高工资，最低工资，平均工资

sql:

create procedure test05(
out pHigh decimal(8,2),
out pLow DECIMAL(8,2),
out pAvg  DECIMAL(8,2)
)
BEGIN
select MAX(sal),MIN(sal),AVG(sal) into pHigh,pLow,pAvg from emp where deptno=10;
end ;

存储函数调用：

call test05(@pHigh,@pLow,@pAvg);
SELECT  @pHigh as high,@pLow as low ,@pAvg as avgSal

2.存储函数一个输入参数，多个输出参数

功能：查询10部门，工资最高的员工姓名，工作，薪资

sql:

create procedure test06(
in  var_deptno int,
out var_name VARCHAR(10),
out  var_job VARCHAR(10),
out var_sal decimal(8,2)
)
BEGIN
select DISTINCT ename,job,sal into var_name,var_job,var_sal from emp where 
deptno=var_deptno and sal=(select max(sal) from emp where deptno=var_deptno);
end ;

存储函数调用：

sql:

call test06(10,@name,@job,@sal);
select @name as ename,@job as job ,@sal as sal

注意事项：

如果10部门最大工资的人数有两个，再次执行上述存储函数将报错