在TestDB数据库中,编写一个存储过程proc_test_stat1,要求:
1)参数 字符串@target 长度 512;
2)要求返回一个结果集:
每个英文单词及其对应的出现次数,别名分别是word,count
注意:@target中仅包含英文单词,单词之间使用空格分隔。
提示:先根据空格分隔字符串得到单词,将每个单词放入临时表,然后在临时表上进行统计。
测试语句:
proc_test_stat1 ‘Whatever is worth doing is worth doing well‘;
create proc proc_test_stat1
@target nvarchar(512)
as
begin
set nocount on;
create table word_num(
word varchar(150)
)
declare @i int
set @i = 0
declare @length int;
set @length=len(@target);
declare @j int
set @j = 1;
while(@i<@length+1)
begin
set @i=@i+1
if(substring(@target,@i,1)=' ' or @i=@length+1)
begin
insert into word_num(word)
values(substring(@target,@j,@i-@j))
set @j=@i+1
end
else
continue;
end
select word 'word',count(*) 'count' from word_num
group by word
set nocount off;
end