[原創]一個考試系統中的預存程序用來產生試卷用的

來源:互聯網
上載者:User
預存程序|原創
CREATE proc paperbuild @papername char(200), @subject char(10), @xzt int,@xztn int,@tkt int,@tktn int,@pdt int,@pdtn int,
@wdt int,@wdtn int as
/*
power by :liujun
address:衡陽師範學院電腦系0102班
選擇題產生部分
*/
declare @bxzt int,@ken varchar(8000)
declare @tempsql varchar(8000),@lxzt int,@lsnd int
set @bxzt=@xzt/2
set @ken=(select top 1 kenlist from tempkenlist where papername=@papername)
set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@bxzt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''選擇題'' and subject='''+@subject+''' and ken in '+@ken+' order by newid()'
execute(@tempsql)

set @tempsql='update paper set papername='''+@papername+''' where papername is null'
execute(@tempsql)

set @lxzt=@xzt-@bxzt
set @lsnd=(select avg(difficulty) from paper where papername=@papername and q_type='選擇題')

if @lsnd>@xztn
begin
 set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@lxzt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''選擇題'' and subject='''+@subject+''' and difficulty<'''+str(@lsnd)+''' and ken in '+@ken+' and question1 not in(select question1 from paper where q_type=''選擇題'' and papername='''+@papername+''') order by newid()'
 execute (@tempsql)
end
else
begin
 set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@lxzt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''選擇題'' and subject='''+@subject+''' and difficulty>'''+str(@lsnd)+''' and ken in '+@ken+' and question1 not in(select question1 from paper where q_type=''選擇題'' and papername='''+@papername+''') order by newid()'
 execute (@tempsql)
end
set @tempsql='update paper set papername='''+@papername+''' where papername is null'
execute(@tempsql)
/*
填空題產生部分
*/
declare @btkt int,@ltkt int
set @btkt=@tkt/2

set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@btkt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''填空題'' and subject='''+@subject+''' and ken in '+@ken+' order by newid()'
execute(@tempsql)

set @tempsql='update paper set papername='''+@papername+''' where papername is null'
execute(@tempsql)

set @ltkt=@tkt-@btkt
set @lsnd=(select avg(difficulty) from paper where papername=@papername and q_type='填空題')

if @lsnd>@tktn
begin
 set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@ltkt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''填空題'' and subject='''+@subject+''' and difficulty<'''+str(@lsnd)+''' and ken in '+@ken+' and question1 not in(select question1 from paper where q_type=''填空題''  and papername='''+@papername+''') order by newid()'
 execute (@tempsql)
end
else
begin
 set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@ltkt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''填空題'' and subject='''+@subject+''' and difficulty>'''+str(@lsnd)+''' and ken in '+@ken+' and question1 not in(select question1 from paper where q_type=''填空題'' and papername='''+@papername+''') order by newid()'
 execute (@tempsql)
end
set @tempsql='update paper set papername='''+@papername+''' where papername is null'
execute(@tempsql)

/*
判斷題產生部分
*/
declare @bpdt int,@lpdt int
set @bpdt=@pdt/2

set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@bpdt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''判斷題'' and subject='''+@subject+''' and ken in '+@ken+' order by newid()'
execute(@tempsql)

set @tempsql='update paper set papername='''+@papername+''' where papername is null'
execute(@tempsql)

set @lpdt=@pdt-@bpdt
set @lsnd=(select avg(difficulty) from paper where papername=@papername and q_type='判斷題')
if @lsnd>@pdtn
begin
 set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@lpdt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''判斷題'' and subject='''+@subject+''' and difficulty<'''+str(@lsnd)+''' and ken in '+@ken+' and question1 not in (select question1 from paper where q_type=''判斷題'' and papername='''+@papername+''') order by newid()'
 execute (@tempsql)
end
else
begin
 set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@lpdt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''判斷題'' and subject='''+@subject+''' and difficulty>'''+str(@lsnd)+''' and ken in '+@ken+' and question1 not in (select question1 from paper where q_type=''判斷題'' and papername='''+@papername+''') order by newid()'
 execute (@tempsql)
end
set @tempsql='update paper set papername='''+@papername+''' where papername is null'
execute(@tempsql)

/*
問答題產生部分
*/
declare @bwdt int,@lwdt int
set @bwdt=@wdt/2

set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@bwdt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''問答題'' and subject='''+@subject+''' and ken in '+@ken+'  order by newid()'
execute(@tempsql)

set @tempsql='update paper set papername='''+@papername+''' where papername is null'
execute(@tempsql)

set @lwdt=@wdt-@bwdt
set @lsnd=(select avg(difficulty) from paper where papername=@papername and q_type='問答題')
select @wdtn
if @lsnd>@wdtn
begin
 set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@lwdt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''問答題'' and subject='''+@subject+''' and ken in '+@ken+' and difficulty<'+str(@lsnd)+' and question1 not in (select question1 from paper where q_type=''問答題'' and papername='''+@papername+''') order by newid()'
 execute (@tempsql)
end
else
begin
 set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@lwdt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''問答題'' and subject='''+@subject+''' and ken in '+@ken+' and difficulty>'+str(@lsnd)+' and question1 not in (select question1 from paper where q_type=''問答題'' and papername='''+@papername+''') order by newid()'
 execute (@tempsql)
end
set @tempsql='update paper set papername='''+@papername+''' where papername is null'
execute(@tempsql)



相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。