CODEWITHSUNDEEP
oracle-databaseplsql
user1838000
user1838000

Reputation: 275

Oracle procedure or function to return multiple values

I need to write a procedure or a function which returns the count of status, age and type which should satisfy the below criteria

select * from ABC
where ABC_id = 2001
and ABC_LEVEL_ID = 1 --status
and ABC_REQUEST_DATE < sysdate --age
and ABC_TYPE_ID = 5; --type

If ABC_ID = 2001 and ABC_LEVEL_ID = 1 
THEN return COUNT(STATUS)


If ABC_ID = 2001 and ABC_REQUEST_DATE < SYSDATE 
THEN return COUNT(AGE)


If ABC_ID = 2001 and ABC_TYPE_ID = 5
THEN return COUNT(TYPE)

All three values should be OUT parameters which are passed to front end application.

Upvotes: 1

Views: 2645

Answers (2)

Rahul
Rahul

Reputation: 77866

You can use a CASE expression to your query to include those constraint like

select *,
case when ABC_ID = 2001 and ABC_LEVEL_ID = 1  then COUNT(STATUS) else null end as testcol1,
case when ABC_ID = 2001 and ABC_REQUEST_DATE < SYSDATE  then COUNT(AGE) else null end as testcol2,
case when ABC_ID = 2001 and ABC_TYPE_ID = 5  then COUNT(TYPE) else null end as testcol3
from ABC
where ABC_id = 2001
and ABC_LEVEL_ID = 1 --status
and ABC_REQUEST_DATE < sysdate --age
and ABC_TYPE_ID = 5; --type

Per Comment: modified query (to include @jeffrykemps earlier edit)

select *,
case when ABC_LEVEL_ID = 1  then COUNT(STATUS) end as testcol1,
case when ABC_REQUEST_DATE < SYSDATE  then COUNT(AGE) end as testcol2,
case when ABC_TYPE_ID = 5  then COUNT(TYPE) end as testcol3
from ABC
where ABC_id = 2001
and ABC_LEVEL_ID = 1 --status
and ABC_REQUEST_DATE < sysdate --age
and ABC_TYPE_ID = 5; --type

Upvotes: 2

APC
APC

Reputation: 146229

I think it would make more sense if your WHERE clause uses OR operations rather than AND. It's easy to do the counts in the projection of the query, using CASE statements.

As the code belongs to a stored procedure you need to select into something. Here I've assumed direct assignment to the OUT parameters. However, if you code contains additional requirements you should populate local variables instead, and assign them to the OUT parameters at the end of the procedure.

create or replace procedure get_counts
     ( p_out_status_count out pls_integer
         , p_out_age_count out pls_integer
         , p_out_type_count out pls_integer
as
begin
    select 
        count (case ABC_LEVEL_ID = 1  then 1 else null end),
        count (case ABC_REQUEST_DATE < SYSDATE  then 1 else null end),
        count (case ABC_TYPE_ID = 5  then 1 else null end)
    into p_out_status_count
         , p_out_age_count
         , p_out_type_count
    from ABC
    where ABC_id = 2001
    and (ABC_LEVEL_ID = 1 --status
        or  ABC_REQUEST_DATE < sysdate --age
        or  ABC_TYPE_ID = 5); -- type
end get_counts;

Also you might want to paramterize the ABC_ID. In which case the procedure's signature might be:

create or replace procedure get_counts
     ( p_abc_id in abc.abc_id%type   
         , p_out_status_count out pls_integer
         , p_out_age_count out pls_integer
         , p_out_type_count out pls_integer
     )

and the WHERE clause would be 

    ....
    from ABC
    where ABC_id = p_abc_id
    ....

Upvotes: 2

Related Questions