Анализ PL/SQL кода в Oracle 11g
("Zoom In on Your Code" By Steven Feuerstein
)
Стивен Фейерштейн(steven.feuerstein@quest.com)
ORACLE ACE
Используйте 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:
У этого параметра есть два возможных значения: 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
