Анализ PL/SQL кода в Oracle 11g

("Zoom In on Your Code" By Steven Feuerstein)

Стивен Фейерштейн
(steven.feuerstein@quest.com)
ORACLE ACE 

Источник: блог Саяна Малакшинова, март 2012, http://www.xt-r.com/2012/04/plsql-oracle-11g-zoom-in-on-your-code.html, исходный текст: журнал Oracle Magazine,http://www.oracle.com/technetwork/issue-archive/2010/10-sep/o50plsql-165471.html

Используйте PL/Scope для анализа вашего PL/SQL кода

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

Благодаря PL/Scope разработчики могут значительно лучше и легче выполнять анализ кода. Некоторые полезные приемы его использования и будут рассмотрены в данной статье.

Включение PL/Scope

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

ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL'

Существуют два возможных значения: IDENTIFIERS:ALL и IDENTIFIERS:NONE. По умолчанию - IDENTIFIERS:NONE. Вы можете увидеть значение plscope_settings для каждой программной единицы с помощью запроса к [ALL/USER/DBA]_PLSQL_OBJECT_SETTINGS [прим пер.: также как и другие полезные вещи, включая уровень pl/sql оптимизации и т.д.]

select name
      ,type
      ,plscope_settings
from USER_PLSQL_OBJECT_SETTINGS 

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

NAME	VARCHAR2(30)	Название объекта
TYPE	VARCHAR2(12)	Тип объекта(Type,package,package body,procedure,function...)
PLSQL_OPTIMIZE_LEVEL	NUMBER	Уровень оптимизации(10g: 0-2, 11g: 0-3)
PLSQL_CODE_TYPE	VARCHAR2(4000)	Тип компиляции: Interpreted/Native
PLSQL_DEBUG	VARCHAR2(4000)	Включен ли debug
PLSQL_WARNINGS	VARCHAR2(4000)	Включен ли вывод предупреждений при компиляции.Кстати, "любимый" Кайтовский "when others" без raise обрел свое личное предупреждение-PLW-06009, а если установить параметр в 'ERRORS:ALL', то объект не будет скомпилирован пока не избавитесь от всех предупреждений
NLS_LENGTH_SEMANTICS	VARCHAR2(4000)	Параметр сравнения длины строковых переменных: байты/символы
PLSQL_CCFLAGS	VARCHAR2(4000)	Переменные условной компиляции
PLSCOPE_SETTINGS	VARCHAR2(4000)	IDENTIFIERS:ALL/ IDENTIFIERS:NONE

У этого параметра есть два возможных значения: IDENTIFIERS:ALL и IDENTIFIERS:NONE. По умолчанию - IDENTIFIERS:NONE - сбор идентификаторов отключен.

Представление ALL_IDENTIFIERS

При компиляции объекта с включенным PL/Scope собирается информация обо всех идентификаторах используемых в объекте, которая доступна через представление ALL_IDENTIFIERS.

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

Столбец	Значение
OWNER	Владелец объекта, содержащего идентификатор
NAME	Имя идентификатора
TYPE	Тип идентификатора, например: FORALL OUT (out аргумент), CONSTANT, PACKAGE, or RECORD
SIGNATURE	Уникальная строка для каждого идентификатора сквозная для всех объектов, для однозначного определения из идентификаторов с одинаковыми именами(прим. переводчика: далее сигнатура)
OBJECT_NAME	Имя объекта
OBJECT_TYPE	Тип объекта, например: PACKAGE, TRIGGER, или PROCEDURE
USAGE	Тип использования(объявление или присваивание)
USAGE_ID	Порядковый номер вхождения идентификатора внутри объекта
USAGE_CONTEXT_ID	Внешний ключ на родительский USAGE_ID; (например, контекст определения переменной - это имя подпрограммы, в которой эта переменная определена)
LINE	Порядковый номер строки вхождения переменной
COL	Порядковый номер символа с начала строки места положения переменной

Таблица 1: Описание ALL_IDENTIFIERS

Вы можете получить информацию по определенному объекту следующим запросом:

SELECT *
  FROM all_identifiers ai
 WHERE ai.owner = USER 
   AND ai.object_type = '<program_type>' 
   AND ai.object_name = '<program_name>'
ORDER BY line

Отслеживание использования переменной с помощью PL/Scope

PL/Scope сохраняет детальную информацию о каждом идентификаторе используемом в вашем коде. Каждая строка в ALL_IDENTIFIERS относится к определенной строке определенного объекта. Тип использования указывается в столбце USAGES:

• ASSIGNMENT - присваивание - значение переменной в данном месте может измениться, то есть переменная находится либо в левой части оператора присваивания, либо находится в запросе в блоке INTO, либо передается OUT/IN OUT параметром.
• CALL - вызов функции, процедуры или sql-запроса.
• DECLARATION - объявление идентификатора.
• REFERENCE - обращение. Означает, что идентификатор используется без изменения значения, например: вызов исключения, передача идентификатора как IN или IN OUT параметр подпрограммы или списке USING блока EXECUTE IMMEDIATE, и использование идентификатора в определении %TYPE.
• DEFINITION - определение: говорит компилятору как использовать или использовать объявленный ранее идентификатор. Следующие типы будут иметь строку с DEFINITION в ALL_IDENTIFIERS: FUNCTION, OBJECT, PACKAGE, PROCEDURE, TRIGGER, и EXCEPTION.

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

SELECT ai.object_name
     , ai.object_type
     , ai.name variable_name
     , ai.name context_name
  FROM all_identifiers ai
 WHERE ai.owner = USER AND 
       ai.TYPE = 'VARIABLE' AND 
       ai.usage = 'DECLARATION'
ORDER BY ai.object_name, 
ai.object_type, ai.usage_id

Использование usage_id для понимания иерархии идентификаторов

Пакет может содержать несколько подпрограмм, которые также могут содержать один или несколько параметров. Вы можете использовать PL/Scope, чтобы показать эту иерархию. Покажем на примере пакета, представленного в Листинге 1.

Листинг 1: Код пакета plscope_demo

CREATE OR REPLACE PACKAGE plscope_demo
IS
   PROCEDURE my_procedure (param1_in IN INTEGER
                         , param2 IN employees.last_name%TYPE
                          );
END plscope_demo;
/
CREATE OR REPLACE PACKAGE BODY plscope_demo
IS
   PROCEDURE my_procedure (param1_in IN INTEGER
                         , param2 IN employees.last_name%TYPE
                          )
   IS
      c_no_such   CONSTANT NUMBER := 100;
      l_local_variable     NUMBER;
   BEGIN
      IF param1_in > l_local_variable
      THEN
         DBMS_OUTPUT.put_line (param2);
      ELSE
         DBMS_OUTPUT.put_line (c_no_such);
      END IF;
   END my_procedure;
END plscope_demo;
/

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

Листинг 2: Запрос отображения иерархии идентификаторов

WITH plscope_hierarchy
        AS (SELECT line
                 , col
                 , name
                 , TYPE
                 , usage
                 , usage_id
                 , usage_context_id
              FROM all_identifiers
             WHERE     owner = USER
                   AND object_name = 'PLSCOPE_DEMO'
                   AND object_type = 'PACKAGE BODY')
SELECT    LPAD (' ', 3 * (LEVEL - 1))
       || TYPE
       || ' '
       || name
       || ' ('
       || usage
       || ')'
          identifier_hierarchy
  FROM plscope_hierarchy
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_context_id
ORDER SIBLINGS BY line, col
/

Результат запроса:

PACKAGE PLSCOPE_DEMO (DEFINITION)
   PROCEDURE MY_PROCEDURE (DEFINITION)
      FORMAL IN PARAM1_IN (DECLARATION)
         SUBTYPE INTEGER (REFERENCE)
      FORMAL IN PARAM2 (DECLARATION)
      CONSTANT C_NO_SUCH (DECLARATION)
         CONSTANT C_NO_SUCH (ASSIGNMENT)
         NUMBER DATATYPE NUMBER (REFERENCE)
      VARIABLE L_LOCAL_VARIABLE (DECLARATION)
         NUMBER DATATYPE NUMBER (REFERENCE)
      FORMAL IN PARAM1_IN (REFERENCE)
      VARIABLE L_LOCAL_VARIABLE (REFERENCE)

Использование Signature для отличия идентификаторов с одинаковыми именами

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

PROCEDURE plscope_demo_proc
IS
  plscope_demo_proc   NUMBER;
BEGIN
  DECLARE
    plscope_demo_proc   EXCEPTION;
  BEGIN
    RAISE plscope_demo_proc;
  END;

  plscope_demo_proc := 1;
END plscope_demo_proc;

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

PL/Scope упростил эту задачу, добавив столбец SIGNATURE в представление ALL_IDENTIFIERS. Каждый идентификатор имеет собственную сигнатуру - 32-байтную строку уникальную как внутри данной программной единицы (далее - юнит), так и вне ее (в отличие от USAGE_ID уникального только внутри своего юнита)

Листинг 3: Идентификаторы с одним именем

SELECT line
     , TYPE
     , usage
     , signature
  FROM all_identifiers
 WHERE     owner = USER
       AND object_name = 'PLSCOPE_DEMO_PROC'
       AND name = 'PLSCOPE_DEMO_PROC'
ORDER BY line

LINE  TYPE       USAGE        SIGNATURE                        
1     PROCEDURE  DEFINITION   51B3B5C5404AE8307DA49F42E0279915 
1     PROCEDURE  DECLARATION  51B3B5C5404AE8307DA49F42E0279915 
3     VARIABLE   DECLARATION  021B597943C0F31AD3938ACDAAF276F3 
6     EXCEPTION  DECLARATION  98E0183501FB350439CA44E3E511F60C 
8     EXCEPTION  REFERENCE    98E0183501FB350439CA44E3E511F60C 
11    VARIABLE   ASSIGNMENT   021B597943C0F31AD3938ACDAAF276F3

Заметьте, что каждая сигнатура появляется в выводе дважды. Для самой процедуры - DEFINITION и DECLARATION. Для переменных, констант, исключений и т.п. обязательно сначала будут строки с DECLARATION и лишь потом строки с такой же сигнатурой, показывающие строки кода в которых эти идентификаторы используются.

С помощью сигнатур можно легко отфильтровать только те строки кода, в которых используется конкретный искомый идентификатор, несмотря на многочисленные идентификаторы с тем же именем. Например, в Листинге 4 запрашиваются все присваивания и обращения к переменной PLSCOPE_DEMO_PROC .

Листинг 4: Запрос всех присваиваний и обращений к переменной PLSCOPE_DEMO_PROC

SELECT usg.line
     , usg.TYPE
     , usg.usage
  FROM all_identifiers dcl, 
      all_identifiers usg
 WHERE     
    dcl.owner = USER
 AND dcl.object_name = 'PLSCOPE_DEMO_PROC'
 AND dcl.name = 'PLSCOPE_DEMO_PROC'
 and dcl.usage = 'DECLARATION'
 and dcl.type = 'VARIABLE'
 and usg.signature = dcl.signature
 and usg.usage <> 'DECLARATION'
ORDER BY line

Давайте теперь рассмотрим для чего еще можно использовать PL/Scope:

• Проверка соглашений именования объектов и переменных
• Определение нарушений рекомендаций "Best practices"

Проверка соглашений именования объектов и переменных

С помощью PL/Scope легко и просто различить типы идентификаторов (переменные, константы, параметры и т.д.), поэтому можно также проверять не нарушают ли они правила именования для своего типа.

К примеру, я следую следующим соглашениям при именовании параметров:
IN параметры заканчивается постфиксом "_in"
OUT параметры - _out
IN OUT параметры - _io

Чтобы проверить следует ли юнит данным правилам, я посмотрю в строки с usage = declaration и типом FORMAL IN, FORMAL OUT или FORMAL IN OUT.

Предположим, есть пакет с описанием, представленным в Листинге 5.

Листинг 5: Создание пакета plscope_demo

CREATE OR REPLACE PACKAGE plscope_demo
IS
   PROCEDURE my_procedure (param1_in IN INTEGER, param2 IN DATE);

   FUNCTION my_function (param1    IN INTEGER
                       , in_param2 IN DATE
                       , param3_in IN employees.last_name%TYPE
                        )
      RETURN VARCHAR2;
END plscope_demo;

Выполнив запрос из Листинга 6, мы можем найти нарушения правил именования параметров в данном пакете.

Листинг 6:Поиск нарушений правил именования

SELECT prog.name subprogram, parm.name parameter
  FROM all_identifiers parm, all_identifiers prog
 WHERE     parm.owner = USER
       AND parm.object_name = 'PLSCOPE_DEMO'
       AND parm.object_type = 'PACKAGE'
       AND prog.owner = parm.owner
       AND prog.object_name = parm.object_name
       AND prog.object_type = parm.object_type
       AND parm.usage_context_id = prog.usage_id
       AND parm.TYPE IN ('FORMAL IN', 'FORMAL IN OUT', 'FORMAL OUT')
       AND parm.usage = 'DECLARATION'
       AND ( (parm.TYPE = 'FORMAL IN'
              AND LOWER (parm.name) NOT LIKE '%\_in' ESCAPE '\')
            OR (parm.TYPE = 'FORMAL OUT'
                AND LOWER (parm.name) NOT LIKE '%\_out' ESCAPE '\')
            OR (parm.TYPE = 'FORMAL IN OUT'
                AND LOWER (parm.name) NOT LIKE '%\_io' ESCAPE '\'))
ORDER BY prog.name, parm.name

Обратите внимание на использование usage_context_id для поиска подпрограммы-"владельца" параметра.

Определение нарушений рекомендаций "Best practices"

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

• Переменные декларированные в спецификации пакета. Это опасно тем, что любой пользователь с правом execute на пакет может читать и менять содержимое этих переменных напрямую.
• Объявленные, но не используемые в коде исключения. Разработчики могут объявить собственные исключения в блоке и, если они не используются, то от них желательно избавиться.(Это же, кстати, относится и к неиспользуемым переменным)

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

SELECT object_name, name, line
  FROM all_identifiers ai
 WHERE ai.owner = USER
     AND ai.TYPE = 'VARIABLE'
     AND ai.usage = 'DECLARATION'
     AND ai.object_type = 'PACKAGE'

Чтобы проверить объявленные, но не вызываемые исключения, нужно сначала понять какие значения USAGE возможны для исключений. Рассмотрим следующую процедуру:

PROCEDURE plscope_demo_proc
IS
   e_bad_data   EXCEPTION;
   PRAGMA EXCEPTION_INIT (
                e_bad_data, -20900);
BEGIN
   RAISE e_bad_data;
EXCEPTION
   WHEN e_bad_data
   THEN
      log_error ();
END plscope_demo_proc;

Посмотрим, что покажет PL/Scope об идентификаторе "e_bad_data":

SELECT line
     , TYPE
     , usage
  FROM all_identifiers
 WHERE owner = USER
   AND object_name = 
              'PLSCOPE_DEMO_PROC'
   AND name = 'E_BAD_DATA'
ORDER BY line
/

LINE  TYPE       USAGE
-----  ------------  ---------------
3     EXCEPTION  DECLARATION 
4     EXCEPTION  ASSIGNMENT  
6     EXCEPTION  REFERENCE   
8     EXCEPTION  REFERENCE  

Из этого можно сделать вывод, что EXCEPTION_INIT отображается как assignment - присваивание именованному исключению кода ошибки, а RAISE и WHEN - как references - использование.

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

Листинг 7:Запрос всех объявленных, но неиспользуемых исключений

WITH subprograms_with_exception
        AS (SELECT DISTINCT owner
                          , object_name
                          , object_type
                          , name
              FROM all_identifiers has_exc
             WHERE     has_exc.owner = USER
                   AND has_exc.usage = 'DECLARATION'
                   AND has_exc.TYPE = 'EXCEPTION'),
     subprograms_with_raise_handle
        AS (SELECT DISTINCT owner
                          , object_name
                          , object_type
                          , name
              FROM all_identifiers with_rh
             WHERE     with_rh.owner = USER
                   AND with_rh.usage = 'REFERENCE'
                   AND with_rh.TYPE = 'EXCEPTION')
SELECT *
  FROM subprograms_with_exception
MINUS
SELECT *
  FROM subprograms_with_raise_handle

Я реализовал многие из показанных в статье запросов в одном пакете, доступном по адресу http://www.oracle.com/technetwork/issue-archive/2010/10-sep/o50plsql-165565.zip