How we can implement Ref Cursors in SQL?

How we can implement Ref Cursors in SQL?
create or replace procedure my_procedure ( p_ename in varchar2 default NULL,
p_hiredate in date default NULL,
p_sal in number default NULL)
as
type rc is REF CURSOR;
l_cursor rc;
l_query varchar2(512)
default 'select * from emp where 1 = 1 ';
cursor l_template is select * from emp;
l_rec l_template%rowtype;
begin
if ( p_ename is NOT NULL ) then
dbms_session.set_context( 'MY_CTX', 'ENAME',
'%'||upper(p_ename)||'%');
l_query := l_query ||
' and ename like
sys_context( ''MY_CTX'', ''ENAME'' ) ';
end if;
if ( p_hiredate is NOT NULL ) then
dbms_session.set_context( 'MY_CTX', 'HIREDATE',
to_char(p_hiredate,'yyyymmddhh24miss'));
l_query := l_query ||
' and hiredate >
to_date(
sys_context( ''MY_CTX'',
''HIREDATE'' ),
''yyyymmddhh24miss'') ';
end if;

if ( p_sal is NOT NULL ) then
dbms_session.set_context( 'MY_CTX', 'SAL', p_sal);
l_query := l_query ||
' and sal >
to_number(
sys_context( ''MY_CTX'',
''SAL'' )
) ';
end if;
dbms_output.put_line(l_query);
p( l_query );
open l_cursor for l_query;
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;
/

No comments:

 
Top Blogs