Generic Architecture for Caching Table Data: Supercharge Your PL/SQL Applications - An Example
(Page 3 of 5 )
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:
g_dept_cache department_tbl;
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.
Next: Retrieving department data >>
More Oracle Articles
More By Mark Vilrokx
