Explain returning a query result set to a caller with an OUT SYS_REFCURSOR

A weakly-typed SYS_REFCURSOR can be opened for any query and returned through an OUT parameter or a function result. The caller — a Java/ODP.NET client, a reporting tool, or another PL/SQL block — fetches and closes it. This is the standard way to expose a query from a package API without a pipelined function or a view. OPEN ... FOR can also take a dynamic string with USING binds.

CREATE OR REPLACE PROCEDURE get_dept_emps (
  p_dept_id IN  NUMBER,
  p_rows    OUT SYS_REFCURSOR
) AS
BEGIN
  OPEN p_rows FOR
    SELECT employee_id, last_name, salary
      FROM employees
     WHERE department_id = p_dept_id
     ORDER BY last_name;
END;
/
-- Caller (SQL*Plus): VARIABLE rc REFCURSOR;  EXEC get_dept_emps(10, :rc);  PRINT rc;