数据库高级数据库学习--上机练习7（Transact-SQL 函数定义和调用）

数据库高级数据库学习--上机练习7（Transact-SQL 函数定义和调用）

上机练习7

在Transact SQL中，有一类特殊的自定义函数，其返回值为一张表，该类自定义函数被称作内嵌（联）表值函数，其基本语句格式如下：
CREATE FUNCTION函数名称[( {@参数名称 [AS] 标量数据类型}[,...n])]
RETURNS TABLE 
[AS]
    RETURN [{SELECT 语句}] 

试采用该类自定义函数完成下列操作：
1、    创建一个不带输入参数的表值函数返回所有学生信息。
CREATE FUNCTION studentInformation()
RETURNS TABLE
AS
RETURN
SELECT * FROM Student
2、    创建一个自定义函数，实现输入学生姓名，查询对应的学号。如果存在多个学号，则输出全部结果。
CREATE FUNCTION findSno(@Sna AS char(10))
RETURNS TABLE
AS
RETURN
    SELECT Sno FROM Student WHERE Sname=@Sna

DECLARE @Sname char(10)
SET @Sname = '张得民'
SELECT * FROM dbo.findSno(@Sname);
3、    创建一个自定义函数实现输入课程名查询选修了该课程的学号及其成绩。
CREATE FUNCTION findSnoGrade(@Cna AS char(10))
RETURNS TABLE
AS
RETURN
    SELECT Sno,CScore FROM Score,Course 
    WHERE Cname=@Cna AND Course.Cno = Score.Cno

SELECT * FROM dbo.findSnoGrade('计算机基础');
4、    创建一个自定义函数返回毕业班中目前无法毕业的学生信息，假设毕业班的学生学号以“14”开头，毕业条件为已修总学分10以上。
CREATE FUNCTION credictIsOK(@Sno AS char(10))
RETURNS real
BEGIN
    DECLARE @sumCredict int
    SELECT @sumCredict = (SELECT SUM(Ccredit) FROM Score,Course 
                            WHERE Course.Cno = Score.Cno AND Score.Sno = @Sno
                            AND Cscore IS NOT NULL)
    RETURN @sumCredict
END

CREATE FUNCTION findCannotGradate()
RETURNS TABLE
AS
RETURN
    SELECT Sno FROM Student 
    WHERE  Sno LIKE '14%' 
    AND (dbo.credictIsOK(Sno)<=10 
        OR dbo.credictIsOK(Sno) IS NULL);
        
SELECT * FROM dbo.findCannotGradate();