自定义函数和存储过程

  1 --例8.2.1创建自定义标量函数TOTAL（）用来计算任意两数之和。
create function total(@a int, @b int)
returns int
begin
    declare @c int
    select @c = @a + @b
    return @c
end
--调用
select dbo.total(10, 20)
--例8.2.2编写一个函数，可以通过输入借书时间来判断是否到期，当借阅时间大于30天，返回已经过期；否则返回还未到期。
alter function isdateout(@a datetime)
returns varchar(20)
as
begin
    declare @res varchar(20)
    if (datediff(day, @a, getdate()) > 30)
        set @res = '已过期'
    else
        set @res = '未到期'
    return @res
end
--调用
select dbo.isdateout('2019-11-1')
--例8.2.3 创建标量函数sumstudent（）统计选课学生总数。
create function cal()
returns int
begin
    return (select count(distinct cno) from course)
end
--调用
print dbo.cal()
--例8.2.4 求选课表中某门课的平均成绩。
create function calavg(@cname char(4))
returns float
as
begin
    declare @res float
    select @res = avg(grade)
    from sc, course
    where sc.cno = course.cno and course.cname = @cname
    return @res
end
--调用
select dbo.calavg('英语') as 英语的平均成绩
--例8.2.5  创建函数fun_table( )返回一组查询的结果。
create function fun_table(@sno char(9), @grade int)
returns table
as
return 
(
    select *
    from sc
    where sno = @sno and grade > @grade
)
--调用
select *
from dbo.fun_table('200515001', 80)
--例8.2.6 查询某个专业所有学生的学号、姓名、所选课程的课程号和成绩。
alter function show(@major char(10))
returns table
as
return
(
    select student.sno 学号, sname 姓名, cno 课程号, grade 成绩
    from student, sc
    where student.sno = sc.sno and sdept = @major
)

select *
from dbo.show('cs')
--例8.2.7 查询计算机专业所有学生的学号、姓名、所选的课程号和成绩。
update dbo.show('cs')
set 姓名 = '张力'
where 学号 = '2005150033'
--？？？？违反了 PRIMARY KEY 约束“PK_student”。不能在对象“dbo.student”中插入重复键。重复键值为 (200515001)。
--例8.2.8  创建函数fun_multi_table( )返回一个临时表。
create function fun_multi_table()
returns @tmp_table table(学号 char(9), 课程名 varchar(20), 成绩 int)
as
begin
    insert @tmp_table
    select sno, cname, grade
    from sc, course
    where sc.cno = course.cno
    return
end
select *
from dbo.fun_multi_table()
--例8.2.9 创建多语句表值函数，通过学号作为实参调用该函数，可显示该学生的姓名以及各门功课的成绩和学分。
create function st_score(@no char(9))
returns @score table(sno char(9), sname char(10), cname char(10), score int, credit int)
as
begin
insert @score
select s.sno, s.sname, c.cname, c.credit, sc.grade
from student s, course c, sc
where s.sno = sc.sno and c.cno = sc.cno and s.sno = @no
return
end
--调用
select *
from st_score('200515001')

--例8.3.1 带有复杂 SELECT 语句的存储过程：查询计算机系学生的考试情况，列出学生的姓名、课程名和考试成绩。
create procedure p_grade1
as
select sname, cname, grade
from student, course, sc
where student.sno = sc.sno and course.cno = sc.cno and sdept = 'cs'

exec p_grade1
--例8.3.2 带有输入参数的存储过程：查询某个指定系学生的考试情况，列出学生的姓名、所在系、课程名和考试成绩。
create procedure p_grade2 @dept varchar(20)
as
select sname, sdept, cname, grade
from student s, sc, course c
where s.sno = sc.sno and c.cno = sc.cno and sdept = @dept

exec p_grade2 'is'
--例8.3.3 含多个输入参数并有默认值的存储过程：查询某个学生某门课程的考试成绩，若没有指定课程，则默认课程为“数据库”
create procedure p_grade3 @sname varchar(10), @cname varchar(10) = '数据库'
as
select sname, cname, grade
from student s, course c, sc
where s.sno = sc.sno and c.cno = sc.cno and s.sname = @sname and c.cname = @cname

exec p_grade3 '赵菁菁', '数据结构'
--例8.3.4含多个输入参数并均指定默认值的存储过程。查询指定系、指定性别的学生中年龄大于等于指定年龄的学生详细信息。系的默认值为“计算机系”，性别默认值为“男”，年龄默认值为20。
create procedure p_student @dept char(20) = 'cs', @sex char(2) = '男', @age int = 20
as
select *
from student
where sdept = @dept and ssex = @sex and sage >= @age

exec p_student 'is', 10
exec p_student @dept = 'is', @sex = '女'
--例8.3.5含输出参数的存储过程。计算两个数的乘积，将计算结果用输出参数返回给调用者。
create procedure p_sum @var1 int, @var2 int, @var3 int output
as
set @var3 = @var1 * @var2

declare @res int
exec p_sum 5, 9, @res output
print @res
--例8.3.6 含输入参数和一个输出参数的存储过程。统计指定课程（课程名）的平均成绩，并将统计的结果用输出参数返回。
alter procedure p_avggrade @cname char(20), @avg float output
as
select @avg = avg(grade)
from sc join course c on c.cno = sc.cno
where c.cname = @cname

declare @avg_grade float
exec p_avggrade '数据库', @avg_grade output
print @avg_grade
--例8.3.7 含输入参数和多个输出参数的存储过程。统计指定课程的平均成绩和选课人数，将统计的结果用输出参数返回。
create procedure p_avgcount @cname varchar(10), @avg float output, @number int output
as
select @avg = avg(grade), @number = count(*)
from sc, course c
where sc.cno = c.cno and cname = @cname

declare @avg float, @number int
exec p_avgcount '数据结构', @avg output, @number output
select @avg as 平均成绩, @number as 选课人数
--例8.3.8  将指定课程的学分增加2分。
create procedure p_updatecredit1 @cname char(10)
as
update course
set credit = credit + 2
where cname = @cname

exec p_updatecredit1 '操作系统'
--例8.3.9 将指定课程的学分改为指定值，要求指定值必须在1~10之间，否则不予修改。
create procedure p_updatecredit2 @cno char(6), @credit int
as
if @credit between 1 and 10
    update course
    set credit = @credit
    where cno = @cno

exec p_updatecredit2 1, 9
--例8.3.10 修改p_grade2存储过程，使其能查询指定系中考试成绩大于等于80分的学生姓名、所在系、课程名和考试成绩。
alter procedure p_grade2 @dept varchar(20)
as
select sname, sdept, cname, grade
from student s, course c, sc
where s.sno = sc.sno and c.cno = sc.cno and sdept = @dept and grade >= 80

exec p_grade2 'is'
--删除存储过程
drop procedure p_grade2