1. Test a lookup

PL/SQL tables can be indexed by varchars as well as simple integers, so,

type id_lookup is table of integer index by emp.name%type ;
emp_lookup id_lookup ;

— create a cache in a global package variable somewhere
for e in (select emp_id, name from emp)
  emp_lookup(e.name) := e.emp_id
end loop ;

— Later define functions
function emp_exists( ename in emp.name%type )  return boolean is
   return emp_lookup.exists(ename) ;
end emp_exists ;
function get_emp_id( ename in emp.name%type )  return integer is
    if emp_exists(ename)
      return emp_lookup(ename);
    return null ;
  end if ;
end get_emp_id ;

Of course, you’ll have to do things like maybe force the ename to be upper case or some such to make it work properly but I didn’t want to clutter this up.

2. (more to follow)