PL/SQL Standards

ACS Documentation : ACS Engineering Standards : PL/SQL Standards

• General
• Code
• Style

General

1. All PL/SQL code must be well documented. We must write code that is maintainable by others, this is especially true in our case because we are building an open source toolkit than anyone can download and browse the source code. So document like you are trying to impress your "Introduction to Programming" professor or TA.
2. It is important to be consistent throughout an application as much as is possible given the nature of team development. This means carrying style and other conventions suchs as naming within an application, not just within one file.

1. Encapsulation of related fuctionality is key to maintainability and upgradeability of our software. Try to bundle your code into packages whenever possible. This will make upgrading, bug fixing, and customizing, among other things, a possibility.
2. When creating functions or procedures use the following template, it demonstrates most of the guidelines set forth in this document that correspond to functions and procedures:

          
           create or replace procedure|function <proc_or_func_name> (
                    <param_1>    in|out|inout <datatype>,
                    <param_2>    in|out|inout <datatype>,
                    ...
                    <param_n>    in|out|inout <datatype>
           )
           [return <datatype>]
           is
                   <local_var_1>    <datatype>
                   <local_var_2>    <datatype>
                   ...
                   <local_var_n>    <datatype>
           begin
                   ...
           end <proc_or_func_name>;
           /
           show errors
         
        

3. Always use create or replace procedure|function <proc_or_func_name>. It makes reloading packages much easier and painless to someone who is upgrading or fixing a bug.
4. Always qualify end statements, i.e., the end statement for a package should be end <package_name>;, not just end;; same goes for procedures, functions, package bodies, and triggers.
5. Always use the "show errors" SQL*Plus command after each PL/SQL block. It will help you debug when there are compilation errors in your PL/SQL code.
6. Name parameters as simply as possible, i.e., use the column name if the parameter corresponds to a table column. We're deprecating the v_* and *_in syntax in favor of named parameters notation:

         
           acs_user.create(first_names => 'Jane', last_name => 'Doe', etc.)
         
           instead of
         
           acs_user.create(first_names_in => 'Jane', last_name_in => 'Doe', etc.)
         
        

   To achieve this we must fully qualify arguements passed into procedures or functions when using them inside a SQL statement. This will get rid of any ambiguities in your code, i.e. it will tell the parser when you want the value of the column and when you want the value from the local variable. Here is an example:

         
           create or replace package body mypackage 
               .
               .
               procedure myproc(party_id in parties.party_id%TYPE) is begin
                   .
                   .
                   delete
                     from parties
                    where party_id = myproc.party_id;
                   .
                   .
               end myproc;
               .
               .
           end mypackage;
           /
           show errors
         
        

7. Explicitly designate each parameter as "in," "out," or "inout."
8. Each parameter should be on its own line, with a tab after the parameter name, then in/out/inout, then a space, and finally the datatype.
9. Use %TYPE and %ROWTYPE whenever possible.
10. Use 't' and 'f' for booleans, not the PL/SQL "boolean" datatype because it can't be used in SQL queries.
11. All new functions (e.g., acs_object.new, party.new, etc.) should optionally accept an ID:

          
            create or replace package acs_object
            as
                function new (
                    object_id       in acs_objects.object_id%TYPE default null,
                    object_type     in acs_objects.object_type%TYPE default 'acs_object',
    	        creation_date   in acs_objects.creation_date%TYPE default sysdate,
                    creation_user   in acs_objects.creation_user%TYPE default null,
                    creation_ip     in acs_objects.creation_ip%TYPE default null,
                    context_id      in acs_objects.context_id%TYPE default null
               ) return acs_objects.object_id%TYPE;
         
        

    takes the optional argument object_id. Do this to allow people to use the same API call when they are doing double click protection, that is, tehy have already gotten an object_id and now they want to create the object with that object_id.

1. Standard indentation is 4 spaces. Our PL/SQL code is not only viewable in the SQL files but also through our SQL and PL/SQL browsers. This means that we should try to make it as consistent as possible to all source code readers.
2. Lowercase everything, with the exception of %TYPE and %ROWTYPE.