HomeOracle Page 3 - Generic Architecture for Caching Table Data: Supercharge Your PL/SQL Applications
An Example - Oracle
In this series I am going to introduce you to a generic PL/SQL caching architecture. The goal is to give you an understanding of how you can store Table data in PL/SQL collections (“the cache”) and how to retrieve data from those same PL/SQL collections. The proposed architecture will be generic enough for you to use throughout your applications everywhere you need to retrieve data from the database (and isn’t that what PL/SQL applications are all about?).
To keep this example simple, I am going to use the EMP and DEPT tables that are installed (in the SCOTT schema) when you install an Oracle database. Typically an organization has many employees but relatively few departments for which all of these employees work. Now imagine that you are receiving a collection of employee data and you are asked to write a procedure that finds out what the department name is for which each of these employees work and the location of that department. This is a perfect scenario for caching.
We start with the construction of our cache. We know that it needs to contain the department name and location because that is what is being asked from us. Furthermore it needs to contain the department identifier because that is the only way to join the employee to his or her department. This makes the department id a special attribute of our cache as we shall see in a bit.
Here is the declaration of my PL/SQL type to hold the department data:
TYPE department_tp IS RECORD ( NAME dept.dname%TYPE, LOCATION dept.loc%TYPE );
Now I can hear you think: hold on a minute, where is the department identifier? As I mentioned, the department identifier is a special attribute of my cache. It will be used to access the data in the cache and as such it will be the index of the record it represents. So when I declare my associative array (formerly known as Index-By table) as
TYPE department_tbl IS TABLE OF department_tp INDEX BY BINARY_INTEGER;
my department identifier is actually the INDEX BY BINARY_INTEGER bit in this declaration. This will become clearer later on in the example.
With all my types in place, I can now declare my actual cache:
Let’s start with some basic code for our cache. I need to be able to write data to my cache and read data back from it. I also need a function that can read data from the database. Here is how these modules look:
PROCEDURE write_to_cache ( p_dept_id IN dept.deptno%TYPE, p_dept_data IN department_tp ) AS BEGIN g_dept_cache (p_dept_id).NAME := p_dept_data.NAME; g_dept_cache (p_dept_id).LOCATION := p_dept_data.LOCATION; END write_to_cache; FUNCTION read_from_cache (p_dept_id IN dept.deptno%TYPE) RETURN department_tp AS l_dept_data department_tp; BEGIN IF (g_dept_cache.EXISTS (p_dept_id)) THEN l_dept_data.NAME := g_dept_cache (p_dept_id).NAME; l_dept_data.LOCATION := g_dept_cache (p_dept_id).LOCATION; END IF; RETURN l_dept_data; END read_from_cache; FUNCTION read_from_db (p_dept_id IN dept.deptno%TYPE) RETURN department_tp AS l_dept_data department_tp; CURSOR csr_dept_data (p_dept_id IN dept.deptno%TYPE) IS SELECT dname, loc FROM dept WHERE deptno = p_dept_id; BEGIN OPEN csr_dept_data (p_dept_id); FETCH csr_dept_data INTO l_dept_data; CLOSE csr_dept_data; RETURN l_dept_data; END read_from_db;
Notice how the department identifier (p_dept_id) is used everywhere to identify the department in the cache.