Day 8(11.15):自定义函数和存储过程实训答案

-- 准备工作：
create table stu_info
(t_number char(8),t_name varchar(10),t_gender char(2),t_birthday datetime)


create table course
(c_number char(6),c_name varchar(20),c_credit int,c_hour int,c_teacher varchar(10))


create table exam
(t_number char(8),c_number char(6),t_grade decimal(5,2))


--向stu_info表插入数据
insert into stu_info values('20040301','张华','女','19840113')
insert into stu_info values('20040302','王立','男','19830624')
insert into stu_info values('20040303','蒋超','男','19841115')
insert into stu_info values('20040304','王浩雨','男','19851020')
insert into stu_info values('20040305','张静','女','19840418')
insert into stu_info values('20050301','李华','女','19830113')
insert into stu_info values('20050302','张立','男','19840624')
insert into stu_info values('20050303','黄超','男','19851125')
insert into stu_info values('20050304','汪雨','男','19861020')
insert into stu_info values('20050305','王静','女','19850418')


--向course表插入数据
insert into course values('100101','高等数学',2,60,'赵金')
insert into course values('100102','大学英语',3,80,'王维')
insert into course values('100103','大学物理',2,60,'李华')
insert into course values('100104','大学英语',4,80,'刘杰')
insert into course values('100105','大学英语',NULL,80,'刘杰')


--向exam表插入数据
insert into exam values('20040301','100101',79)
insert into exam values('20040301','100102',88)
insert into exam values('20040302','100101',90)
insert into exam values('20040302','100103',75)
insert into exam values('20040303','100101',79)
insert into exam values('20040303','100102',75)
insert into exam values('20040303','100103',95)
insert into exam values('20040304','100102',43)
insert into exam values('20040304','100103',68)
insert into exam values('20040305','100101',64)
insert into exam values('20040305','100102',87)
insert into exam values('20040305','100103',92)


-- 练习：


-- 1. 编写用户定义函数,要求根据学生学号、课程号查找出考试成绩。并调用函数查看学号为20040301、课程号为100101的成绩。
create function dbo.f1(@tm char(8),@cm char(6))
returns decimal(5,2)
as
   begin
     declare @ret decimal(5,2)
     select @ret=t_grade from exam where t_number=@tm and c_number=@cm
     return @ret
   end


select dbo.f1('20040305','100103')


-- 2. 编写用户定义函数,要求根据学号得到该学生的学生姓名和各科考试成绩。并调用函数查看学号为20040301的考试成绩
create function dbo.f2(@tm char(8))
returns table
as
   return(select t_name,t_grade from stu_info as s join exam as e on s.t_number=e.t_number where s.t_number=@tm)


select * from dbo.f2('20040301')


-- 3.  编写用户定义函数。要求得到每个学生的学生编号、学生姓名以及该学生的平均分。并调用函数查看查找每个学生的平均分。 
create function dbo.f3()
returns table
as
  return(select s.t_number,t_name,avg(t_grade) as pj from stu_info as s join exam as e on s.t_number=e.t_number
         group by s.t_number,t_name)


select * from dbo.f3()




-- 2. 编写用户定义函数dbo.FN_stuExistsOrNot。要求根据学生编号查找出该用户是否存在，如果存在 则返回 1；如果不存在 则返回 0
   -- 并分别调用函数查看'20040301'、'20040309'结果。
create function dbo.f4(@tm char(8))
returns int
as
  begin
    declare @ret int
    if exists(select * from stu_info where t_number=@tm)
        select @ret=1
    else
        set @ret=0
    return @ret
  end


select dbo.f4('20040301')


create function dbo.f5(@tm char(8))
returns bit
as
  begin
    declare @ret bit
    declare @count int 
    select @count=count(*) from stu_info where t_number=@tm
    if @count>0
       set @ret=1
    else
       set @ret=0
    return @ret 
  end


select dbo.f5('20040301')


-- 3. 编写用户定义函数dbo.FN_search_grade1,要求根据学生编号得到该学生的学生编号和各科考试成绩。
   -- 并调用函数查看学号为20040301的考试成绩
create function dbo.FN_search_grade1(@tm char(8))
returns table
as
  return(select t_number,t_grade from exam where t_number=@tm)




-- 4. 编写用户定义函数dbo.FN_search_grade2,要求根据学生姓名得到该学生的学生姓名和各科考试成绩。
   -- 并调用函数查看学生姓名为张华的考试成绩
create function dbo.FN_search_grade2(@tm varchar(10))
returns table
as
   return(select t_name,t_grade from stu_info join exam on stu_info.t_number=exam.t_number where t_name=@tm)




-- 5.  编写用户定义函数dbo.FN_avg_grade。要求根据学生编号得到每个学生的学生编号以及该学生的平均分。
    -- 并调用函数查看查找每个学生的平均分。 
create function dbo.FN_avg_grade(@tm char(8))
returns table
as
 return(select t_number,avg(t_grade) as pjf from exam group by t_number)




 -- 6. 编写用户定义函数dbo.FN_get_stu。要求输入学生编号、输出该学生的编号、姓名、出生年月信息。
    -- 并分别调用函数查看'20040301'、'20040309'结果。
create function dbo.FN_get_stu(@tm char(8))
returns table
as
return(select t_number,t_name,t_birthday from stu_info where t_number=@tm)






 -- 7. 编写用户定义函数。要求输入用户编号、判断该用户是否存在，
       --  如果存在，输出该学生的编号、姓名、出生年月；如果不存在，输出 '该人员不存在'
-- 并分别调用函数查看'20040301'、'20040309'结果。
create function f6(@tm char(8))
returns @t table(t_number char(8),t_name varchar(10),t_birthday datetime,memo varchar(200))
as
  begin
     if exists(select * from stu_info where t_number=@tm)
         insert @t(t_number,t_name,t_birthday) select t_number,t_name,t_birthday from stu_info where t_number=@tm
     else
         insert @t(memo) values('bcz')
     return 
  end


select * from dbo.f6('20040301')
select * from dbo.f6('20040309')


sp_configure


create table dbo.t_online 
(
idx         int identity(1,1),
game_id     int default('1'),
area_id     int default('1'),
server_id   int default('1'),
counter     int,
create_time datetime default(getdate())
)


declare @start int
declare @end   int
select  @start=1,@end=100
while @start<=@end
  begin
    insert t_online(counter) values (convert(int,rand()*10000+1))
    waitfor delay '00:00:02'
    set @start=@start+1
  end


alter proc dbo.p_Statistic
as
select top 2 min(game_id) as game_id,min(area_id) as area_id,min(server_id) as server_id
       ,avg(counter) as counter,convert(char(16),create_time,120) as stat_time
       from t_online 
       where game_id=1 and area_id=1 and server_id=1
       group by convert(char(16),create_time,120)
       order by convert(char(16),create_time,120) desc
Go


select * from pubs..employee


exec  dbo.p_Statistic


select * from t_online


-- 练习 --------------------------------------------------
-- 1 统计 t_online 表中，每1分钟内的最低，平均，最高在现人数，按照时间降序
create  procedure p1
as
  select convert(char(16),create_time,120),min(counter),max(counter),avg(counter) from t_online
  group by convert(char(16),create_time,120)
  order by convert(char(16),create_time,120) desc
-- 2 查找 ItemUUID 相同的记录
/*
create table item
(
userindex   int,
avatarindex int,
ItemUUID    uniqueidentifier default(newid())
)
insert item values (588,1,newid())
insert item values (588,2,newid())
insert item values (375,2,'A9937E3D-558F-4BCA-85C6-A3966DCD6FE1')
insert item values (483,2,newid())
insert item values (210,2,newid())
insert item values (694,2,newid())
insert item values (815,2,newid())
insert item values (179,2,'A9937E3D-558F-4BCA-85C6-A3966DCD6FE1')
insert item values (82,2,newid())
insert item values (981,2,newid())


select * from item*/
create procedure p2
as
select * from item where itemuuid in(
select itemuuid from item group by itemuuid having count(*)>1)


create function f1(@jid int)
returns table
as
  return(select * from pubs..employee where job_id=@jid)


create procedure p11
@jid int
as
   select * from pubs..employee where job_id=@jid


exec p11 10
exec p11 @jid=10




alter proc dbo.p_Input_2 
@fname nvarchar(50)
as
if not exists (select * from pubs.dbo.employee where fname=@fname)
   begin
      select 'User '''+@fname+''' does not exist.'
      return
   end
select * from pubs.dbo.employee where fname=@fname
Go


exec dbo.p_Input_2 @fname='Maria'


exec dbo.p_Input_2 @fname='Tom'


create table staff
(
idx      int identity(1,1),
name     nvarchar(50),
gender   bit,
deptid   int,
jobid    int,
hiredate datetime
) 




create proc dbo.p_InputStaff
@name     nvarchar(50),
@gender   bit = null,
@deptid   int = null,
@jobid    int = null,
@hiredate datetime = null
as
insert staff (name,gender,deptid,jobid,hiredate) values (@name,@gender,@deptid,@jobid,@hiredate)
Go


-- 通过参数传递  参数顺序与存储过程一致
exec dbo.p_InputStaff @name='Tom',@gender=1,@deptid=5,@jobid=23,@hiredate='2005-01-01'
select * from staff
-- 通过参数传递  参数顺序与存储过程不一致
exec dbo.p_InputStaff @name='Andy',@gender=1,@jobid=24,@deptid=5,@hiredate='2005-01-02'
select * from staff
-- 定位传递
exec dbo.p_InputStaff 'Jane',0,17,3,'2005-02-14'
select * from staff
-- 错误的定位传递
exec dbo.p_InputStaff 'Marie',0,3,'2005-01-19'
select * from staff
-- 省略参数的定位传递
exec dbo.p_InputStaff 'Marie',0,null,3
select * from staff


create proc dbo.p_Output
@emp_id int,
@sale   money output
as
select @sale=sum(UnitPrice*Quantity*(1-Discount)) from Northwind..[order details] 
       where orderid in (select orderid from Northwind..Orders where employeeid=@emp_id)
Go




declare @out money
exec dbo.p_Output @emp_id=9,@sale=@out output
select @out


create proc pp1 
@emp_id int
as
  declare @out money
  exec dbo.p_Output @emp_id=9,@sale=@out output
  select @out


-- 存储过程实训


准备工作：


create table stu_info
(
t_number char(8),
t_name char(10),
t_gender char(2),
t_birthday datetime
)


create table course
(
c_number char(6),
c_name char(20),
c_credit int,
c_hour int,
c_teacher char(10)
)




create table exam
(
t_number char(8),
c_number char(6),
t_grade decimal(5,2)
)


insert into stu_info values('20040301','张华','女','19840113')
insert into stu_info values('20040302','王立','男','19830624')
insert into stu_info values('20040303','蒋超','男','19841115')
insert into stu_info values('20040304','王浩雨','男','19851020')
insert into stu_info values('20040305','张静','女','19840418')
insert into stu_info values('20050301','李华','女','19830113')
insert into stu_info values('20050302','张立','男','19840624')
insert into stu_info values('20050303','黄超','男','19851125')
insert into stu_info values('20050304','汪雨','男','19861020')
insert into stu_info values('20050305','王静','女','19850418')


--向course表插入数据
insert into course values('100101','高等数学',2,60,'赵金')
insert into course values('100102','大学英语',3,80,'王维')
insert into course values('100103','大学物理',2,60,'李华')
insert into course values('100104','大学英语',4,80,'刘杰')
insert into course values('100105','大学英语',NULL,80,'刘杰')


--向exam表插入数据
insert into exam values('20040301','100101',79)
insert into exam values('20040301','100102',88)
insert into exam values('20040302','100101',90)
insert into exam values('20040302','100103',75)
insert into exam values('20040303','100101',79)
insert into exam values('20040303','100102',75)
insert into exam values('20040303','100103',95)
insert into exam values('20040304','100102',43)
insert into exam values('20040304','100103',68)
insert into exam values('20040305','100101',64)
insert into exam values('20040305','100102',87)
insert into exam values('20040305','100103',92)


-- 1. 创建存储过程pro_exam，显示exam表中的所有记录
create procedure pro_exam
as
  select * from exam


-- 2. 执行存储过程pro_exam
exec pro_exam


-- 3. 查栏存储过程pro_exam的定义文本
sp_helptext pro_exam


-- 4. 修改存储过程pro_exam，加密定义文本，并验证
alter procedure pro_exam
with encryption
as
  select * from exam


-- 5. 查看存储过程pro_exam的相关性信息
sp_depends pro_exam


-- 6. 查看存储过程pro_exam的目录信息




-- 7. 重命名存储过程pro_exam 为 pro_exam1,并查看
sp_rename pro_exam,peo_exam1


-- 8. 删除存储过程pro_exam1
drop procedure peo_exam1


-- 9. 创建一个带有参数的存储过程pro_exam2，该存储过程根据传入的学生编号、课程号显示该学生的考试成绩
create procedure pro_exam2
@tm char(8),
@cm char(6)
as
   select t_grade from exam where t_number=@tm and c_number=@cm


-- 10. 执行存储过程pro_exam2,查看学号为'20040301'课程号为'100101'的成绩
exec pro_exam2 @tm='20040301',@cm='100101'


-- 11. 使用pubs 数据库authors表，创建一个带有参数的存储过程pro_au，该存储过程传入作者所在的州，显示所有这个州的所有作者信息
create procedure pro_au
@state char(10)
as
  select * from pubs..authors where state=@state


-- 12. 执行存储过程pro_au,查看所有state 为 'CA'州的所有作者信息
exec pro_au @state='CA'
--准备工作:
create table student(
SID  int, --学号
SName varchar(20), --姓名
enterDate datetime, --入学日期
job varchar(10), --职位
high numeric(8,2), --身高
teacherID int, --班主任代号
classID int) --班级编号


-- 13. 创建存储过程add_student1 向已经存在的student表，
  -- 插入数据学号、姓名、入学日期、职位、身高、班主任代号、班级编号
create procedure add_student1
@sid int,@sname varchar(20),@enterdate datetime,@job varchar(10),@high numeric(8,2),@teacherid int,@classid int
as
  insert student values(@sid,@sname ,@enterdate,@job,@high,@teacherid,@classid)


-- 14. 执行存储过程add_student1插入数据 1,'zhangsan','20100901','monitor',180,101,1001
exec add_student1 1,'zhangsan','20100901','monitor',180,101,1001
select * from student


-- 15. 创建存储过程add_student2 向已经存在的student表，
  -- 插入数据学号、姓名、入学日期、职位、身高、班级编号
create procedure add_student2
@sid int,@sname varchar(20),@enterdate datetime,@job varchar(10),@high numeric(8,2),@classid int
as
  insert student(sid,sname,enterdate,job,high,classid) values(@sid,@sname ,@enterdate,@job,@high,@classid)




-- 16. 执行存储过程add_student2插入数据 2,'lisi','20100901','member',165,1001
exec add_student2 2,'lisi','20100901','member',165,1001


--游标
declare my_cursor scroll cursor                -- SCROLL 关键字允许在 FETCH 语句中随意定位行，否则只能使用 FETCH next
for select * from pubs.dbo.employee


open my_cursor


declare @n int   
select @n=convert(int,rand()*@@cursor_rows+1)  -- 获得随机数，范围从 1 到 @@cursor_rows (系统变量 @@cursor_rows = 最近一个打开的游标中填充的行数)


fetch absolute @n from my_cursor               -- 使用 FETCH ABSOLUTE n 语句来定位游标中的绝对位置


close my_cursor
deallocate my_cursor                           -- 不要忘记关闭和释放游标


select * from pubs..employee
go
declare my1 scroll cursor for select * from pubs..employee
open my1
fetch next from my1
while @@fetch_status=0
  begin
     fetch relative 2 from my1
  end
close my1
deallocate my1


create table dbo.t_emp_mgr
(
emp varchar(30) primary key,
mgr varchar(30) NULL foreign key references t_emp_mgr(emp),
NoOfReports int DEFAULT 0
)
insert t_emp_mgr(emp, mgr) values ('Harry', NULL)
insert t_emp_mgr(emp, mgr) values ('Alice', 'Harry')
insert t_emp_mgr(emp, mgr) values ('Paul', 'Alice')
insert t_emp_mgr(emp, mgr) values ('Joe', 'Alice')
insert t_emp_mgr(emp, mgr) values ('Dave', 'Joe')


select * from t_emp_mgr


要求计算出 t_emp_mgr 表的 NoOfReports 值（NoOfReports 为所在记录的员工的直接下属人数）


*/


declare my_cursor cursor
for select emp from t_emp_mgr  -- 注意这里不是 * 而是一个字段，配合下面的 INTO 子句


open my_cursor


declare @emp varchar(30)       -- 定义一个变量，用于保存当前游标中的 emp 值


fetch next from my_cursor into @emp  -- 第一次使用 FETCH next 即指向结果集的第一行（INTO 关键字将游标中的结果集保存在变量中）
while @@fetch_status=0               -- 最近一个 FETCH 语句成功，被检索的行存在
  begin
     update t_emp_mgr set NoOfReports=(select count(*) from t_emp_mgr where mgr=@emp) where current of my_cursor -- WHERE CURRENT OF cursor_name 定位当前行，用于更新或删除
     fetch next from my_cursor into @emp  -- 第 n 次使用 FETCH next，即指向下一行
  end


close my_cursor
deallocate my_cursor           -- 不要忘记关闭和释放游标


--编写存储过程，根据job_id ，采用游标方式输出数据
工号   工种   工作级别
-------------------------
GGG哈哈哈哈哈哈哈哈哈哈


alter procedure pp2
@jid int
as
begin
  declare my2 cursor for select emp_id,job_id,job_lvl from pubs..employee
  open my2
  declare @eid varchar(20),@jjid int,@jv int
  fetch next from my2 into @eid,@jjid,@jv
  print '工号   工种   工作级别'
  print '----------------------'
  while @@fetch_status=0
    begin
       print @eid+'   '+cast(@jid as varchar)+'    '+convert(varchar,@jv)
       fetch next from my2 into @eid,@jid,@jv
   end
  close my2
  deallocate my2  
end


exec pp2 10