Like any other part of the OpenACS, PL/SQL (or pl/pgsql) code +must be maintainable and professional. This means that it must be +consistent and therefore must abide by certain standards. The +standards will ensure that our product will be useful long after +the current people building and maintaining it are around. +Following are some standards and guidelines that will help us +achieve this goal:
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.
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.
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.
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 + ++
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.
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.
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.
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 arguments 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 + ++
Explicitly designate each parameter as "in," +"out," or "inout."
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.
Use %TYPE and %ROWTYPE whenever possible.
Use 't' and 'f' for booleans, not the PL/SQL +"boolean" datatype because it can't be used in SQL +queries.
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, they have already gotten an object_id and now they want to create the
+object with that object_id.
Some general style guidelines to follow for the purpose of +consistency across applications.
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.
Lowercase everything, with the exception of %TYPE and +%ROWTYPE.