EXPERT RESPONSE
Continued from page 1.
If you can't use CASE in PL/SQL, you can:
Use IF ... ELSIF ...
If you have to use Oracle 8.1 (or earlier), PL/SQL's IF ... ELSIF ... provides the functionality of a searched CASE expression, using very similar syntax. To save space, the remaining examples will not show all the comments.
CREATE OR REPLACE FUNCTION foo
(
in_val IN NUMBER -- Number to be categorized
)
RETURN PLS_INTEGER
IS
return_val PLS_INTEGER;
BEGIN
IF in_val < 350 THEN return_val := 0;
ELSIF in_val <= 730 THEN return_val := 1;
ELSIF in_val <= 740 THEN return_val := 2;
ELSIF in_val <= 760 THEN return_val := 3;
ELSIF in_val > 760 THEN return_val := 0;
ELSIF in_val IS NULL THEN return_val := -1;
ELSE return_val := 0;
END IF;
RETURN return_val;
END foo;
/
If you're unfamiliar with ELSIF, it's just a cleaner way of writing nested
IF statements. The two examples below produce the same results:
-- Using nested IF
IF in_date < (SYSDATE - .5)
THEN
y_val := 1;
y_text := 'Past'
ELSE
IF in_date < (SYSDATE + .5)
THEN
y_val := 2;
y_text := 'Present';
ELSE
y_val := 3;
y_text := 'Future';
END IF;
END IF;
|
-- Using ELSIF
IF in_date < (SYSDATE - .5)
THEN
y_val := 1;
y_text := 'Past'
ELSIF in_date < (SYSDATE + .5)
THEN
y_val := 2;
y_text := 'Present';
ELSE
y_val := 3;
y_text := 'Future';
END IF;
|
Use a SQL View
You can't use CASE in PL/SQL 8.1, but you can reference a view that uses it. You might be able to work around your problem like this:
CREATE OR REPLACE VIEW b_plus
AS
SELECT a,
CASE
WHEN a < 350 THEN 0
WHEN a BETWEEN 350 AND 730 THEN 1
WHEN a BETWEEN 731 AND 740 THEN 2
WHEN a BETWEEN 741 AND 760 THEN 3
WHEN a > 760 THEN 0
WHEN a IS NULL THEN -1
ELSE 0
END AS a_minus
FROM b;
Your PL/SQL code can use this view instead of the base table. I find a certain perverse pleasure in this solution. Often I've needed a view that included some value that was difficult to calculate in SQL, so I wrote a PL/SQL function to use in the view. Here we're doing just the opposite.
By the way, it would be infintesimally faster not to test for a < 350 and a > 760, since they yield the default value anyway. I like the way you wrote the CASE statement because it's easy to understand and maintain, but be aware that the following produces the same results:
CASE
-- WHEN a < 350 THEN 0 -- Handled by ELSE
WHEN a BETWEEN 350 AND 730 THEN 1
WHEN a BETWEEN 731 AND 740 THEN 2
WHEN a BETWEEN 741 AND 760 THEN 3
-- WHEN a > 760 THEN 0 -- Handled by ELSE
WHEN a IS NULL THEN -1
ELSE 0
END
Use DECODE
DECODE can do anything that CASE can do. I don't recommend DECODE for this problem, but you keep it in mind for other instances of this problem, especially if they involve discrete values or regular intervals between ranges.
|
