Retrieving department data - Oracle

With these three procedures in place, the logic for retrieving department data becomes very simple really:

• Read the department data from the cache

§        If it was not found in the cache, query it from the database

§        If it was found in the database, write it to the cache.

   FUNCTION dept_data (p_dept_id IN dept.deptno%TYPE)
      RETURN department_tp
   AS
      l_dept_data   department_tp;
   BEGIN
      l_dept_data := read_from_cache (p_dept_id);
      IF (l_dept_data.NAME IS NULL)
      THEN
         l_dept_data := read_from_db (p_dept_id);
         IF (l_dept_data.NAME IS NOT NULL)
         THEN
            write_to_cache (p_dept_id, l_dept_data);
         END IF;
      END IF;
      RETURN l_dept_data;
   END dept_data;

Now wrap all these functions and procedures in a package body, call it dept_cache and expose all the types and the dept_data function (by putting them in the package header).  There is no need to expose any of the other procedures.

Now, if you want to get department data, you just call the dept_data function.

   DECLARE
      l_dept_data   dept_cache.department_tp;
   BEGIN
      l_dept_data := dept_cache.dept_data ('10');
   END;

Notice how the whole caching logic is completely transparent for the users of your package.  Nobody knows that it is using a cache (and neither should they have to know).

When you get your collection of employee data you can now simply loop over the data and invoke dept_cache.dept_data passing in the DEPTNO of the record you are currently processing, e.g.:

   DECLARE
      l_dept_data   dept_cache.department_tp;
   BEGIN
      FOR emp_rec IN (SELECT *
                        FROM emp)
     LOOP
         l_dept_data := dept_cache.dept_data (emp_rec.deptno);
         DBMS_OUTPUT.put_line (   emp_rec.ename
                               || ' works in the '
                               || INITCAP (l_dept_data.NAME)
                               || ' department which is located
in '
                               || INITCAP (l_dept_data.LOCATION)
                              );
      ENDLOOP;
   END;

In order to find out how much of a performance gain this caching is giving us I conducted a little test.  We will query 200.000 records using the read_from_db function (note that you will have to make the read_from_db function temporarily public to be able to run the test).  Then we will do the same test using the dept_data function.  Here is what I get on my Test DB:

   Timings when reading 200000 records from the DB = 2132 ms
   Timings when reading 200000 records, using the cache = 238 ms

These results will obviously vary, depending on your DB (version, setup…) and the volume of data in your department table, but typically you will see a huge increase in speed when caching your data.