В каких задачах используется явный или неявный курсоры?

Полная цитата упомянутая ТС в вопросе и комментарии:

Поэтому вместо формулировки «явный или неявный?» лучше спросить: «инкапсулированный или открытый?» И ответ будет таким: всегда инкапсулируйте однострочные запросы, скрывая их за интерфейсом функции (желательно пакетной) и возвращая данные через RETURN.

Осмелюсь сокращёно перефразировать: меньше надо уделять внимания выбору между явным и неявным курсором, лучше позаботится о включении повторяюшихся запросов в пакетные функции, скрыв их тем самым от пользователя.

Практические примеры для закрепления материала изложенного в книге:

Фейерштейн С., Прибыл Б. "Oracle PL/SQL. Для профессионалов" 6-е изд. — СПб.: Питер, 2015.
Глава 15, подглава "Выбор между явным и неявным курсорами"

Эти примеры помогут понять работу с 2-мя типами курсоров: явными и неявными, в случае явных курсоров со строгой и слабой типизацией результирующего сета данных.

Тестовые данные и спецификация пакета:

create table params as 
    select 'key'||level key, 'value'||level value , 'memo about this param' memo
    from dual connect by level<=3;
/
create or replace package democursor as
    type paramtype is record (key varchar2 (8), value varchar2 (16));
    type weakcurtype is ref cursor;
    type strongcurtype is ref cursor return paramtype;
    type paramstype is table of paramtype;
    
    function getParam (key varchar2) return paramtype;  
    procedure openParams (cur out strongcurtype);  
    function getParams (likevalue varchar2) return paramstype;  
end;
/
create or replace package body democursor as
    function getParam (key varchar2) return paramtype is
        param paramtype;
    begin 
        select key, value into param
        from params
        where key = getParam.key;
        return param;
    exception when no_data_found then null;     
    end getParam;
    procedure openParams (cur out strongcurtype) is  
    begin
        open cur for 
            select key, value 
            from params order by key;
    end openParams;
    function getParams (likevalue varchar2) return paramstype is
        cur weakcurtype;
        params paramstype; 
    begin
        open cur for select key, value from params 
        where value like likevalue||'%' order by key;
        fetch cur bulk collect into params;
        close cur; 
        return params;
    end;
end;
/

Пример различных случаев использования курсоров и функций с ними из ракета:

var rc refcursor;  
declare 
    param1 democursor.paramtype;
    param2 democursor.paramtype;
    params democursor.paramstype;

    function getParam (nth int) return democursor.paramtype is
        cur democursor.strongcurtype;   
        ret democursor.paramtype; 
    begin 
        democursor.openParams (cur);
        <<fechnth>> loop 
            fetch cur into ret; 
            exit fechnth when cur%rowcount = nth or cur%notfound;  
        end loop; 
        return ret;
    end; 
begin 
    param1 := democursor.getParam (key=>'key1');
    param2 := getParam (nth=>2); 
    params := democursor.getParams (likevalue=>'val');
    
    open :rc for 
        select 'got first param' what, param1.key||'='||param1.value param from dual union all
        select 'got nth=2 param' what, param2.key||'='||param2.value param from dual union all
        select 'got val% params' what, params.key||'='||params.value from table (params) params;
end;
/ 

Результат работы:

WHAT            PARAM                                                            
--------------- -----------------------------------------------------------------
got first param key1=value1                                                      
got nth=2 param key2=value2                                                      
got val% params key1=value1                                                      
got val% params key2=value2                                                      
got val% params key3=value3   

Воспроизводимые примеры выше поместил на db<>fiddle для изменения с целью, попробовать и лучше понять отделные моменты работы с курсорами.