Oracle中自动生成试题和阅卷的过程
drop table answer;
drop table stuInfo;
drop table paper;
drop table exam;
--试题信息表
create table exam(
eid int primary key, --试题编号
eName varchar2(200), --试题名称
answer1 varchar2(100), --答案1
answer2 varchar2(100), --答案2
answer3 varchar2(100), --答案3
answer4 varchar2(100), --答案4
qans varchar2(10), --标准答案
marks varchar2(20) --试题备注
);
--学生信息表
create table stuInfo(
sid int primary key, --学生学号
stuName varchar2(50), --学生姓名
password varchar2(50), --登录密码
marks varchar2(100) --备注
);
--试卷信息表
create table paper(
pid int primary key, --试卷编号
pname varchar2(50), --试题名称
qNo varchar2(400), --试题编号 1-A,2-B,4-A,16-D,30-B
avgscore decimal(5,2), --每题分数
marks varchar2(100) --试卷备注
);
--学生答案信息表
create table answer(
aid int primary key, --答案编号
pid int --试卷编号
constraint FK_pid references paper(pid),
sid int
constraint FK_sid references stuInfo(sid),
ans varchar2(400), --答案 1-B,2-C,4-A
score decimal(5,2), --分数
marks varchar2(100) --试题备注
);
--创建序列
drop sequence seq_eid; --试题编号
drop sequence seq_sid; --学生学号
drop sequence seq_pid; --试卷编号
drop sequence seq_aid; --答案编号
create sequence seq_eid start with 1 increment by 1; --试题编号
create sequence seq_sid start with 1 increment by 1; --学生学号
create sequence seq_pid start with 1 increment by 1; --试卷编号
create sequence seq_aid start with 1 increment by 1; --答案编号
--创建触发器
create or replace trigger tri_eid
before insert on exam
for each row
begin
select seq_eid.nextval into :new.eid from dual;
end;
create or replace trigger tri_sid
before insert on stuInfo
for each row
begin
select seq_sid.nextval into :new.sid from dual;
end;
drop trigger tri_pid;
create or replace trigger tri_pid
before insert on paper
for each row
begin
select seq_pid.nextval into :new.pid from dual;
end;
create or replace trigger tri_aid
before insert on answer
for each row
begin
select seq_aid.nextval into :new.aid from dual;
end;
insert into exam values('1','2+2=','1','2','3','4','D','');
insert into exam values('1','2+2=','1','2','3','4','A','');
insert into exam values('1','2+2=','1','2','3','4','C','');
insert into exam values('1','2+2=','1','2','3','4','A','');
insert into exam values('1','2+2=','1','2','3','4','D','');
insert into exam values('1','2+2=','1','2','3','4','B','');
insert into exam values('1','2+2=','1','2','3','4','C','');
insert into exam values('1','2+2=','1','2','3','4','A','');
insert into exam values('1','2+2=','1','2','3','4','B','');
insert into exam values('1','2+2=','1','2','3','4','D','');
insert into exam values('1','2+2=','1','2','3','4','C','');
insert into exam values('1','2+2=','1','2','3','4','A','');
insert into exam values('1','2+2=','1','2','3','4','B','');
insert into exam values('1','2+2=','1','2','3','4','C','');
insert into exam values('1','2+2=','1','2','3','4','B','');
insert into exam values('1','2+2=','1','2','3','4','D','');
insert into exam values('1','2+2=','1','2','3','4','A','');
insert into exam values('1','2+2=','1','2','3','4','D','');
insert into exam values('1','2+2=','1','2','3','4','C','');
insert into exam values('1','2+2=','1','2','3','4','B','');
insert into exam values('1','2+2=','1','2','3','4','D','');
insert into exam values('1','2+2=','1','2','3','4','D','');
insert into exam values('1','2+2=','1','2','3','4','A','');
insert into exam values('1','2+2=','1','2','3','4','C','');
insert into exam values('1','2+2=','1','2','3','4','D','');
insert into exam values('1','2+2=','1','2','3','4','B','');
insert into exam values('1','2+2=','1','2','3','4','A','');
insert into exam values('1','2+2=','1','2','3','4','C','');
insert into exam values('1','2+2=','1','2','3','4','D','');
insert into exam values('1','2+2=','1','2','3','4','A','');
select * from exam;
insert into stuInfo values('1','张三','a','');
insert into stuInfo values('1','李四','a','');
insert into stuInfo values('1','王五','a','');
insert into stuInfo values('1','赵柳','a','');
insert into stuInfo values('1','钱七','a','');
insert into stuInfo values('1','王八','a','');
insert into stuInfo values('1','田九','a','');
insert into stuInfo values('1','凤姐','a','');
insert into stuInfo values('1','元方','a','');
insert into stuInfo values('1','八戒','a','');
insert into stuInfo values('1','悟空','a','');
insert into stuInfo values('1','二师兄','a','');
select * from stuInfo;
--写一个过程用来完成试卷的生成
--需求：根据用户输入的试题的数量和试题名称，自动生成试题（题号不能重复），并添加到试卷信息表中
create or replace procedure pro_paper(v_pnum number,v_pname varchar2,v_result out varchar2)
as
v_sum number; --试题总量
v_index number; --重复数字的索引
v_num number; --随机生成的数字
v_str varchar2(200):=''; --随机生成题目
v_answer varchar2(10); --正确答案
begin
select count(*) into v_sum from exam;
if v_sum < v_pnum then
v_result:='对不起!题库题量不足';
else
--循环生成用户指定的试题数
for i in 1..v_pnum loop
--生成1到题库总量数之间的任何一个随机数
v_num:=trunc(dbms_random.value(1,v_sum));
--判断生成的随机数是否存在，从v_str中查找v_num,搜索位置从1开始, 搜索v_num第一次出现的位置
select instr(v_str,v_num,1,1) into v_index from dual;
--如果搜索到的索引下标大于0，说明重复了，循环开始生成
while v_index > 0 loop
v_num:=trunc(dbms_random.value(1,v_sum));
select instr(v_str,v_num,1,1) into v_index from dual;
end loop;
--如果生成的题号没有重复，则查询该题号对应的正确答案
select qans into v_answer from exam where eid=v_num;
--拼接到试题中去
v_str:=v_str||v_num||'-'||v_answer||',';
end loop;
--去掉最后的一个,号
select rtrim(v_str,',') into v_str from dual;
--添加试题到试题库
insert into paper values('1',v_pname,v_str,100/v_pnum,'');
v_result:='试题生成成功';
end if;
exception
when others then
v_result:='生成试卷出错';
end;
declare
v_result varchar2(100);
begin
pro_paper(10,'第1个测试',v_result);
dbms_output.put_line(v_result);
end;
select * from paper for update;
--写一个过程用来完成自动阅卷
--需求：根据给定的试卷编号和考生答案编号，自动计算分数，并将分数保存到对应考生答案的score字段中
create or replace procedure pro_score(v_pid number,v_sid varchar2,v_result out varchar2)
as
v_cans varchar2(10); --正确答案
v_qNo varchar2(400); --试题答案
v_qans varchar2(400); --正确答案备份
v_avgsocre number; --每题的分数
v_total number:=0; --总分
v_index number; --索引
v_stuindex number; --正确答案索引
type cur_sor is ref cursor;
cur cur_sor;
v_ans answer%rowtype;
begin
--查询试卷信息和每题的分数
select qNo,avgscore into v_qNo,v_avgsocre from paper where pid=v_pid;
v_qans:=v_qNo;
--如果根据试卷编号能查到试卷信息
if v_qNo is not null then
open cur for 'select * from answer where pid=1 and sid in ('||v_sid||')';
loop
fetch cur into v_ans;
exit when cur%notfound;
--判断正确答案是否判断完毕
select instr(v_qNo,',',1,1) into v_index from dual;
while v_index > 0 loop
--截取正确答案 1-C,2-B,3-A->1-C 2-B,3-A
select substr(v_qNo,1,instr(v_qNo,',',1,1)-1),substr(v_qNo,instr(v_qNo,',',1,1)+1,length(v_qNo)) into v_cans,v_qNo from dual;
--判断用户是否做对
select instr(v_ans.ans,v_cans,1,1) into v_stuindex from dual;
--如果找到，总分累加
if v_stuindex > 0 then
v_total:=v_total+v_avgsocre;
end if;
select instr(v_qNo,',',1,1) into v_index from dual;
end loop;
update answer set score=v_total where aid=v_ans.aid;
v_qNo:=v_qans;
v_total:=0;
end loop;
end if;
v_result:='阅卷成功';
exception
when others then
rollback;
v_result:='阅卷失败';
end;
declare
v_result varchar2(100);
begin
pro_score(1,'1,2',v_result);
dbms_output.put_line(v_result);
end;
select * from answer for update;
