CODEWITHSUNDEEP
oracle-databasecursor
Xasthur
Xasthur

Reputation: 23

Using default value to procedure in oracle

For this procedure, if i pass 1 and 2 separately, it should give me two different results. But if I do not pass any parameter, proc should return data of both parameters 1 and 2.

The problem is getting data for default NULL value. How can i do this ? should i use ref cursors ? or is there any way I can put a if/case condition in 'where' clause ? -- 1 for partitioned, 2 for non-partitioned tables, NULL as default

create or replace
PROCEDURE stats(p_run_type IN NUMBER DEFAULT NULL ) IS
    ...

BEGIN
    FOR c IN
       (  SELECT  exe_order
                 ,table_name
                 ,exec_gather_stat
          FROM   view_stats
          WHERE  exe_order = p_run_type             
         )
  LOOP
   BEGIN
<..do insert..>

Upvotes: 0

Views: 190

Answers (2)

Alex Poole
Alex Poole

Reputation: 191380

As an alternative to the nvl, you could also do:

WHERE (p_run_type IS NULL OR exe_order = p_run_type)

This may generate a better (or at least different) plan if exe_order is indexed.

Upvotes: 1

Emmanuel
Emmanuel

Reputation: 14209

A nvl should do the trick:

...
WHERE  exe_order = nvl(p_run_type, exe_order)
...

This will take all possible values of exe_order if p_run_type is null.

Upvotes: 2

Related Questions