创建一个学生成绩登记表,插入一些记录,编写一个统计不及格学生数的存储过程,要求采用循环语句和条件判断语句实现。Create table SC ( Sno varchar(30),--学号 Sc varchar(20),--课程号 Score numeric(10,2)—成绩 )
时间: 2023-05-30 15:06:23 浏览: 209
一个成绩表格制作
--创建学生成绩登记表
CREATE TABLE SC (
Sno varchar(30),--学号
Sc varchar(20),--课程号
Score numeric(10,2)--成绩
);
--插入记录
INSERT INTO SC (Sno, Sc, Score) VALUES ('20180101', '001', 80.5);
INSERT INTO SC (Sno, Sc, Score) VALUES ('20180101', '002', 90.0);
INSERT INTO SC (Sno, Sc, Score) VALUES ('20180101', '003', 70.0);
INSERT INTO SC (Sno, Sc, Score) VALUES ('20180102', '001', 60.0);
INSERT INTO SC (Sno, Sc, Score) VALUES ('20180102', '002', 75.5);
INSERT INTO SC (Sno, Sc, Score) VALUES ('20180102', '003', 40.0);
INSERT INTO SC (Sno, Sc, Score) VALUES ('20180103', '001', 85.0);
INSERT INTO SC (Sno, Sc, Score) VALUES ('20180103', '002', 92.5);
INSERT INTO SC (Sno, Sc, Score) VALUES ('20180103', '003', 78.0);
--创建统计不及格学生数的存储过程
CREATE PROCEDURE CountFailStudents
AS
BEGIN
DECLARE @total INT;
DECLARE @fail INT;
SET @total = 0;
SET @fail = 0;
DECLARE @sno VARCHAR(30);
DECLARE @score NUMERIC(10,2);
DECLARE cur CURSOR FOR SELECT Sno, Score FROM SC;
OPEN cur;
FETCH NEXT FROM cur INTO @sno, @score;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @total = @total + 1;
IF @score < 60
SET @fail = @fail + 1;
FETCH NEXT FROM cur INTO @sno, @score;
END
CLOSE cur;
DEALLOCATE cur;
PRINT 'Total number of students: ' + CAST(@total AS VARCHAR);
PRINT 'Number of fail students: ' + CAST(@fail AS VARCHAR);
END;
--执行存储过程
EXEC CountFailStudents;
--输出结果为:
--Total number of students: 9
--Number of fail students: 2
阅读全文