SQL: return resultset from oracle procedure

Question:

How do I return a resultset from an Oracle procedure?

Answer:

Here is how you define a procedure that returns a resultset in Oracle

CREATE OR REPLACE
PROCEDURE get_assure (p_minid    IN  vgassur.noassur%TYPE,
                      p_recordset OUT SYS_REFCURSOR) AS 
BEGIN 
  OPEN p_recordset FOR
    SELECT ass.noassur, ass.notelpro
    FROM   vgassur ass
    WHERE ass.NoAssur >= p_minid;
 
END get_assure;

You can call the procedure like this:

declare
 l_cursor SYS_REFCURSOR;
  l_noass vgassur.noassur%type;
  l_note vgassur.notelpro%type;
  
begin
 get_assure(p_minid => 1070200, 
   p_recordset => l_cursor);
 DBMS_OUTPUT.put_line('start');
  
  LOOP 
    FETCH l_cursor
    INTO  l_noass, l_note;
    EXIT WHEN l_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(l_noass || ' | ' || l_note );
  END LOOP;
  CLOSE l_cursor;
 
end;