Friday, March 9, 2012

PL/SQL Help!

[Oracle 8.1.7][PL/SQL question...]
In a stored procedure, I want to open a cursor to return, for which the query will join a few tables, like so:
PROCEDURE ppp (
crsr IN OUT crsr_type, value1_in IN INT, value2_in IN INT
op_in IN varchar2) IS
BEGIN
OPEN crsr FOR
SELECT c1, c2, c3
FROM p, q, r
WHERE ...(join-conditions)...
AND (question below);
RETURN;
END;

There is one condition in my WHERE clause that will depend on an input parameter of the procedure, namely 'op_in' (operator), and depending on what op_in is, my AND part could be:

if (op_in = 'EQ')
AND p.col = value1_in
else if (op_in = 'GT')
AND p.col > value1_in
else if (op_in = 'BTW')
AND p.col BETWEEN value1_in AND value2_in

As you see, it is the operator of the where clause changes.

I tried using DECODE, but I couldn't get it to decode on an operator.
I tried to make the crsr query a dynamic sql string, but I think it's a 9i feature (OPEN crsr FOR dyn_sql_string).

The procedure only does this and returns out the cursor.

I am a newbie to Oracle myself, and wonder if there are ways I can get around with this?You can use dynamic SQL:

No comments:

Post a Comment