DECLARE
s_name varchar2(20);
BEGIN
SELECT SNAME INTO s_name FROM student WHERE SNO=&no;
dbms_output.put_line('学生号:'||s_name);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('请输入正确的学号!');
END;DECLARE
TYPE s_record_type IS RECORD
(name student.SNAME%TYPE,
sex student.SEX%TYPE,
sage student.SAGE%TYPE);
s_record s_record_type;
BEGIN
SELECT SNAME,SEX,SAGE INTO s_record FROM student WHERE SNO='95001';
dbms_output.put_line('学号:'||s_record.name);
END;DECLARE
TYPE sname_table_type IS TABLE OF student.SNAME%TYPE
INDEX BY BINARY_INTEGER;
sname_table sname_table_type;
BEGIN
SELECT SNAME INTO sname_table(1) FROM student
WHERE SNO='95001';
dbms_output.put_line('学号:'||sname_table(1));
END;CREATE OR REPLACE PROCEDURE query_grade
(s_no VARCHAR2,s_cno NUMBER,name OUT VARCHAR2,grade IN OUT NUMBER)
IS
s_grade NUMBER;
BEGIN
SELECT SNAME INTO name FROM student WHERE SNO=s_no;
SELECT Grade INTO s_grade FROM SC WHERE SNO=s_no AND CNO=s_cno;
grade:=grade+s_grade;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20000,'该学生不存在');
END query_grade;
var name VARCHAR2 (20)
var grade NUMBER
exec:grade:=2
exec query_grade('95001',2,:name,:grade)
print name grade
s_name varchar2(20);
BEGIN
SELECT SNAME INTO s_name FROM student WHERE SNO=&no;
dbms_output.put_line('学生号:'||s_name);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('请输入正确的学号!');
END;DECLARE
TYPE s_record_type IS RECORD
(name student.SNAME%TYPE,
sex student.SEX%TYPE,
sage student.SAGE%TYPE);
s_record s_record_type;
BEGIN
SELECT SNAME,SEX,SAGE INTO s_record FROM student WHERE SNO='95001';
dbms_output.put_line('学号:'||s_record.name);
END;DECLARE
TYPE sname_table_type IS TABLE OF student.SNAME%TYPE
INDEX BY BINARY_INTEGER;
sname_table sname_table_type;
BEGIN
SELECT SNAME INTO sname_table(1) FROM student
WHERE SNO='95001';
dbms_output.put_line('学号:'||sname_table(1));
END;CREATE OR REPLACE PROCEDURE query_grade
(s_no VARCHAR2,s_cno NUMBER,name OUT VARCHAR2,grade IN OUT NUMBER)
IS
s_grade NUMBER;
BEGIN
SELECT SNAME INTO name FROM student WHERE SNO=s_no;
SELECT Grade INTO s_grade FROM SC WHERE SNO=s_no AND CNO=s_cno;
grade:=grade+s_grade;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20000,'该学生不存在');
END query_grade;
var name VARCHAR2 (20)
var grade NUMBER
exec:grade:=2
exec query_grade('95001',2,:name,:grade)
print name grade
这一句返回了多行吧
too-many-rows
但是我数据库里有
成绩表SC里,学生表student里都有学号为95001的
这行报的错,你直接查
select grade from sc where sno='95001' and cno=2
应该是没记录。
BEGIN query_grade('95001',2,:name,:grade); END;*
第 1 行出现错误:
ORA-20000: 该学生不存在
ORA-06512: 在 "SYSTEM.QUERY_GRADE", line 11
ORA-06512: 在 line 1
SQL> print name gradeNAME
-------------------------------- GRADE
----------
2
执行select * from SC ,看看有没有你输入的那个:name对应的score
SELECT SNAME INTO name FROM student WHERE SNO=s_no;
SELECT Grade INTO s_grade FROM SC WHERE SNO=s_no AND CNO=s_cno;
grade:=grade+s_grade; 出现NO_DATA_FOUND 异常,进入你的异常处理里面了。
并不一定是第一句,也有可能是第二句,就是说,学生存在,但grade不存在(为NULL)同样也会报这个错。