CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Rita
Rita

Reputation: 1237

Oracle PL/sql if input varchar param is empty, how to return all the records?

I have below query. If the input parameter is not null and passed a value, it will return all the matching records for that URL. if the Input parameter is empty, it should return all the records. How to fix that Update condition?

CREATE OR REPLACE PROCEDURE GetDatesList (
p_URL               IN varchar2,
P_RECORDSET         OUT SYS_REFCURSOR 
)
as

begin

OPEN P_RECORDSET FOR
select 
      PCBS.KeyId as Key, PCBS.PublishDate
from (select 
      Serlog.*, PS.ServerType, row_number() over (partition by Serlog.KeyId order by Serlog.PublishDate desc) as RowNu
      from PConfigByServerLog Serlog 
      join PServer PS on PS.ServerName = Serlog.ServerName 
      and Serlog.URL = PS.URL
      where Serlog.URL = p_URL
      --Here if we pass a value f p_podURL, we get those matching records back. If it is empty, then it should bring all the records back 
      ) PCBS 
where PCBS.RowNu = 1 and PCBS.IsActive = 'T';

end;

Upvotes: 0

Views: 1871

Answers (3)

Matthew McPeak
Matthew McPeak

Reputation: 17944

 where Serlog.URL = p_URL OR p_URL is null

or

 where Serlog.URL = nvl(p_URL, Serlog.URL)

Note about performance

One of the other answers warned about performance.

If Serlog.URL is indexed, Oracle will be smart enough to use it if p_URL is not null.

Consider a SQL like this:

SELECT * FROM sys.obj$
where obj# = nvl(:b1, obj# )

The plan would be:

7 SELECT STATEMENT
    6 CONCATENATION  
        2 FILTER  
            1 TABLE ACCESS FULL SYS.OBJ$
        5 FILTER  
            4 TABLE ACCESS BY INDEX ROWID SYS.OBJ$
                3 INDEX RANGE SCAN SYS.I_OBJ1

The two filter operations (#2 and #5) are :b1 is null and :b1 is not null, respectively. So, Oracle will only execute whichever branch of the plan make sense, depending on whether the parameter has been given.

Upvotes: 3

Gary_W
Gary_W

Reputation: 10360

You could also set a default value for p_URL in the procedure definition if that is appropriate:

CREATE OR REPLACE PROCEDURE GetDatesList (
p_URL               IN varchar2 DEFAULT '//svr1/dir1/folder2/etc',
P_RECORDSET         OUT SYS_REFCURSOR 
)
as

So if NULL is passed in this value will be used instead.

Upvotes: 0

Carlo Sirna
Carlo Sirna

Reputation: 1251

Just write

     where (Serlog.URL = p_URL) OR p_URL is null

Instead of 

       where Serlog.URL = p_URL

Keep in mind that this would give you suboptimal ececution plan: if the url parameter is indexed it might not use the index.

If performance is something to be concerned, you'd better handle separately the two cases with two different cursors

Upvotes: 2

Related Questions