pd1.sql

网络考试系统

--自动组卷存储过程
--传入教师id, 
create procedure pd_make_testPage
	@teacherId int, @des varchar(100), @totalVal int, @seleVal int, @fillVal int,
	@judgeVal int, @crId int, @tpId int out
as
	--新的事务
	begin tran;

	--新的试卷id
	insert into tb_test_paper(tp_des, tp_th_id, tp_cr_id, tp_value)
		values(@des, @teacherId, @crId, @totalVal);
	set @tpId = @@identity;

	--用户进程ID
	declare @spid int;
	set @spid = @@spid;

	--循环遍历变量
	declare @i int;
	set @i = 0;


	--选择题处理
	--选择题号和选择题分值
	declare @id int, @value real;

	--选择题游标
	declare cur_select cursor local for 
		select sls_id, sls_value from tb_selection_subject 
			where sls_kp_id in (select kp_id from tb_knowledge_point 
				where kp_cr_id = @crId);

	open cur_select;

	--插入临时表
	fetch next from cur_select into @id, @value;
	while @@fetch_status = 0
		begin
			insert into tb_makeTP_tmp([key], value_id, def_value, spid)
				values(@i, @id, @value, @spid);
			set @i = @i + 1;
			fetch next from cur_select into @id, @value;
		end

	--关闭游标
	close cur_select;

	set @i = @i - 1;

	--随机数
	declare @re int;
	--题目的数量
	declare @j int, @num int;
	set @num = round(@seleVal / @value, 0);
	set @j = 0;

	declare @tmp real;
	 
	--利用随机数插入选择题号
	while @j < @num 
		begin
			--生成0~i之间的随机数
			set @re = round(rand() * @i, 0)
			print @re;
			print @j;
			
			--根据随机数查找记录
			set @id = (select value_id from tb_makeTP_tmp 
				where [key] = @re and spid = @spid);
			--把该题插到明细表中
			insert into tb_test_paper_detail(tpd_type, tpd_tp_id, tpd_subject_id)
				values('1', @tpId, @id);
			
			--继续下一道题
			set @j = @j + 1;
		end
	
	--清空临时表
	delete from tb_makeTP_tmp where spid = @spid;

	--判断题处理
	set @i = 0;
	declare cur_jud cursor local for
		select jds_id, jds_value from tb_judgement_subject
			where jds_kp_id in (select kp_id from tb_knowledge_point
				where kp_cr_id = @crId);

	open cur_jud;
	--插入临时表
	fetch next from cur_jud into @id, @value;
	while @@fetch_status = 0 
		begin
			insert into tb_makeTP_tmp([key], value_id, def_value, spid)
				values(@i, @id, @value, @spid);
			set @i = @i + 1;
			fetch next from cur_jud into @id, @value;
		end
	--关闭游标
	close cur_jud;

	set @i = @i - 1;
	--判断题目的数量
	set @num = round(@judgeVal / @value, 0);
	set @j = 0;

	while @j < @num
		begin
			set @re = round(rand() * @i, 0);
			set @id = (select value_id from tb_makeTP_tmp
				where [key] = @re and spid = @spid);
			insert into tb_test_paper_detail(tpd_type, tpd_tp_id, tpd_subject_id)
				values('3', @tpId, @id);
			set @j = @j + 1;
		end
	delete from tb_makeTP_tmp where spid = @spid;

	--填空题处理
	set @i = 0;
	declare cur_fill cursor local for
		select fls_id, fls_value from tb_filling_subject
			where fls_kp_id in (select kp_id from tb_knowledge_point
				where kp_cr_id = @crId);
	open cur_fill;
	--插入临时表
	fetch next from cur_fill into @id, @value;
	while @@fetch_status = 0
		begin
			insert into tb_makeTP_tmp([key], value_id, def_value, spid)
				values(@i, @id, @value, @spid);
			set @i = @i + 1;
			fetch next from cur_fill into @id, @value;
		end
	--关闭游标
	close cur_fill;
	
	set @i = @i - 1;
	--判断题目的数量
	set @num = round(@fillVal / @value, 0);
	set @j = 0;
	
	while @j < @num
		begin
			set @re = round(rand() * @i, 0);
			set @id = (select value_id from tb_makeTP_tmp
				where [key] = @re and spid = @spid);
			insert into tb_test_paper_detail(tpd_type, tpd_tp_id, tpd_subject_id)
				values('2', @tpId, @id);
			set @j = @j + 1;
		end
	delete from tb_makeTP_tmp where spid = @spid;

	
	--结束所有处理
	commit