Допустим, нам потребовалось спроектировать систему администрирования корпорации Health$.Com, работающей в сфере здравоохранения. Для минимизации издержек база данных Oracle делается распределенной, поэтому на каждом региональном сервере должны храниться таблицы с информацией о клиентах больниц, принадлежащих Health$.Com.
Начнем с определения объектного типа (person) и типа VARRAY (preexisting_conditions):
/* Файл в Сети: health$.pkg */
CREATE OR REPLACE TYPE person AS OBJECT (
name VARCHAR2(50), dob DATE, income NUMBER);
/
CREATE OR REPLACE TYPE preexisting_conditions IS TABLE OF VARCHAR2(25);
/После определения этих типов можно создать пакет для управления важнейшей информацией — данными, необходимыми для управления прибылью Health$.Com. Приведем его спецификацию:
PACKAGE health$
AS
PROCEDURE setup_new_hospital (hosp_name IN VARCHAR2);
PROCEDURE add_profit_source (
hosp_name IN VARCHAR2,
pers IN Person,
cond IN preexisting_conditions);
PROCEDURE minimize_risk (
hosp_name VARCHAR2,
min_income IN NUMBER := 100000,
max_preexist_cond IN INTEGER := 0);
PROCEDURE show_profit_centers (hosp_name VARCHAR2);
END health$;
Имея такой пакет, мы можем создать новую таблицу для хранения информации о больнице. Вот как данная задача реализуется в теле пакета:
FUNCTION tabname (hosp_name IN VARCHAR2) IS
BEGIN
RETURN hosp_name || '_profit_center';
END;
PROCEDURE setup_new_hospital (hosp_name IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE
'CREATE TABLE ' || tabname (hosp_name) || ' (
pers Person,
cond preexisting_conditions)
NESTED TABLE cond STORE AS cond_st';
END;
Кроме того, пакет позволяет добавлять в таблицу записи о потенциальных клиентах, включая сведения о состоянии их здоровья. Ниже показано, как в теле пакета реализуется эта задача:
PROCEDURE add_profit_source (
hosp_name IN VARCHAR2,
pers IN Person,
cond IN preexisting_conditions)
IS
BEGIN
EXECUTE IMMEDIATE
'INSERT INTO ' || tabname (hosp_name) ||
' VALUES (:revenue_generator, :revenue_inhibitors)'
USING pers, cond;
END;
Работа с объектами и коллекциями в PL/SQL полностью прозрачна с точки зрения программиста. Вместо них можно было бы использовать числа и даты — ни синтаксис, ни код от этого не изменятся.
Пакет позволяет удалить из списка всех пациентов, страдающих множественными заболеваниями или имеющих низкие доходы. Эта программа самая сложная:
PROCEDURE minimize_risk (
hosp_name VARCHAR2,
min_income IN NUMBER := 100000,
max_preexist_cond IN INTEGER := 1)
IS
cv RefCurTyp;
human Person;
known_bugs preexisting_conditions;
v_table VARCHAR2(30) := tabname (hosp_name);
v_rowid ROWID;
BEGIN
/* Находим все записи о больных, у которых количество заболеваний
превышает пороговое значение или доход ниже определенного уровня,
и удаляем их из таблицы. */
OPEN cv FOR
'SELECT ROWID, pers, cond
FROM ' || v_table || ' alias
WHERE (SELECT COUNT(*) FROM TABLE (alias.cond))
> ' ||
max_preexist_cond ||
' OR
alias.pers.income < ' || min_income;
LOOP
FETCH cv INTO v_rowid, human, known_bugs;
EXIT WHEN cv%NOTFOUND;
EXECUTE IMMEDIATE
'DELETE FROM ' || v_table || ' WHERE ROWID = :rid'
USING v_rowid;
END LOOP;
CLOSE cv;
END;
Я решил получать
ROWIDкаждого пациента, чтобы упростить идентификацию записей при выполненииDELETE. Было бы очень удобно использовать запросFOR UPDATEс последующим включениемWHERE CURRENT OFcv в командуDELETE, но это невозможно по двум причинам: (1) чтобы на курсорную переменную можно было ссылаться в команде динамического SQL, она должна обладать глобальной доступностью, и (2) курсорные переменные нельзя объявлять в пакетах, потому что они не обладают долгосрочным состоянием.
Тем не менее в общем случае при выборке строк, которые вы планируете каким-либо образом изменять, желательно использовать конструкцию FOR UPDATE, чтобы предотвратить возможную потерю изменений.
