Работа с объектами и коллекциями в PL/SQL на примере

объекты и коллекции в PL/SQLДопустим, нам потребовалось спроектировать систему администрирования корпорации 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 OF cv в команду DELETE, но это невозможно по двум причинам: (1) чтобы на курсорную перемен­ную можно было ссылаться в команде динамического SQL, она должна обладать глобальной доступностью, и (2) курсорные переменные нельзя объявлять в пакетах, потому что они не обладают долгосрочным состоянием.

Тем не менее в общем случае при выборке строк, которые вы планируете каким-либо образом изменять, желательно использовать конструкцию FOR UPDATE, чтобы предотвратить возможную потерю изменений.

 

Вас заинтересует / Intresting for you:

Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 6915 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 3065 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 5666 просмотров Doctor Thu, 12 Jul 2018, 08:41:33
Символьные функции и аргументы...
Символьные функции и аргументы... 10753 просмотров Анатолий Wed, 23 May 2018, 18:54:01
Войдите чтобы комментировать