[中学作文]存储过程实验.doc

一、统计“数据库原理”的成绩分布情况，即按照各分数段统计人数1、创建需要的表create table rank(分数段 char(20), 人数 int);2、创建存储过程CREATE PROCEDURE statistic_mark AS DECLARE less60 INT, b60a70 INT , b70a80 INT , b80a90 INT , more90 INT , curcno CHAR(4); /*设字符型变量curcno 存放输入的课程名称参数*/BEGIN SELECT curcno=cno /*根据课程名称在Course 表中查询查询该课程号*/ FROM Course WHERE cname = 数据库; SELECT less60=count(*) /*查询分数低于60 分的学生人数*/ FROM sc WHERE cno = curcno AND grade = 60 AND grade = 70 AND grade = 80 AND grade = 90; /* 向表Rank 中插入五条记录，显示成绩分布情况*/ INSERT INTO Rank VALUES(0,60),less60); INSERT INTO Rank VALUES(60,70),b60a70); INSERT INTO Rank VALUES(70,80),b70a80); INSERT INTO Rank VALUES(80,90),b80a90); INSERT INTO Rank VALUES(90,100,more90); END; 3、执行存储过程exec statistic_mark；4、查询结果select * from rank;二、统计任意一门课的平均成绩1、创建需要的表CREATE TABLE Avggrade( cname CHAR(50), avgg NUMERIC(10, 6); 2、创建存储过程CREATE PROCEDURE collect_avggrade AS DECLARE /*声明变量*/ curname CHAR(50), curcno CHAR(4), curavgg NUMERIC(10, 6); declare mycursor CURSOR FOR /*声明游标mycursor 查询课程号和名称*/ SELECT cno, cname FROM course; BEGIN OPEN mycursor; /*打开游标*/ FETCH mycursor INTO curcno, curname; while (fetch_status=0) begin SELECT curavgg=AVG(grade) FROM SC /*求该课程的平均值送变量 */ WHERE cno = curcno; INSERT INTO Avggrade VALUES(curname, curavgg); /* 向Avggrade 表中插入记录，显示课程名称和平均成绩*/ FETCH mycursor INTO curcno, curname; end CLOSE mycursor; /*关闭游标*/ END;3、执行存储过程 exec collect_avggrade;4、查询结果SELECT * FROM Avggrade;三、将学生选课成绩从百分制改为等级制（即A、B、C、D、E）1、在SC表中加入新列newgradeALTER TABLE SC ADD newgrade CHAR(1); -由于之前的成绩值为整数型，而修改后的结果为字符型，因此在SC中先增加一列newgrade，存放换算后的成绩 2、创建存储过程法(1)、用IF.ELSE.语句CREATE PROC change_gradeAS DECLARE -声明变量 chgrade CHAR(1), curgrade int; declare mycursor cursor for select grade from sc; BEGIN open mycursor; FETCH mycursor INTO curgrade; while fetch_status=0 -循环处理每个学生的成绩 begin IF curgrade 60 -如果成绩低于60 分，将成绩置为E select chgrade = E; ELSe IF curgrade 60&70，将成绩置为D select chgrade = D; ELSe IF curgrade 70&80，将成绩置为C select chgrade = C; ELSe IF curgrade 80&90，将成绩置为B select chgrade = B; ELSE -如果成绩高于90 分，将成绩置为A select chgrade = A; UPDATE SC SET newgrade = chgrade -/修改newgrade 列值，填入换算后的成绩 where grade=curgrade; FETCH mycursor INTO curgrade; end; close mycursor; END;法(2)、用when语句CREATE PROCEDURE change_grade AS DECLARE curgrade int; declare mycursor cursor for select grade from sc; BEGIN open mycursor; FETCH mycursor INTO curgrade; while fetch_status=0 begin UPDATE SC SET newgrade = -修改newgrade 列值，填入换算后的成绩 case when curgrade 60 -如果成绩低于60 分，将成绩置为E then E when curgrade 60&70，将成绩置为D then D when curgrade 70&80，将成绩置为C then C when curgrade 80&= 90 -如果成绩高于90 分，将成绩置为A then A end where grade=curgrade FETCH mycursor INTO curgrade; end close mycursor;end3、执行存储过程exec change_grade;4、修改表SCALTER TABLE SC DROP COLUMN grade; -删除原有的列grade EXEC sp_rename sc.newgrade, Grade, COLUMN; -重命名newgrade列为Grade5、查询结果select * from sc;四、删除存储过程1. 删除存储过程 statisti