6、验证命名是否规范 Validate Naming Conventions
假设我有以下要求：
IN parameters: end with _in
OUT parameters: end with _out
IN OUT parameters: end with _io
为了验证一个程序单元符合这个规则，我将针对FORMAL IN, FORMAL OUT, or FORMAL IN OUT检索其声明情况。
假设我声明了以下测试包：
Code Listing
5: Creating the package specification
for 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;
Code Listing
6: Querying
to find naming violations
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
‘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
;
Oracle数据库之PL/SQL程序基础设计
PL/SQL Developer实用技巧分享
