Exception Handling for Oracle series

Refer to table structure and data for the processing of tables Oracle Build table interpolation data and more

Use the SELECT INTO statement to read a line of Tb_employee and use exception handling to handle system-predefined exceptions for No_data_found and Two_many_rows

SetServeroutput on;
DeclareEMP Tb_employee%RowType;begin
Select *  intoEmp fromTb_employeewhereEname= 'SCOTT'; Dbms_output.put_line ('SCOTT"'s SAL is:' ||emp.sal); Exception whenNo_data_found ThenDbms_output.put_line ('no data found exception'); End;
/

DeclareEMP Tb_employee%RowType;begin
Select *  intoEmp fromTb_employeewhereDeptno= -; Dbms_output.put_line ('The Sal is:' ||emp.sal); Exception whenToo_many_rows ThenDbms_output.put_line ('too many rows exception'); End;
/

Using nested exception-side processing, iterate through the employees between Emp_id_minval and emp_id_maxval so that there is no employee number. Output of the employee's ename, non-existent employees output "not exists such empolyee".

Declareemp_id tb_employee.pk_employee_id%type; V_ename tb_employee.ename%type; Emp_id_minval tb_employee.pk_employee_id%type; Emp_id_maxval tb_employee.pk_employee_id%type;beginEmp_id_minval:=&emp_id_minval; Emp_id_maxval:=&Emp_id_maxval; foremp_idinchEmp_id_minval. Emp_id_maxval Loopbegin
SelectEname intoV_ename fromTb_employeewherepk_employee_id=emp_id; Dbms_output.put_line ('ename:'||v_ename); Exception whenNo_data_found ThenDbms_output.put_line ('Not exists such empolyee'); End;
EndLoop;End;
/

Write a sample program and exception handling for system exceptions that handle ora-01400 (cannot insert null values)

Declareinsert_excep exception; pragma exception_init (insert_excep,-01400); begin
Insert  intotb_department (pk_department_id, dname)Values( -,NULL); Exception whenInsert_excep ThenDbms_output.put_line ('INSERT Operation FAILED');
Dbms_output.put_line (SQLERRM); End;
/

Use SQLCODE,SQLERRM to get exception numbers and error messages, and to output exception numbers and error messages

DeclareEMP Tb_employee%RowType; Error_code Number; Error_messagevarchar2(255); begin
Select *  intoEmp fromTb_employeewhereEname= 'SCOTT'; Dbms_output.put_line ('SCOTT"'s salary is:' ||emp.sal); Exception whenToo_many_rows ThenError_code:=SQLCODE; Error_message:=sqlerrm; Dbms_output.put_line (Error_code|| ' ' ||error_message); End;
/

Customize an exception, throw the exception, and handle

Declareinvalid_department exception; namevarchar2( -) := '&name'; Deptno Number:= &Deptno;begin
Updatetb_departmentSetDname=namewherepk_department_id=Deptno;ifSql%NotFound Thenraise invalid_department;End if; exception whenInvalid_department ThenDbms_output.put_line ('No Such department'); End;
/

Throw an app exception using Raise_application_error

Declarev_ename Tb_employee.ename%Type='&v_ename'; e_name exception; pragma exception_init (E_name,-20999); begin
Delete  fromTb_employeewhereEname=V_ename;ifSql%NotFound ThenRaise_application_error (-20999,'This is a valid ename'); End if; exception whenE_name ThenDbms_output.put_line ('The ename'||V_ename||'exists, please choose again'); End; /

Exception Handling for Oracle series