EXCEPTION CASE_NOT_FOUND

Oracle function Case
Oracle case



SET SERVEROUT ON

DECLARE

p BINARY_INTEGER:= 0;
q BINARY_INTEGER := 0;

BEGIN

BEGIN
CASE
WHEN p>100 THEN q:= 1;
WHEN p>50 THEN q:= 2;
WHEN p>10 THEN q:= 3;
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
q := 5;
END;

DBMS_OUTPUT.PUT_LINE('q = ' || q);

END;

/



case for null


SET SERVEROUT ON

DECLARE

p BINARY_INTEGER:= null;
-- p BINARY_INTEGER:= 0;
q BINARY_INTEGER := 0;

BEGIN

BEGIN
CASE
WHEN p is null then q:=10000;
WHEN p>100 THEN q:= 1;
WHEN p>50 THEN q:= 2;
WHEN p>10 THEN q:= 3;
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
q := 4;
END;

DBMS_OUTPUT.PUT_LINE('q = ' || q);

END;

/