t_sql函数习题及答案(完整版)

作业参考答案： -1.设数据库中有学生成绩表 sc(sno char(10), cno char(5),grade tinyint),设计一个函数 Count_course，返回指定学生的选修的课程 门数. 注意：没有选修时应返回 0； create function Count_course (sno char(10) returns int as begin return( select count(0) from sc where sno=sno) end -2.写一个 T-sql 函数 reversion，完成颠倒 一个字符串,即：select dbo.reversion(abcd) 输出的结果为 dcba； create function reversion (str varchar(4000) returns varchar(4000) as begin declare ret varchar(4000) declare i int set ret= set i=len(str) while i0 begin set ret=ret+substring(str,i,1) set i=i -1 end return ret end -select dbo.reversion(abcdefg) -对照: 下面的算法有错误，当字符串长度 超过 2000 后不能得到正确结果 create alter function reversion2 (str varchar(4000) returns varchar(4000) as begin declare i int, j int set i=len(str) set j=len(str) while i0 begin set str=str+substring(str,i,1) set i=i -1 end set str=right(str,j) return str end -select dbo.reversion2(abcdefg) -3.写一个函数 T-sql 函数 elimination(a,b)将 出现在第一个字符串中的第二个字符串中 的所有字符删除，即：select dbo.elimination(abcdefg12345,bd2) 输出为： acefg1345； create function elimination (a varchar(4000), b varchar(4000) returns varchar(4000) as begin -declare ret varchar(4000) declare i int -set ret= set i=1 while i =50; create function IsGrduate(sno char(6) returns bit begin declare ret bit if exists(select 1 from XS where sno=sno and totalCredit=50) set ret=1 else set ret=0 return ret end select dbo.IsGrduate(001204) select dbo.IsGrduate(001101) 3:用游标完成输出具备毕业资格的学生的 选课情况：门数、平均分、最高分、最低 分 declare PutOut cursor for select sno from XS_KC where dbo.IsGrduate(sno)=1 open PutOut declare sno char(6) fetch next from PutOut into sno while FETCH_STATUS=0 begin select sno 学号,count(1) 门数,avg(grade) 平均分,max(grade) 最高分,min(grade) 最低 分 from XS_KC where sno=sno group by sno fetch next from PutOut into sno end close PutOut deallocate PutOut 4:创建一个存储过程 sp_addCourse,完成课 程信息的添加； create procedure sp_addCourse (cno char(3), cname char(16), term tinyint, hours tinyint, credit tinyint ) as insert into KC values(cno,cname,term,hours,credi t) sp_addCourse 23,English,4,32,3 5:创建一个存储过程 sp_deleteStudent,删除 指定学号的学生； create procedure sp_deleteStudent (sno char(6) as delete from XS where sno=sno sp_deleteStudent 001241 6:创建一个存储过程，完成学生成绩的保 存， 要求提供三个参数sno,cno,grade ； create procedure sp_storeGrade (sno char(6),cno char(3),grade tinyint) as insert into XS_KC select sno,cno,grade,credit from KC where cno=cno sp_storeGrade 001241,101,87 7:创建一个触发器完成课程的学时数只增 不减； create trigger tr_limitUpdate on KC after update as begin if exists(select 1 from deleted,inserted where o=o and 5 inserted.hoursdeleted.hours) rollback transaction end 8:创建一个触发器，当输入学生的成绩或 成绩发生改变后， 将该学生所得的总学分（XS 表中的 totalCredit 字段）实时修改； create trigger tr_autoCallTotalCredit on xs_kc after insert,update as begin if update(grade) begin declare sno char(6),totalCredit int select sno=sno from inserted select totalCredit=sum(credit) from xs_kc where sno=sno update xs set totalCredit=totalCredit where sno=sno end end 9:通过触发器，完成一个 xs 表回收站的功 能：当删除 xs 表的数据后， 将删除后的数据保存到 recycle_xs 表中。 create trigger tr_recycle on xs after delete as begin if exists(select 1 from sysobjects where name=xs_recycle and xtype=U) insert into xs_recycle select *,getdate() delTime from deleted else select *,getdate() delTime into xs_recycle from deleted end 10:设计一个函数，计算一个字符串在另一 个字符串中出现的次数。 create function countString (str1 varchar(100),str2 varchar(100) returns int as begin declar