InterviewSolution
Saved Bookmarks
InterviewSolution
| 1. |
How to handle exceptions in PL/SQL? |
|
Answer» Like any other language, PL/SQL also provides a functionality to handle exceptions. This is done using the EXCEPTION block. To raise exceptions explicitly, use the RAISE command. To handle exceptions in PL/SQL, here is the syntax: DECLARE <declarations section> BEGIN <executable command(s)> EXCEPTION <exception handling > WHEN exception1 THEN exception1-handling-statements WHEN exception2 THEN exception2-handling-statements WHEN exception3 THEN exception3-handling-statements ........ WHEN others THEN exception-handling-statements END;Above, WHEN is used for exceptions like: WHEN no_data_found THENWith that, the following is to be used for default exception: WHEN others THENBefore beginning our example, LET’s say we have the following table: <DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+The following is our example wherein we are handling exceptions. The dept DECLARE d_id deptbudget.deptid%type := 20; d_ name deptbudget.deptname%type; d_loc deptbudget.deptloc%type; BEGIN SELECT deptname, deptloc INTO d_name, d_loc FROM deptbudget WHERE deptid = d_id; DBMS_OUTPUT.PUT_LINE ('Name: '|| d_name); DBMS_OUTPUT.PUT_LINE ('Address: ' || d_loc); END IF; EXCEPTION WHEN no_data_found THEN dbms_output.put_line('No such department in the company!'); WHEN others THEN dbms_output.put_line('Error!'); END; /The output: No such department in the company |
|