Exceptions in database (pre-defined, non-predefined, user-defined)

DECLARE --  Predefined exception information does not declare an exception V_empno emp.empno%TYPE:=&empno; V_sal Emp.sal%TYPE; BEGIN SELECT sal into v_sal from emp WHERE empno=V_empno; IF V_sal<=1500Then UPDATE emp SET sal=sal+100 WHERE empno=V_empno; Dbms_output. Put_Line (' Encode as ' | | v_empno| | ' Employee pay has been updated! '); ELSE Dbms_output. Put_Line (' Encode as ' | | v_empno| | ' Employee pay has exceeded the specified value! ');  END IF; END; -------------------------------------------------------------------------------------------------------- user-defined exception DECLARE v_empno emp.empno%TYPE:=&empno; --Declaring variable v_empno, type emp.empno%The type is  consistent with the EMP table empno column type no_result EXCEPTION; -- declares an exception named No_result BEGIN UPDATE emp SET sal=sal+100 WHERE empno=V_empno; IF SQL%NOTFOUND then RAISE no_result;--Trigger exception                      -- raise trigger END IF; EXCEPTION when No_result then-- When an exception is triggered dbms_output. Put_Line (' Your data UPDATE statement failed! '); When OTHERS Then-- can be understood as exception packets, which receive exception dbms_output beyond the definition exception . Put_Line (SQLCODE| | ' ---' | |SQLERRM); END; --------------------------------------------------------------------------------------------------------- non-pre-defined exception --Define exception conditions in the Declarations section of the PL/SQL block: --< anomalies > EXCEPTION; --to associate its defined anomalies with standard Oracle errors, using the Exception_init statement--pragma Exception_init (< exception, < error code >);--In pl/The  exception handling section of the SQL block handles the exception situation accordingly DECLARE V_deptno Dept.deptno%TYPE:=&Deptno; Deptno_remaining EXCEPTION;--Declaring exceptions---PRAGMA exception_init (deptno_remaining,-2292);/*-2292 is an error code that violates the consistency constraint*/                          --Abnormal situation       --oracle Error code/*deptno_remaining exception is triggered if a 2292 error occurs in Oracle statement execution*/    ---BEGIN DELETE from dept WHERE Deptno=V_deptno; EXCEPTION when Deptno_remaining and then Dbms_output. Put_Line (' Violate data integrity constraints! '); When OTHERS and then Dbms_output. Put_Line (SQLCODE| | ' ---' | |SQLERRM); END; -------------------------------------------------------------------------------------------------------

Exceptions in database (pre-defined, non-predefined, user-defined)