1,617
社区成员
发帖
与我相关
我的任务
分享
create or replace
package body set_pass_rate_pkg
is
create or replace procedure main1(
p_pass_rate in number,
p_class_id in number,
p_course_name in varchar2
)
is
CURSOR get_adjust_cur(p_adjust_cnt number)
is
select tt.row_id
from(
select wst.rowid row_id,ws.class_id,ws.student_id,ws.student_name,
wct.course_id,wct.course_name,wst.score,
case when wst.score-60<0
then
rank()over(partition by
case when wst.score-60>=0
then null
else 'A'
end order by wst.score desc)
end adjust_flag
from wl_student_tab ws, wl_course_tab wct, wl_score_tab wst
where ws.class_id=p_class_id and wct.course_id=(select wl_course_tab.course_id
from wl_course_tab
where wl_course_tab.course_name=p_course_name)
) tt
where tt.adjust_flag<=p_adjust_cnt; --游标是项目的固定写法 有点长 但是完全没有错误
--------------------------------------------------------------------------
begin
select count(*) into In_total_cnt
from wl_score_tab wst, wl_student_tab ws
where ws.class_id=p_class_id and wst.course_id=(
select course_id from wl_course_tab where course_name=p_course_name);
--------------------------------------------------------------------------
if(In_total_cnt=0)
then
RAISE normal_expt;
end if;
--------------------------------------------------------------------------
select count(*) into In_pass_cnt
from wl_score_tab wst, wl_student_tab ws
where ws.class_id=p_class_id and wst.course_id=(
select course_id from wl_course_tab where course_name=p_course_name)
and wst.score-60>=0;
In_pass_rate := round (In_pass_cnt/In_total_cnt*100,2);
--------------------------------------------------------------------------
if(In_pass_rate>=p_pass_rate) then
RAISE normal_expt;
else
In_adjust_cnt:= ceil((p_pass_rate-In_pass_rate)*In_total_cnt/100);
end if;
--------------------------------------------------------------------------
open get_adjust_cur(In_adjust_cnt);
fetch get_adjust_cur bulk COLLECT into lt_adjust;
close get_adjust_cur;
--------------------------------------------------------------------------
forall i in 1..lt_adjust.count
update wl_score_tab wst
set wst.score=60
where wst.rowid=lt_adjust(i)
returning wst.student_id,wst.course_id bulk collect into lt_student,lt_course;
--------------------------------------------------------------------------
for i in lt_student.first..lt_student.last
loop
if(i=lt_student.first)
then
dbms_output.put_line('UPDATED RECORD LIST:');
end if;
dbms_output.put_line('STUDENT_ID:'||TO_CHAR(lt_student(i))||'COURSE_ID'||TO_CHAR(lt_course(i)));
end loop;
--------------------------------------------------------------------------
exception
when normal_expt then
dbms_output.putline('Data need not adjust');
end main1;
end set_pass_rate_pkg