-- 上課內容：第五單元 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()