Re: Dynamic query in stored procedure
Date: Tue, 14 Oct 2008 09:02:25 -0700 (PDT)
Message-ID: <de80e440-5eec-4654-91c5-bf3830dea9bf@k36g2000pri.googlegroups.com>
On Oct 13, 10:57 pm, Tim Mickelson <tim_mickel..._at_hotmail.com> wrote:
> Hi
>
> This question is about stored procedures. I know i can make a query
> such as:
>
> query := SELECT A, B, C FROM USERS WHERE NAME = :a AND SURNAME = :b
> OPEN my_cursor FOR query USING var_a, var_b
>
> But my question is, if I don't know how many parameters I have in the
> where part, how do I do? I do not want to concatenate part by part.
>
> E.g.
>
> query := SELECT A, B, C FROM USERS WHERE 1 > 0
> IF(var_a IS NOT NULL) THEN
> query := query || ' AND NAME = :name'
> END IF;
>
> ....
>
> How do I open this query, I don't know how many params I have?
If the cursor is only going to get used once in the configuration (column names, values), you may want to consider just creating the entire where clause based on the values themselves:
IF var_a IS NOT NULL THEN
qry := qry || ' AND naym = ''' || var_a || '''';
END IF:
....
OPEN my_cursor FOR qry;
If binding is optimal, try this (there may be better ways of doing this, but I did something like this in the past).
You can create a collection:
TYPE rec_var IS RECORD ( col_name VARCHAR2(30),
col_val VARCHAR2(255) ); -- or whatever
max length is...
TYPE tbl_var IS TABLE OF rec_var INDEX BY BINARY_INTEGER; var_tbl tbl_var
Add to the collection based on not null
IF var_a IS NOT NULL THEN
var_tbl(var_tbl.count).col_name := 'NAYM'; --reserved word friendly NAME
var_tbl(var_tbl.last).col_val := var_a;
END IF;
...
FOR i IN var_tbl.first .. var_tbl.last LOOP
qry := qry || ' AND ' || var_tbl(i).col_name || ' = :var_' || i;
END LOOP; Then open the cursor based on tbl_var.count:
CASE var_tbl.count
WHEN 1 THEN OPEN my_cursor FOR qry USING var_tbl(0);
WHEN 2 THEN OPEN my_cursor FOR qry USING var_tbl(0),
var_tbl(1);
...
END CASE; Of course, things get more tedious when dealing with date variables, etc, but you get the point. Also, watch out for when the filter being desired is null? IE. ....AND nayme IS NULL. Received on Tue Oct 14 2008 - 11:02:25 CDT