CREATE TABLE frc_employees
AS
SELECT * FROM hr.employees
Table created.
Three Functions with Result Cache Enabled
CREATE OR REPLACE PACKAGE frc_demo
IS
TYPE last_names_aat IS TABLE OF frc_employees.last_name%TYPE
INDEX BY PLS_INTEGER;
TYPE employees_aat IS TABLE OF frc_employees%ROWTYPE
INDEX BY PLS_INTEGER;
FUNCTION last_name (employee_id_in IN frc_employees.employee_id%TYPE)
RETURN frc_employees.last_name%TYPE
RESULT_CACHE;
FUNCTION last_names (department_id_in IN frc_employees.department_id%TYPE)
RETURN last_names_aat
RESULT_CACHE;
FUNCTION employees_in_dept (
department_id_in IN frc_employees.department_id%TYPE)
RETURN employees_aat
RESULT_CACHE;
END;
Package created.
CREATE OR REPLACE PACKAGE BODY frc_demo
IS
FUNCTION last_name (employee_id_in IN frc_employees.employee_id%TYPE)
RETURN frc_employees.last_name%TYPE
RESULT_CACHE
IS
l_name frc_employees.last_name%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE (
'EXECUTED BODY OF FUNCTION: got last name for employee ID '
|| employee_id_in);
SELECT last_name
INTO l_name
FROM frc_employees
WHERE employee_id = employee_id_in;
RETURN l_name;
END;
FUNCTION last_names (department_id_in IN frc_employees.department_id%TYPE)
RETURN last_names_aat
RESULT_CACHE
IS
l_names last_names_aat;
BEGIN
DBMS_OUTPUT.PUT_LINE (
'Getting all last names for department ID ' || department_id_in);
SELECT last_name
BULK COLLECT INTO l_names
FROM frc_employees
WHERE department_id = department_id_in;
RETURN l_names;
END;
FUNCTION employees_in_dept (
department_id_in IN frc_employees.department_id%TYPE)
RETURN employees_aat
RESULT_CACHE
IS
l_employees employees_aat;
BEGIN
DBMS_OUTPUT.PUT_LINE (
'Getting all rows for department ID ' || department_id_in);
SELECT *
BULK COLLECT INTO l_employees
FROM frc_employees
WHERE department_id = department_id_in;
RETURN l_employees;
END;
END;
Package Body created.
Ten Calls, Just One Function Execution
BEGIN
FOR indx IN 1 .. 10
LOOP
DBMS_OUTPUT.put_line (frc_demo.last_name (138));
END LOOP;
END;
EXECUTED BODY OF FUNCTION: got last name for employee ID 138
Stiles
Stiles
Stiles
Stiles
Stiles
Stiles
Stiles
Stiles
Stiles
Stiles
DECLARE
l_names frc_demo.last_names_aat;
BEGIN
l_names := frc_demo.last_names (50);
DBMS_OUTPUT.put_line (
'1. Count of names in department 50 = ' || l_names.COUNT);
/*
Querying all rows into a collection does NOT help with caching
based on a single employee ID, even if the data returned is the "same"
and has been cached by another function.
*/
DBMS_OUTPUT.put_line ('Last name for 198 = ' || frc_demo.last_name (198));
l_names := frc_demo.last_names (50);
l_names := frc_demo.last_names (60);
DBMS_OUTPUT.put_line (
'2. Count of names in department 60 = ' || l_names.COUNT);
END;
Getting all last names for department ID 50
1. Count of names in department 50 = 45
EXECUTED BODY OF FUNCTION: got last name for employee ID 198
Last name for 198 = OConnell
Getting all last names for department ID 60
2. Count of names in department 60 = 5
DECLARE
l_employees frc_demo.employees_aat;
BEGIN
l_employees := frc_demo.employees_in_dept (50);
DBMS_OUTPUT.put_line (
'1. Count of names in department 50 = ' || l_employees.COUNT);
l_employees := frc_demo.employees_in_dept (50);
l_employees := frc_demo.employees_in_dept (60);
DBMS_OUTPUT.put_line (
'2. Count of names in department 60 = ' || l_employees.COUNT);
END;
Getting all rows for department ID 50
1. Count of names in department 50 = 45
Getting all rows for department ID 60
2. Count of names in department 60 = 5
Change In Your Session, Cache Disabled
BEGIN
DBMS_OUTPUT.put_line ('BEFORE update 1: ' || frc_demo.last_name (144));
DBMS_OUTPUT.put_line ('BEFORE update 2: ' || frc_demo.last_name (144));
DBMS_OUTPUT.PUT_LINE ('-');
UPDATE frc_employees
SET last_name = 'Uh-oh'
WHERE employee_id = 198;
/* Shows me the new value and caching is disabled. */
DBMS_OUTPUT.put_line ('AFTER update 1: ' || frc_demo.last_name (144));
DBMS_OUTPUT.put_line ('AFTER update 2: ' || frc_demo.last_name (144));
DBMS_OUTPUT.PUT_LINE ('-');
/* After commit, will it start caching again? Yes! */
COMMIT;
DBMS_OUTPUT.put_line ('AFTER commit 1: ' || frc_demo.last_name (144));
DBMS_OUTPUT.put_line ('AFTER commit 2: ' || frc_demo.last_name (144));
DBMS_OUTPUT.PUT_LINE ('-');
/* Reset last name */
UPDATE frc_employees
SET last_name = 'OConnell'
WHERE employee_id = 198;
COMMIT;
END;
EXECUTED BODY OF FUNCTION: got last name for employee ID 144
BEFORE update 1: Vargas
BEFORE update 2: Vargas
-
EXECUTED BODY OF FUNCTION: got last name for employee ID 144
AFTER update 1: Vargas
EXECUTED BODY OF FUNCTION: got last name for employee ID 144
AFTER update 2: Vargas
-
EXECUTED BODY OF FUNCTION: got last name for employee ID 144
AFTER commit 1: Vargas
AFTER commit 2: Vargas
-