编写函数样例
样例一
编写函数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('李君')
标签:Degree,int,样例,WHEN,Server,Sql,SC,编写,SELECT
From: https://www.cnblogs.com/qiang-1999/p/17236202.html