使用PL/Scope分析PL/SQL代码 - 数据库编程

t_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

‘7、识别违反最佳做法的操作 Identify Violations of Best Practices

1）声明在包说明中的变量 Variables declared in the specification of a package，这种情况下任何对包有执行权限的用户都可直接读取该变量。

2）已声明但未在程序中抛出的异常 Exception declared but not raised in a program unit.

以上2类操作都是不合理的。

检查第一种情况简单：

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';

第二种情况，先要观察一下异常在程序中的各种使用类型（USAGES）

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;

Let’s see what PL/Scope has to say about the e_bad_data identifier:

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被当做赋值操作；RAISE statement and the WHEN clause被认为是引用操作。如此一来，我们声明一下语句即可：

Code Listing 7: Querying all subprograms in which an exception is declared but not referenced 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

使用PL/Scope分析PL/SQL代码(三)