insert into stu_grade values (2012211106,'A',100,74,72,81);

insert into stu_grade values (2012211107,'A',71,84,91,151);

insert into stu_grade values (2012211108,'A',127,144,146.5,232);

insert into stu_grade values (2012211109,'A',76,114,56,99.5);

insert into stu_grade values (2012311110,'A',19,81,84.5,51.5);

insert into stu_grade values (2012341401,'SP',90,88,98.5,177);

insert into stu_grade values (2012341402,'SP',39,64,51,70);

insert into stu_grade values (2012341403,'SP',65,94,108,159);

insert into stu_grade values (2012341404,'AR',99,110,98.5,251);

insert into stu_grade values (2012341405,'AR',89,114,96,193);

insert into stu_grade values (2012341406,'AR',29,76,99.5,201);

insert into stu_grade values (2012341407,'AR',63,82,73,106);
www.2cto.com

5.建立提档线表（low_criterion）

create table low_criterion(

branch varchar2(3) check(branch in('A','S','SP','AR')),

batch_id number(5),batch_name varchar2(10),low_cri number(4,1));

insert into low_criterion values('S',1001,'BKYP',582);

insert into low_criterion values('S',1002,'BKEP',430);

insert into low_criterion values('A',1003,'BKYP',573);

insert into low_criterion values('A',1004,'BKEP',450);

insert into low_criterion values('SP',1005,'SPORT',399);

insert into low_criterion values('AR',1006,'ART',300);

6.建立程序查询存储过程
www.2cto.com

create or replace procedure pro_search_grade(stu_n in number)

as

pro_bra varchar2(3);

grade_sum number(4,1):=0;

pro_batch varchar2(10);

max_low number(4,1):=0;

pro_name varchar2(10);

max_sum_grade number(4,1):=0;

max_stu_name varchar2(10);

max_stu_num number(10):=0;

begin

select stu_name into pro_name

from stu_info where stu_num=stu_n;

dbms_output.put('Student: ');

dbms_output.put_line(pro_name);

select branch into pro_bra

from stu_grade where stu_num=stu_n;

select grade_mat+grade_che+grade_eng+grade_inte into grade_sum

from stu_grade where stu_num=stu_n;

dbms_output.put('Your grade is ');

dbms_output.put_line(grade_sum);

select max(low_cri) into max_low

from low_criterion

where branch=pro_bra and grade_sum>=low_cri;

if max_low>0 then

select batch_name into pro_batch

from low_criterion

where low_cri=max_low;

dbms_output.put('Congratulations to you for inetrnational ');

dbms_output.put_line(pro_batch);

else

dbms_output.put_line('You will dead~');

end if;

select max(sum_grade) into max_sum_grade

from

(select grade_mat+grade_che+grade_eng+grade_inte sum_grade from
stu_grade where branch=pro_bra);

with tmp_t1

as

(select stu_num,grade_mat+grade_che+grade_eng+grade_inte
sum_grade from stu_grade)

select stu_num into max_stu_num

from tmp_t1

where sum_grade=max_sum_grade;

select stu_name into max_stu_name

from stu_info where stu_num=max_stu_num;