Ref CUrsor Example

I ref cursor is a variable that holds a cursor. Underneath, it is a "regular" cursor. It is a pointer to a cursor. 

Normally a cursor is a static thing, it points to A query: 


declare 
c1 cursor for select * from emp; 
.... 


It is clear that C1 will run that query and none other. A ref cursor can have an arbitrary query associated with it: 

declare 
type rc is ref cursor; 
c1 rc; 
begin 
if ( a = b ) then 
open c1 for select * from emp; 
else 
open c1 for select * from dept; 
end if;

How to use parameters in a 'where value in…' clause? (integer)

DECLARE
EX varchar2(100):='10,20,01,02,03,04';
param1 varchar2(100):='vishnu';
param2 varchar2(100):='teja';
retval BOOLEAN;
BEGIN
retval:=F_DEMO(EX);
END;

//////////////////////////////////////////////////
CREATE OR REPLACE FUNCTION F_DEMO(EX VARCHAR2)
RETURN BOOLEAN
IS


  l_rc       sys_refcursor;
  l_tbbestu_rec tbbestu%rowtype;
  begin
   open l_rc for 'SELECT  *
FROM tbbestu
WHERE   xxxxxxxx =('|| param1||')
and yyyyyyyy=('|| param2||')
 tbbestu_exemption_code  IN(' || EX || ')';
   loop
    fetch l_rc into l_tbbestu_rec;
     exit when l_rc%notfound;
     dbms_output.put_line( l_tbbestu_rec.tbbestu_exemption_code );
   end loop;
  close l_rc;
  return true;
 end F_DEMO;

\\ 

How to use parameters in a 'where value in…' clause? (Char)

CREATE OR REPLACE FUNCTION F_DEMO(pprccmt_cmty_code0 VARCHAR2,stu_pidm VARCHAR2)
RETURN BOOLEAN
IS
QUERY24       sys_refcursor;
l_pprccmt_rec pprccmt%rowtype; 
BEGIN
dbms_output.put_line( stu_pidm||'  '||pprccmt_cmty_code0);
OPEN QUERY24 FOR 'select
*
from payroll.pprccmt
where pprccmt_pidm = '|| stu_pidm ||'
AND pprccmt_cmty_code IN ('|| pprccmt_cmty_code0 ||')';
LOOP
  FETCH QUERY24 INTO l_pprccmt_rec;
  EXIT WHEN QUERY24%notfound;
  dbms_output.put_line( l_pprccmt_rec.pprccmt_cmty_code||'  '||l_pprccmt_rec.pprccmt_pidm);
END LOOP;
close QUERY24;
return true;
end F_DEMO;
//////////////////////////////
DECLARE
pprccmt_cmty_code varchar2(100):='''TRS'',''HD4'',''ORH''';
stu_pidm VARCHAR2(100):='5564';
retval BOOLEAN;
BEGIN
retval:=F_DEMO(pprccmt_cmty_code,stu_pidm);
END;