CODEWITHSUNDEEP
sqloracle-databaseplsql
AAA
AAA

Reputation: 13

if-else condition not working correctly in PL/SQL

I am writing a procedure to delete department from a table. It takes depatment id as argument and delete the department with given id. but it is not working correctly.When i didnot use EXCEPTION, it only give output when gien department id is present in table but if the id is not present in table it throw error. When i use exception, It did not check the if else condition. Here is my procedure

CREATE OR REPLACE PROCEDURE del_job(j_id number) IS
   jj_id  bb_department.iddepartment%type; 
BEGIN 
   SELECT IDDEPARTMENT 
   INTO jj_id
   FROM BB_DEPARTMENT
   WHERE IDDEPARTMENT=j_id; 
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
        jj_id := NULL;
   IF (jj_id=j_id) THEN 
      DELETE FROM BB_DEPARTMENT
      WHERE IDDEPARTMENT=j_id;
       dbms_output.put_line ('Job Deleted'); 
    ELSIF(jj_id=0) THEN
      dbms_output.put_line ('No Job Deleted'); 
   END IF; 
END; 
/

Upvotes: 0

Views: 1625

Answers (2)

Belayer
Belayer

Reputation: 14861

You are making things overly difficult. DELETE sets sql%rowcount to the number of rows processed. So there is no need to select the department; just delete the appropriate id. If you want a confirmation message then test sql%rowcount. If the row was deleted it will contain 1 (or greater), if the id did not exist it will contain 0. Print the appropriate message.

create or replace procedure del_job(j_id number) is 
begin 
    delete 
      from bb_department
     where iddepartment=j_id; 
      
     if sql%rowcount > 0 then 
        dbms_output.put_line ('Job Deleted'); 
     else 
        dbms_output.put_line ('No Job Deleted'); 
     end if; 
end del_job; 
/

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231651

Your indentation seems to imply that you want your if statement to be part of the normal flow rather than part of the exception block. But your actual code has the if statement in the exception handler. Since you're assigning a null to jj_id in the exception handler before running the if statement and null is never equal to nor unequal to any value, neither your if nor your elsif clause can ever be true so neither dbms_output call will be made.

Assuming your indentation shows your actual intent, my guess is that you want a nested PL/SQL block for the select statement and exception handler.

CREATE OR REPLACE PROCEDURE del_job(j_id number) IS
   jj_id  bb_department.iddepartment%type; 
BEGIN 
   BEGIN
      SELECT IDDEPARTMENT 
      INTO jj_id
      FROM BB_DEPARTMENT
      WHERE IDDEPARTMENT=j_id; 
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
        jj_id := NULL;
   END;

   IF (jj_id=j_id) THEN 
      DELETE FROM BB_DEPARTMENT
      WHERE IDDEPARTMENT=j_id;
       dbms_output.put_line ('Job Deleted'); 
    ELSIF(jj_id=0) THEN
      dbms_output.put_line ('No Job Deleted'); 
   END IF; 
END; 
/

Upvotes: 1

Related Questions