create table t1 (id int,shuxue int,yuwen int,waiyu int,kaohao int);
insert into t1 values (1,92,87,89,101);
insert into t1 values (2,85,63,94,102);
insert into t1 values (3,67,59,71,103);
insert into t1 values (4,97,68,45,104);
insert into t1 values (5,99,100,98,105);
insert into t1 values (6,99,87,61,106);
insert into t1 values (7,41,29,98,107);
insert into t1 values (8,55,70,62,108);
insert into t1 values (9,87,64,70,109);
insert into t1 values (10,56,70,25,110);
commit;

create table t2 (id int,name varchar2(20),lufen int);
insert into t2 values (1001,'qinghuadaxue',280);
insert into t2 values (1002,'beijingdaxue',250);
insert into t2 values (1003,'waiyudaxue',220);
insert into t2 values (1004,'wuhandaxue',200);
insert into t2 values (1005,'dianshidaxue',180);
insert into t2 values (1006,'youdiandaxue',160);
insert into t2 values (1007,'shanghaidaxue',150);
commit;

create table t3 (id int,name varchar2(20),xingbie varchar2(10),kaohao int,zy1 int,zy2 int,zy3 int);
insert into t3 values (1,'dailei','nan',101,1001,1002,1003);
insert into t3 values (2,'liboxiang','nan',102,1002,1003,1004);
insert into t3 values (3,'zhangtengzi','nv',103,1001,1003,1004);
insert into t3 values (4,'lishuo','nan',104,1003,1004,1006);
insert into t3 values (5,'youchong','nan',105,1001,1002,1003);
insert into t3 values (6,'peiyanlan','nv',106,1002,1003,1004);
insert into t3 values (7,'zhangnan','nan',107,1005,1006,1007);
insert into t3 values (8,'yangfei','nan',108,1003,1005,1007);
insert into t3 values (9,'yangxiaowei','nan',109,1003,1004,1005);
insert into t3 values (10,'zhangxiaolong','nan',110,1002,1006,1007);
commit;

create or replace procedure pro_aa(name in varchar2,kh in int)
as
a int:=0;
sum int:=0;
m int:=0;
c int:=0;
e int:=0;
n int:=0;
x int:=0;
k int:=0;
xn int:=0;
begin
select f1.kaohao into a from t1 f1 where f1.name=name;
if a != kh
then
dbms_output.put_line('kh error!');
else
select f2.shuxue into m from t1 f2 where kh=f2.kaohao;
select f2.yuwen into c from t1 f2 where kh=f2.kaohao;
select f2.waiyu into e from t1 f2 where kh=f2.kaohao;
dbms_output.put_line(m+c+e);
select r1.name into n from t3 r1 where kh=r1.kaohao;
select r1.xingbie into x from t3 r1 where kh=r1.kaohao;
select r1.kaohao into k from t3 r1 where name=r1.name;
dbms_output.put_line(n,x,k,m,c,e);
end if;
if
with ls as (select s1.name,s1.lufen from t2 s1 where s1.id=(select zy1 from t3 where kaohao=kh))
select o1.name into xn from ls o1 where o1.lufen < (select sum(shuxue+yuwen+waiyu) from t1 where kaohao=kh);
then
dbms_output.put_line(xn);
elsif
with ls1 as (select s2.name,s2.lufen from t2 s2 where s2.id=(select zy2 from t3 where kaohao=kh))
select o2.name into xn from ls1 o2 where o2.lufen < (select sum(shuxue+yuwen+waiyu) from t1 where kaohao=kh);
then
dbms_output.put_line(xn);
elsif
with ls2 as (select s3.name,s3.lufen from t2 s3 where s2.id=(select zy3 from t3 where kaohao=kh))
select o3.name into xn from ls2 o3 where o3.lufen < (select sum(shuxue+yuwen+waiyu) from t1 where kaohao=kh);
then
dbms_output.put_line(xn);
else
dbms_output.put_line(“your died”);
end if;
end;
/