plsqlの異常処理部分学習整理

ORACLEのPLSQLプログラミング、異常処理は主に3つのブロックに分けられます
 
AOARACLEシステムの事前定義異常、例えばNO_DATA_FOUND;
 
B非事前定義異常、PRAGMAで定義
 
Cカスタム例外
 
次は主なテストスクリプトです.

--         （     ）
set serveroutput on;

declare
  v_dividend number:=50;
  v_divisor number:=0;
  v_quotient number;
 
 begin
 		v_quotient:=v_dividend/v_divisor;
 		exception 
 			when   ZERO_DIVIDE THEN
 			dbms_output.put_line('    ');
 			end;
 /
 
--no_data_foud  （     ）
select * from students;

declare
	v_id students.student_id%type;
	v_sname students.name%type;
begin
  v_id:=&student_id;
  select name into v_sname from students where student_id=v_id;
  dbms_output.put_line('    '||v_sname);
  exception 
  	when no_data_found then
  	dbms_output.put_line('        ');
end;

--too_many_rows   （     ）
declare
	v_SPECIALTY students.SPECIALTY%type;
	v_sname students.name%type;
begin
  v_SPECIALTY:='&specialty';
  select name into v_sname from students  where SPECIALTY=v_SPECIALTY;
  dbms_output.put_line('    '||v_sname);
  exception 
  	when too_many_rows then
  	dbms_output.put_line('           ');
end;

--            too_many_rows  no_data_found  （     ）
declare
	v_SPECIALTY students.SPECIALTY%type;
	v_sname students.name%type;
begin
  v_SPECIALTY:='&specialty'; --                    ？
  select name into v_sname from students  where SPECIALTY=v_SPECIALTY;
  dbms_output.put_line('    '||v_sname);
  exception 
  	when too_many_rows then
  	dbms_output.put_line('           ');
  	when no_data_found then
  	dbms_output.put_line('    ');
end;

--       e_deptid EXCEPTION;(               )
--	pragma EXCEPTION_INIT(e_deptid ,-2292);
select * from departments;
set serveroutput on;
declare
	e_deptid EXCEPTION;
	pragma EXCEPTION_INIT(e_deptid ,-2292);
begin
	delete from departments where department_id=101;
	exception
	when e_deptid then
	dbms_output.put_line('          '); --       ，        ，                   
end;

--             
select * from teachers;
set serveroutput on;
declare
	e_deptid EXCEPTION;
	pragma EXCEPTION_INIT(e_deptid ,-2292);
begin
	insert into teachers values (11101,'  ','  ','01-9 -1990',1000,3000,999);
	exception
	when e_deptid then
	dbms_output.put_line('               '); --       ，        ，                   
end;


---           
select * from students;
set serveroutput on;
declare
	e_studentid EXCEPTION;
	pragma EXCEPTION_INIT(e_studentid ,-0001);
begin
	insert into students values (10205,null,'  ',' ','26-12 -1989','   ');
	exception
	when e_studentid then
	dbms_output.put_line('        ---'||SQLCODE||'    ---'||SQLERRM); --       ，        ，                   
end;

--     
declare
		e_wage EXCEPTION;
		v_wage teachers.wage%type;
	begin
			v_wage:=&wage;
			insert into teachers values (10111,'  ','  ','01-9 -1990',1000,v_wage,101);
			if v_wage <0 then 
			 raise e_wage;
			end if;
			exception 
				when e_wage then
				dbms_output.put_line('        ');
				rollback;
				when others then
					dbms_output.put_line('    ');
	end;
/