Handle exceptions on procedures PL/SQL

Question

Hi I would like to handle exceptions on my procedures to make it more useful.

My procedure adding occupations and it's wage, here is table:

CREATE TABLE Occupations(
id_oc NUMBER(2),
Name_oc VARCHAR2(20) CONSTRAINT OC_name_cc NOT NULL ,
Min_wage NUMBER(7,2) CONSTRAINT OC_min_wg NOT NULL CHECK(Min_wage>100),
CONSTRAINT Oc_id_pk PRIMARY KEY (id_oc),
CONSTRAINT OC_na UNIQUE (Name_oc)
);

Here is the procedure

create or replace procedure zad_2a(name varchar2, min_wage number)
  is
  isFound occupations.name_oc%TYPE;
  cursor c1
  is
  select name_oc from occupations where name_oc = name;
  begin
    open c1;
    fetch c1 into isFound;
    if c1%found then dbms_output.put_line('Occupation already exists');
    else insert into occupations values(Seq_Occupations.NEXTVAL, name, min_wage);
    end if;
    close c1;
  end;
  /

This procedure should have following exceptions:

• When I set wage fewer than 100 and more than 5000
• While I add bad occupation name - using numbers instead of characters
• handling other unexcepted errors

How to do this?

Answer 1

You can try as below and modify as per your requirements.

CREATE OR REPLACE PROCEDURE zad_2a (ename VARCHAR2, min_wage NUMBER)
IS
   isfound              occupations.name_oc%TYPE;
   e_custom_exception   EXCEPTION;
   PRAGMA EXCEPTION_INIT (e_custom_exception, -20001);
   l_count_int          NUMBER;

   CURSOR c1
   IS
      SELECT name_oc
        FROM occupations
       WHERE name_oc = ename;
BEGIN
   IF (min_wage < 100 OR min_wage > 5000)
   THEN
      RAISE e_custom_exception;
   END IF;

   SELECT COUNT (*)
     INTO l_count_int
     FROM DUAL
    WHERE REGEXP_LIKE (ename, '[[:digit:]]');

   IF (l_count_int > 0)
   THEN
      RAISE e_custom_exception;
   END IF;

   OPEN c1;

   FETCH c1 INTO isfound;

   IF c1%FOUND
   THEN
      DBMS_OUTPUT.put_line ('Occupation already exists');
   ELSE
      INSERT INTO occupations
           VALUES (Seq_Occupations.NEXTVAL, ename, min_wage);
   END IF;

   CLOSE c1;
EXCEPTION
   WHEN e_custom_exception
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
   WHEN OTHERS
   THEN
      RAISE;
END;
/