1.

How do we use BETWEEN operation in PL/SQL?

Answer»

The RAISE statement is used in PL/SQL to raise exceptions. Through this a user can raise exceptions explicitly.

The following is the SYNTAX to display how to use raise exceptions with RAISE statement:

DECLARE   exception_name EXCEPTION; BEGIN   IF condition THEN      RAISE exception_name;   END IF; EXCEPTION   WHEN exception_name THEN   statement; END;

An EXAMPLE can be if the ID is less than zero, the raise an exception:

IF d_id <= 0 THEN      RAISE my_exception;

Above “my_exception” is the following we set above:

my_exception EXCEPTION;

Let us see an example wherein we are raising exceptions. The following is the table we are taking into consideration:

<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      | +----------+------------------+------------------+--------------+

Let’s say we created the following procedure and a user-defined exception in it:

DECLARE   id deptbudget.id%type := &d_id;   deptname deptbudget.deptname%type;   deptloc deptbudget.deptloc%type;     my_exception  EXCEPTION; BEGIN   IF d_id <= 0 THEN      RAISE my_exception;   ELSE      SELECT  deptname, deptloc INTO deptname, deptloc      FROM deptbudget      WHERE id = d_id;      DBMS_OUTPUT.PUT_LINE ('Name: '||  deptname);      DBMS_OUTPUT.PUT_LINE ('Address: ' || deptloc);   END IF; EXCEPTION   WHEN my_exception THEN      dbms_output.put_line('ID is always greater than zero!');   WHEN no_data_found THEN      dbms_output.put_line('No such department in the company!');   WHEN others THEN      dbms_output.put_line('Error!');   END; /

On executing the above procedure, the following output is visible. Let’s say our input is department id as “-2”, yes, a negative number. When execution, it raises the following user-defined error:

ID is always greater than zero!


Discussion

No Comment Found

Related InterviewSolutions