"PL/SQL Practice" custom exceptions

Defined by the user himself
1, define the exception name, type exception
2, cite the exception (raise Excepttion)
3. Catching exceptions

Sql> Declare  2E_user_err exception; 3V_deptno Dept.deptno%Type:= &No; 4  begin  5       UpdateDeptSetLoc='HongKong' whereDeptno=V_deptno; 6    7       ifSql%NotFound Then  8raise E_user_err; 9       End if; Ten       Commit;  OneException A         whenE_user_err Then  -Dbms_output.put_line ('This department'||V_deptno|| 'is isn't in dept table, please input correct number!');  -  End;

Non-predefined exceptions, create exception captures with Oracle ORA-XXXX error codes!

-------Violate the referential constraintsDeclareE_emp_remain exception; pragma exception_init (e_emp_remain,-2292); V_deptno Dept.deptno%Type:=&No; begin     Delete  fromDeptwhereDeptno=V_deptno; Commit; Exception whenE_emp_remain ThenDbms_output.put_line ('Don"'T Remove this'||V_deptno||'Department!'||', this record was in EMP!');  whenOthers ThenDbms_output.put_line ('Others Error!'); End;

Sql> Declare  2E_emp_remain exception; 3pragma exception_init (E_emp_remain,-2291); 4    5V_empno Emp.empno%Type:=&N; 6V_deptno Emp.deptno%Type:=&nn; 7    8    begin  9       UpdateEmpSetDeptno=V_deptnowhereEmpno=V_empno;Ten       Commit;  OneException A        whenE_emp_remain Then  -Dbms_output.put_line ('Department is NOT exists!');  -        whenOthers Then  theDbms_output.put_line ('Others Error!');  -   End;

Get error codes and error messages in others:

Sqlcode:oracle error code sqlerrm:oracle error message

Sql> Declare   2V_code Errors.code%type; 3V_msg errors.msg%type; 4V_sal Emp.sal%type; 5      6      begin   7           8     SelectSal intoV_sal fromEmpwhereDeptno=&No; 9       TenException One       whenNo_data_found Then   ADbms_output.put_line ('No This department ID');  -       whenOthers Then   -Dbms_output.put_line ('Others Error');  theV_code:=Sqlcode;  -V_msg:=SQLERRM;  -Dbms_output.put_line (v_msg);  -                  +     Insert  intoErrorsValues(V_CODE,V_MSG);  -     Commit;  +  End;

To create a non-predefined exception from the captured code:

Sql> Declare  2E_emp_err exception; 3pragma exception_init (E_emp_err,-1422); 4    5V_sal Emp.sal%type; 6    7    begin  8       SelectSal intoV_sal fromEmpwhereDeptno=&No; 9       Commit; TenException One        whenE_emp_err Then  ADbms_output.put_line ('Return rows more than one row!');  -        whenOthers Then  -Dbms_output.put_line ('Others Error!');  the   End;

"PL/SQL Practice" custom exceptions