数据库实验报告六-存储过程

1/6HUNANUNIVERSITY数据库实验报告学生姓名学生学号专业班级指导老师2017年5月24日2/6《数据库系统概论》实验报告（六）题目：PL/SQL存储过程姓名：Nomad日期：2017.5.24一、实验环境1、硬件：联想笔记本电脑2、操作系统：Windows7旗舰版3、应用软件：MySQL6.3，NavicatPreminum11.2.16二、实验内容熟悉使用存储过程来进行数据库应用程序的设计。1、统计离散数学的成绩分布情况，即按照各分数段统计人数。代码如下：DELIMITER$$CREATEPROCEDURE`sumScore`()/*统计课程离散数学各分数段人数*/BEGINdeclareless60smallintdefault0;/*60*/declareb60a70smallintdefault0;/*60~70*/declareb70a80smallintdefault0;/*70~80*/declareb80a90smallintdefault0;/*80~90*/declaremore90smallintdefault0;/*90*/declarecountcnochar(4)default'****';SELECTCnoINTOcountcnoFROMcourseWHEREcname='数学';/*确保course表中有数学这门课*/SELECTCOUNT(*)INTOless60FROMscWHEREcno=countcnoANDgrade60;SELECTCOUNT(*)INTOb60a70FROMscWHEREcno=countcnoANDgrade=60ANDgrade70;SELECTCOUNT(*)INTOb70a80FROMscWHEREcno=countcnoANDgrade=70ANDgrade80;SELECTCOUNT(*)INTOb80a90FROMscWHEREcno=countcnoANDgrade=80ANDgrade90;3/6SELECTCOUNT(*)INTOmore90FROMscWHEREcno=countcnoANDgrade=90;/*将结果存入新表sumScore中*/createtablesumScore(scorestagechar(10),numbersmallint);insertintosumScorevalues('x60',less60);insertintosumScorevalues('60=x70',b60a70);insertintosumScorevalues('70=x80',b70a80);insertintosumScorevalues('80=x90',b80a90);insertintosumScorevalues('x=90',more90);END$$callsumScore();/*调用上述存储过程*/首先创建存储过程，然后再调用存储过程。结果如下：（上述结果图截自Navicat软件）可以看到，在stuinfo中新建了一个基本表sumscore，表中内容是数学课程成绩的各分数段的人数。2、统计任意一门课的平均成绩。代码如下：DELIMITER$$CREATEPROCEDURE`scoreAvg`()BEGINdeclarecurnamechar(40)defaultnull;/*临时存放课程名*/declarecurcnochar(4)defaultnull;/*临时存放课程号*/declarecuravgfloat;/*临时存放平均成绩*/declaremycursorcursorfor/*定义游标*/selectcno,cnamefromcourse;4/6declarecontinuehandlerfornotfoundsetcuravg=-1;openmycursor;/*打开游标*/fetchmycursorintocurcno,curname;/*从结果集中取第一条记录到临时变量中*/repeat/*重复取记录*/selectavg(grade)intocuravgfromscwherecno=curcno;insertintoscoreAvgvalues(curcno,curname,curavg);/*将结果存入新表scoreAvg中*/fetchmycursorintocurcno,curname;untilcuravg=-1endrepeat;closemycursor;/*关闭游标*/END$$callscoreAvg();/*调用上述存储过程*/首先创建存储过程，然后再调用存储过程。结果如下：可以看到，在stuinfo中新建了一个基本表scoreavg，表中内容是各课程的平均成绩。3、将学生选课成绩从百分制改为等级制（即A、B、C、D、E）。代码如下：DELIMITER$$CREATEPROCEDURE`changeRank`()/*改变成绩等级*/BEGINdeclarechgradechar(1)defaultnull;/*临时存放新的成绩等级*/declarecurcnosmallint(6);/*临时存放课程号*/declarecurgradeint;/*临时存放成绩*/declareflagintdefault0;5/6declaremycursorcursorforselectcno,gradefromsc;/*定义游标*/declarecontinuehandlerfornotfoundsetflag=-1;/*在sc表中新建一列，用于存放新的成绩等级*/altertablescaddcolumn(newgradechar(1));openmycursor;/*打开游标*/fetchmycursorintocurcno,curgrade;repeat/*当游标打开时进行下列循环处理*/ifcurgrade60thensetchgrade='E';elseifcurgrade70thensetchgrade='D';elseifcurgrade80thensetchgrade='C';elseifcurgrade90thensetchgrade='B';elsesetchgrade='A';endif;updatescsetnewgrade=chgradewherecno=curcnoandgrade=curgrade;fetchmycursorintocurcno,curgrade;untilflag=-1/*直到游标指向空记录*/endrepeat;closemycursor;/*关闭游标*/select*fromsc;END$$SETSQL_SAFE_UPDATES=0;/*此步骤是必要的，否则将无法修改sc表*/callchangeRank();/*调用上述存储过程*/altertablescdropcolumnnewgrade;/*在调用该存储过程前，需要先执行此语句，因为在创建存储过程时已经在sc表中添加了这个属性*/首先创建存储过程，然后再调用存储过程，结果如下：6/6可以看到，各成绩被赋予了新的等级。三、出现的问题与解决方案1、语句格式不正确。一开始按照课本上的格式来创建存储过程，结果无论是在MySQL还是Postgresql上都报语法错误，后来经网上查阅资料，按照MySQL的标准格式对代码进行修改后，可以执行。2、在本次实验中，创建的存储过程都是不带参数的，因此创建存储过程和调用存储过程都是执行相同的代码，有些语句被重复执行会报错，还有一些语句涉及修改基本表，但权限不够也会报错，对于这些，都需要做出相应的措施。比如上述的”SETSQL_SAFE_UPDATES=0;”和”altertablescdropcolumnnewgrade;”。附录：MySQL源代码