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; /
|levels for reporting||5||65|
|Join SQL view with APEX item as the join condition||4||47|
|Error when running a simple alter statement in Aqua studio for Oracle||6||44|
|Query to identify changes between rows of two tables||8||37|
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
20 Experts available now in Live!