Кэширование на уровне пакета

Доброго времени суток. Хочу рассказать об одном из способов кэширования данных в моем любимом Oracle.
Давайте рассмотрим кэширование редко меняющихся данных (например справочников) на уровне пакета. Этот метод не является масштабируемым, потому как основан он на состоянии пакета — кэшированные данные попадают в PGA, т.е. в «приватную» область памяти, недоступную другим пользователям. Т.о. если у вас 1000 пользователей и они вызывают функции пакета, то у вас будет 1000 экземпляров кэшированных данных. «Плохо» — скажете вы, да, плохо, но не обязательно всем пользователям давать права на выполнение данного пакета. Тем не менее, данный способ широко используется.
Плох он или хорош можно решить только для конкретного случая.

Итак начнем:
Сначала табличка:

CREATE TABLE testname
(
ID NUMBER,
Name VARCHAR2(100)
);

Теперь наполним ее:

INSERT INTO TestName(id, name) VALUES (1, 'Ivan');
INSERT INTO TestName(id, name) VALUES (2, 'Stepan');
INSERT INTO TestName(id, name) VALUES (3, 'Sidor');
COMMIT;

Теперь собственно пакет:

CREATE OR REPLACE PACKAGE TestNamePck IS

-- собственно получение имени по ID
FUNCTION GetNameByID(pID NUMBER) RETURN VARCHAR2;

END TestNamePck;

/

CREATE OR REPLACE PACKAGE BODY TestNamePck IS

-- курсор со значениями справочника
CURSOR TestCur IS
SELECT * FROM TestName;

SUBTYPE TTestName IS TestName%ROWTYPE;

TYPE TTestName_ByID IS TABLE OF TTestName INDEX BY VARCHAR2(38);

myTestName TTestName;

TestName_ByID TTestName_ByID;

-- собственно получение имени по ID
FUNCTION GetNameByID(pID NUMBER) RETURN VARCHAR2 IS
BEGIN
IF TestName_ByID.EXISTS(TO_CHAR(pId)) THEN
RETURN TestName_ByID(TO_CHAR(pId)) .Name;
ELSE
RETURN NULL;
END IF;
END;

-- инициализация кэша
BEGIN
OPEN TestCur;
FETCH TestCur INTO myTestName;
WHILE TestCur%found LOOP
TestName_ByID(TO_CHAR(myTestName.ID)) := myTestName;
FETCH TestCur INTO myTestName;
END LOOP;
CLOSE TestCur;
END TestNamePck;

/


После всего вышеперечисленного имеем:

SQL> set serveroutput on;
SQL> set feedback off;
SQL>exec dbms_output.put_line(testnamepck.getnamebyid(pid => 1));

Ivan

SQL>exec dbms_output.put_line(testnamepck.getnamebyid(pid => 2));

Stepan

SQL>exec dbms_output.put_line(testnamepck.getnamebyid(pid => 3));

Sidor

SQL>


Как видно из кода тела пакета, сначала происходит инициализация кэша справочника путем заполнения ассоциативного массива TestName_ByID. После чего функция получения имени по ID просто использует метод Exists ассоциативного массива для определения существования значения.
Естественно, необходимо добавить обработчики ошибок, здесь приведен простой пример для объяснения принципа кэширования на основе состояния пакета.