DECODE is an old function that has been replaced by the easier to understand and more common CASE. Unlike
DECODE, CASE may also be used directly within PL/SQL.
Noncompliant code example
SET SERVEROUTPUT ON
DECLARE
operand CHAR(1) := 'B';
l_result PLS_INTEGER;
BEGIN
-- Noncompliant
SELECT DECODE(operand, 'A', 1
, 'B', 2
, 'C', 3
, 'D', 4
, 'E', 5
, 'F', 6
, 7)
INTO l_result
FROM dual;
DBMS_OUTPUT.PUT_LINE('l_result = ' || l_result); -- 2
END;
/
Compliant solution
SET SERVEROUTPUT ON
DECLARE
operand CHAR(1) := 'B';
l_result PLS_INTEGER;
BEGIN
l_result := CASE operand
WHEN 'A' THEN 1
WHEN 'B' THEN 2
WHEN 'C' THEN 3
WHEN 'D' THEN 4
WHEN 'E' THEN 5
WHEN 'F' THEN 6
ELSE 7
END;
DBMS_OUTPUT.PUT_LINE('l_result = ' || l_result); -- 2
END;
/
Exceptions
No issue is raised when DECODE contains only one case (i.e. only one search and one result components)
because using CASE would make the code less readable in this scenario.
