Dynamic query in stored procedure
Tim Mickelson
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?
sri...@hotmail.com
DA Morgan
Looking at your table and column names I am led to suggest you learn
the following query:
SELECT keyword
FROM gv$reserved_words;
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Vince
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.
