Продолжается подписка на наши издания! Вы не забыли подписаться?

О спецификациях и изменении WHERE
Как лучше всего изменять заголовки и обрабатывать различные выражения WHERE

Автор: Стивен Ферстайн
Источник: Журнал «Oracle Magazine», #5 2008
Опубликовано: 12.07.2010
Версия текста: 1.1

Изменение WHERE

Я поддерживаю большое приложение, разработанное на PL/SQL. В последнее время в связи с некоторыми усовершенствованиями потребовалось внести изменения в спецификации нескольких процедур и функций. Мне нужно было добавить одни и удалить другие параметры. Это привело к необходимости изменить другие программы, которые вызывают эти процедуры и функции. Как минимизировать такие изменения?

Если процедура или функция уже используется (вызывается из других программ), следует быть очень осторожными при изменении заголовка (спецификации) процедуры или функции. Вы должны сообщить команде разработчиков приложения, что они могут использовать программу, если вызовут ее другим способом. Таким образом, текущий заголовок (имя, список параметров и возвращаемое значение, если это функция) представляет собой некий вид договорённости с пользователями программы. Любое изменение в заголовке, как вы можете заметить, скорее всего вызовет «эффект домино» в остальном коде. В лучшем случае, зависимые программы станут «инвалидными» и должны будут повторно скомпилироваться. На самом деле благодаря некоторым улучшениям Oracle Database 11g больше не будет «инвалидить» ваши программы (или делать это с существенно меньшей частотой). В худшем случае зависимые программы будут содержать ошибки компиляции и надо будет изменить способ вызова программы из всех зависимых программ.

Изменения должны быть распространены из разрабатываемого экземпляра на один или более экземпляров для тестирования и, разумеется, на производственную систему. Наилучшее управление изменением заголовка и минимизация влияния этих изменений, сокращение времени простоя производственного экземпляра – это опыт обновления кода приложения.

В этой статье я первым делом рассмотрю влияние изменения в заголовках (сколько других программ будет затронуто). Затем – различные типы изменений спецификаций программ и соответствующие действия, которые необходимо предпринять, чтобы синхронизировать остальную часть кода.

Как Oracle Database выполняет изменения. Oracle Database автоматически поддерживает информацию обо всех зависимых объектах базы данных. Эта информация становится доступна через представления словаря данных, например ALL_DEPENDENCIES.

Всякий раз, когда изменяется объект базы данных, все зависимые PL/SQL-программы помечаются как «инвалидные» – они должны быть повторно cкомпилированы перед тем, как их можно будет опять использовать. Тем самым Oracle Database делает для нас очень большую работу по поддержке кода в синхронизированном состоянии.

До Oracle Database 11g существовала степень детализации прослеживания зависимостей на уровне объекта. Вот пример, в котором процедура PKG1.PROC1 вызывает подпрограмму PROC2 из пакета PKG2. Любое изменение в PKG2 или PROC2, или в любой другой подпрограмме пакета, вызовет сброс статуса пакета PKG1. Кроме того, если программа зависит от таблицы базы данных, любые изменения в этой таблице, даже если они не затрагивают программу, изменят статус программы на INVALID.

В Oracle Database 11g предлагается мелкогранулированное (fine-grained) прослеживание зависимостей: теперь поддерживается степень детализации зависимости на уровне элемента в пределах программы. Это означает, что Oracle Database помечает зависимую единицу программы как «инвалидную» намного реже, чем раньше, а это минимизирует необходимость перекомпиляции.

В следующем примере я продемонстрирую это мелкогранулированное отслеживание зависимостей. Первым делом я скомпилирую процедуру (use_last_name), которая ссылается только на столбец last_name таблицы employees. Следующим шагом я изменю другой столбец (first_name), а статус моей процедуры останется VALID:

CREATE OR REPLACE PROCEDURE 
use_last_name (
   id_in IN 
     employees.employee_id%TYPE)
IS
   l_name   
     employees.last_name%TYPE;

BEGIN
   SELECT e.last_name
     INTO l_name
     FROM employees e
    WHERE e.employee_id = 
             use_last_name.id_in;
END use_last_name;
/

ALTER TABLE employees 
MODIFY first_name VARCHAR2(2000)
/

SELECT status
  FROM user_objects
 WHERE object_name = 'USE_LAST_NAME'
/

VALID

Заметьте, я сделал так, чтобы каждая ссылка на столбец содержала алиас таблицы, а каждая ссылка на PL/SQL-пе­ременную содержала название процедуры. Этот подход всегда был рекомендуемым, а при мелкогранулированном прослеживании зависимостей он стал ещё более важным. Во время оценки этих идентификаторов Oracle Database может ещё больше уменьшить необходимость сброса статуса и дальнейшей перекомпиляции программ.

Как узнать сферу влияния. Прежде, чем произвести какие либо изменения в существующих программах, необходимо проанализировать все возможные последствия таких изменений. Для анализа последствий изменения спецификации программы можно выполнить следующее:

Поискать свой исходный код (или в файлах, или в представлении словаря данных ALL_SOURCE), чтобы найти все программные единицы, которые содержат определенные подстроки, такие как название программы. Например, следующий запрос найдет все программы, которые вызывают my_package.my_procedure:

SELECT * FROM ALL_SOURCE
  WHERE upper (text) 
     LIKE '%MY_PACKAGE.MY_PROCEDURE%'

Выполнить запрос к одному из представлений, показывающему зависимости, чтобы найти все программные единицы, которые будут затронуты изменением в программе, если его произвести. Вот, например, запрос, возвращающий все программные единицы, которые зависят от пакета (my_package), определенного в схеме hr:

SELECT * FROM ALL_DEPENDENCIES
  WHERE referenced_owner = 'HR'
     AND referenced_name = 'MY_PACKAGE'

Только после перехода на Oracle Database 11g использовать новую возможность PL/Scope для получения более подробной информации о том, как ваши программы используются в приложении.

Анализ, основанный на представлении ALL_DEPENDENCIES, говорит нам, что одна программная единица зависит от другой. Это, однако, не говорит нам ни о природе зависимости, ни даёт подробной информации об этой зависимости. Я могу узнать, например, что процедура PROC1 зависит от пакета PKG1, но не могу определить, какую подпрограмму в пределах PKG1 фактически она вызывает.

PL/Scope может сделать не только это, и многое другое. Полная обработка PL/Scope выходит за рамки этой статьи, но следующий пример продемонстрирует мощь этой возможности. Чтобы использовать PL/Scope, я должен сначала указать компилятору PL/SQL, чтобы он проанализировал все идентификаторы в программе при её компиляции:

ALTER SESSION SET
plscope_settings='IDENTIFIERS:ALL'
/

Тогда при компиляции программной единицы, Oracle Database заполнит словарь данных подробной информацией о том, как каждый именованный элемент (переменные, типы, программы, и так далее) используются в моей программе. Затем я создам пакет и две процедуры уровня схемы, чтобы каждая процедура ссылалась на разные программы этого пакета:

CREATE OR REPLACE PACKAGE my_package
IS
   FUNCTION func (arg NUMBER)
      RETURN NUMBER;

   PROCEDURE proc (arg VARCHAR2);
END my_package;
/

CREATE OR REPLACE PROCEDURE use_proc
IS
BEGIN
   my_package.proc ('a');
END use_proc;
/

CREATE OR REPLACE PROCEDURE use_func
IS

BEGIN
   DBMS_OUTPUT.put_line 
        (my_package.func (1));
END use_func;
/

Теперь я хочу найти все места в своем коде, где вызывается процедура my_package.proc. Это можно сделать, выполнив запрос к представлению USER_IDENTIFIERS:

SELECT called.object_name
  FROM user_identifiers called, 
          user_identifiers declared
 WHERE declared.USAGE = 
                   'DECLARATION'
   AND declared.NAME = 
                   'PROC'
   AND declared.object_name = 
                   'MY_PACKAGE'
   AND called.USAGE = 'CALL'
   AND called.signature = 
                    declared.signature
   AND called.object_name <> 
                     'MY_PACKAGE'
/

который вернет мне единственную строку:

USE_PROC

Заметьте, что процедура use_func не отобразится в результатах; она зависит от другой подпрограммы пакета. Хотелось бы надеяться, что благодаря этому простому примеру, вы сможете увидеть, что PL/Scope обладает огромным потенциалом в понимании кода и анализирует влияния на этот код.

Теперь исследуем различные способы, которыми можно было бы изменить спецификацию программы.

Перемещение программы уровня схемы в пакет. Предположим, что вы работаете с существующим приложением, у которого уже есть одна функция уровня схемы, которая вычисляет процентную ставку по закладной, и теперь вам нужно создать несколько новых. Вы создаете пакет, пишете новые функции и помещаете существующую функцию в пакет. Так лучше!

Теперь, однако, надо изменить каждый вызов исходной программы на вызов программы из пакета. Это нетрудная задача. Только подписать "имя_пакета" перед названием программы. Однако, что случится, если эту программу вызывают во множестве существующих производственных программ, а ваш менеджер совершенно не хочет изменять все эти программы только потому, что вам нравятся пакеты?

Просто. Оставьте программу уровня схемы на месте, но измените её код так, чтобы это было не что иное, как вызов пакетной программы. Например:

FUNCTION loan_rate (
   loan_type_in
      IN loans.loan_type%TYPE
      ,years_in in
      PLS_INTEGER) RETURN NUMBER
IS

BEGIN
   RETURN mortgage_calcs.loan_rate
      (loan_type_in, years_in);
END loan_rate;

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

Изменение названия программы. Названия программ очень важны. Если название программы вводит в заблуждение или усложнено, то разработчикам будет трудно (и они будут сопротивляться) использовать и многократно использовать эту программу. Если программа используется в приложении, не следует изменять её название только из-за веры в то, что новые имена лучше. Разработчики знают названия программ, и когда вы изменяете их, то создаете трудности для команды. Поэтому меняйте названия существующих программ с большой осторожностью и только тогда, когда это абсолютно необходимо. При этом следуйте совету предыдущего раздела: создайте программу передачи со старым названием, которая просто вызывает программу с новым названием. Тем самым вы и сохраните существующий код, и сможете выборочно (возможно в течение долгого времени) "модернизировать" старые названия на новые.

Изменение названий параметров. Плохие названия параметров делают использование программу тяжелее. И можно подумать, что изменить названия параметров это не проблема, потому что название появляется только где-нибудь в заголовке программы. Так ли это на самом деле?

Конечно, если вызвать программу, используя позиционную нотацию, то название параметра не появится в вызовах этой программы. Вот пример, который вызывает функцию loan_rate с позиционной нотацией:

DECLARE
   l_type
       loans.loan_type%TYPE 
          := 'FIXED';
   l_term
      PLS_INTEGER 
          := 30;

BEGIN
   DBMS_OUTPUT.PUT_LINE (
      mortgage_calcs.loan_rate 
          (l_type, l_term));
END loan_rate;

Если бы во всех вызовах loan_rate использовалась позиционная нотация, то я мог бы менять названия параметров каждый день, и это не имело бы никакого значения. С другой стороны, если я использую именованную нотацию, то такие изменения сломают код:

DECLARE
   l_type
      loans.loan_type%TYPE 
         := 'FIXED';
   l_term
      PLS_INTEGER := 30;

BEGIN
   DBMS_OUTPUT.PUT_LINE (
      mortgage_calcs.loan_rate (
         loan_type_in => l_type
        ,years_in        => l_term);
END loan_rate;

Именованная нотация нуждается в кодировании, что является более удобным для чтения и легким в поддержке, однако в вызове программы появляются жестко закодированные названия параметров.

К сожалению, нет такого способа, который позволил бы замаскировать изменения названий параметров, когда в коде используется именованная нотация. Вы должны найти все места с именованной нотацией при вызове измененной программы и изменить в них названия параметров.

Изменение типа программы. Иногда может потребоваться (или вы почувствуете такую необходимость) изменить тип программы из процедуры в функцию или наоборот. Предыдущий разработчик мог, к примеру, создать функцию, которая возвращает значения через OUT-параметры функции. Многие из нас чувствуют, что это плохой стиль, и предпочитают использовать процедуру.

В этом случае я бы удостоверился, что программа находится в пакете, и затем создал вторую перегруженную программу с тем же самым названием, но другим типом. Тогда я сделал бы в одной из этих программ непосредственный вызов другой так, чтобы и алгоритм программы был реализован только один раз и чтобы позволить существующим вызовам программы оставаться нетронутыми.

Добавление IN-параметра со значением по умолчанию в конец списка параметров. Самое распространённое изменение программ, это добавление параметра к списку параметров. Если вы добавляете IN-параметр в конец списка параметров, ни один из существующих вызовов программы не нужно изменять, если только указать значение по умолчанию для этого параметра.

Если какой-либо из вызовов должен использовать для нового IN-параметра значения не по умолчанию, необходимо изменить только эти вызовы, добавив значение этого параметра. Иначе PL/SQL автоматически сделает значение по умолчанию актуальным и затем выполнит код программы.

Любые другие изменения в списке параметров. Предположим, что надо изменить список параметров другим способом:

Любое из этих изменений параметров требует изменения каждого существующего вызова программы. Чтобы этого избежать, надо оставить существующую программу без изменений и добавить перегрузку с тем же самым названием и новым списком параметров, чтобы все существующие вызовы этой программы остались бы действительными. Таким образом, можно выборочно изменять только те вызовы, которым действительно нужны изменения в списке параметров. Заключение. Мы изменяем заголовок программы, потому что пользователи изменили требования к этой программе. Иногда эти изменения затрагивают каждый отдельный вызов программы уже в самом приложении. А в других случаях некоторые или все существующие вызовы не затрагиваются изменением требований. Если вносить изменения осторожно, то можно минимизировать необходимость их распространения по коду приложения. Лучшим способом будет перемещение программ уровня схемы в пакеты и использование перегрузок, добавление IN-параметров со значениями по умолчанию и переопределение существующих программ в вызовы этих перегрузок.

Изменение WHERE

Я должен написать процедуру обработки множества строк данных таблицы, и каждый раз, когда я вызываю процедуру, выражение WHERE может изменяться. Я хотел бы использовать EXECUTE IMMEDIATE, но эта команда позволяет мне возвратить только одну строку данных. Как я могу избежать кошмара поддержания кода множества процедур, которые полностью идентичны за исключением выражения WHERE?

Когда динамический SQL был впервые введен в Oracle8i Database, EXECUTE IMMEDIATE можно было использовать только для запроса единственной строки данных. С другой стороны даже в Oracle8i Database для достижения желаемых результатов можно было использовать предложение OPEN FOR с переменной курсора, основанной на динамически построенном запросе.

Листинг 1 показывает простой пример такой программы, процедуру process_rows, которая запрашивает данные из классической таблицы employees.

Листинг 1: процедура process_rows, использующая предложение OPEN FOR
CREATE OR REPLACE PROCEDURE process_rows (where_in IN VARCHAR2)
IS
   TYPE weak_rc IS REF CURSOR;
   l_cursor   weak_rc;
   l_name     employees.last_name%TYPE;

BEGIN
   DBMS_OUTPUT.put_line (
      'Employees identified by " ' || where_in || ' " ');

   OPEN l_cursor FOR 
      'SELECT last_name FROM employees WHERE ' || where_in;

   LOOP
      FETCH l_cursor INTO l_name;

      EXIT WHEN l_cursor%NOTFOUND;
      /*
      Do the processing here.
      */
      DBMS_OUTPUT.put_line (l_name);
   END LOOP;

   CLOSE l_cursor;
END process_rows;
/

Затем я запускаю программу process_rows для двух различных выражений WHERE и смотрю на результаты:

BEGIN
   process_rows ('department_id = 10');
   process_rows ('salary > 15000');
END;
/

Employees identified by
"department_id = 10"

Whalen

Employees identified by
"salary > 15000"

King
Kochhar
De Haan

В Oracle9i Database добавлен предопределенный слабосвязанный тип курсора, называемый SYS_REF­CUR­SOR, позволяющий упростить декларацию переменной курсора до показанной ниже:

l_cursor SYS_REFCURSOR;

В Oracle9i Database было добавлено ещё больше полезностей. Начиная с этой версии, EXECUTE IM­ME­DIATE можно использовать для извлечения множества строк данных, помещая результат в коллекцию. Это позволяет написать более простой код и улучшить производительность, потому что в этом случае используется ещё и BULK COLLECT. Листинг 2 показывает переписанную процедуру process_rows с EXECUTE IMME­DIATE.

Листинг 2: процедура process_rows с использованием EXECUTE IMMEDIATE
PROCEDURE process_rows (where_in IN VARCHAR2)
IS
   TYPE employees_t IS TABLE OF employees.last_name%TYPE
      INDEX BY PLS_INTEGER;

   l_names employees_t;

BEGIN
   DBMS_OUTPUT.put_line (
      'Employees identified by " ' || where_in || ' " ');

   EXECUTE IMMEDIATE 
      'SELECT last_name FROM employees WHERE ' || where_in
      BULK COLLECT INTO l_names;

   FOR l_index IN 1 .. l_names.COUNT
   LOOP
      /*
      Do the processing here.
      */
      DBMS_OUTPUT.put_line (l_names (l_index));
   END LOOP;
END process_rows;
/

Заметьте, что я прохожу по коллекции, начиная с 1 до числа элементов в коллекции. Вместо этого можно было использовать следующее:

FOR l_index IN
l_employees.FIRST.. l_employees.LAST

но если коллекция пуста, то и FIRST-метод, и LAST-метод возвращают NULL. Если и нижнее, и верхнее значение цикла FOR соответствует NULL, возникает исключение ORA-06502 VALUE_ERROR. Так как BULK COL­LECT всегда заполняет коллекцию с 1, то при проходе от 1 до COUNT мы избегаем даже возможности этой ошибки. Использование EXECUTE IMMEDIATE с BULK COLLECT – это самое простое решение задачи. Однако, если в таблице очень много строк, то заполняемой коллекции потребуется очень много памяти. Чтобы достичь лучшей производительности с разумным потреблением памяти, необходимо использовать OPEN FOR и FETCH BULK COLLECT с выражением LIMIT, как показано на Листинге 3.

Листинг 3: Использование OPEN FOR, FETCH BULK COLLECT и LIMIT
PROCEDURE process_rows (
   where_in IN VARCHAR2, limit_in IN PLS_INTEGER DEFAULT 100)
IS
   TYPE employees_t IS TABLE OF employees.last_name%TYPE
      INDEX BY PLS_INTEGER;

   l_names    employees_t;
   l_cursor    sys_refcursor;

BEGIN
   DBMS_OUTPUT.put_line (
      'Employees identified by " ' || where_in || ' " ');

   OPEN l_cursor FOR 
      'SELECT last_name FROM employees WHERE ' || where_in;

   LOOP
      FETCH l_cursor
      BULK COLLECT INTO l_names LIMIT limit_in;

      FOR l_index IN 1 .. l_names.COUNT
      LOOP
         /* Do the processing here. */
         DBMS_OUTPUT.put_line (l_names (l_index));
      END LOOP;

      EXIT WHEN l_cursor%NOTFOUND;
   END LOOP;

   CLOSE l_cursor;
END process_rows;
/

В результате вы точно можете избежать написания множества процедур для различных выражений WHERE, если будете использовать EXECUTE IMMEDIATE или OPEN FOR. Кроме того, вы можете усилить BULK COL­LECT, чтобы существенно ускорить извлечение строк. Одно заключительное замечание: в этих примерах кода я создал две процедуры, каждая из которых принимает выражение WHERE как динамический текст, чтобы объединить его со статической частью SQL-запроса. Это классический сценарий SQL Injection, в котором взломщик исхитряется заставить SQL-ядро выполнять непреднамеренные команды. Вот небольшая демонстрация SQL Injection, для которого используется моя процедура process_rows:

BEGIN
   process_rows (
      'department_id = 10 
       UNION 
       SELECT username 
       FROM all_users');
END;
/

Employees identified by 
"department_id = 10 
       UNION 
       SELECT username 
   FROM all_users"

ANONYMOUS
APEX_PUBLIC_USER
BI
...
Whalen
XDB

Заметьте, что я теперь вижу фамилию Whalen, а так же всех пользователей экземпляра Oracle. Это не является намеченным использованием процедуры, но умный (и злонамеренный) пользователь может воспользоваться моей процедурой для выполнения кода совсем другого класса.

SQL Injection представляет собой серьезную проблему безопасности в любой среде разработки баз данных. В этой колонке у меня недостаточно места для обсуждения этой темы, но у Oracle есть Security Technology Center, входящий в Oracle Technology Network. Security Technology Center предлагает руководство по вопросам SQL Injection и другим проблемам, связанным с безопасностью. Я настоятельно рекомендую использовать эти рекомендации, чтобы уменьшить вероятность производственного использования каких-либо хранимых процедур, которые позволяют пользователям создать текст, который будет присоединён к динамическим строкам с SQL.


Любой из материалов, опубликованных на этом сервере, не может быть воспроизведен в какой бы то ни было форме и какими бы то ни было средствами без письменного разрешения владельцев авторских прав.

Copyright © 1994-2016 ООО "К-Пресс"