代码
1
use
master
2 if exists ( select * from sysdatabases where name = ' TT ' )
3 drop database TT -- -判读数据库是否存在
4 go
5 create database TT
6 go
7 use TT
8
9 if exists ( select * from sysobjects where name = ' stuinfor ' )
10 drop table stuinfor -- --判读表是否存在
11
12 create table stuinfor
13 (
14 stuid int primary key identity ( 1 , 1 ),
15 stuname varchar ( 20 )
16 )
17
18 if exists ( select * from sysobjects where name = ' student ' )
19 drop table student -- --判读表是否存在
20 create table student
21 (
22 id int primary key identity ( 1 , 1 ),
23 stuid int ,
24 subject int ,
25 chengji decimal ( 3 , 0 )
26 )
27 go
28
29
30
31
32 if exists ( select * from sysobjects where name = ' proresult ' )
33 drop proc proresult -- --判读存储过程是否存在
34 go
35 create proc proresult
36 as
37 begin
38 declare @sql nvarchar ( 200 )
39 if ( object_id ( ' temp_table ' ) is not null ) -- -判读是否已经存在临时表名temp_table
40 begin
41 drop table temp_table
42 end
43 create table temp_table(stuid int primary key , stuname nvarchar ( 20 ),chinese decimal ( 3 , 0 ),math decimal ( 3 , 0 ),english decimal ( 3 , 0 ),avgage decimal ( 3 , 0 ), appraise varchar ( 20 )) -- -创建临时表
44 declare cursor_table cursor for
45 select stuid,stuname from stuinfor -- 定义游标
46 open cursor_table -- -打开游标
47 declare @stuid int , @stuname nvarchar ( 20 )
48 fetch next from cursor_table into @stuid , @stuname -- -游标移动
49 while ( @@fetch_status = 0 ) -- -判读是否已经移动到最尾端
50 begin
51 declare @bid int , @chinese decimal ( 3 , 0 ), @math decimal ( 3 , 0 ), @english decimal ( 3 , 0 ), @description nvarchar ( 20 ), @avgage decimal ( 3 , 0 )
52 set @bid = 0
53 while ( @bid < 3 )
54 begin
55 set @bid = @bid + 1
56 print @bid
57 if ( @bid = 1 )
58 begin
59 select @chinese = chengji from student where stuid = @stuid and subject = @bid
60 end
61 else if ( @bid = 2 )
62 begin
63 select @math = chengji from student where stuid = @stuid and subject = @bid
64 end
65
66 else if ( @bid = 3 )
67 begin
68 select @english = chengji from student where stuid = @stuid and subject = @bid
69 end
70 end
71 declare @total decimal ( 3 , 0 )
72 set @total = @chinese + @math + @english
73 set @avgage = @total / 3
74 -- print @total
75 if @total / 3 = 0
76 begin
77 set @description = ' 未考试 '
78 end
79 else if @total / 3 < 60
80 begin
81 set @description = ' 不及格 '
82 end
83 else if ( @total / 3 >= 60 and @total / 3 < 80 )
84 begin
85 set @description = ' 良好 '
86 end
87 else if ( @total / 3 >= 80 and @total / 3 < 90 )
88 begin
89 set @description = ' 优秀 '
90 end
91 else if @total / 3 >= 90
92 begin
93 set @description = ' 很好 '
94 end
95 print @description
96 insert into temp_table(stuid,stuname,chinese,math,english,avgage,appraise) values ( @stuid , @stuname , @chinese , @math , @english , @avgage , @description ) -- -将记录插入到临时表中
97 fetch next from cursor_table into @stuid , @stuname -- 向下移动
98 end
99 close cursor_table -- 关闭游标
100 deallocate cursor_table -- -销毁游标
101
102
103 set @sql = N ' select stuid as 编号, stuname as 姓名, chinese as 语文, math as 数学, english as 英语 ,avgage as 平均成绩 , appraise as 评价 from temp_table '
104 exec sp_executesql @sql -- -执行查询临时表
105 drop table temp_table -- -删除临时表
106 end
107 go
108
109 exec proresult -- -执行存储过程
110
111
2 if exists ( select * from sysdatabases where name = ' TT ' )
3 drop database TT -- -判读数据库是否存在
4 go
5 create database TT
6 go
7 use TT
8
9 if exists ( select * from sysobjects where name = ' stuinfor ' )
10 drop table stuinfor -- --判读表是否存在
11
12 create table stuinfor
13 (
14 stuid int primary key identity ( 1 , 1 ),
15 stuname varchar ( 20 )
16 )
17
18 if exists ( select * from sysobjects where name = ' student ' )
19 drop table student -- --判读表是否存在
20 create table student
21 (
22 id int primary key identity ( 1 , 1 ),
23 stuid int ,
24 subject int ,
25 chengji decimal ( 3 , 0 )
26 )
27 go
28
29
30
31
32 if exists ( select * from sysobjects where name = ' proresult ' )
33 drop proc proresult -- --判读存储过程是否存在
34 go
35 create proc proresult
36 as
37 begin
38 declare @sql nvarchar ( 200 )
39 if ( object_id ( ' temp_table ' ) is not null ) -- -判读是否已经存在临时表名temp_table
40 begin
41 drop table temp_table
42 end
43 create table temp_table(stuid int primary key , stuname nvarchar ( 20 ),chinese decimal ( 3 , 0 ),math decimal ( 3 , 0 ),english decimal ( 3 , 0 ),avgage decimal ( 3 , 0 ), appraise varchar ( 20 )) -- -创建临时表
44 declare cursor_table cursor for
45 select stuid,stuname from stuinfor -- 定义游标
46 open cursor_table -- -打开游标
47 declare @stuid int , @stuname nvarchar ( 20 )
48 fetch next from cursor_table into @stuid , @stuname -- -游标移动
49 while ( @@fetch_status = 0 ) -- -判读是否已经移动到最尾端
50 begin
51 declare @bid int , @chinese decimal ( 3 , 0 ), @math decimal ( 3 , 0 ), @english decimal ( 3 , 0 ), @description nvarchar ( 20 ), @avgage decimal ( 3 , 0 )
52 set @bid = 0
53 while ( @bid < 3 )
54 begin
55 set @bid = @bid + 1
56 print @bid
57 if ( @bid = 1 )
58 begin
59 select @chinese = chengji from student where stuid = @stuid and subject = @bid
60 end
61 else if ( @bid = 2 )
62 begin
63 select @math = chengji from student where stuid = @stuid and subject = @bid
64 end
65
66 else if ( @bid = 3 )
67 begin
68 select @english = chengji from student where stuid = @stuid and subject = @bid
69 end
70 end
71 declare @total decimal ( 3 , 0 )
72 set @total = @chinese + @math + @english
73 set @avgage = @total / 3
74 -- print @total
75 if @total / 3 = 0
76 begin
77 set @description = ' 未考试 '
78 end
79 else if @total / 3 < 60
80 begin
81 set @description = ' 不及格 '
82 end
83 else if ( @total / 3 >= 60 and @total / 3 < 80 )
84 begin
85 set @description = ' 良好 '
86 end
87 else if ( @total / 3 >= 80 and @total / 3 < 90 )
88 begin
89 set @description = ' 优秀 '
90 end
91 else if @total / 3 >= 90
92 begin
93 set @description = ' 很好 '
94 end
95 print @description
96 insert into temp_table(stuid,stuname,chinese,math,english,avgage,appraise) values ( @stuid , @stuname , @chinese , @math , @english , @avgage , @description ) -- -将记录插入到临时表中
97 fetch next from cursor_table into @stuid , @stuname -- 向下移动
98 end
99 close cursor_table -- 关闭游标
100 deallocate cursor_table -- -销毁游标
101
102
103 set @sql = N ' select stuid as 编号, stuname as 姓名, chinese as 语文, math as 数学, english as 英语 ,avgage as 平均成绩 , appraise as 评价 from temp_table '
104 exec sp_executesql @sql -- -执行查询临时表
105 drop table temp_table -- -删除临时表
106 end
107 go
108
109 exec proresult -- -执行存储过程
110
111