讲义14：自定义函数

-- 上课内容：第五单元 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 gamersp_help gamer -- 5.4.3 用户自定义函数的创建  -- 5.4.3.1 标量值函数--语法：CREATE FUNCTION [ owner_name.] function_name    ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS scalar_return_data_type[ WITH < function_option> [ [,] ...n] ] [ AS ]BEGIN    function_body    RETURN scalar_expressionEND  -- 例1：编写用户定义函数。要求根据用户编号查找出该用户的积分。并调用函数查看结果。    --  编写用户定义函数CREATE FUNCTION dbo.FN_search_jf(@g_id int)RETURNS intAS  BEGIN    DECLARE @jf int    SET @jf=(SELECT g_jf FROM gamer WHERE g_id=@g_id)    RETURN @jf  END   -- 通过该函数，查找编号为2的用户的积分SELECT dbo.FN_search_jf(2) AS '该用户的积分'  -- 例2：编写用户定义函数。要求根据用户编号查找出该用户是否存在，如果存在 则返回 1；如果不存在 则返回 0CREATE FUNCTION dbo.FN_gamerExistsOrNot1(@g_id int)RETURNS intAS  BEGIN    DECLARE @count int,@result int    SET @count=(SELECT count(*) FROM gamer WHERE g_id=@g_id)    IF @count > 0        SET @result = 1    ELSE        SET @result = 0    RETURN @result  END   -- 通过该函数，查找编号为2的用户是否存在SELECT dbo.FN_gamerExistsOrNot1(2) AS '该用户'   -- 通过该函数，查找编号为9的用户是否存在SELECT dbo.FN_gamerExistsOrNot1(9) AS '该用户'   -- 判断记录是否存在除了使用  SET @count=(SELECT count(*) FROM gamer WHERE g_id=@g_id)   -- 还可以使用             if exists(select * from student where id=@id) CREATE FUNCTION dbo.FN_gamerExistsOrNot2(@g_id int)RETURNS intAS  BEGIN    DECLARE @result int    IF EXISTS(SELECT g_id FROM gamer WHERE g_id=@g_id)        SET @result = 1    ELSE        SET @result = 0    RETURN @result  END   -- 通过该函数，查找编号为2的用户是否存在SELECT dbo.FN_gamerExistsOrNot2(2) AS '该用户'   -- 通过该函数，查找编号为9的用户是否存在SELECT dbo.FN_gamerExistsOrNot2(9) AS '该用户'  -- 5.4.3.2 内嵌表值函数--语法：CREATE FUNCTION  function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type     [ = default ] }     [ ,...n ]  ])RETURNS TABLE    [ WITH <function_option> [ ,...n ] ]    [ AS ]    RETURN [ ( ] select_stmt [ ) ][ ; ]  -- 例3：编写用户定义函数。要求根据用户编号得到该用户的用户编号、姓名和积分。并调用函数查看结果   -- 编写用户定义函数 CREATE FUNCTION dbo.FN_search_gamer(@g_id int) RETURNS TABLE AS   RETURN (SELECT g_id,g_name,g_jf FROM  gamer WHERE g_id=@g_id)    -- 执行该用户定义函数     --查找用户编号为2的用户的信息为：SELECT * from dbo.FN_search_gamer(2)    --查找用户编号为3的用户的信息为：SELECT * from dbo.FN_search_gamer(3)  -- 例4：编写用户定义函数。要求根据部门编号得到该部门的所有人员信息CREATE FUNCTION dbo.FN_search_dept(@g_dept int) RETURNS TABLE AS   RETURN (SELECT * FROM  gamer WHERE g_dept=@g_dept)    --查找部门编号为1的用户的信息：SELECT * from dbo.FN_search_dept(1)  -- 例5：编写用户定义函数。要求统计每个部门的总积分CREATE FUNCTION dbo.FN_search_jf() RETURNS TABLE AS   RETURN (SELECT g_dept,sum(g_jf)as totaljf FROM  gamer group by g_dept)    --查找部门编号为1的用户的信息：SELECT * from dbo.FN_search_jf()  -- 5.4.3.3 多语句表值函数--语法：CREATE FUNCTION  function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type     [ = default ] }     [ ,...n ]  ])RETURNS @return_variable TABLE < table_type_definition >    [ WITH <function_option> [ ,...n ] ]    [ AS ]    BEGIN                 function_body         RETURN    END[ ; ]  -- 先介绍一个语句create table aa1(aid int,aname varchar(10))insert into aa1 values(1,'aa1')insert into aa1 values(2,'aa2')insert into aa1 values(3,'aa3')insert into aa1 values(4,'aa4')create table aa2(aid int,aname varchar(10))insert into aa2 select top 2 * from aa1select * from aa1select * from aa2   -- 例6： 编写用户定义函数。要求输入用户编号、输出该用户的积分等信息。并调用函数查看结果。 CREATE FUNCTION dbo.FN_get_jf(@g_id int)RETURNS @result TABLE(g_id int,g_jf int,g_dept int)ASBEGIN    INSERT INTO @result SELECT g_id,g_jf,g_dept FROM gamer where g_id=@g_id     RETURN END     -- 通过该函数用户编号为1的用户积分等信息SELECT * from dbo.FN_get_jf(1)     -- 通过该函数用户编号为9的用户积分等信息SELECT * from dbo.FN_get_jf(9)   -- 例7： 编写用户定义函数。要求输入用户编号、判断该用户是否存在，       --  如果存在，输出该用户的编号、积分、部门编号；如果不存在，输出 '该人员不存在'CREATE FUNCTION dbo.get_jf(@g_id int)RETURNS @result TABLE(g_id int,g_jf int,g_dept int,msg varchar(20))ASBEGIN    IF dbo.FN_gamerExistsOrNot1(@g_id) = 1       INSERT INTO @result SELECT g_id,g_jf,g_dept,'' FROM gamer where g_id=@g_id       ELSE       INSERT INTO @result(msg) values('该人员不存在')    RETURN END     -- 通过该函数用户编号为1的用户积分等信息SELECT * from dbo.get_jf(1)     -- 通过该函数用户编号为9的用户积分等信息SELECT * from dbo.get_jf(9) -- 5.4.4 查看用户自定义函数信息sp_help FN_avg_jfsp_helptext FN_avg_jf -- 5.4.5 用户自定义函数的修改   -- 语法基本与create function相同，注意 with encrytion 的位置，在 as 的前面，与视图、存储过程等类似，在每次alter function时要使用才使定义被加密。ALTER FUNCTION dbo.FN_avg_jf()RETURNS @result TABLE(e_dept varchar(10),avg_jf int) WITH ENCRYPTIONAS  BEGIN    INSERT INTO @result SELECT g_dept,AVG(g_jf) FROM gamer GROUP BY g_dept    RETURN  END -- 5.4.6 用户自定义函数的删除DROP FUNCTION dbo.FN_avg_jf