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 @i>0 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 @i>0 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 < len(@b) begin

set

@a=replace(@a,substring(@b,@i,1),'')

set @i= @i + 1

end

return @a

end

--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)

AS

begin

Declare @v_LowerStr V ARCHAR(200) -- 小写金额

Declare @v_UpperPart V ARCHAR(200)

Declare @v_UpperStr V ARCHAR(200) -- 大写金额

Declare @i_I int

select @v_LowerStr=

LTRIM(RTRIM(STR(@n_LowerMoney,20,2) )) --四舍五入为指定的精度并删除数据左右空格

select @i_I = 1

select @v_UpperStr = ''

while ( @i_I <= len(@v_LowerStr))

begin

select @v_UpperPart=case

substring(@v_LowerStr,len(@v_LowerStr) - @i_I + 1,1) WHEN '.' THEN '元'

WHEN '0' THEN '零'

WHEN '1' THEN '壹'

WHEN '2' THEN '贰'

WHEN '3' THEN '叁'

WHEN '4' THEN '肆'

WHEN '5' THEN '伍'

WHEN '6' THEN '陆'

WHEN '7' THEN '柒'

WHEN '8' THEN '捌'

WHEN '9' THEN '玖'

END

+ case @i_I

WHEN 1 THEN '分'

WHEN 2 THEN '角'

WHEN 3 THEN ''

WHEN 4 THEN ''

WHEN 5 THEN '拾'

WHEN 6 THEN '佰'

WHEN 7 THEN '仟'

WHEN 8 THEN '万'

WHEN 9 THEN '拾'

WHEN 10 THEN '佰'

WHEN 11 THEN '仟'

WHEN 12 THEN '亿'

WHEN 13 THEN '拾'

WHEN 14 THEN '佰'

WHEN 15 THEN '仟'

WHEN 16 THEN '万'

ELSE ''

END

select @v_UpperStr = @v_UpperPart + @v_UpperStr

select @i_I = @i_I + 1

end

--print '//v_UpperStr='+@v_UpperStr +'//' if ( @v_TransType=0 )

begin

select @v_UpperStr=

REPLACE(@v_UpperStr,'零拾','零')

select @v_UpperStr=

REPLACE(@v_UpperStr,'零佰','零')

select @v_UpperStr=

REPLACE(@v_UpperStr,'零仟','零')

select @v_UpperStr=

REPLACE(@v_UpperStr,'零零零','零') select @v_UpperStr=

REPLACE(@v_UpperStr,'零零','零')

select @v_UpperStr=

REPLACE(@v_UpperStr,'零角零分','整') select @v_UpperStr=

REPLACE(@v_UpperStr,'零分','整')

select @v_UpperStr=

REPLACE(@v_UpperStr,'零角','零')

select @v_UpperStr=

REPLACE(@v_UpperStr,'零亿零万零元','亿元')

select @v_UpperStr=

REPLACE(@v_UpperStr,'亿零万零元','亿元' select @v_UpperStr=

REPLACE(@v_UpperStr,'零亿零万','亿') select @v_UpperStr=

REPLACE(@v_UpperStr,'零万零元','万元') select @v_UpperStr=

REPLACE(@v_UpperStr,'万零元','万元') select @v_UpperStr=

REPLACE(@v_UpperStr,'零亿','亿')

select @v_UpperStr=

REPLACE(@v_UpperStr,'零万','万')

select @v_UpperStr=

REPLACE(@v_UpperStr,'零元','元')

select @v_UpperStr=

REPLACE(@v_UpperStr,'零零','零')

end

-- 对壹元以下的金额的处理