第十一次课 存储过程

0、函数：一般来讲是为存储过程服务的，函数不支持执行sql语句。

1、存储过程: 是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

2、存储过程的声明格式：

a、无输入无输出的存储过程

    --查询公司中平均薪水以上的员工信息

create procedure getSal

as

begin

  declare @sal numeric(8,2)

  select @sal = avg(sal) from emp

  select * from emp where sal > @sal

end

--调用存储过程

execute getSal

-- 计算公司中薪水超过的员工人数，打印出来

create procedure getNums

as

begin

  declare @num int

  select @num = count(*) from emp where sal > 8000

  print '薪水超过以上的员工人数是:' + cast(@num as varchar(255))

end

exec getNums

b、有输入参数的存储过程

--计算公司中某个部门的人数（传入的是部门的名称）

create procedure getDeptNums

       @dname varchar(255)

as

begin

  declare @deptno int,@nums int

  select @deptno = deptno from dept where dname = @dname

  select @nums = count(*) from emp where deptno = @deptno

  print @dname + '的人数是:' + cast(@nums as varchar(255))

end

exec getDeptNums '后勤部'    --调用方式

exec getDeptNUms @dname = '后勤部'  --调用方式

 c、有输入有输出的存储过程

 --查询某个部门的平均薪水，要求将平均薪水返回！

alter procedure getDeptAvgSals

          @dname varchar(25),@avgsal numeric(8,2) output

as

begin

   declare @deptno int

   if exists(select * from dept where dname = @dname)

     begin

       select @deptno = deptno from dept where dname = @dname

       select @avgsal = avg(sal) from emp where deptno = @deptno

     end

    else

      print @dname + '不存在'

end

--调用存储过程，并获取该存储过程的返回值！

declare @sal decimal(8,2)

exec getDeptAvgSals @dname = '航空部',@avgsal = @sal output

print @sal

--- 如何判断存储过程是否存在

if exists(select * from sysobjects where name = 'getSal' and xtype = 'p')

   begin

     print '该存储过程存在'

     drop procedure getSal

   end

else

   print '该存储过程不存在'

d、存储过程中嵌套while循环

  --例子：将每个部门中薪水在该部门平均薪水以上的员工找出来，最后打印如下：

         --1号部门平均薪水：xxx，薪水在平均薪水以上的人数有xx人，分别是:xxx/xxx/xxx/xxx

         --2号部门平均薪水：xxx，薪水在平均薪水以上的人数有xx人，分别是:xxx/xxx/xxx/xxx

         --.......

一、写存储过程查询某个部门的平均薪水以上的人，返回值为所有满足条件的员工姓名

create procedure getAvgSalPeople

                 @deptno int,@s varchar(255) output

as

begin

   declare @avgsal numeric(8,2),@i int,@num int,@name varchar(255)

   select @avgsal = avg(sal) from emp where deptno = @deptno

   select identity(int,1,1) 'id', ename into #avg from emp where sal > @avgsal and deptno = @deptno

   select @num = count(*) from #avg

   set @i = 1

   set @s = ''

   while @i <= @num

     begin

       select @name = ename from #avg where id = @i

       set @s=  @s + @name + '/'

       set @i = @i + 1

     end

end

--测试

declare @a varchar(255)

exec getAvgSalPeople 3,@a output

print @a 

---测试---

alter procedure getAvgSalNums

as

begin

  declare @i int,@num int,@deptno int,@avgsal numeric(8,2),@people int,@s varchar(255)

  select  distinct(deptno),identity(int,1,1) 'id'  into #emp  from emp

  select @num = count(*)  from #emp

  set @i = 1

  while @i < @num

  begin

    ---start----

      select @deptno = deptno from #emp where id = @i   --循环每个部门编号

      select @avgsal = avg(sal) from emp where deptno = @deptno   --找出当前部门的平均薪水

      select @people = count(*) from emp where sal > @avgsal and deptno = @deptno  --找出当前平均薪水以上的人数

      exec  getAvgSalPeople @deptno,@s output  --调用上面的存储过程获取某个部门平均薪水以上的员工姓名

      print cast(@deptno as varchar(255)) + '号部门平均薪水:' + cast(@avgsal as varchar(255)) + ',在平均薪水以上的人数有:'+ cast(@people as varchar(255))

      +'人,分别是:' + @s

    ---end------

    set @i = @i + 1

  end

end

--调用存储过程

exec  getAvgSalNums

---写存储过程打印每个部门的优秀员工信息

create procedure getGoodEmp

       @deptno int

as

begin

  set nocount on

  declare @j int,@nums int,@ename varchar(255),@time int,@comm numeric(8,2),@empno int,@avgtimes int

  select @avgtimes = avg(datediff(mm,hiredate,getdate())) from emp where deptno = @deptno  --查询当前部门的平均工龄

  select identity(int,1,1) 'id', empno into #ee from emp where deptno = @deptno and datediff(mm,hiredate,getdate()) > @avgtimes  and sal between 5000 and 8000 and empno in (select mgr from emp)    

  select @nums = count(*) from #ee

  set @j = 1

  while @j <= @nums

   begin

     ----start----------

       select @empno = empno from #ee where id = @j     --循环所有的优秀员工的编号

       select @ename = ename,@time = datediff(mm,hiredate,getdate()),@comm = (isnull(sal,0) + isnull(comm,0))* 2 from emp where empno = @empno   --查找当前优秀员工的各项属性

       print cast(@deptno as varchar(255)) + '号部门中的优秀员工为:'+@ename + ',工龄是:'+cast(@time as varchar(255)) + '月' + ',奖金为:' + cast(@comm as varchar(255)) + '元'

     ----end----------

     set @j = @j + 1

   end

  set nocount off

end

------------------------------------------------------------------------------------------   

create procedure getGood

as

begin

  set nocount on

  declare @i int,@num int,@deptno int

  select distinct(deptno), identity(int,1,1) 'id' into #emp from emp

  set @num = (select count(*) from #emp)

  set @i = 1  

  while @i < @num

  begin

    ---begin----

       select @deptno = deptno from #emp where id = @i   --循环所有的部门

       exec getGoodEmp @deptno   --调用上面的存储过程打印每个部门的优秀员工

    ---end------

    set @i = @i + 1

  end

  set nocount off

end

set nocount on------不返回语句运行的计数信息

set nocount off------返回语句运行的计数信息

set ansi_warnings off --关闭系统警告

set ansi_warnings on  --打开系统警告

作业：

A、写存储过程，求平均薪水最高部门的部门名称和编号，将这两个参数返回出来

B、写一个存储过程，查询某个部门的总人数，部门编号不存在要提示！（部门编号作为输入参数，返回值作为输出参数）

C、写一个存储过程，删除某个部门，如果该部门下有员工，则提示：（该部门有？个员工，不能删除）

D、写一个存储过程，求某个薪水范围内员工的总人数！(薪水范围作为输入参数，人数作为返回值)

--统计中公司中各个级别下的总人数，显示结果为：

   1级工资以下的人数:xxx人,分别为:xxx/xxx/xxx/xxx

   2级工资以下的人数:xxx人,分别为:xxx/xxx/xxx/xxx

   .........................

--查询公司中每个领导的下属人数，最后打印结果如下

   编号:xxx,姓名:xxx,下属人数:xxx,下属姓名：xxx、xxx、xxx.........

--公司准备给优秀员工发奖金，优秀员工评选规则如下：

   1、每个部门中该员工的工龄必须超过该部门所有人的平均工龄

   2、其薪水在~8000之间、且有下属的员工

   3、奖金发放额度为该员工的奖金和薪水之和的倍

   问：公司每个部门的谁可以领到奖金?奖金额度为多少，打印如下:

       1号部门的优秀员工为:张三，工龄为xxx月、奖金为xxx元