PL SQL + SQL simple statement using substitution variables

Question

I want to create a simple IF THEN ELSE PL SQL without creating an annonymous block like below for use in SQL TOOLS:

variable a varchar2(20) 
variable b varchar2(20) 

EXEC :a := '&insert_value_a'
EXEC :b := '&insert_value_b'

--this next part I don't know how to do:

IF value :a is given and it's not NULL THEN

EXECUTE
SELECT column1,column2,column3 
FROM table
WHERE condition
AND column2 = :a or column3 = :a;

ELSE

-- if value b is given use it to get value :a

EXECUTE
SELECT column2 INTO :a 
FROM table
WHERE column_x = :b;

SELECT column1,column2,column3 
    FROM table
    WHERE condition
    AND column2 = :a or column3 = :a;
END;

score 0 · Answer 1 · answered Sep 16 '15 at 16:16

Try using NVL, something like this:

SELECT column1,column2,column3 
  FROM table
 WHERE condition
   AND column2 = NVL(:a,SELECT column2
                          FROM table
                         WHERE column_x = :b)
    or column3 = NVL(:a,SELECT column2
                          FROM table
                        WHERE column_x = :b);

PL SQL + SQL simple statement using substitution variables

1 Answers1