Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
create or replace procedure my_new_procedure ( p_ename in varchar2 default NULL, p_hiredate in date default NULL, p_sal in number default NULL) as l_cursor sys_refcursor; l_query varchar2(512) default ‘select * from emp’; cursor l_template is select * from emp; l_rec l_template%rowtype; begin if ( p_ename is NOT NULL ) then l_query := l_query ||‘ where ename like ‘’%’’||:p_ename||’’%’’ ‘; else l_query := l_query ||‘ where (1=1 or :p_ename is null) ‘; end if; if ( p_hiredate is NOT NULL ) then l_query := l_query ||‘ and hiredate > :p_hiredate ‘; else l_query := l_query || ‘ and (1=1 or :p_hiredate is null) ‘; end if; if ( p_sal is NOT NULL ) then l_query := l_query || ‘ and sal > :p_sal ‘; else l_query := l_query || ‘ and (1=1 or :p_sal is null) ‘; end if; dbms_output.put_line ( l_query ); open l_cursor for l_query using p_ename, p_hiredate, p_sal; loop fetch l_cursor into l_rec; exit when l_cursor%notfound; dbms_output.put_line ( l_rec.ename || ‘,’ || l_rec.hiredate || ‘,’ || l_rec.sal ); end loop; close l_cursor; end; /
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.