Re: Dynamic query in stored procedure

From: Javier Montero <jmontero19_at_gmail.com>
Date: Tue, 4 Jun 2013 07:09:41 -0700 (PDT)
Message-ID: <18e837fa-334d-4fa7-95db-abfea1610113_at_googlegroups.com>

On Tuesday, October 14, 2008 10:02:25 AM UTC-6, Vince wrote:
> 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.

Thank U Vince for your idea Received on Tue Jun 04 2013 - 16:09:41 CEST