T_SQL函数习题及答案(完整版).doc

作业参考答案：-1.设数据库中有学生成绩表sc(sno char(10),cno char(5),grade tinyint),设计一个函数Count_course，返回指定学生的选修的课程门数. 注意：没有选修时应返回0；create function Count_course(sno char(10)returns intas 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 retend-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 strend-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 len(b) begin set a=replace(a,substring(b,i,1),) set i= i + 1 end return aend-select dbo.elimination(abcdefg12345,bd2)-4.写一个函数Output_RMB,完成将人民币数值转换为汉字大写,即：select dbo.Output_RMB(4567.12) 输出为：肆仟伍佰陆拾柒元壹角贰分； /*方法二：推荐使用的方法，支撑两种转换类型select dbo.Convert_money(20002.45,0)select dbo.Convert_money(20002.45,1)*/Create function Convert_money( n_LowerMoney numeric(15,2), v_TransType int)returns varchar(200)ASbegin Declare v_LowerStr VARCHAR(200) - 小写金额 Declare v_UpperPart VARCHAR(200) Declare v_UpperStr VARCHAR(200) - 大写金额 Declare i_I int selectv_LowerStr= LTRIM(RTRIM(STR(n_LowerMoney,20,2) -四舍五入为指定的精度并删除数据左右空格 select i_I = 1 select v_UpperStr = while ( i_I =50;create function IsGrduate(sno char(6)returns bitbegin declare ret bit if exists(select 1 from XS where sno=sno and totalCredit=50) set ret=1 else set ret=0 return retendselect dbo.IsGrduate(001204)select dbo.IsGrduate(001101)3:用游标完成输出具备毕业资格的学生的选课情况：门数、平均分、最高分、最低分declare PutOut cursor forselect sno from XS_KC where dbo.IsGrduate(sno)=1open PutOutdeclare sno char(6)fetch next from PutOut into snowhile FETCH_STATUS=0begin select sno 学号,count(1) 门数,avg(grade) 平均分,max(grade) 最高分,min(grade) 最低分 from XS_KC where sno=sno group by sno fetch next from PutOut into snoendclose PutOutdeallocate PutOut4:创建一个存储过程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,credit)sp_addCourse 23,English,4,32,35:创建一个存储过程sp_deleteStudent,删除指定学号的学生；create procedure sp_deleteStudent(sno char(6)as delete from XS where sno=snosp_deleteStudent 0012416:创建一个存储过程，完成学生成绩的保存，要求提供三个参数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=cnosp_storeGrade 001241,101,877:创建一个触发器完成课程的学时数只增不减；create trigger tr_limitUpdateon KCafter updateasbegin if exists(select 1 from deleted,inserted where o=o and inserted.hoursdeleted.hours) rollback transactionend8:创建一个触发器，当输入学生的成绩或成绩发生改变后，将该学生所得的总学分（XS表中的totalCredit字段）实时修改；create trigger tr_autoCallTotalCrediton xs_kcafter insert,updateasbegin 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 endend9:通过触发器，完成一个xs表回收站的功能：当删除xs表的数据后，将删除后的数据保存到recycle_xs表中。create trigger tr_recycleon xsafter deleteasbegin 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 deletedend10:设计一个函数，计算一个字符串在另一个字符串中出现的次数。create function countString(str1 varchar(100),str2 varchar(10