天天看點

PLSQL Naming Convention Checking

Overview   Though we can set down some rules for the PL/SQL naming convention, it’s totally depending on developers’ morality to obey or ignore these rules. Fortunately, Oracle 11g introduces a new feature called “PL/SCOPE” which can be used for naming violation examination. By querying the Oracle view “USER_IDENTIFIERS”, you can  know which variable in PL/SQL code violates the rules we have made. However, it’s not trivial to do this by simply querying this view, it’s good to have some utility wrapper for this. This article is intended to introduce the package “PACK_PLSQL_DEV_TOOLKIT” (currently, there is only one function in the package) you can use to check your PL/SQL code for naming convention violation.

Naming Convention Rules      

Category Naming Convention Naming Case
Global variable g_<name> Lower case
Local variable l_<name> Lower case
Local constant C_<NAME> Upper case
Global constant GC_<NAME> Upper case
Exception e_<name> N/A
Type definition T_<name> N/A
procedure/function parameter p_<name> N/A

  And there is another rule: No global variable in package specification.   User Guide   1.        Prerequisites -           Please note that “PL/SCOPE” is available in Oracle 11g (and later) only, so you cannot have the package “PACK_PLSQL_DEV_TOOLKIT” compiled in your Oracle database if your db is prior to 11g. -           Before using the package, you need to compile your package that needs to be checked with the setting: PLSCOPE_SETTINGS=IDENTIFIERS:ALL.            For example,             ALTER PACKAGE PACK_PLSQL_DEV_TOOLKIT COMPILE PLSCOPE_SETTINGS='identifiers:all';              Alternatively, you can issue the following command first and then compile your package…             alter session set PLSCOPE_SETTINGS='identifiers:all';

                    or           alter system set PLSCOPE_SETTINGS='identifiers:all';              I recommend you set the setting system-widely, i.e. using “alter system…”   2.        Create the package PACK_PLSQL_DEV_TOOKLIT in your database.

CREATE OR REPLACE TYPE t_plsql_code_analysis_obj
IS OBJECT
( 
  source VARCHAR2(5000),
  name   VARCHAR2(30),
  message VARCHAR2(1000)
);
/

CREATE OR REPLACE TYPE t_plsql_code_analysis IS TABLE OF t_plsql_code_analysis_obj;
/




CREATE OR REPLACE PACKAGE PACK_PLSQL_DEV_TOOLKIT
AS
/*------------------------------Attention--------------------*/
--This package used Oracle 11g PLSCOPE feture for code analysis, 
    --so this package should be used only in 11g
    --
    --Before use this package, remember compile the package(procedure,etc)
    --with PLSCOPE_SETTINGS=IDENTIFIERS:ALL
    --e.g. 
    --ALTER PACKAGE PACK_PLSQL_DEV_TOOLKIT COMPILE PLSCOPE_SETTINGS='identifiers:all';
    --Or 
    --alter session set PLSCOPE_SETTINGS='identifiers:all' and then compile packages
    /*------------------------------------------------------------*/

/*
    * Check whether the code violate the following rules...
    *
    *--type definitions should be named starting with t_
    *--global (package level) variables should be called g_<name>
    *--parameters are named p_<parameter description>
    *--exceptions are named e_<name>
    *--local constant are named c_<name>
    *--global constant are named gc_<name>
    *--local variables have names starting with l_<name>
    *--variable and parameter names should be written in lowercase
    *--plus: no global variables in package spec
    *
    *@param p_object_name the plsql code unit name, e.g. package, procedure, function
    *@usage SELECT * FROM TABLE(pack_plsql_dev_toolkit.check_naming_violation('PACK_PLSQL_DEV_TOOLKIT'));
*/
FUNCTION check_naming_violation(p_object_name IN VARCHAR2) RETURN t_plsql_code_analysis PIPELINED;

END PACK_PLSQL_DEV_TOOLKIT;
/



CREATE OR REPLACE PACKAGE BODY PACK_PLSQL_DEV_TOOLKIT
AS
/*------------------------------Attention--------------------*/
--This package used Oracle 11g PLSCOPE feture for code analysis, 
    --so this package should be used only in 11g
    --
    --Before use this package, remember compile the package(procedure,etc)
    --with PLSCOPE_SETTINGS=IDENTIFIERS:ALL
    --e.g. 
    --ALTER PACKAGE PACK_PLSQL_DEV_TOOLKIT COMPILE PLSCOPE_SETTINGS='identifiers:all';
    --Or 
    --alter session set PLSCOPE_SETTINGS='identifiers:all' and then compile packages
    /*------------------------------------------------------------*/

/*
    * Check whether the code violate the following rules...
    *
    *--type definitions should be named starting with t_
    *--global (package level) variables should be called g_<name>
    *--parameters are named p_<parameter description>
    *--exceptions are named e_<name>
    *--local constant are named c_<name>
    *--global constant are named gc_<name>
    *--local variables have names starting with l_<name>
    *--variable and parameter names should be written in lowercase
    *--plus: no global variables in package spec
    *
    *@param p_object_name the plsql code unit name, e.g. package, procedure, function
    *@usage SELECT * FROM TABLE(pack_plsql_dev_toolkit.check_naming_violation('PACK_PLSQL_DEV_TOOLKIT'));
*/
FUNCTION check_naming_violation(p_object_name IN VARCHAR2) 
RETURN t_plsql_code_analysis PIPELINED
AS
BEGIN

FOR rec IN
        (

WITH identifiers AS
            (
SELECT    ui.name,
                        ui.type,
                        ui.usage,
                        ui.line,
                        ui.object_type,
                        ui.object_name,
                        us.text AS source
FROM    user_identifiers ui,
                        user_source us
WHERE    ui.object_name = us.name
AND        ui.object_type = us.type
AND        ui.line = us.line
AND        ui.usage = 'DECLARATION'
AND        ui.object_name = UPPER(p_object_name)
--AND        ui.object_type = 'PACKAGE BODY'
            ),
            global_section AS
            (
SELECT  min(line) AS end_line
FROM    identifiers
WHERE    type IN ('PROCEDURE', 'FUNCTION')
AND        object_type = 'PACKAGE BODY'
            ),
            names_violation AS
            (
SELECT  object_name || '[' || object_type || '] @ line<' || line || '>: ' || source AS source,
                        name AS var_name,
-- 1. parameters are named p_<parameter description>
                        CASE WHEN   type IN ('FORMAL IN','FORMAL OUT','FORMAL IN OUT') 
--AND usage = 'DECLARATION'
                             THEN   CASE WHEN SUBSTR(name, 1, 2) <> 'P_'
THEN 'procedure(function) parameter violation: should be called p_<name>!'
END

-- 2. type definitions should be named starting with t_
                             WHEN    type IN  ('ASSOCIATIVE ARRAY', 'SUBTYPE', 'RECORD', 'NESTED TABLE') 
--AND    usage = 'DECLARATION'
                             THEN     CASE WHEN SUBSTR(name, 1, 2) <> 'T_'
THEN 'type name violation: should be called t_<name>!'
END

-- 3. Local constant name should be named starting with c_ and in upper case
                        --    Global constant name should be named starting with gc_ and in upper case
                             WHEN    type in ('CONSTANT') 
--AND usage = 'DECLARATION'
                             THEN   CASE WHEN      line < global_section.end_line 
AND (SUBSTR(name, 1, 3) <> 'GC_' OR INSTR(source, name) = 0)
THEN  'global constant name violation: should be called GC_<NAME> and in upper case!'
WHEN      line > global_section.end_line
AND (SUBSTR(name, 1, 2) <> 'C_' OR INSTR(source, name) = 0)
THEN  'local constant name violation: should be called C_<NAME> and in upper case!'
END

-- 4. Exception name should be named starting with e_
                            WHEN    type in ('EXCEPTION') 
--AND usage = 'DECLARATION'
                            THEN    CASE WHEN SUBSTR(name, 1, 2) <> 'E_'
THEN 'exception name violation: should be called e_<name>!'
END

-- 5. local variables name should be starting with l_<game>
                        --    gocal variables name should be starting with g_<game>
                            WHEN    type in ('VARIABLE') 
--AND usage = 'DECLARATION'
                            THEN     CASE WHEN      line < global_section.end_line 
AND (SUBSTR(name, 1, 2) <> 'G_' OR INSTR(source, LOWER(name)) = 0)
THEN      'global variable name violation: should be called g_<name> and in lower case!'
WHEN      line > global_section.end_line
AND (SUBSTR(name, 1, 2) <> 'L_' OR INSTR(source, LOWER(name)) = 0)
THEN  'local variable name violation: should be called l_<name> and in lower case!'
END                    
END AS violation  

FROM     identifiers, 
                        global_section
WHERE    --usage = 'DECLARATION'
                        TYPE IN ('FORMAL IN','FORMAL OUT','FORMAL IN OUT',
'ASSOCIATIVE ARRAY', 'SUBTYPE', 'RECORD', 'NESTED TABLE',
'CONSTANT',
'EXCEPTION',
'VARIABLE')
            ),
            global_violation AS
            (
SELECT    object_name || '[' || object_type || '] @ line<' || line || '>: ' || source AS source,
                        name AS var_name,
'global variable should not defined in package specification!' AS violation
FROM    identifiers
WHERE    object_type = 'PACKAGE'
AND        type IN ('VARIABLE')
AND        usage = 'DECLARATION'
            )
SELECT  source,
                    var_name,
                    violation
FROM     names_violation
WHERE     violation IS NOT NULL
UNION ALL
SELECT     source,
                    var_name,
                    violation
FROM       global_violation
        )
        LOOP
PIPE ROW(t_plsql_code_analysis_obj(rec.source, rec.var_name, rec.violation));
END LOOP;

RETURN;

END check_naming_violation;

END PACK_PLSQL_DEV_TOOLKIT;
/      

  3.        To use the package is quite easy, you can just issue a SQL statement to examine your package, for example…   select * from table(PACK_PLSQL_DEV_TOOLKIT.check_naming_violation('PACK_LO_RUN_GEM_PROCESS'));   If you get some rules (mentioned in the section “Naming Convention Rules”) violated, you will get some records presented to you, like….  

PLSQL Naming Convention Checking

If you get no records returned, then congratulations that you have no rule violated!

轉載于:https://www.cnblogs.com/fangwenyu/archive/2011/11/28/2266094.html