章例外高级概念声明oracle pl sql实例精解chapterscripts.pdf
Chapter10
Exceptions:Advanced
Concepts
DECLARE
v_student_idSTUDENT.STUDENT_ID%TYPE:=sv_student_id;
v_total_coursesNUMBER;
e_invalid_idEXCEPTION;
BEGIN
IFv_student_id0THEN
RAISEe_invalid_id;
ELSE
SELECTCOUNT(*)
INTOv_total_courses
FROMenrollment
WHEREstudent_id=v_student_id;
DBMS_OUTPUT.PUT_LINE(Thestudentisregisteredfor||
v_total_courses||courses);
ENDIF;
DBMS_OUTPUT.PUT_LINE(Noexceptionhasbeenraised);
EXCEPTION
WHENe_invalid_idTHEN
DBMS_OUTPUT.PUT_LINE(Anidcannotbenegative);
END;
DECLARE
v_student_idSTUDENT.STUDENT_ID%TYPE:=sv_student_id;
v_total_coursesNUMBER;
BEGIN
IFv_student_id0THEN
RAISE_APPLICATION_ERROR(-20000,Anidcannotbenegative);
ELSE
SELECTCOUNT(*)
INTOv_total_courses
FROMenrollment
WHEREstudent_id=v_student_id;
DBMS_OUTPUT.PUT_LINE(Thestudentisregisteredfor||
v_total_courses||courses);
ENDIF;
END;
Entervalueforsv_student_id:-4
old2:v_student_idSTUDENT.STUDENT_ID%TYPE:=sv_student_id;
new2:v_student_idSTUDENT.STUDENT_ID%TYPE:=-4;
Anidcannotbenegative
PL/SQLproceduresuccessfullycompleted.
Entervalueforsv_student_id:-4
old2:v_student_idSTUDENT.STUDENT_ID%TYPE:=sv_student_id;
new2:v_student_idSTUDENT.STUDENT_ID%TYPE:=-4;
DECLARE
*
ERRORatline1:
ORA-20000:Anidcannotbenegative
ORA-06512:atline6
DECLARE
v_student_idSTUDENT.STUDENT_ID%TYPE:=sv_student_id;
v_nameVARCHAR2(50);
BEGIN
SELECTfirst_name||||last_name
INTOv_name
FROMstudent
WHEREstudent_id=v_student_id;
DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION
WHENNO_DATA_FOUNDTHEN
RAISE_APPLICATION_ERROR(-20001,ThisIDisinvalid);
END;
Entervalueforsv_studen