Sql Server 编写函数

编写函数样例

样例一

编写函数Fmax，求二数大者。

CREATE FUNCTION Fmax  (@x int,@y int)
RETURNS int
AS
BEGIN
    DECLARE @Z INT
    IF @X>@Y 
        SET @Z=@X
    ELSE
        SET @Z=@Y
    RETURN(@Z)
END

SELECT DBO.fMAX(1,2)

样例二

编写函数Fc，参数为两个整数和一个运算符（+，-，*，/），计算其结果。

CREATE FUNCTION FC  (@x int,@y int,@C CHAR)
RETURNS int
AS
BEGIN
    DECLARE @Z INT
    SET  @Z = CASE @C
        WHEN '+' THEN @X+@Y
        WHEN '-' THEN @X-@Y
        WHEN '*' THEN @X*@Y
        WHEN '/' THEN @X/@Y
    END
RETURN(@Z)
END

SELECT DBO.fC(1,2,'+')

样例三

编写函数 Fsum 对自然数列 1 ~ n（参数）求和。

CREATE FUNCTION Fsum(@n INT)
RETURNS int AS
BEGIN
    DECLARE @sum INT, @i INT
    SET @sum = 0
    SET @i = 1
    WHILE @i <= @n
    BEGIN
        SET @sum = @sum + @i
        SET @i = 1 + @i
    END
    RETURN(@sum)
END

SELECT DBO.Fsum(100)

样例四

Student 表、Score 表和 Course 表中有如下一些数据。



编写函数 Fs，参数为姓名和课程名，返回该生该课程的成绩。

CREATE FUNCTION FS  (@xM NCHAR(4),@KCM VARCHAR(20))
RETURNS DECIMAL(4,1)
AS
BEGIN
    DECLARE @Z DECIMAL(4,1)
    SELECT @Z = DEGREE 
        FROM Student S 
        JOIN Score   SC ON SC.SNO=S.SNO
        JOIN Course  C  ON C.CNO=SC.CNO 
        WHERE SNAME = @XM AND CNAME = @KCM
    RETURN(@Z)
END

SELECT DBO.fS('李君帅',	'计算机导论')

样例五

编写函数 Fsc，参数为姓名，返回该生的所有成绩。

CREATE FUNCTION Fsc(@XM Nchar(4))
RETURNS TABLE
AS
RETURN (
    SELECT SNAME,CNAME,DEGREE 
    FROM Student S 
    JOIN Score   SC ON SC.SNO = S.SNO
    JOIN Course  C  ON C.CNO = SC.CNO 
    WHERE SNAME = @XM 
)

SELECT *  FROM DBO.Fsc('李君帅')

样例六

编写函数 FRANK，参数为学号，返回该生平均分班级排名。

CREATE FUNCTION FRANK(@sno char(3))
RETURNS int
AS
BEGIN
    DECLARE @rank INT
    DECLARE @class CHAR(5)
    SET @class = (SELECT class FROM Student WHERE Sno = @sno)

    SET @rank = (
        SELECT a_rank
        FROM (
            SELECT row_number() OVER(ORDER BY AVG(Degree) DESC) a_rank, S.Sno  
            FROM Score SC  
            JOIN  Student S ON S.Sno = SC.Sno 
            WHERE Class = @class
            GROUP BY s.sno
        ) T
        WHERE T.Sno = @sno
    )
    RETURN @rank
END

SELECT DBO.FRANK(101)

样例七

编写函数 FCJA，参数为姓名或姓名一部分，返回该生的所有课程的成绩（学号，姓名，课程名，成绩等级）。选修成绩等级 A:90～100 B:80~90 C: 70~80 D:60~70 E:<60。

CREATE FUNCTION FCJA(@sname nvarchar(4))
RETURNS TABLE AS
RETURN(
    SELECT SC.Sno, Sname, Cname, Degree,
    CASE
        WHEN Degree <  60                   THEN 'A'
        WHEN Degree >= 60 AND Degree < 70   THEN 'B'
        WHEN Degree >= 70 AND Degree < 80   THEN 'C'
        WHEN Degree >= 80 AND Degree < 90   THEN 'D'
        WHEN Degree >= 90 AND Degree <= 100 THEN 'E'
        ELSE 'N'
    END 等级
    FROM Score SC
    JOIN Student S ON SC.Sno = S.Sno
    JOIN Course C  ON C.Cno = SC.Cno
    WHERE Sname like '%'+ @sname +'%'
)

SELECT * FROM  FCJA('李君')