SYS_REFCURSOR

 A cursor variable is a variable that references to a cursor. Different from implicit and explicit cursors, a cursor variable is not tied to any specific query. Meaning that a cursor variable can be opened for any query.

The most important benefit of a cursor variable is that it enables passing the result of a query between PL/SQL programs. Without a cursor variable, you have to fetch all data from a cursor, store it in a variable e.g., a collection, and pass this variable as an argument. With a cursor variable, you simply pass the reference to that cursor.

Starting from Oracle 9i, you can use SYS_REFCURSOR,which is a predefined weak typed REF CURSOR, to declare a weak REF CURSOR as follows:

Eg:

Declare

C_Customer SYS_REFCURSOR;

Requirement: Business will send us the Data in the .CSV file and  we need to load in to the TEMP table(every time we have to create a new TEMP table and column structure and naming will be different).

Using the TEMP table, we have to do a Join on the Existing table in  the Database  and do an update.

Since TEMP table and Column names are variable for each run, we have to pass the TABLE NAME and COLUMN from TEMP table which is used in the JOIN operation as VARIABLES. So, we cannot use Normal cursor in Declaration section.

So, Here SYS_REFCURSOR comes handy where we can refer the cursor from Declaration section where the actual cursor will be in the Executable section.

Following is the just small example for SYS_REFCURSOR  and not for the above requirement:

SET SERVEROUTPUT ON;

Declare

V_Loaded_Table_name varchar2(100):='SCHEMA.ACCOUNTS';

V_LOAD_MAP_CBL_COL_NAME varchar2(100):='ACCOUNT_NUMBER';

c_mg_accounts sys_refcursor;

V_acc_cur_cbl_acc_num SCHEMA.ACCOUNTS.ACCOUNT_NUMBER%type;

Begin 

dbms_output.Put_line('################################################################');

dbms_output.Put_line('PROC EXECUTION STARTED');

dbms_output.Put_line('################################################################');

open c_mg_accounts for 

'SELECT

k.'||V_LOAD_MAP_CBL_COL_NAME||'

FROM '||V_Loaded_Table_name||' k ';

LOOP

FETCH c_mg_accounts into V_acc_cur_cbl_acc_num;

EXIT WHEN c_mg_accounts%notfound  ;

dbms_output.Put_line('ACCOUNT NUMBER IS: '||V_acc_cur_cbl_acc_num);

  END LOOP;

   CLOSE c_mg_accounts; 

   END;

   /

Reference:
1. https://www.oracletutorial.com/plsql-tutorial/plsql-cursor-variables/