I have a procedure, based on the parameters, the where condition would differ.
The OUT parameters have to be INTO clause so that I can return the columns from the procedure.
Rather than having SQL condition for each if condition, what is the efficient way of doing this.
It looks to me more like a design question. So it depends on what you need to achieve and how you want to organize your code.
1- your "if" chain of queries in the same procedure
2- one procedure for each query
3 - if the differencies between the "where" parts are not so big use sql constructs, including unions, case , and\or etc to let coexist different cases in one query
4- build sql dynamically and use execute immediate
Usually i don't like the 1, i would try with 3 or 4, then proceed with 2 if i can't.
With dynamic sql, for getting out results you can do
EXECUTE IMMEDIATE stmt into o_total_count,o_total_sum,o_hold_status,o_normal_status;
In case you have input params for the query, you have to mark them with : and then add the USING clause with the appropriate input param.
EXECUTE IMMEDIATE 'select count(*) from departments where department_id=:id' INTO l_cnt USING l_dept_id;