Re: Help: Dynamic where clause in a PL/SQL proced
Date: 1996/11/05
Message-ID: <55npnt$39j_at_hardcopy.ny.jpmorgan.com>#1/1
In article 185D_at_cs.wmich.edu, Muhammad Ahmad Malik <mamalik_at_cs.wmich.edu> () writes:
> I want to pass as a variable the "where clause" for a cursor in the
> procedure. There must be someway to handle this. Unfortunatly I don't
> have the manuals.
Stored Procedures gain most of their performance from the ability of the database to parse, select a query plan, and store them BEFORE runtime. They most likely need the "where clause" to decide which indices to use, which tables to join first, etc. So even if you get around the restrictions, you aren't going to gain much by having a dynamic "where clause." Plus, a big reason programmers like stored procedures is that it gives you the ability to see how the database is going to do its processing, and say "No, please do it this way" instead, by changing the where clause, etc. You'd be shocked at how often the databases select strange ways of performing an operation.
Now, if your where clause can be narrowed down to several choices, why not put all of them in one or more stored procedures and send in a parameter telling the proc which cursor to use? Ain't pretty or elegant, but works.
Good luck,
Mark McNulty
JYACC Consultant
mmcnul_at_jyacc.com
mmcnul_at_quadris.com
Note- these views are mine and not those of JYACC, Quadris, or JP Morgan. Received on Tue Nov 05 1996 - 00:00:00 CET