所有的存储过程与函数.docx
《所有的存储过程与函数.docx》由会员分享，可在线阅读，更多相关《所有的存储过程与函数.docx（15页珍藏版）》请在冰点文库上搜索。
所有的存储过程与函数
1.学员信息录入存储过程userinfo_insert
创建学员信息表中的ID序列userSequence
createorreplaceprocedureuserinfo_insert（username1userInfo.username%type,password1userInfo.password%type,userClass1userInfo.userClass%type,actor1userInfo.actor%type）
is
begin
--判断角色是否为STUDENT或者TEACHER
ifactor1='student'oractor1='teacher'then
insertintouserinfo（id,username,password,userClass,actor,state）
values（userSequence.nextval,username1,password1,userClass1,actor1,0）;
endif;
exception
whenothersthen
dbms_output.put_line（'Exception'）;
end;
2.学员信息修改存储过程userinfo_update
createorreplaceprocedureuserinfo_update（id1userInfo.id%type,username1userInfo.username%type,password1userInfo.password%type,userClass1userInfo.userClass%type,actor1userInfo.actor%type,state1userInfo.state%type）
is
begin
--信息修改后，但是角色必须为STUDENT或者TEACHER
ifactor1='student'oractor1='teacher'then
updateuserinfosetusername=username1,password=password1,userClass=userClass1,actor=actor1,state=statewhereid=id1;
endif;
exception
whenothersthen
dbms_output.put_line（'Exception'）;
end;
3.试卷录入存储过程
为表TESTPAPER新建testpaperSequence序列
createorreplaceproceduretestpaper_insert（testpapername1TESTPAPER.testpaperName%type,number1outint）
is
--声明变量用户接收查询的结果
testpaperidint;
begin
--将查询出来的结果赋值给变量
selectcount（testpaperid）intotestpaperidfromtestpaperwheretestpapername=testpapername1;
--判断需要录入的题目是否已经存在
iftestpaperid=0then
insertintotestpapervalues（testpaperSequence.nextval,testpapername1,0）;
number1:
=1;
else
dbms_output.put_line（'该题目已经存在，请重新输入题目！
'）;
number1:
=0;
endif;
exception
whenothersthen
dbms_output.put_line（'Exception'）;
end;
4.题库录入存储过程question_insert
为表question新建questionSequence序列
createorreplaceprocedurequestion_insert（question1
QUESTION.question%type,optionA1QUESTION.optionA%type,optionB1QUESTION.optionB%type,optionC1QUESTION.optionC%type,optionD1QUESTION.optionD%type,answer1QUESTION.answer%type,testpaperId1QUESTION.testpaperId%type,number1outint）
is
--声明变量用于接收查询到的ID信息
testpaperid2testpaper.TESTPAPERID%type;
testpapername2testpaper.testpaperName%type;
begin
--将查询到的ID信息赋值给变量
selectcount（TESTPAPERID）intotestpaperid2fromtestpaperwhereTESTPAPERID=testpaperId1;
--如果查询到题目的信息，就可以根据题目信息进行添加题，并将试卷表
--中的相应题目类型的总数加1
iftestpaperid2>0then
selecttestpapernameintotestpapername2fromtestpaperwhereTESTPAPERID=testpaperId1;
--将相应的题添加到题库表中
insertintoquestion（id,question,optionA,optionB,optionC,optionD,answer,testpaperId）values（questionSequence.nextval,question1,optionA1,optionB1,optionC1,optionD1,answer1,testpaperId1）;
--将试卷表中的相应题目的总数加1
updateTESTPAPERsetquestionCount=questionCount+1wheretestpaperName=testpapername2;
number1:
=1;
else
dbms_output.put_line（'试卷表中不存在该试卷！
'）;
number1:
=0;
endif;
exception
whenothersthen
dbms_output.put_line（'Exception'）;
end;
5.题库（答案）修改，相关表QUESTION
--在question表中插入题目的信息的存储过程
createorreplaceprocedurequestion_update（questionidQUESTION.id%type,question1QUESTION.question%type,
optionA1QUESTION.optionA%type,optionB1QUESTION.optionB%type,
optionC1QUESTION.optionC%type,optionD1QUESTION.optionD%type,
answer1QUESTION.answer%type,testpaperId1QUESTION.testpaperId%type,unmber1outint）
is
--声明变量用于接收查询未更改的试卷表ID信息
testpaperid2testpaper.testpaperid%type;
--声明变量用于接收查询未梗罚ide试卷表题名称信息
testpapername2testpaper.testpaperName%type;
--声明变量用于接收修改后试卷表中的ID
testpaperid3testpaper.testpaperid%type;
--声明变量用于接收修改后试卷表中的题名
testpapername3testpaper.testpaperName%type;
begin
--将更改前的试卷表ID赋值给变量
selecttestpaperIdintotestpaperid2fromQUESTIONwhereid=questionid;
--根据更改前的试卷表ID，得到更改前的题目名称
selecttestpaperNameintotestpapername2fromTESTPAPERwheretestpaperid=testpaperid2;
--判断是否有修改试卷表的ID
iftestpaperid2=testpaperId1then
--没有修改试卷表的ID，那么只需要修改题库表中的信息
updateQUESTIONsetquestion=question1,optionA=optionA1,optionB=optionB1
optionC=optionC1,optionD=optionD1,answer=answer1,
testpaperId=testpaperId1whereid=questionid;
unmber1:
=1;
else
--修改试卷表的ID，那么需要进行判断修改的ID在试卷表中是否存在
selectcount（testpaperid）intotestpaperid3
fromtestpaperwheretestpaperid=testpaperId1;
--如果查询到题目的信息，就可以根据题目信息进行修改题，并将试卷表
--中的相应题目类型的总数减1
iftestpaperid3>0then
selecttestpapernameintotestpapername3fromtestpaperwheretestpaperid=testpaperId1;
--将相应的题修改到题库表中
updateQUESTIONsetquestion=question1,optionA=optionA1,
optionB=optionB1,optionC=optionC1,optionD=optionD1,answer=answer1,
testpaperId=testpaperId1whereid=questionid;
--将试卷表中修改前的相应题目的总数减1
updateTESTPAPERsetquestionCount=questionCount-1
wheretestpaperName=testpapername2;
--将试卷表中修改后的相应题目的总数加1
updateTESTPAPERsetquestionCount=questionCount+1
wheretestpaperName=testpapername3;
unmber1:
=1;
else
dbms_output.put_line（'试卷表中不存在该试卷！
'）;
unmber1:
=0;
endif;
endif;
exception
whenothersthen
dbms_output.put_line（'Exception'）;
end;
6.学员考试答案录入存储过程stuanswer_insert
为stuanswer表新建stuAnswerSequence序列
--为stuanswer表中插入信息的存储过程
createorreplaceprocedurestuanswer_insert（stuId1stuAnswer.stuId%type,
testpaperId1stuAnswer.testpaperId%type,stuAns1stuAnswer.stuAns%type,number1outint）
is
--声明变量用于接收stuId，学号,对应userInfo表中的id
stuid2int;
--声明变量用于接收testpaperId试卷id，对应TESTPAPER表中的id
testpaperid2int;
begin
--将userInfo表中查询出来的值赋值给变量
selectcount（id）intostuid2fromuserInfowhereid=stuId1;
--将TESTPAPER表中查询出来的值赋值给变量
selectcount（testpaperid）intotestpaperid2fromTESTPAPERwheretestpaperid=testpaperId1;
--判断输入的参数stuId1，testpaperId1是否在userInfo表，TESTPAPER表中存在
ifstuid2>0andtestpaperid2>0then
--将输入的参数信息添加到stuanswer表中
insertintostuAnswervalues（stuAnswerSequence.nextval,stuId1,testpaperId1,stuAns1）;
number1:
=1;
else
dbms_output.put_line（'输入错误，请重新输入'）;
number1:
=0;
endif;
exception
whenothersthen
dbms_output.put_line（'Exception'）;
end;
7.生成学员分数存储过程score_report
createorreplaceprocedurescore_report（tpIdtestpaper.testpaperid%type）
as
--存放该试卷的正确答案的游标
cursoranswer_curisselectanswerfromquestionwheretestpaperId=tpId;
--存放某试卷的学员答案的游标
cursorstuAnswer_curisselect*fromstuAnswerwheretestpaperId=tpId;
--正确答案字符串
Qanswervarchar2（500）;
--循环变量
avarchar（500）;
--长度（试题数）
qsizeint;
numint:
=0;--答对题计数器
scoreint;--分数
stagevarchar2（20）;--等级
sameCountint;--成绩表中存在相同数据的标志变量
stuAnswer_rowstuAnswer%rowtype;--学员答案表的行
begin
openanswer_cur;--打开正确答案的游标,循环,把所有该试卷的题的正确答案用字符串起来,存入正确答案字符串变量中
loop
fetchanswer_curintoa;
exitwhenanswer_cur%notfound;
selectQanswer||aintoQanswerfromdual;
endloop;
closeanswer_cur;--关闭正确答案游标
qsize:
=length（Qanswer）;--得到题目数
openstuAnswer_cur;--打开学员答案游标
loop
fetchstuAnswer_curintostuAnswer_row;
exitwhenstuAnswer_cur%notfound;
selectstuAnswer_row.stuAnsintoafromdual;
num:
=0;--计数器清0
foriin1..qsizeloop
ifsubstr（a,i,1）=substr（Qanswer,i,1）then
num:
=num+1;--累加答对题数
endif;
endloop;
score:
=round（num/qsize*100）;
dbms_output.put_line（score）;
ifscore<=100andscore>=80then
stage:
='优秀';
elsifscore<80andscore>=70then
stage:
='良好';
elsifscore<70andscore>=60then
stage:
='及格';
else
stage:
='不及格';
endif;
selectcount（*）intosameCountfromstuScorewherestuId=stuAnswer_row.stuIdandtestpaperId=tpId;--在成绩表中查询该学员是否考过该试卷
ifsameCount=0then
insertintostuScorevalues（stuScoreSequence.nextval,stuAnswer_row.stuId,tpId,score,stage）;--没有,则往成绩表中插入数据
endif;
endloop;
closestuAnswer_cur;
exception
whenothersthen
dbms_output.put_line（'Exception'）;
endscore_report;
8.查看学员分数存储过程searchscore
createorreplaceproceduresearchScore（
classname2varchar2,--班级
stuId2int,--学号
testpaperid2int,--试卷号
score_curoutsys_refcursor--返回成绩游标（学号,姓名,分数,级别,试卷名）
）
is
score_csys_refcursor;--变量
begin
--如果班级参数不为空
ifclassname2!
=''then
openscore_cfor'selects.stuid,u.userName,s.stuScore,s.standard,testpaperName
fromstuScores,userInfou,TESTPAPERtwheres.stuid=u.idandt.testpaperid=s.testpaperidandu.userClass=:
1ands.testpaperId=:
2'
usingclassname2,testpaperid2;
--如果学号不为0
elsifstuId2!
=0then
openscore_cfor'selects.stuid,u.userName,s.stuScore,s.standard,testpaperName
fromstuScores,userInfou,TESTPAPERtwheres.stuid=u.idandt.testpaperid=s.testpaperidandu.id=:
1ands.testpaperId=:
2'
usingstuId2,testpaperid2;
else
openscore_cfor'selects.stuid,u.userName,s.stuScore,s.standard,testpaperName
fromstuScores,userInfou,TESTPAPERtwheres.stuid=u.idandt.testpaperid=s.testpaperidands.testpaperId=:
1'
usingtestpaperid2;
endif;
score_cur:
=score_c;
end;
9.登录存储过程user_login
createorreplaceprocedureuser_login（userName1userInfo.userName%type,password1userInfo.password%type,userClass1userInfo.userClass%type,actor1userInfo.actor%type,loginInfooutint）
is
--声明一个变量用于接收用户信息
useridint;
userCountint;
--声明一个变量用于接收用户的状态
userstateuserinfo.state%type;
begin
--将获取到的值赋值给变量
selectcount（id）intouserCountfromuserinfowhereuserName=userName1andpassword=password1anduserClass=userClass1andactor=actor1;
--判断输入的用户信息是否存在
ifuserCount>0then
--把用户id查出来
selectidintouseridfromuserinfowhereuserName=userName1andpassword=password1anduserClass=userClass1andactor=actor1;
--查询用户的状态
selectstateintouserstatefromuserinfowhereid=userid;
--判断状态是否为已登录
ifuserstate=0then
--用户处于未登录状态，让用户登录并修改状态为登录状态
updateuserinfosetstate=1whereid=userid;
loginInfo:
=userid;
else
--用户处于登录状态
loginInfo:
=0;
endif;
else
--该用户不存在
loginInfo:
=-1;
endif;
exception
whenothersthen
dbms_output.put_line（'Exception'）;
end;
10创建触发器完成删除某个班级的一个学员，在删除之前备份该学员信息到stuInfo_bak表
CREATEORREPLACETRIGGERuserinfo_trigger
--在删除某班级之前备份一份
BEFOREdeleteonuserInfo
foreachrow
begin
--判断是否为学员信息
if:
OLD.actor='student'then
升级会员 