);
dbms_output.put_line(max_stu_name);
dbms_output.put('His grade is ');
dbms_output.put_line(max_sum_grade);
dbms_output.put('His Student Number is ');
dbms_output.put_line(max_stu_num);
end;
/
#执行结果如下==================================
7.建立录取查询存储过程
www.2cto.com
create or replace procedure pro_search_admit(stu_n in number)
as
grade_sum number(4,1):=0;
wish_uni_grade number(4,1):=999;
uni_num number(5):=0;
univer_name varchar2(10);
begin
select grade_mat+grade_che+grade_eng+grade_inte into grade_sum
from stu_grade where stu_num=stu_n;
select fir_university_id into uni_num from stu_wish
where stu_num=stu_n;
select tmp_t1.low_cri into wish_uni_grade
from university_info tmp_t1,stu_wish tmp_t2
where tmp_t1.uni_id=tmp_t2.fir_university_id
and tmp_t2.stu_num=stu_n;
if wish_uni_grade>=grade_sum then
wish_uni_grade := 999;
end if;
if wish_uni_grade = 999 then
select sec_university_id into uni_num from stu_wish
where stu_num=stu_n;
select tmp_t1.low_cri into wish_uni_grade
from university_info tmp_t1,stu_wish tmp_t2
where tmp_t1.uni_id=tmp_t2.sec_university_id
and tmp_t2.stu_num=stu_n;
end if;
if wish_uni_grade<=grade_sum then
select uni_name into univer_name
from university_info
where uni_id=uni_num;
dbms_output.put('Congratulations to you that you are admitted by ');
dbms_output.put_line(univer_name);
else
dbms_output.put_line('Sorry,I have search so hardly,But not find any message!');
end if;
end;
/
#执行结果如下==================
