CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
soulchef85
soulchef85

Reputation: 1

ELSE Statement Not Printing

I have a stored procedure that searches if a customer ID exists in a database. The procedure has two parameters, the customer ID and a number to indicated 1 if it was found and 2 if it is not. I am using and ELSE statement to print to screen is the ID is found or not. The valid ID I enter prints a valid message, but the invalid (non-existing ID) I enter does not. My ELSE put_line in my else block is not being executed(printed). Here is the procedure and the two function calls I make to it using a valid ID and an invalid ID. Why won't the put_line in the ELSE block print?

    CREATE OR REPLACE PROCEDURE find_customer(cus_customer_id IN NUMBER, found OUT NUMBER) IS
cus_id NUMBER;
BEGIN
SELECT customer_id INTO cus_id FROM customers WHERE customer_id = cus_customer_id;
IF (cus_id != cus_customer_id)
THEN
found:=0;
dbms_output.Put_line('This Customer ID does not exist!');
ELSE
found:=1;
dbms_output.Put_line('This Customer ID (' || cus_id ||'} was found!');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
found:=0;
WHEN OTHERS THEN
dbms_output.Put_line('error');
END;

-- FUNCTION CALL -- Find Customer Existing 319--

DECLARE
fnd NUMBER;
id NUMBER:= 319;
BEGIN
find_customer(id, fnd);
dbms_output.Put_line(fnd);
END;

-- FUNCTION CALL -- Find Customer Non-Existing 320--

DECLARE
fnd NUMBER;
id NUMBER:= 320;
BEGIN
find_customer(id, fnd);
dbms_output.Put_line(fnd);
END;

This is the output I am currently getting:

Procedure FIND_CUSTOMER compiled

This Customer ID (319) was found! 1

PL/SQL procedure successfully completed.

0

PL/SQL procedure successfully completed.

Upvotes: 0

Views: 85

Answers (1)

MT0
MT0

Reputation: 168416

You are trying to find a row that does not exist and a NO_DATA_FOUND exception is thrown and then execution of the main block terminates and switches to executing the EXCEPTION block so the IF statement is never reached.

What you appear to want is:

CREATE PROCEDURE find_customer(
  cus_customer_id IN  CUSTOMERS.CUSTOMER_ID%TYPE,
  found           OUT NUMBER
) IS
  cus_id NUMBER;
BEGIN
  SELECT customer_id
  INTO   cus_id
  FROM   customers
  WHERE customer_id = cus_customer_id;

  found:=1;
  dbms_output.Put_line('This Customer ID (' || cus_id ||'} was found!');
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    dbms_output.Put_line('This Customer ID does not exist!');
    found:=0;
END;
/

Note: catching OTHERS in the exception handler is considered bad practice as it will hide all errors and you will not know when or why they happen. What you want to do is catch the expected exceptions and then, unless you have a very good reason not to, let the other exceptions cause a failure that you can then debug and fix.

db<>fiddle here

Upvotes: 2

Related Questions