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

1. 学员信息录入存储过程userinfo_insert 创建学员信息表中的ID序列userSequence create or replace procedure userinfo_insert(username1 userInfo.username%type,password1 userInfo.password%type,userClass1 userInfo.userClass%type,actor1 userInfo.actor%type) is begin --判断角色是否为STUDENT 或者TEACHER if actor1=student or actor1=teacher 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.id%type,username1 userInfo.username%type,password1 userInfo.password%type,userClass1 userInfo.userClass%type,actor1 userInfo.actor%type,state1 userInfo.state%type) is begin --信息修改后，但是角色必须为STUDENT 或者 TEACHER if actor1=studentor actor1=teacher then update userinfo set username=username1,password=password1,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 --声明变量用户接收查询的结果 testpaperid 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(该题目已经存在，请重新输入题目！); number1:=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%type,optionD1 QUESTION.optionD%type,answer1 QUESTION.answer%type,testpaperId1 QUESTION.tes