my_exception_application.sql

pl/sql的综合运用上例用到的oracle里关于游标的几个内置函数:％isopen,％notfound,％rowcount。 隐式游标更多用在for loop中

SET serveroutput ON;

DECLARE
	   e_too_many EXCEPTION;
	   e_too_low EXCEPTION;
	   PRAGMA EXCEPTION_INIT(e_too_many,-20200);
	   PRAGMA EXCEPTION_INIT(e_too_low,-20201);
	   v_count NUMBER(10);
BEGIN 
	SELECT COUNT(empno) INTO v_count FROM emp 
	WHERE sal > (SELECT AVG(sal)+1000 FROM emp);
	IF v_count > 5 THEN RAISE_APPLICATION_ERROR(-20200,'高工资人数太多'); END IF;
	IF v_count < 5 THEN RAISE_APPLICATION_ERROR(-20201,'低工资人数太多'); END IF;
EXCEPTION
		 WHEN e_too_many THEN
		 	  dbms_output.put_line(SQLCODE);
			  dbms_output.put_line(SQLERRM);
		 WHEN e_too_low THEN
		 	  dbms_output.put_line(SQLCODE);
			  dbms_output.put_line(SQLERRM);
		 WHEN OTHERS THEN
		 	  dbms_output.put_line(SQLERRM);
END;