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

  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

  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

  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 VARCHAR(200) -- 小写金额

  Declare @v_UpperPart VARCHAR(200)

  Declare @v_UpperStr VARCHAR(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  '玖'

+ case @i_I

  WHEN  1  THEN  '分'

  WHEN  2  THEN  '角'

  WHEN  3  THEN  ''

  WHEN  5  THEN  '拾'

  WHEN  6  THEN  '佰'

 仟'

 万'

  WHEN  9  THEN  '拾'

 WHEN  10  THEN  '佰'

  WHEN  11  THEN  '仟'

  WHEN  12  THEN  '亿'

 拾'

  WHEN  14  THEN  '佰'

  WHEN  15  THEN  '仟'

 WHEN  16  THEN  '万'

  end

  --print  '//v_UpperStr='+@v_UpperStr +'//'

  if ( @v_TransType=0 )

  begin

  @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

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

  if ( substring(@v_UpperStr,1,1)='元' )  begin

  @v_UpperStr= substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))

  end

  if (substring(@v_UpperStr,1,1)= '零') begin

  @v_UpperStr= substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))

  end

  if (substring(@v_UpperStr,1,1)='角') begin

  @v_UpperStr= substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))

  end  

  if ( substring(@v_UpperStr,1,1)='分')  begin

  @v_UpperStr= substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))

  end

  if (substring(@v_UpperStr,1,1)='整')begin

 零元整'

  end

  return @v_UpperStr

end

go

/*5.写一个函数Custom_AVG模拟多名裁判打分时求平均值的函数，去掉一个最高分、一个最低分、然后取平均。设数据库表结构为：

  create table player(playerID int, playerName varchar(20))

 create table score(

 id int identity(1,1) primary key,

 playerID int, umpireID int,

  score int check (score between 0 and 100

  然后创建一个视图，视图由两列构成：选手编号playerID和最终所得分

player(playerID  int,playerName varchar(20))

*/

create  function Custom_AVG

(@playerID int)

returns decimal(5,2)

as begin return (select(sum(score)-max(score)-min(score))*1.0/(count(0)-2)

  score where playerID=@playerID)

  /*  或者

  return (select cast((sum(score)-max(score)-min(score))as decimal) /(count(0)-2)

from score where playerID=@playerID)*/

end

go

create view v_grade as

  select playerID, Custom_AVG (playerID) from player

go

/*测试

 create table score(

id int identity(1,1) primary key,

playerID int,

umpireID int,

score int check (score between 0 and 100))

insert into score values(1,1,)

insert into score values(1,2,86)

insert into score values(1,3,85)

insert into score values(1,4,80)

insert into score values(1,5,83)

insert into score values(1,7,85)

select dbo.Custom_AVG(1)

*/

1:创建一个自定义函数，完成将指定时间显示为：xxxx年xx月xx日 xx时xx分xx秒

create  function FormatDate(@date datetime)

returns varchar(50)

begin

 年'

 月'

 日'

 时'

 分'

 秒'

end

select dbo.FormatDate(getdate())

针对给定的数据库XSCJ，完成如下设计：

2:创建一个自定义函数，实现判别学生是否具备毕业资格，返回值为bit类型，毕业资格条件是:总学分>=50;

create function IsGrduate(@sno char(6))

returns bit

begin

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) 最低分

  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,@credit)

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

sp_storeGrade '001241','101',87

7:创建一个触发器完成课程的学时数只增不减；

create trigger tr_limitUpdate

on KC

after update

as

begin

end

8:创建一个触发器，当输入学生的成绩或成绩发生改变后，

将该学生所得的总学分（XS表中的totalCredit字段）实时修改；

create trigger tr_autoCallTotalCredit

on xs_kc

after insert,update

as

begin

  if update(grade)

end

9:通过触发器，完成一个xs表回收站的功能：当删除xs表的数据后，

将删除后的数据保存到recycle_xs表中。

on xs

after delete

as

begin

  if exists(select 1 from sysobjects where name='xs_recycle'

  else 

end

10:设计一个函数，计算一个字符串在另一个字符串中出现的次数。

create  function countString

(@str1 varchar(100),@str2 varchar(100))

returns int

as

begin

  declare @n int,@I int

  set @n=0

  set @I=1

while @I<=(len(@str1)-len(@str2)+1)

  begin

  end

  return @n

end

select dbo.countString('afhhdfah','afh')