所有的存储过程与函数.docx

1、所有的存储过程与函数1. 学员信息录入存储过程userinfo_insert创建学员信息表中的ID序列userSequencecreate or replace procedure userinfo_insert(username1 userInfo.username%type,password1 userInfo.password%type,userClass1 userInfo.userClass%type,actor1 userInfo.actor%type)isbegin -判断角色是否为STUDENT 或者TEACHER if actor1=student or actor1=tea

2、cher then insert into userinfo(id,username,password,userClass,actor,state) values(userSequence.nextval,username1,password1,userClass1,actor1,0); end if;exception when others then dbms_output.put_line(Exception);end;2. 学员信息修改存储过程userinfo_update create or replace procedure userinfo_update(id1 userInfo

3、.id%type,username1 userInfo.username%type,password1 userInfo.password%type,userClass1 userInfo.userClass%type,actor1 userInfo.actor%type,state1 userInfo.state%type)isbegin -信息修改后，但是角色必须为STUDENT 或者 TEACHER if actor1=studentor actor1=teacher thenupdate userinfo set username=username1,password=password

4、1,userClass=userClass1,actor=actor1,state=state where id=id1; end if;exception when others then dbms_output.put_line(Exception);end;3.试卷录入存储过程为表TESTPAPER新建testpaperSequence序列create or replace procedure testpaper_insert(testpapername1 TESTPAPER.testpaperName%type,number1 out int)is -声明变量用户接收查询的结果 tes

5、tpaperid int;begin -将查询出来的结果赋值给变量 select count(testpaperid) into testpaperid from testpaper where testpapername=testpapername1; -判断需要录入的题目是否已经存在 if testpaperid=0 then insert into testpaper values(testpaperSequence.nextval,testpapername1,0); number1:=1; else dbms_output.put_line(该题目已经存在，请重新输入题目！); nu

6、mber1:=0; end if;exception when others then dbms_output.put_line(Exception);end;4. 题库录入存储过程question_insert为表question新建questionSequence序列create or replace procedure question_insert(question1 QUESTION.question%type,optionA1 QUESTION.optionA%type,optionB1 QUESTION.optionB%type,optionC1 QUESTION.optionC

7、%type,optionD1 QUESTION.optionD%type,answer1 QUESTION.answer%type,testpaperId1 QUESTION.testpaperId%type,number1 out int)is-声明变量用于接收查询到的ID信息 testpaperid2 testpaper.TESTPAPERID%type; testpapername2 testpaper.testpaperName%type;begin -将查询到的ID信息赋值给变量 select count(TESTPAPERID) into testpaperid2 from tes

8、tpaper where TESTPAPERID=testpaperId1; -如果查询到题目的信息，就可以根据题目信息进行添加题，并将试卷表 -中的相应题目类型的总数加1 if testpaperid20 then select testpapername into testpapername2 from testpaper where TESTPAPERID=testpaperId1; -将相应的题添加到题库表中 insert into question(id,question,optionA,optionB,optionC,optionD,answer,testpaperId) valu

9、es(questionSequence.nextval,question1,optionA1,optionB1,optionC1,optionD1,answer1,testpaperId1); -将试卷表中的相应题目的总数加1 update TESTPAPER set questionCount=questionCount+1 where testpaperName=testpapername2; number1:=1; else dbms_output.put_line(试卷表中不存在该试卷！); number1:=0; end if;exception when others then d

10、bms_output.put_line(Exception);end;5.题库（答案）修改 ，相关表QUESTION-在question表中插入题目的信息的存储过程create or replace procedure question_update(questionid QUESTION.id%type,question1 QUESTION.question%type,optionA1 QUESTION.optionA%type,optionB1 QUESTION.optionB%type,optionC1 QUESTION.optionC%type,optionD1 QUESTION.op

11、tionD%type,answer1 QUESTION.answer%type,testpaperId1 QUESTION.testpaperId%type,unmber1 out int)is -声明变量用于接收查询未更改的试卷表ID信息 testpaperid2 testpaper.testpaperid%type; -声明变量用于接收查询未梗罚ide试卷表题名称信息 testpapername2 testpaper.testpaperName%type; -声明变量用于接收修改后试卷表中的ID testpaperid3 testpaper.testpaperid%type; -声明变量用

12、于接收修改后试卷表中的题名 testpapername3 testpaper.testpaperName%type;begin -将更改前的试卷表ID赋值给变量 select testpaperId into testpaperid2 from QUESTION where id=questionid; -根据更改前的试卷表ID，得到更改前的题目名称 select testpaperName into testpapername2 from TESTPAPER where testpaperid=testpaperid2; -判断是否有修改试卷表的ID if testpaperid2=test

13、paperId1 then -没有修改试卷表的ID，那么只需要修改题库表中的信息 update QUESTION set question=question1,optionA=optionA1,optionB=optionB1 ,optionC=optionC1,optionD=optionD1,answer=answer1, testpaperId=testpaperId1 where id=questionid; unmber1:=1; else -修改试卷表的ID，那么需要进行判断修改的ID在试卷表中是否存在 select count(testpaperid) into testpape

14、rid3 from testpaper where testpaperid=testpaperId1; -如果查询到题目的信息，就可以根据题目信息进行修改题，并将试卷表 -中的相应题目类型的总数减1 if testpaperid30 then select testpapername into testpapername3 from testpaper where testpaperid=testpaperId1; -将相应的题修改到题库表中 update QUESTION set question=question1,optionA=optionA1, optionB=optionB1,op

15、tionC=optionC1,optionD=optionD1,answer=answer1, testpaperId=testpaperId1 where id=questionid; -将试卷表中修改前的相应题目的总数减1 update TESTPAPER set questionCount=questionCount-1 where testpaperName=testpapername2; -将试卷表中修改后的相应题目的总数加1 update TESTPAPER set questionCount=questionCount+1 where testpaperName=testpape

16、rname3; unmber1:=1; else dbms_output.put_line(试卷表中不存在该试卷！); unmber1:=0; end if; end if; exception when others then dbms_output.put_line(Exception);end;6. 学员考试答案录入存储过程stuanswer_insert为stuanswer表新建stuAnswerSequence序列-为stuanswer表中插入信息的存储过程create or replace procedure stuanswer_insert(stuId1 stuAnswer.st

17、uId%type,testpaperId1 stuAnswer.testpaperId%type,stuAns1 stuAnswer.stuAns%type,number1 out int)is -声明变量用于接收stuId ，学号,对应userInfo表中的id stuid2 int; -声明变量用于接收testpaperId试卷id，对应TESTPAPER表中的id testpaperid2 int;begin -将userInfo表中查询出来的值赋值给变量 select count(id) into stuid2 from userInfo where id=stuId1; -将TEST

18、PAPER表中查询出来的值赋值给变量 select count(testpaperid) into testpaperid2 from TESTPAPER where testpaperid=testpaperId1; -判断输入的参数stuId1，testpaperId1是否在userInfo表，TESTPAPER表中存在 if stuid20 and testpaperid20 then -将输入的参数信息添加到stuanswer表中 insert into stuAnswer values(stuAnswerSequence.nextval,stuId1,testpaperId1,stu

19、Ans1); number1:=1; else dbms_output.put_line(输入错误，请重新输入); number1:=0; end if;exception when others then dbms_output.put_line(Exception);end;7. 生成学员分数存储过程score_reportcreate or replace procedure score_report(tpId testpaper.testpaperid%type)as-存放该试卷的正确答案的游标cursor answer_cur is select answer from questi

20、on where testpaperId =tpId;-存放某试卷的学员答案的游标cursor stuAnswer_cur is select * from stuAnswer where testpaperId =tpId;-正确答案字符串Qanswer varchar2(500);-循环变量a varchar(500);-长度(试题数)qsize int;num int:=0;-答对题计数器score int;-分数stage varchar2(20);-等级sameCount int;-成绩表中存在相同数据的标志变量stuAnswer_row stuAnswer%rowtype;-学员答

21、案表的行begin open answer_cur;-打开正确答案的游标,循环,把所有该试卷的题的正确答案用字符串起来,存入正确答案字符串变量中 loop fetch answer_cur into a; exit when answer_cur%notfound; select Qanswer|a into Qanswer from dual; end loop; close answer_cur;-关闭正确答案游标 qsize:=length(Qanswer);-得到题目数 open stuAnswer_cur;-打开学员答案游标 loop fetch stuAnswer_cur into

22、 stuAnswer_row; exit when stuAnswer_cur%notfound; select stuAnswer_row.stuAns into a from dual; num:=0;-计数器清0 for i in 1.qsize loop if substr(a,i,1)=substr(Qanswer,i,1) then num:=num+1;-累加答对题数 end if; end loop; score:=round(num/qsize*100); dbms_output.put_line(score); if score=80 then stage:=优秀; els

23、if score=70 then stage:=良好; elsif score=60 then stage:=及格; else stage:=不及格; end if; select count(*) into sameCount from stuScore where stuId=stuAnswer_row.stuId and testpaperId=tpId;-在成绩表中查询该学员是否考过该试卷 if sameCount=0 then insert into stuScore values(stuScoreSequence.nextval,stuAnswer_row.stuId ,tpId,

24、score,stage);-没有,则往成绩表中插入数据 end if; end loop; close stuAnswer_cur;exception when others then dbms_output.put_line(Exception);end score_report;8. 查看学员分数存储过程searchscorecreate or replace procedure searchScore( classname2 varchar2,-班级 stuId2 int,-学号 testpaperid2 int,-试卷号 score_cur out sys_refcursor-返回成绩

25、游标(学号,姓名,分数,级别,试卷名)is score_c sys_refcursor; -变量begin -如果班级参数不为空 if classname2!= then open score_c for select s.stuid,u.userName,s.stuScore,s.standard,testpaperName from stuScore s,userInfo u,TESTPAPER t where s.stuid=u.id and t.testpaperid=s.testpaperid and u.userClass=:1 and s.testpaperId=:2 using

26、 classname2, testpaperid2; -如果学号不为0 elsif stuId2!=0 then open score_c for select s.stuid,u.userName,s.stuScore,s.standard,testpaperName from stuScore s,userInfo u,TESTPAPER t where s.stuid=u.id and t.testpaperid=s.testpaperid and u.id=:1 and s.testpaperId=:2 using stuId2, testpaperid2; else open sco

27、re_c for select s.stuid,u.userName,s.stuScore,s.standard,testpaperName from stuScore s,userInfo u,TESTPAPER t where s.stuid=u.id and t.testpaperid=s.testpaperid and s.testpaperId=:1 using testpaperid2; end if; score_cur:=score_c;end;9. 登录存储过程user_logincreate or replace procedure user_login(userName1

28、 userInfo.userName%type,password1 userInfo.password%type,userClass1 userInfo.userClass%type,actor1 userInfo.actor%type,loginInfo out int)is -声明一个变量用于接收用户信息 userid int; userCount int; -声明一个变量用于接收用户的状态 userstate userinfo.state%type;begin -将获取到的值赋值给变量 select count(id) into userCount from userinfo where

29、 userName=userName1 and password=password1 and userClass=userClass1 and actor=actor1; -判断输入的用户信息是否存在 if userCount0 then -把用户id查出来 select id into userid from userinfo where userName=userName1 and password=password1 and userClass=userClass1 and actor=actor1; -查询用户的状态 select state into userstate from u

30、serinfo where id=userid; -判断状态是否为已登录 if userstate=0 then -用户处于未登录状态，让用户登录并修改状态为登录状态 update userinfo set state=1 where id=userid; loginInfo:=userid; else -用户处于登录状态 loginInfo:=0; end if; else -该用户不存在 loginInfo:=-1; end if;exception when others then dbms_output.put_line(Exception);end;10 创建触发器完成删除某个班级的一个学员，在删除之前备份该学员信息到stuInfo_bak表CREATE OR REPLACE TRIGGER userinfo_trigger -在删除某班级之前备份一份 BEFORE delete on userInfo for each rowbegin -判断是否为学员信息 if :OLD.actor=student then