Управление программным кодом PL/SQL в базе данных Oracle

Стас Белков

Стас Белков

Автор статьи. Известный специалист в мире IT. Консультант по продуктам и решениям Oracle. Практикующий программист и администратор баз данных. Подробнее.

Управление кодом PL/SQL в базе данных OracleПри компиляции программного модуля PL/SQL его исходный код сохраняется в базе данных Oracle. Это дает разработчикам два важных преимущества:

  • Информацию о программном коде можно получить с помощью запросов SQL. Разработчики могут писать запросы и даже целые программы PL/SQL, которые читают информацию из представлений словаря данных и даже могут изменять состояние кода приложения.
  • База данных Oracle управляет зависимостями между хранимыми объектами. В мире PL/ SQL не существует процесса «компоновки» исполняемых файлов, которые затем запускаются пользователями. База данных берет на себя все служебные операции, позволяя разработчику сосредоточиться на реализации бизнес-логики.

В следующих разделах представлены основные источники информации в словаре данных СУБД Oracle.

 

Представления словаря данных

Словарь данных Oracle — настоящие джунгли! Он изобилует полезной информацией, но найти путь к цели порой бывает очень непросто. В нем сотни представлений, основанных на сотнях таблиц, множество сложных взаимосвязей, специальных кодов и слишком много неоптимизированных определений представлений. Вскоре мы рассмотрим подмножество важнейших представлений, а пока выделим три типа (или уровня) представлений словаря данных:

  •  USER_* — представления с информацией об объектах базы данных, принадлежащих текущей схеме.
  •  ALL_* — представления с информацией об объектах базы данных, доступных в текущей схеме (либо принадлежащих ей, либо доступных благодаря соответствующим привилегиям). Обычно они содержат те же столбцы, что и соответствующие представления USER, с добавлением столбца OWNER в представлениях ALL.
  •  DBA_* — представления с информацией обо всех объектах базы данных. Обычно содержат те же столбцы, что и соответствующие представления ALL. Исключение составляют представления v$, gx$ и x$.

В этой статье мы будем работать с представлениями USER; вы можете легко изменить любые сценарии и приемы, чтобы они работали с представлениями ALL, добавив в свою логику столбец OWNER. Вероятно, для разработчика PL/SQL самыми полезными будут следующие представления:

  •  USER_ARGUMENTS — аргументы (параметры) всех процедур и функций схемы.
  •  USER_DEPENDENCIES — все зависимости (входящие и исходящие) для принадлежащих текущей схеме объектов. Представление в основном используется Oracle для пометки неработоспособных объектов, а также средой разработки для вывода информации зависимостей в программах просмотра объектов. Примечание: для полного анализа зависимостей следует использовать представление ALL_DEPENDENCIES на случай, если программная единица будет вызвана другой программной единицей, принадлежащей другой схеме.
  •  USER_ERRORS — текущий набор ошибок компиляции для всех хранимых объектов (включая триггеры). Представление используется командой SQL*Plus SHOW ERRORS, описанной в этом блоге. Вы также можете писать собственные запросы к этому представлению.
  •  USER_IDENTIFIERS — представление появилось в Oracle11g, а для его заполнения используется утилита PL/Scope. Содержит информацию обо всех идентификаторах (имена программ, переменных и т. д.) в кодовой базе; исключительно полезный инструмент анализа кода.
  •  USER_0BJECTS — объекты, принадлежащие текущей схеме. Например, при помощи этого представления можно узнать, помечен ли объект как неработоспособный (INVALID), найти все пакеты, в имени которых присутствует EMP, и т. д.
  •  USER_0BJECT_SIZE — размер принадлежащих текущей схеме объектов. Точнее, это представление возвращает информацию о размере исходного, разобранного и откомпилированного кода. И хотя оно в основном используется компилятором и ядром времени выполнения, вы можете воспользоваться им для поиска больших программ в вашей среде — кандидатов для размещения в SGA.
  •  USER_PLSQL_0B3ECT_SETTINGS — представление появилось в Oracle10g. Содержит информацию о характеристиках объектов PL/SQL, которые могут изменяться командами DDL ALTER и SET: уровни оптимизации, параметры отладки и т. д.
  •  USER_PR0CEDURES — информация о хранимых программах (не только процедурах, как можно было бы подумать по названию) — например, модель AUTHID, признак детерминированности функций и т. д.).
  •  USER_S0URCE — исходный код всех принадлежащих текущей схеме объектов (в Oracle9i и выше — вместе с триггерами баз данных и исходным кодом Java). Это очень удобное представление — вы можете применять к нему всевозможные средства анализа исходного кода, используя SQL и особенно Oracle Text.
  •  USER_ST0RED_SETTINGS — флаги компилятора PL/SQL. Это представление поможет узнать, какие программы были откомпилированы в код аппаратной платформы.
  •  USER_TRIGGERS и USER_TRIG_C0LUMNS — триггеры базы данных, принадлежащие текущей схеме (включая исходный код и описание инициирующих событий), а также столбцы, связанные с триггерами.

Для просмотра структуры любого из этих представлений можно либо воспользоваться

командой DESCRIBE в SQI*Plus, либо обратиться к документации Oracle. Примеры использования этих представлений приведены в следующем разделе.

Вывод информации о хранимых объектах

В представлении USER_0BJECTS содержится ключевая информация об объекте:

  •  0BJECT_name — имя объекта.
  •  OBJECT_TYPE — тип объекта (PACKAGE, FUNCTI0N, TRIGGER и т. д.).
  •  STATUS — состояние объекта (VALID или INVALID).
  •  LAST_DDL_TIME — время последнего изменения объекта.

Следующий сценарий SQL*Plus выводит информацию о состоянии объектов PL/SQL:

/* Файл в Сети: psobj.sql */
SELECT object_type, object_name, status 
   FROM user_objects 
WHERE object_type IN (
      'PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE',
      'TYPE', 'TYPE BODY', 'TRIGGER')
ORDER BY object_type, status, object_name

Результат работы сценария выглядит примерно так:

OBJECT_TYPE OBJECT_NAME STATUS
FUNCTION DEVELOP ANALYSIS INVALID
  NUMBER OF ATOMICS INVALID
PACKAGE CONFIG_PKG VALID
  EXCHDLR PKG VALID


Обратите внимание на два модуля с пометкой INVALID. О том, как вернуть программный модуль в действительное состояние VALID, будет рассказано далее.

 

Вывод и поиск исходного кода

Исходный код программ следует всегда хранить в текстовых файлах (или в средах разработки, предназначенных для хранения и работы с кодом PL/SQL за пределами Oracle). Однако хранение программ в базе данных позволяет использовать SQL-запросы для анализа исходного кода по всем модулям, что непросто сделать в текстовом редакторе.

Представление USER_SOURCE содержит исходный код всех объектов, принадлежащих текущему пользователю. Его структура такова:

Name Null? Type
NAME NOT NULL VARCHAR2(30)
TYPE   VARCHAR2(12)
LINE NOT NULL NUMBER
TEXT   VARCHAR2(4000)


Здесь NAME — имя объекта, TYPE — его тип (от программ PL/SQL до блоков Java и исходного кода триггеров), LINE — номер строки, а TEXT — текст исходного кода. Представление USER_SOURCE является чрезвычайно ценным источником информации для разработчиков. При помощи соответствующих запросов разработчик может:

  •  вывести строку исходного кода с заданным номером;
  •  проверить стандарты кодирования;
  •  выявить возможные ошибки и дефекты в исходном коде;
  •  найти программные конструкции, не выявляемые из других представлений. Предположим, в проекте действует правило, согласно которому отдельные разработчики никогда не должны жестко кодировать пользовательские номера ошибок из диапазона от -20 999 до -20 000 (поскольку это может привести к возникновению конфликтов). Конечно, руководитель проекта не может помешать разработчику написать следующую строку:
RAISE_APPLICATION_ERROR (-20306, 'Balance too low');

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

/* Файлы в Сети: valstd.* */
PROCEDURE progwith (str IN VARCHAR2)
IS
   TYPE info_rt IS RECORD (
      NAME user_source.NAME%TYPE , 
      text user_source.text%TYPE 
   );
   TYPE info_aat IS TABLE OF info_rt 
      INDEX BY PLS_INTEGER;
   info_aa info_aat;
BEGIN
   SELECT NAME || '-' || line 
      , text
   BULK COLLECT INTO info_aa 
      FROM user_source
   WHERE UPPER (text) LIKE '%' || UPPER (str) || '%'
      AND NAME <> 'VALSTD'
      AND NAME <> 'ERRNUMS'; 
   disp_header ('Checking for presence of "' || str || '"');

   FOR indx IN info_aa.FIRST .. info_aa.LAST 
   LOOP
      pl (info_aa (indx).NAME, info_aa (indx).text);
   END LOOP;
END progwith;

После компиляции этого пакета в схеме можно проверить присутствие в программах значений -20NNN:

SQL> EXEC valstd.progwith ('-20')
------------------
------------------
VALIDATE STANDARDS
------------------
------------------
Checking for presence of "-20"
CHECK_BALANCE - RAISE_APPLICATION_ERROR (-20306, 'Balance too low');
MY_SESSION - PRAGMA EXCEPTION_INIT(dblink_not_open,-2081);
VSESSTAT - CREATE DATE : 1999-07-20

Обратите внимание: третья строка не противоречит правилам; она выводится только потому, что условие отбора сформулировано недостаточно жестко.

Конечно, этот аналитический инструмент весьма примитивен, но при желании его можно усовершенствовать. Можно, к примеру, генерировать HTML-документ с информацией, размещая его в интрасети, или выполнять сценарии valstd каждое воскресенье средствами DBMS_JOB, чтобы в понедельник утром администратор мог проверить наличие данных обратной связи в интрасети.

 

Проверка ограничений размера

Представление USER_0BJECT_SIZE предоставляет информацию о размере программ, хранимых в базе данных:

  •  S0URCE_SIZE — размер исходного кода в байтах. Код должен находиться в памяти во время компиляции (включая динамическую/автоматическую перекомпиляцию).
  •  PARSED_SIZE — размер объекта в байтах после разбора. Эта форма должна находиться в памяти при компиляции любого объекта, ссылающегося на данный объект.
  •  C0DE_SIZE — размер кода в байтах. Код должен находиться в памяти при выполнении объекта.

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

/* Файл в Сети: pssize.sql */
SELECT name, type, source_size, parsed_size, code_size 
      FROM user_object_size 
   WHERE code_size > &&1 * 1024 
   ORDER BY code_size DESC

 

Получение свойств хранимого кода

Представление USER_PLSQL_0BJECT_SETTINGS (появившееся в Oracle10g) содержит информацию о следующих параметрах компиляции хранимого объекта PL/SQL:

  •  PLSQL_OPTIMIZE_LEVEL — уровень оптимизации, использовавшийся при компиляции объекта.
  •  PLSQL_CODE_TYPE — режим компиляции объекта.
  •  PLSQL_DEBUG — признак отладочной компиляции.
  •  PLSQL_WARNINGS — настройки предупреждений, использовавшиеся при компиляции объекта.
  •  NLS_LENGTH_SEMANTICS — семантика длины NLS, использовавшаяся при компиляции объекта.

Пара примеров возможного применения этого представления:

  •  Поиск программ, не использующих все возможности оптимизирующего компилятора (уровень оптимизации 1 или 0):
/* Файл в Сети: low_optimization_level.sql */
SELECT owner, name
      FROM user_plsql_object_settings 
   WHERE plsql_optimize_level IN (1,0);
  •  Проверка отключения предупреждений у хранимых программ:
/* Файл в Сети: disable_warnings.sql */
SELECT NAME, plsql_warnings
      FROM user_plsql_object_settings 
   WHERE plsql_warnings LIKE '%DISABLE%';

В представлении USER_PROCEDURES перечисляются все функции и процедуры с их свойствами. В частности, в представление USER_PROCEDURES включается настройка модели authid для программы (definer или current_user). Эта информация позволяет быстро определить, какие программы в пакете используют модель привилегий вызывающей стороны или создателя. Пример такого запроса:

/* Файл в Сети: show_authid.sql */
SELECT AUTHID
         , p.object_name program_name 
         , procedure_name subprogram_name 
      FROM user_procedures p, user_objects o 
   WHERE p.object_name = o.object_name
      AND p.object_name LIKE '<критерии имени программы или пакета>'
ORDER BY AUTHID, procedure_name;

 

 

Анализ и изменение состояний триггеров

Запросы к триггерным представлениям (USER_TRIGGERS, USER_TRIG_COLUMNS) обычно используются для решения следующих задач:

  •  Включение или отключение всех триггеров для заданной таблицы. Вместо того чтобы писать код вручную, вы выполняете соответствующие команды DDL из кода PL/SQL. Пример такой программы приведен в статье «Сопровождение триггеров».
  •  Поиск триггеров, выполняемых при изменении некоторых столбцов, но не имеющих предложения секции WHEN. Следующий запрос поможет найти триггеры, не имеющие секции WHEN, которые являются источниками потенциальных проблем:
/* Файл в Сети: nowhen_trigger.sql */
SELECT *
   FROM user_triggers tr 
WHERE when_clause IS NULL AND 
      EXISTS (SELECT 'x'
         FROM user_trigger_cols 
         WHERE trigger_owner = USER 
         AND trigger_name = tr.trigger_name);

 

 

Анализ аргументов

Представление USER_ARGUMENTS может оказаться исключительно полезным для программиста: оно содержит информацию о каждом аргументе каждой хранимой программы в вашей схеме. Оно одновременно предоставляет разнообразную информацию об аргументах в разобранном виде и запутанную структуру, с которой очень трудно работать. Простой сценарий SQL*Plus для вывода содержимого USER_ARGUMENTS для всех программ в заданном пакете:

/* Файл в Сети: desctest.sql */
SELECT object_name, argument_name, overload
      , POSITION, SEQUENCE, data_level, data_type 
   FROM user_arguments 
WHERE package_name = UPPER ('&&1');

Более совершенная программа на базе PL/SQL для вывода содержимого USER_ARGUMENTS находится в файле show_aN_arguments.sp на сайте книги.

Вы также можете создавать более конкретные запросы к представлению USER_ARGUMENTS для выявления возможных проблем с качеством кодовой базой. Например, Oracle рекомендует воздерживаться от использования типа LONG и использовать вместо него LOB. Кроме того, тип данных CHAR с фиксированной длиной может создать проблемы; намного лучше использовать VARCHAR2. Следующий запрос выявляет использование этих типов в определениях аргументов:

/* Файл в Сети: long_or_char.sql */
SELECT object_name, argument_name, overload
      , POSITION, SEQUENCE, data_level, data_type 
   FROM user_arguments 
WHERE data_type IN ('LONG','CHAR');

Представление USER_ARGUMENTS может использоваться даже для получения информации о программах пакета, которую трудно получить другим способом. Предположим, я хочу получить список всех процедур и функций, определенных в спецификации пакета. Что вы говорите? «Нет проблем — просто выдать запрос к USER_PROCEDURES». И это был бы хороший ответ... вот только USER_PROCEDURES не сообщит вам, является ли программа функцией или процедурой (причем в зависимости от перегрузки она может быть и той и другой!)

Представление USER_ARGUMENTS содержит нужную информацию, но она хранится в далеко не очевидном формате. Чтобы определить, является ли программа функцией или процедурой, можно поискать в USER_ARGUMENTS строку данной комбинации «пакет/ программа», у которой значение POSITION равно 0. Это значение Oracle использует для хранения «аргумента» RETURN функции. Если оно отсутствует, значит, программа должна быть процедурой.

Следующая функция использует эту логику для возвращения строки, обозначающей тип программы (если она перегружена для обоих типов, функция возвращает строку «FUNCTION, PROCEDURE»). Обратите внимание: функция list_to_string, используемая в теле функции, определяется в файле:

/* Файл в Сети: program_type.sf */
FUNCTION program_type (owner_in IN VARCHAR2,
                                 package_in	IN VARCHAR2,
                                 program_in	IN VARCHAR2)
   RETURN VARCHAR2 
IS
   c_function_pos CONSTANT PLS_INTEGER := 0;

   TYPE type_aat IS TABLE OF all_objects.object_type%TYPE 
      INDEX BY PLS_INTEGER;
   l_types                      type_aat;
   retval                       VARCHAR2 (32767);
BEGIN
   SELECT CASE MIN (position)
            WHEN c_function_pos THEN 'FUNCTION'
            ELSE 'PROCEDURE'
         END
      BULK COLLECT INTO l_types 
      FROM all_arguments 
   WHERE owner = owner_in
         AND package_name = package_in 
         AND object_name = program_in 
   GROUP BY overload;

   IF l_types.COUNT > 0 
   THEN
      retval := list_to_string (l_types, ',', distinct_in => TRUE);
   END IF;
  RETURN retval;
END program_type;

Наконец, следует сказать, что встроенный пакет DBMS_DESCRIBE предоставляет программный интерфейс PL/SQL, который возвращает почти ту же информацию, что и USER_ARGUMENTS. Впрочем, эти два механизма отличаются некоторыми особенностями работы с типами данных.

 

 

Анализ использования идентификаторов (Oracle Database 11g)

Проходит совсем немного времени, и рост объема и сложности кодовой базы создает серьезные проблемы с сопровождением и эволюцией. Допустим, мне потребовалось реализовать новую возможность в части существующей программы. Как убедиться в том, что я правильно оцениваю последствия от появления новой функции, и внести все необходимые изменения? До выхода Oracle Database 11g инструменты, которые могли использоваться для анализа последствий, в основном ограничивались запросами к ALL_DEPENDENCIES и ALL_SOURCE. Теперь, с появлением PL/Scope, я могу выполнять намного более подробный и полезный анализ.

PL/Scope собирает информацию об идентификаторах в исходном коде PL/SQL при компиляции кода и предоставляет собранную информацию в статических представлениях словарей данных. Собранная информация, доступная через USER_IDENTIFIERS, содержит очень подробные сведения о типах и использовании (включая объявления, ссылки, присваивание и т. д.) каждого идентификатора, а также о местонахождении использования в исходном коде.

Описание представления USER_IDENTIFIERS:

 

Name Null? Type
NAME   VARCHAR2(128)
SIGNATURE   VARCHAR2(32)
TYPE   VARCHAR2(18)
OBJECT_NAME NOT NULL VARCHAR2(128)
OBJECT_TYPE   VARCHAR2(13)
USAGE   VARCHAR2(11)
USAGE_ID   NUMBER
LINE   NUMBER
COL   NUMBER
USAGE_CONTEXT_ID   NUMBER

 

Вы можете писать запросы к USER_IDENTIFIERS для поиска в коде разнообразной информации, включая нарушения правил об именах. Такие редакторы PL/SQL, как Toad, наверняка скоро начнут предоставлять доступ к данным PL/Scope, упрощая их использование для анализа кода. А пока этого не произошло, вам придется строить собственные запросы (или использовать написанные и опубликованные другими разработчиками).

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

 

ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL'

Чтобы узнать значение PLSC0PE_SETTINGS для любой конкретной программы, обратитесь с запросом к USER_PLSQL_0BJECT_SETTINGS.

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

Рассмотрим несколько примеров использования PL/Scope. Допустим, я создаю следующую спецификацию пакета и процедуру с включенной поддержкой PL/Scope:

/* Файл в Сети: 11g_plscope.sql */
ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL'
/
CREATE OR REPLACE PACKAGE plscope_pkg 
IS
   FUNCTION plscope_func (plscope_fp1 NUMBER)
      RETURN NUMBER;
   PROCEDURE plscope_proc (plscope_pp1 VARCHAR2);
END plscope_pkg;
/
CREATE OR REPLACE PROCEDURE plscope_proc1 
IS
   plscope_var1 NUMBER := 0;
BEGIN
   plscope_pkg.plscope_proc (TO_CHAR (plscope_var1));
   DBMS_OUTPUT.put_line (SYSDATE); 
   plscope_var1 := 1;
END plscope_proc1;
/

Настройки PL/Scope проверяются следующим образом:

SELECT name, plscope_settings 
      FROM user_plsql_object_settings 
   WHERE name LIKE 'PLSCOPE%'

NAME                           PLSCOPE SETTINGS
--------------------------     ----------------
PLSCOPE_PKG	               IDENTIFIERS:ALL
PLSCOPE_PROC1	               IDENTIFIERS:ALL

Проверка объявлений, обнаруженных в процессе компиляции этих двух программ:

SELECT name, TYPE
      FROM user_identifiers
   WHERE name LIKE 'PLSCOPE%' AND usage = 'DECLARATION'ORDER BY type, usage_id

NAME	        TYPE
------------    ---------
PLSC0PE_FP1      FORMAL IN
PLSC0PE_PP1      FORMAL IN
PLSC0PE_FUNC     FUNCTI0N
PLSCOPE_PKG      PACKAGE
PLSCOPE_PROC1    PROCEDURE
PLSCOPE_PROC     PROCEDURE
PLSCOPE_VAR1     VARIABLE

Теперь я могу получить информацию обо всех локально объявляемых переменных:

SELECT a.name variable_name, b.name context_name, a.signature 
      FROM user_identifiers a, user_identifiers b 
   WHERE a.usage_context_id = b.usage_id 
         AND a.TYPE = 'VARIABLE'
         AND a.usage = 'DECLARATION'
         AND a.object_name = 'PLSCOPE_PROC1'
         AND a.object_name = b.object_nameORDER BY a.object_type, a.usage_id
VARIABLE NAME   CONTEXT NAME    SIGNATURE
-------------   -------------   --------------------------------
PLSCOPE_VAR1    PLSCOPE_PROC1   401F008A81C7DCF48AD7B2552BF4E684

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

SELECT usage, usage_id, object_name, object_type 
   FROM user_identifiers sig 
      , (SELECT a.signature
         FROM user_identifiers a 
         WHERE a.TYPE = 'VARIABLE'
               AND a.usage = 'DECLARATION'
               AND a.object_name = 'PLSCOPE_PROC1') variables 
WHERE sig.signature = variables.signature 
ORDER BY object_type, usage_id

USAGE       USAGE_ID   OBJECT_NAME                    OBJECT_TYPE
----------- ---------- ------------------------------ -------------
DECLARATION 3          PLSCOPE_PROC1                  PROCEDURE
ASSIGNMENT  4          PLSCOPE_PROC1                  PROCEDURE
REFERENCE   7          PLSCOPE_PROC1                  PROCEDURE
ASSIGNMENT  9          PLSCOPE_PROC1                  PROCEDURE

Даже из этих простых примеров видно, что PL/Scope предоставляет выдающиеся возможности для того, чтобы лучше разобраться в коде и проанализировать изменения. Лукас Джеллема из AMIS предоставил более интересные и сложные примеры использования PL/Scope для проверки имен. Соответствующие запросы содержатся в файле 11g_plscope_amis.sql на сайте книги.

Кроме того, я создал вспомогательный пакет и демонстрационные сценарии, которые помогут вам начать работу с PL/Scope. Просмотрите файлы plscope_helper*.*, а также другие файлы plscope*.*.

 

 

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

Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 6991 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 3107 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48
Символьные функции и аргументы...
Символьные функции и аргументы... 10835 просмотров Анатолий Wed, 23 May 2018, 18:54:01
Использование записей (records...
Использование записей (records... 9469 просмотров Алексей Вятский Thu, 05 Jul 2018, 07:49:43
Войдите чтобы комментировать