Cursors with parameters

create table tbl (
  i number,
  c varchar2(10) 
);

insert into tbl values(1,'foo');
insert into tbl values(4,'foo');
insert into tbl values(2,'bar');
insert into tbl values(3,'foo');
insert into tbl values(5,'bar');

create or replace function max_of_tbl(val in varchar2)
  return number
as
  cursor cur(v varchar2) is
    select max(i) from tbl 
    where c = v;

  ret number;
begin
  open cur(val);

  fetch cur into ret;
  
  close cur;

  return ret;
end;
/

select max_of_tbl('foo') from dual;
select max_of_tbl('bar') from dual;