CODEWITHSUNDEEP
sqloracle-databaseplsql
Lova Chittumuri
Lova Chittumuri

Reputation: 3313

How to generate the dynamic where condition in pl/sql cursor

Generate the dynamic where condition in pl/sql cursor based on input values

For Example :

Input Values : a b c values

Query Format :

CREATE OR REPLACE Procedure abcprocedure
   ( a IN Number,b IN Number, c IN Number )

    cursor abccursor
        IS
           select 1  
             from pqr  p 
            where ( Prepare the where condition based on a,b,c values and null checks also )

Upvotes: 0

Views: 917

Answers (2)

Littlefoot
Littlefoot

Reputation: 142788

This is how I understood the problem; no dynamic stuff is necessary.

For simpler testing (on my side, as I don't have your tables), on Scott's DEPT table, this might do the job (par_x is name of procedure's parameter).

select *
from dept
where deptno in (par_a, par_b, par_c)
   or (par_a is null and par_b is null and par_c is null);

If those are different columns, no problem either:

select * 
from dept
where (deptno = par_a or par_a is null)
  and (dname  = par_b or par_b is null)
  and (loc    = par_c or par_c is null);

If certain parameter isn't passed and you don't want to see that column's value, then use

select case when par_a is not null then deptno
            else null
       end deptno,
       --
       case when par_b is not null then dname
            else null
       end dname,
       --
       case when par_c is not null then loc
            else null
       end loc
from dept
where (deptno = par_a or par_a is null)
  and (dname  = par_b or par_b is null)
  and (loc    = par_c or par_c is null);

If you want to exclude the column from result set, well, that isn't that simple. If you used (for example) Oracle Apex, you could choose not to render that column. Otherwise, in pure SQL, I wouldn't know how to do that.

Upvotes: 2

Popeye
Popeye

Reputation: 35910

According to your comments, I should suggest using coalesce in where condition:

SELECT *
FROM PQR P
WHERE COALESCE(A, DEPT1) = DEPT1
AND COALESCE(B, DEPT2) = DEPT2
AND COALESCE(C, DEPT3) = DEPT3;

Coalesce will take the passed parameter if it is not null. That way you can achieve the desired result. If a is null then dept1 = dept1 will be the condition which will be always true (if dept1 in your table is not null, That is altogether another scenario)

Cheers!!

Upvotes: 2

Related Questions