How to Create Ref Cursor and sys_refcursor in PL/SQL

Below basic example is to create a ref cursor to print employee name with email address for first 5 rows.

Ref cursor example

set serveroutput on;
declare
type emp_refcursortype is ref cursor;
emp_cursor emp_refcursortype;
emp_rec     per_all_people_f%rowtype;
query_stmt varchar2(300) := 'select * from per_all_people_f ';
v_gender varchar2(5):='F';
begin
if v_gender is null then
query_stmt := query_stmt || ' where rownum<=5';
open emp_cursor for query_stmt;
else
query_stmt := query_stmt || ' where sex = :v_gender and rownum<=5';
open emp_cursor for query_stmt using v_gender;
end if;
loop
fetch emp_cursor into emp_rec;
exit when emp_cursor%notfound;
dbms_output.put_line('Employee_name '|| emp_rec.full_name || ' with email address ' ||  emp_rec.email_address || ' with gender ' || emp_rec.sex);
end loop;
close emp_cursor;
end;

SYS_REFCURSOR DECLARATION

emp_cursor       SYS_Refcursor ;