cursor value in other select query

Question

create or replace PROCEDURE "RESULT" (res1 OUT SYS_REFCURSOR )   
IS   
Cursor c is Select distinct id,fname,lname,dob,gender,address1 from emp where name like '%B%'
  d c%rowtype; 
BEGIN
  OPEN c;
  LOOP
  fetch c into d;
  exit when c%notfound;
  OPEN res1 FOR  select e.id from emp e  where e.poi_user_id IN (d.id);
  End Loop;
END;

Procedure RESULT compiled.

if i run query without procedure i get 5 results but when i am using the above code, it only returns the last result.

SET SERVEROUTPUT ON;

Declare
  c SYS_REFCURSOR;
  id number;
begin
  RESULT(c);
loop
  fetch c into id; -- and other columns if needed
  exit when c%notfound;
  dbms_output.put_line(id);
  end loop;
END;

Result 5

Answer 1

You shouldn't make the coding difficult for yourself to understand. You dont need any loop here in this case a simple SELECT with FILTER condition will be enough to suffice your requirement. Hope below query helps. Also its not a good coding practice to user "" for naming convention.

As far as your question is concerned REFCURSOR is not that intelligent to keep all the records for each iteration and print you the collaborated output.

CREATE OR REPLACE PROCEDURE "RESULT"(
    res1 OUT SYS_REFCURSOR )
IS
BEGIN
  OPEN res1 FOR 
  SELECT e.id FROM emp e 
        WHERE EXISTS
  ( SELECT 1
  FROM emp E1
  WHERE e1.name LIKE '%B%'
  AND e1.poi_user_id = e.id
  );
END;