数据库实验

使用pl/sql编写存储过程访问数据库（一）统计离散数学的成绩分布情况，即按照各分数段统计人数。1）建立表Rank，其中第一列division显示成绩分段划分，第二列number显示的是成绩在该分数段的学生人数。CREATETABLERank(divisionCHAR(20),numberINT);2）编写存储过程。CREATEORREPLACEPROCEDUREstatistic_mark(nameCHAR(50))//存储过程带有一个字符型参数值，便于统计不同科目的分数分布情况ASDECLAREless60INT:=0;//分为五个分数段，并置初始值为0b60a70INT:=0;b70a80INT:=0;b80a90INT:=0;more90INT:=0;curcnoCHAR(4);//设字符型变量curcno存放输入的课程名称参数BEGINSELECTcnoINTOcurcno//根据课程名称在Course表中查询查询该课程号FROMCourseWHEREcname=name;IFNOTFOUNDTHEN//如果不存在用户输入的课程名称，返回错误信息RAISEEXCEPTION'输入错误，没有该课程';ENDIF;SELECTcount(*)INTOless60//查询分数低于60分的学生人数FROMsWHEREcno=curcnoANDgrade60;SELECTcount(*)INTOb60a70//查询分数在60到70之间的学生人数FROMscWHEREcno=curcnoANDgrade=60ANDgrade70;SELECTcount(*)INTOb70a80//查询分数在70到80之间的学生人数FROMscWHEREcno=curcnoANDgrade=70ANDgrade80;SELECTcount(*)INTOb80a90//查询分数在80到90之间的学生人数FROMscWHEREcno=curcnoANDgrade=80ANDgrade90;SELECTcount(*)INTOmore90//查询分数高于90的学生人数FROMscWHEREcno=curcnoANDgrade=90;INSERTINTORankVALUES('[0,60)',less60);//向Rank中插入五条记录显示成绩分布情况INSERTINTORankVALUES('[60,70)',b60a70);INSERTINTORankVALUES('[70,80)',b70a80);INSERTINTORankVALUES('[80,90)',b80a90);INSERTINTORankVALUES('[90,100]',more90);END;3）执行存储过程，首先执行编写好的存储过程statistic_mark，然后在表Rank中查看执行结果。PERFORMPROCEDUREstatistic_mark('离散数学');SELECT*FORMRank;（二）统计任意一门课的平均成绩。1）创建需要的表结构。根据实验要求，我们要统计任意一门课的平均成绩，因此我们建立表Avggrade其中第一列cname显示统计的课程名称，第二列avgg显示选修了该课程的学生平均成绩。CREATETABLEAvggrade(cnameavggCHAR(50),NUMERIC(10,6));2）编写存储过程。CREATEORREPLACEPROCEDUREcollect_avggrade()ASDECLARE//声明变量CurnameCHAR(50);CurcnoCHAR(4);curavggNUMERIC(10,6);CURSORmycursorFOR//声明游标mycursor查询课程号和名称SELECTcno,cnameFROMcourse;BEGINOPENmycursor;//打开游标IFmycursor%ISOPENTHEN//条件控制，游标打开时进行以下处理LOOP//循环控制FETCHmycursorINTOcurcno,curname;//游标推进一行，取结果送变量EXITWHEN(mycursor%NOTFOUND);//如果没有返回值则退出循环SELECTAVG(grade)INTOcuravggFROMSC//求该课程的平均值送变量WHEREcno=curcno;INSERTINTOAvggradeVALUES(curname,curavgg);//向Avggrade中插入记录显示课程名称和平均成绩ENDLOOP;//结束循环控制ENDIF;//结束条件控制CLOSEmycursor;//关闭游标END;3）执行存储过程首先执行编写好的存储过程collect_avggrade，然后在表Avggrade中查看执行结果。PERFORMPROCEDUREcollect_avggrade();SELECT*FORMAvggrade;（三）在表SC中将学生选课成绩从百分制改为等级制（即A、B、C、D、E）。1）创建存储过程根据实验要求，在本实验中存储过程的执行不需要在客户端返回结果，因此我们不需要建立相应的表结构来存放存储过程的执行结果。直接编写存储过程。CREATEORREPLACEPROCEDUREchange_critical()ASDECLAREChgradeCHAR(1);//声明标量CurrecordRECORD;BEGIN//由于之前的成绩值为整数型，而修改后的结果为字符型，因此在SC中先增加一列//newgrade，存放换算后的成绩ALTERTABLESCADDCOLUMN(newgradeCHAR(1));FORcurrecordINSELECT*FROMSCLOOP//FOR循环处理每个学生的成绩IFcurrecord.grade60THEN//如果成绩低于60分，将成绩置为Echgrade='E';ELSIFcurrecord.grade70THEN//如果成绩60&70，将成绩置为Dchgrade='D';ELSIFcurrecord.grade80THEN//如果成绩70&80，将成绩置为Cchgrade='C';ELSIFcurrecord.grade90THEN//如果成绩80&90，将成绩置为Bchgrade='B';ELSE//如果成绩高于90分，将成绩置为Achgrade='A';ENDIF;//结束条件控制UPDATESCSETnewgrade=chgrade//修改newgrade列值，填入换算后的成绩WHEREsno=currecord.snoANDcno=currecord.cno;ENDLOOP;ALTERTABLESCDROPCOLUMNgrade;//删除原有的列gradeALTERTABLESCRENAMEnewgradeTOgrade;//将newgrade列名改为gradeEND;2）执行存储过程PERFORMPROCEDUREchange_critical();（四）删除存储过程存储过程一旦建立，则将被保存在数据库中，便于用户随时、反复调用执行。如果不再需要该存储过程的话，可以将其删除。1.删除存储过程statistic_mark。DROPPROCEDUREstatistic_mark;2.删除存储过程collect_avggrade。DROPPROCEDUREcollect_avggrade;3.删除存储过程change_critical。DROPPROCEDUREchange_critical;