SQL> CREATE OR REPLACE PROCEDURE prc_test (par_dpt OUT sys_refcursor)
  2  IS
  3  BEGIN
  4    OPEN par_dpt FOR SELECT dname FROM DEPT;
  5  END;
  6  /

PROCEDURE created.

SQL> var dpt refcursor;
SQL> EXEC prc_test(:dpt);

PL/SQL PROCEDURE successfully completed.

SQL> print dpt

DNAME
--------------
MANAGING
ACCOUNTING
RESEARCH
SALES
OPERATIONS

SQL>

SQL> create or replace procedure prc_test_2 is
  2  begin
  3    for cur_r in (select dname from dept)
  4    loop
  5      dbms_output.put_line(cur_r.dname);
  6    end loop;
  7* end;
SQL> /

Procedure created.

SQL> set serveroutput on
SQL> exec prc_test_2;
MANAGING
ACCOUNTING
RESEARCH
SALES
OPERATIONS

PL/SQL procedure successfully completed.

SQL>

SQL> create or replace procedure prc_test_3 is
  2    cursor cur_d is select dname from dept;
  3    cur_r cur_d%rowtype;
  4  begin
  5    open cur_d;
  6    loop
  7      fetch cur_d into cur_r;
  8      exit when cur_d%notfound;
  9
 10      dbms_output.put_line(cur_r.dname);
 11    end loop;
 12    close cur_d;
 13  end;
 14  /

Procedure created.

SQL> exec prc_test_3;
MANAGING
ACCOUNTING
RESEARCH
SALES
OPERATIONS

PL/SQL procedure successfully completed.

SQL>

SQL> create or replace procedure prc_test_4
  2  is
  3    dpt dept.dname%type;
  4  begin
  5    select dname into dpt
  6      from dept
  7      where deptno = 10;
  8
  9    dbms_output.put_line(dpt);
 10  end;
 11  /

Procedure created.

SQL> exec prc_test_4;
ACCOUNTING

PL/SQL procedure successfully completed.

SQL>