create or replace procedure fetch_ids(ename in varchar2,hiredate in date)
as
begin
select * from emp where empname=ename and join_date=hiredate ;
end;
Problem statement:
1)if i will not pass the ename, i need to fetch all the employees details.
2)if i will pass the ename value and hiredate value,i need only the marching details.
Please help me on this.
You've got a few choices:
1. Test the parameters and run a custom SQL statement based on this
if ename is null then
select * from emp;
else
select * from emp where empname=ename and join_date=hiredate ;
end if;
2. Have or conditions based on whether ename is null:
select * from emp
where ( ename is not null and join_date=hiredate and empname=ename ) or
( ename is null) ;3. Dynamic SQL
execute immediate 'select * from emp ' ||
case when ename is not null then
'where empname=:ename and join_date=:hiredate'
else
'where :ename is null and nvl(:hiredate, sysdate) is not null'
end
using ename, hiredateFor a simple case like this, I tend towards option 1. As they're separate statements, you'll likely get better plans than option 2. And static SQL is more secure and better code analysis options than dynamic SQL, making it better than 3.