Some non-obvious things to do with PL/SQL tables

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)
loop
  emp_lookup(e.name) := e.emp_id
end loop ;
…
— Later define functions
function emp_exists( ename in emp.name%type )  return boolean is
begin
   return emp_lookup.exists(ename) ;
end emp_exists ;
function get_emp_id( ename in emp.name%type )  return integer is
begin
    if emp_exists(ename)
   then
      return emp_lookup(ename);
  else
    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)