Coding standards PL/SQL SQL 2022

  This article show simple coding standard that I use for coding. Simple rules clean code.

General guidelines  

  1. Do not use names with a leading numeric character. 
  2. Always choose meaningful and specific names. 
  3. Avoid using abbreviations unless the full name is excessively long. 
  4. Avoid long abbreviations. Abbreviations should be shorter than 5 characters. 
  5. Any abbreviations must be widely known and accepted. Create a glossary with all accepted abbreviations. 
  6. Do not use ORACLE reserved words as names. A list of ORACLE’s reserved words may be found in the dictionary view V$RESERVED_WORDS
  7. Avoid adding redundant or meaningless prefixes and suffixes to identifiers. 
    Example: CREATE TABLE EMP_TABLE
  8. Always use the same names for elements with the same meaning.

Database Object Naming Conventions 

 Never enclose object names (table names, column names, etc.) in double quotes to enforce mixed case or lower case object names in the data dictionary. 

Identifier 

Naming conventions 

Column 

  1. singular name of what is stored in the column unless the column data type is a collection, then you use a plural name 
  2. add a comment to the database dictionary for every column 
     
    COMMENT ON COLUMN schema.table.column IS string;   

Object Type 

  1. name of an object type is built by its content (singular) followed by an “_obj” suffix. 
     
    Examples:
  • orders_obj 
  • invoices_obj 

Collection Type 

  1. collection type should include the name of the collected objects in their name. 
  2. should have the suffix “_tab” to identify it as a collection. 
     
    Examples:  
  • orders_tab 
  • invoices_tab   

Primary Key Constraint 

  1. table name or table abbreviation followed by the suffix “_pk”. 
  2. optionally prefixed by a project abbreviation. 
     
    Examples:
  • dim_geo_pk 
  • dim_prod_pk   

Foreign Key Constraint 

  1. table abbreviation followed by referenced table abbreviation followed by a “_fk” an and optional number suffix. 
  2. optionally prefixed by a project abbreviation. 
     
    Examples:  
  • fact_orders_dim_prod_fk 
  • fact_invoices_dim_geo_fk   

Function 

  1. name is built from a verb followed by a noun. The name of the function should answer the question “What is the outcome of the function?” 
  2. It should have prefix “fn_“Examples: 
  • fn_get_name 

     3. If more than one function provides the same outcome, you have to be more specific with the name.  

     Examples:  

  • fn_get_next_name 
  • fn_get_next_email 

Index 

  1. Indexes serving a constraint (primary, unique or foreign key) are named accordingly like constraints 
  2. other indexes should have the name of the table and columns (or their purpose) in their name and should also have “_idx” as a suffix. 
     
    Examples:
  • date_key_orders_ idx 
  • date_key_invoices_idx 

   3. bitmap indexes should use different suffix than b*tree indexes “_bidx”   

Examples:

  • product_key_invoices_bidx 
  • sales_category_orders_bidx   

Package 

  1. Name is built from the content that is contained within the package. 
  2. Prefix “pkg_” 
     
    Examples:
  • pkg_warehouse_processing
  • pkg_oltp_processing 

Procedure 

  1. name is built from a verb followed by a noun. The name of the procedure should answer the question “What is done?”  
  2. should use prefix “prc_” 
     
    Examples:
  • prc_calculate_salary 
  • prc_set_hiredate 
  • prc_check_order_state   

Sequence 

  1. name is built from the table name (or its abbreviation) the sequence serves as primary key generator and the suffix “_seq” or the purpose of the sequence followed by a _seq. 
     
    Examples: 
  • migration_seq 
  • product_seq   

Table  

  1. plural name of what is contained in the table unless the table is designed to always hold one row only – then you should use a singular name 
  2. optionally prefixed by a project abbreviation or type of table 
  • STG_ – stage tables 
  • EXT_ – external tables 
  • META_ – metadata tables 
  • FCT_ – fact tables 
  • DIM_ – dimension tables 
  • AGGR_ – aggregation tables 
  • CACHE_ – cache tables 
  • BCKP_ – backup tables 
  1. add a comment to the database dictionary for every table. 
     
    COMMENT ON TABLE schema.table IS string; 
     
    Examples:
  • stg_geo_load
  • dim_geo
  • fact_order
  • meta_setup  

Temporary Table 

  1. naming as described for tables. 
  2. suffixed by “_tmp” 

 Examples:  

  • dim_date_tmp 
  • dim_geo_tmp 

Unique Key Constraint 

  1. table name or table abbreviation followed by the role of the unique constraint, an “_uk” and an optional number suffix. 
  2. optionally prefixed by a project abbreviation. 
     
    Examples:  
      
  • dim_date_uk 
  • dim_geo_uk   

Materialized View, View 

  1. Plural name of what is contained in the view. 
  2. Optionally prefixed by a project abbreviation. 
  3. Optionally suffixed by an indicator identifying the object as a view 
  4. (mostly used, when a 1:1 view layer lies above the table layer) 
  5. Add a comment to the database dictionary for every view and every column. 
  6. prefix “vw_” for views and “mv_” for materialized views 
     
    Examples: 
  • vw_active_products
  • mv_sum_orders  

Naming conventions for variables 

Basic type of declarations are global, local and parameters 

Base type of variable 

Prefix 

Naming conventions – examples 

Global variable 

Examples: 

  • g_name   

Local variable 

Examples: 

  • l_name   

Parameter 

Examples: 

  • p_name   

More advanced attributes that can be declared for variable in PL/SQL 

Variable definitions 

Suffix 

Naming conventions – examples

Cursor 

cur 

Examples: 

  • g_name_cur 
  • l_name_cur 
  • p_name_cur   

Record 

rec 

Examples: 

  • g_name_rec 
  • l_name_rec 
  • p_name_rec   

Array/Table 

tab 

Examples: 

  • g_name_tab 
  • l_name_tab 
  • p_name_tab   

Object 

obj 

Examples: 

  • g_name_obj 
  • l_name_obj 
  • p_name_obj   

Constants 

const 

Examples: 

  • g_name_const 
  • l_name_const   

Type definitions inside code is very special case. It’s not variable but new type that a variable can use to define its type.  

Definition 

prefix and suffix 

Naming conventions 

Type definition 

type_ _rec,tab,obj 

Exception it should have prefix type_ and suffix  

Examples: 

  • type_name_rec 
  • type_name_tab 
  • type_name_obj 

Please notice that types created on database level don’t have type_ prefix only suffix _obj or _tab 

Have a fun

  Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.