create function level(@score real) --定义一个评级函数
returns char(8)
as
begin
declare @level char(8)
set @level=
case
when @score>100 then '输入错误'
when @score<=100 and @score>=90 then '优秀'
when @score<90 and @score>=80 then '良'
when @score<80 and @score>=70 then '中'
when @score<70 and @score>=60 then '及格'
else '不及格'
end
return @level
end
create table score --定义一个表
(
s_id int not null,
s_name varchar(10) not null,
s_score real not null
)insert score values(1,'小明',45) --插入五条记录
insert score values(1,'小明',56)
insert score values(1,'小明',98)
insert score values(1,'小明',23)
insert score values(1,'小明',100)
declare iflevel cursor
for
select s_score from scoreopen iflevelwhile @@fetch_status=0
begin
declare @ifl int
fetch next from iflevel into @ifl
select dbo.level(@ifl) --我的表格是五条记录,但是它老输出六条这是为什么呢?
end
returns char(8)
as
begin
declare @level char(8)
set @level=
case
when @score>100 then '输入错误'
when @score<=100 and @score>=90 then '优秀'
when @score<90 and @score>=80 then '良'
when @score<80 and @score>=70 then '中'
when @score<70 and @score>=60 then '及格'
else '不及格'
end
return @level
end
create table score --定义一个表
(
s_id int not null,
s_name varchar(10) not null,
s_score real not null
)insert score values(1,'小明',45) --插入五条记录
insert score values(1,'小明',56)
insert score values(1,'小明',98)
insert score values(1,'小明',23)
insert score values(1,'小明',100)
declare iflevel cursor
for
select s_score from scoreopen iflevelwhile @@fetch_status=0
begin
declare @ifl int
fetch next from iflevel into @ifl
select dbo.level(@ifl) --我的表格是五条记录,但是它老输出六条这是为什么呢?
end
for
select s_score from score
declare @ifl int
set @ifl=-1
open iflevel
fetch next from iflevel into @ifl
while @@fetch_status=0
begin
select dbo.level(@ifl)
fetch next from iflevel into @ifl
end
close iflevel
dealocate iflevel
go
create function level(@score real) --定义一个评级函数
returns char(8)
as
begin
declare @level char(8)
set @level=
case
when @score>100 then '输入错误'
when @score<=100 and @score>=90 then '优秀'
when @score<90 and @score>=80 then '良'
when @score<80 and @score>=70 then '中'
when @score<70 and @score>=60 then '及格'
else '不及格'
end
return @level
end
go
if object_id('score')is not null drop table score
go
create table score --定义一个表
(
s_id int not null,
s_name varchar(10) not null,
s_score real not null
)
insert score values(1,'小明',45) --插入五条记录
insert score values(1,'小明',56)
insert score values(1,'小明',98)
insert score values(1,'小明',23)
insert score values(1,'小明',100)
declare iflevel cursor
for select s_score from score
open iflevel
declare @ifl real
fetch next from iflevel into @ifl
while @@fetch_status=0
begin
select dbo.level(@ifl) --我的表格是五条记录,但是它老输出六条这是为什么呢?
fetch next from iflevel into @ifl
end
close iflevel
DEALLOCATE iflevel
/*--------
不及格 (1 行受影响)
--------
不及格 (1 行受影响)
--------
优秀 (1 行受影响)
--------
不及格 (1 行受影响)
--------
优秀 (1 行受影响)*/