1 create or replace procedure showStudentInfo(ThisStudentName varchar2)
2 as
3 thismax number;
4 thismin number;
5 thisavg number;
6 sno number;
7 line student%rowtype;
8 begin
9 select sid into sno from student where sname=ThisStudentName; --先求学号,检测学号是否合法,不存在立即进入异常
10 select max(cmark),min(cmark),trunc(avg(cmark),2) into thismax,thismin,thisavg
11 from mark
12 where sid = sno;
13 dbms_output.put_line(ThisStudentName||'的最高分为:'||thismax||'最低分为:'||thismin||'平均分为:'||thisavg);
14 exception
15 when no_data_found then
16 dbms_output.put_line(ThisStudentName||'不存在,请核对!');
17 when too_many_rows then
18 for line in(select*from student where sname=ThisStudentName) loop ---取出学生表的每一行 循环取出
19 select max(cmark),min(cmark),trunc(avg(cmark),2) into thismax,thismin,thisavg
20 from mark
21 where sid=line.sid;
22 dbms_output.put_line(line.sid||ThisStudentName||'的最高分为:'||thismax||'最低分为:'||thismin||'平均分为:'||thisavg);
23 end loop;
24 end;
1 -----调用---
2 set serveroutput on
3 begin
4 showStudentInfo('萧瑾');
5 end;