实验17-实现用户自定义函数参考答案

实现用户自定义函数
[说明]
/*现在要做一个会员查询网页，供人们查询某会员的基本信息。

使用者在网页输入会员的部分姓名，网页就返回匹配该查
询条件的所有会员的基本信息。

输入的部分姓名可以多种多样，比如，查找“张大维”，可以输入“张”、“大”、“维”、“张维”、
“大维”等等。

假设局部变量@PartialName存放输入的部分会员姓名，请用Transact-SQL语句写一段代码完成该查询。

*/
处理缺陷
1、该过程如果放置前台作为数据请求，代码冗长
2、只能针对于用户表的用户名检索，是否可以用于其它表字段，如何处理？
create function fnStrProc(@PartialName varchar(20))--接收输入关键字
returns varchar(50)
as
begin
--实现思路：将输入关键字张大维-> 处理为-> %张%大%维
declare @outPut varchar(20),@times int,@len int
--@output 用于存储分解完的字符串
--@times 用于存储循环的次数
--@len 用于存储检索关键字的长度
--@partialName 用于存储检索关键字
set @len =len(@partialName)
set @times = 1
set @output ='%'
while @times <= @len --判断循环次数是否小于等于字符串长度
begin
set @outPut = @outPut +
substring(@partialname,@times,1)+'%'--截取某一字符与上一次截取进行连接
set @times =@times + 1
end
return @output
end
--调用该函数，查询bbsUsers表用户名字段内容中是否含有关键字’东败’
select*from bbsUsers where uname like dbo.fnStrProc('东败') --检索用户表，查看用户名中是否包含‘东败’
--调用该函数，查询bbsTopic表发帖内容中是否含有关键字’jsp文件’
select*from bbsTopic where tContents like dbo.fnStrProc('jsp') --检索用户表，查看用户名中是否包含‘东败’
2、网页上传输的数据在转换过程中会变成非法字符，现希望在验证用户登录时对用户输入的用户名和密码中的非法字符串（例如:'',:,-,”）进行过滤。

请写函数实现create function fn_filter(@str varchar(255)) –接收过滤字符
returns varchar(255) --返回过滤后的字符串
as
begin
set @str =replace(@str,'''','')--''''代表一个单引号
set @str =replace(@str,':','')
set @str =replace(@str,'-','')
set @str =replace(@str,'“','')
return @str
end
--调用该函数，验证用户登录。

用户输入信息为：用户名:ad'min, 密码：ad-min 对其进行过滤,
select*from bbsUsers where Uname =dbo.fn_filter('ad''''min') and upassword = dbo.fn_filter('ad-min')
3、统计在发帖内容中单词”的”在一个段文中出现的频率。

提示：113 要统计1字符的个数，可以将1替换为1*，而后统计替换后的字符换长度与源字符串长度之差即为1字符的个数。

--函数名: fnCharCount
--入口参数:源字符串,循环替换关键字
--出口参数：个数
--统计某个单词在某个短文中出现的频率
create function fnCharCount(@str varchar(50),@key varchar(20)) returns int
as
begin
declare @tmp varchar(100)
set @tmp =replace(@str,@key,@key +'*')
return len(@tmp)-len(@str)
--替换
--替换串长度- 源串长度
end
--统计在发帖内容中单词”的”在一个段文中出现的频率。

select dbo.fnCharCount(tcontents,'小月月')from dbo.bbsTopic
二、表值函数
1、创建一函数fnBirUser，用于显示指定月份，日过生日的会员信息
create function fnBirUser(@month int,@day int)--制定月份，日returns table--返回表
as
return
(select*from bbsUsers where month(ubirthday)= @month and day(ubirthday)=@day)
go
--调用该函数，显示1月2日生日的会员
Select * from dbo.fnBirUser(1,2)
2、创建一函数fnTopics，显示指定版块名称的发帖信息，要求显示帖子编号，主题，
create function fnTopics(@sname varchar(50))
returns table
as
return
(
select tid 帖子编号,ttopic 主题,tContents 内容from bbsTopic where tsid =(
select sid from bbsSection where sname =@sname )
)
go
--调用该函数，显示编号为.net 的版块的发帖信息。

select*from fnTopics('.Net技术')
三、多表值函数
1、例如我有一个字符串作为函数的输入参数中。

字符串是用逗号隔开的格式（即''aaa,bbb,ccc,ddd,eee''之类的)。

我想这个函数能返回一个记录集，记录集就是
aaa
bbb
ccc
ddd
--结果返回表集
--函数体包含多语句，因此选用多语句表值函数进行封装，供其他用户调用，简化操作
create function fnGetRecords(@str varchar(2000))--接收处理字符串
returns @Rec table(Record varchar(40))--定义返回的表变量结构，因为只接收一个字段，故表变量只拥有一个属性
as
begin
declare @r varchar(2000)
declare @i int--用于存放','存放的位置
set @i=CHARINDEX(',',@str)--搜索分割字符','的索引位置，默认从第一个位置开始找
while @i>0
begin
set @r=left(@str,@i-1)--截取左串
insert @Rec values(@R)--将截取部分插入到表变量
set @str=right(@str,len(@str)-@i)--截取剩下的部分
set @i=CHARINDEX(',',@str)
end--重复处理
if len(@str)>0 --剩余最后截取部分的处理
insert @Rec values(@str)
return
end
go
--调用
select*from fnGetRecords('aaa,bbb,ccc,ddd,eee')。