-- 上课内容：第五单元 Transact-SQL语言(用户自定义函数)
-- 5.4 用户自定义函数
-- 5.4.1 用户自定义函数的概念
-- 函数是由一个或多个 Transact-SQL 语句组成的子程序，可用于封装代码以便重新使用
-- 允许用户创建自己的用户定义函数
-- 用户自定义函数中可以包含0个或多个参数，函数的返回值可以是数值，也可以是一个表
-- 5.4.2 用户自定义函数的分类
-- 标量值函数
-- 标量函数返回在 RETURNS 子句中定义的类型的单个数据值
-- 内嵌表值函数
-- 表值函数返回 table
-- 没有函数主体；表是单个 SELECT 语句的结果集
-- 多语句表值函数
-- 表值函数返回 table
-- 在 BEGIN...END 块中定义的函数主体包含 TRANSACT-SQL 语句，这些语句可生成行并将行插入将返回的表中
-- 准备工作
create table gamer(g_id int,g_name varchar(10),g_gender char(2),g_jf int,g_dept int)
insert into gamer values(1,'aaa1','男',200,1)
insert into gamer values(2,'aaa2','男',800,2)
insert into gamer values(3,'aaa3','男',2000,3)
insert into gamer values(4,'aaa4','男',1200,1)
insert into gamer values(5,'aaa5','男',5600,2)
insert into gamer values(6,'aaa6','女',8700,3)
insert into gamer values(7,'aaa7','女',1000,1)
select * from gamer
sp_help gamer
-- 5.4.2.1 标量值函数
-- 例：编写用户定义函数。要求根据用户姓名查找出用户的工资。并调用函数查看结果。
-- 编写用户定义函数
CREATE FUNCTION dbo.FN_jf(@g_name varchar(10))
RETURNS int
AS
BEGIN
DECLARE @jf int
SET @jf=(SELECT g_jf FROM gamer WHERE g_name=@g_name)
RETURN @jf
END
-- 通过该函数，查找姓名为aaa2的用户的积分
SELECT dbo.FN_jf('aaa2') AS '该用户的积分'
-- 5.4.2.2 内嵌表值函数
-- 例：编写用户定义函数。要求根据用户姓名得到该用户的用户名称和积分。并调用函数查看结果
-- 编写用户定义函数
CREATE FUNCTION dbo.FN_search_gamer(@g_name varchar(10))
RETURNS TABLE
AS
RETURN (SELECT g_name,g_jf FROM gamer WHERE g_name=@g_name)
-- 执行该用户定义函数
--查找名称为aaa2的用户的信息的程序为：
SELECT * from dbo.FN_search_gamer('aaa2')
--查找名称为aaa3的用户的信息的程序为：
SELECT * from dbo.FN_search_gamer('aaa3')
-- 5.4.2.3 多语句表值函数
-- 例： 编写用户定义函数。要求得到每个部门的部门编号、部门名称以及该部门的平均工资。并调用函数查看结果。
-- 查找名称为tony的用户的信息的程序为：
CREATE FUNCTION dbo.FN_avg_jf()
RETURNS @result TABLE(e_dept varchar(10),avg_jf int)
AS
BEGIN
INSERT INTO @result SELECT g_dept,AVG(g_jf) FROM gamer GROUP BY g_dept
RETURN
END
-- 通过该函数返回各部门的平均积分
SELECT * from dbo.FN_avg_jf()
select * from gamer
-- 准备工作：
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)
)
drop table exam
drop table course
drop table stu_info
--向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的成绩。
-- 2. 编写用户定义函数,要求根据姓名得到该学生的学生姓名和各科考试成绩。并调用函数查看学号为20040301的考试成绩
-- 3. 编写用户定义函数。要求得到每个部门的部门编号、部门名称以及该部门的平均工资。并调用函数查看查找每个学生的平均分。
-- 练习参考答案：
-- 1. 编写用户定义函数,要求根据学生学号、课程号查找出考试成绩。并调用函数查看学号为20040301、课程号为100101的成绩。
CREATE alter FUNCTION dbo.FN_grade(@t_num char(8),@c_num char(6))
RETURNS decimal(5,2)
AS
BEGIN
DECLARE @grade decimal(5,2)
SET @grade=(SELECT t_grade FROM exam WHERE t_number=@t_num and c_number=@c_num)
RETURN @grade
END
-- 通过该函数，查找学号为20040301、课程号为100101的成绩
SELECT dbo.FN_grade('20040301','100102') AS '成绩'
-- 2. 编写用户定义函数,要求根据姓名得到该学生的学生姓名和各科考试成绩。并调用函数查看学号为20040301的考试成绩
-- 编写用户定义函数
CREATE alter FUNCTION dbo.FN_search_grade(@t_name varchar(10))
RETURNS TABLE
AS
RETURN (SELECT s.t_name,e.c_number,e.t_grade FROM exam as e,stu_info as s WHERE (t_name=@t_name and s.t_number=e.t_number))
-- 执行该用户定义函数,查找名称为aaa2的用户的信息的程序为：
SELECT * from dbo.FN_search_grade('张华')
-- 3. 编写用户定义函数。要求得到每个部门的部门编号、部门名称以及该部门的平均工资。并调用函数查看查找每个学生的平均分。
-- 查找学生平均分的程序为：
CREATE FUNCTION dbo.FN_avg_grade()
RETURNS @grade TABLE(t_number char(8),avg_grade decimal(5,2))
AS
BEGIN
INSERT INTO @grade SELECT t_number,AVG(t_grade) FROM exam GROUP BY t_number
RETURN
END
-- 通过该函数返回各学生的平均分
SELECT * from dbo.FN_avg_grade()
