1

Topic: How to generate beautifully request.

Greetings to all.
There is such task:
Procedure on which input the dial-up of parameters moves, should return result of request. Passed-in parameters as a matter of fact it is conditions for where. I.e. it is necessary to generate dynamic request which will use passed-in parameters (parameters or have values or empty) as  variables.
Such variants as concatenation and usage case with a heap of variants of request is not desirable. Can eat means oracle implementing such formation of request?

2

Re: How to generate beautifully request.

orcl_1989;
p_id - The entering parameter.

Where (id = p_id or p_id is null)

Dynamic requests in inept hands potential sql injection

3

Re: How to generate beautifully request.

p_id - The entering parameter.
Where (id = p_id or p_id is null)
If parameter null in a condition where it should not be at all.

4

Re: How to generate beautifully request.

orcl_1989;
If you correctly read written above the bracket always produces True if p_id is null - and as consequence expression in brackets will not influence request (as though it and is not present at all)

5

Re: How to generate beautifully request.

MaximaXXL;
Present that parameters of tens. Also present as indexes will be used at such spelling?
orcl_1989
pipelined functions in which the request if I correctly understood column collection is dynamic under construction and fulfilled does not change. Therefore pipe transits without problems.
The second variant ref cursor returned to the client.

6

Re: How to generate beautifully request.

At usage Where (id = p_id or p_id is null)
The request is fulfilled longer on the order, on with an alignment with request where conditions are specified rigidly.

7

Re: How to generate beautifully request.

orcl_1989, generally it is possible to write for the correct usage of indexes

select * from table where p1_val = p1 and p1 is not null
union all
select * from table where p2_val = p2 and p2 is not null

And in this case the task of the made decision has no.

8

Re: How to generate beautifully request.

There was such idea, but refused.
I esteem about pipelined functions, can so it turns out.

9

Re: How to generate beautifully request.

orcl_1989 wrote:

at usage Where (id = p_id or p_id is null)
The request is fulfilled longer on the order, on with an alignment with request where conditions are specified rigidly.

Since the task  about  a horse in vacuum, try so

Where id = nvl (p_id, id)

10

Re: How to generate beautifully request.

orcl_1989 wrote:

Greetings to all.
There is such task:
Procedure on which input the dial-up of parameters moves, should return result of request. Passed-in parameters as a matter of fact it is conditions for where. I.e. it is necessary to generate dynamic request which will use passed-in parameters (parameters or have values or empty) as  variables.
Such variants as concatenation and usage case with a heap of variants of request is not desirable. Can eat means oracle implementing such formation of request?

Collect WHERE somewhere outside where empty parameters do not get to an array
Also transfer at once WHERE