搜索
使用PL/SQL编写存储过程访问数据库一、实验目的熟悉使用存储过程来进行数据库应用程序的设计。二、实验内容对学生-课程数据库，编写存储过程，完成下面功能：1.统计离散数学的成绩分布情况，即按照各分数段统计人数；2.统计任意一门课的平均成绩；3.将学生选课成绩从百分制改为等级制（即A、B、C、D、E）。要求：提交源程序并表示必要的注释。保证程序能正确编译和运行，认真填写实验报告。三、实验步骤实验之前，已经建立数据库，有student，course和sc三张基本表。（一）统计离散数学的成绩分布情况，即按照各分数段统计人数。1.建立表Rank，其中第一列division显示成绩分段划分，第二列number显示的是成绩在该分数段的学生人数。CREATETABLERank(divisionCHAR(20),numberINT);2.编写存储过程。createorreplaceprocedurestatistic_mark(namechar(50))ASDECLAREless60INT:=0;//分为五个分数段，并置初始值为0b60a70INT:=0;b70a80INT:=0;b80b90INT:=0;more90INT:=0;curcnoCHAR(4);beginselectcnoINTOcurcno//根据课程名称在Course表中查询查询该课程号fromcoursewherecname=name;IFNOTFOUNOTHEN//如果不存在用户输入的课程名称，返回错误信息RAISEEXCEPTIONENDIF;SELECTcount(*)INTOless60//查询分数低于60分的学生人数FROMSCWHEREcno=curcnoANDgrade60;SELECTcount(*)INTOb60a70//查询分数在60到70之间的学生人数FROMSCWHEREcno=curcnoANDgrade=60ANDgrade70;SELECTcount(*)INTOb70a80//查询分数在70到80之间的学生人数FROMSCWHEREcno=curcnoANDgrade=70ANDgrade80;SELECTcount(*)INTOb80a90//查询分数在80到90之间的学生人数FROMSCWHEREcno=curcnoANDgrade=80ANDgrade90;SELECTcount(*)INTOmore90FROMSCWHEREcno=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之后，在“查询分析器”中选择菜单中的“单事务执行”命令，这样系统就创建好了存储过程然后使用PERFORM调用该过程，在表rank中查看执行的结果。performprocedurestatistic_mark('离散数学')；select*fromrank；(二)统计任意一门课程的平均成绩1.创建存储过程（1）创建需要的表结构。根据实验要求，要统计任意一门课程的平均成绩，因此需要建立表avggrade，其中第一列cname显示被统计的课程名称，第二列avg显示选修了该课程的学生的平均成绩。createtableavggrade（cnamechar(50),avgnumeric(10,6)）；（1）编写存储过程createorreplaceprocedurecollect_avg（）asdeclare//声明变量curnamechar(50);curnochar(4);curavggchar(10,6);cursormycursorfor//声明游标mycursor查询课程号和课程名称selectcno,cnamefromcourse;beginopenmycursor;//打开游标IFmycursor%ISOPENTHEN//条件控制，游标打开时进行以下处理LOOP//循环控制FETCHmycursorINTOcurcno,curname;//游标推进一行取结果送变量EXITWHEN(mycursor%NOTFOUND);//如果没有返回值，则退出循环SELECTAVG(grade)INTOcuravggFROMSC//求该课程的平均值送变量WHEREcno=curcno;//向avggrade//表中插入记录，显示课程名称和平均成绩INSEREINTOavggradeVALUES(curname,curavgg);ENDLOOP;//结束循环控制ENDIF;//结束条件控制CLOSEmycursor;END;2.执行存储过程首先执行编写好的存储过程collect_avg,然后在表avggrade中查看执行结果。PERFORMPROCEDUREcollect_avg();SELECT*FROMavggrade;(三)在表SC中将学生选课成绩从百分制改为等级制1.创建存储过程根据实验要求，本实验中存储过程的执行不需要在客户端返回结果，因此不需要建立相应的表结构来存放存储过程的执行结果。直接编写存储过程。createorreplaceprocedurechange_critical()ASDECLAREchgradeCHAR(1);currecordrecord;BEGINALTERTABLESCADDCOLUMN(newgradeCHAR(1));FORcurrecordINSELECT*FROMSCLOOPIFcurrecord.grade60thenchgrade='E';ELSIFcurrecord.grade70thenchgrade='D';ELSIFcurrecord.grade80thenchgrade='C;ELSIFcurrecord.grade90thenchgrade='B';ELSEchgrade='A';ENDIF;UPDATESCSETnewgrade=chgradeWHEREsno=currecord.snoANDcno=currecord.cno;ENDLOOP;ALTERTABLESCDROPCOLUMNgrade;ALTERTABLESCRENAMEnewgradeTOgrade;END;2执行存储过程PERFORMPROCEDUREchange_critical();(四)删除存储过程存储过程一旦建立，则将被保存在数据库中，便于用户随时，反复地调用和执行。如果不再需要该存储过程，可以将其删除。删除存储过程statistic_mark。DROPPROCEDUREstatistic_mark;(1)删除存储过程collect_avg,DROPPROCEDUREcollect_avg,(2)删除存储过程change_critical;DROPPROCEDUREchange_critical;四、实验总结