<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 'http://www.w3.org/TR/html4/loose.dtd"'>
<html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>PL/SQL Standards</title><link rel="stylesheet" type="text/css" href="openacs.css"><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot"><link rel="home" href="index.html" title="OpenACS Core Documentation"><link rel="up" href="eng-standards.html" title="Chapter 12. Engineering Standards"><link rel="previous" href="eng-standards-filenaming.html" title="ACS File Naming and Formatting Standards"><link rel="next" href="variables.html" title="Variables"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><a href="http://openacs.org"><img src="/doc/images/alex.jpg" style="border:0" alt="Alex logo"></a><table width="100%" summary="Navigation header" border="0"><tr><td width="20%" align="left"><a accesskey="p" href="eng-standards-filenaming.html">Prev</a> </td><th width="60%" align="center">Chapter 12. Engineering Standards</th><td width="20%" align="right"> <a accesskey="n" href="variables.html">Next</a></td></tr></table><hr></div><div class="sect1"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="eng-standards-plsql"></a>PL/SQL Standards</h2></div></div></div><div class="authorblurb"><p>
By Richard Li and Yon Feldman
</p>
OpenACS docs are written by the named authors, and may be edited
by OpenACS documentation staff.
</div><p>
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:
</p><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a name="eng-standards-plsql-general"></a>General</h3></div></div></div><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>
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.
</p></li><li class="listitem"><p>
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.
</p></li></ol></div></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a name="eng-standards-plsql-code"></a>Code</h3></div></div></div><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>
Encapsulation of related functionality is key to maintainability
and upgradeability of our software. Try to bundle your code into
<a class="ulink" href="https://docs.oracle.com/database/121/LNPLS/packages.htm#LNPLS009" target="_top">packages</a>
whenever possible. This will make upgrading, bug fixing, and
customizing, among other things, a possibility.
</p></li><li class="listitem"><p>
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:
</p><pre class="programlisting">
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
</pre></li><li class="listitem"><p>
Always use <code class="computeroutput">create or replace procedure|function
<proc_or_func_name></code>. It makes reloading packages much
easier and painless to someone who is upgrading or fixing a bug.
</p></li><li class="listitem"><p>
Always qualify <code class="computeroutput">end</code> statements, i.e., the
<code class="computeroutput">end</code> statement for a package should be <code class="computeroutput">end
<package_name>;</code>, not just <code class="computeroutput">end;</code>; same
goes for procedures, functions, package bodies, and triggers.
</p></li><li class="listitem"><p>
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.
</p></li><li class="listitem"><p>
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:
</p><pre class="programlisting">
<code class="computeroutput">
acs_user.create(first_names => 'Jane', last_name => 'Doe', etc.)
</code>
instead of
<code class="computeroutput">
acs_user.create(first_names_in => 'Jane', last_name_in => 'Doe', etc.)
</code>
</pre><p>
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:
</p><pre class="programlisting">
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
</pre></li><li class="listitem"><p>
Explicitly designate each parameter as "in," "out," or "inout."
</p></li><li class="listitem"><p>
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.
</p></li><li class="listitem"><p>
Use %TYPE and %ROWTYPE whenever possible.
</p></li><li class="listitem"><p>
Use 't' and 'f' for booleans, not the PL/SQL "boolean" datatype
because it can't be used in SQL queries.
</p></li><li class="listitem"><p>
All <code class="computeroutput">new</code> functions (e.g., <code class="computeroutput">acs_object.new,
party.new,</code> etc.) should optionally accept an ID:
</p><pre class="programlisting">
<code class="computeroutput">
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;
</code>
</pre><p>
takes the optional argument <code class="computeroutput">object_id</code>. 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
<code class="computeroutput">object_id</code> and now they want to create the object with
that <code class="computeroutput">object_id</code>.
</p></li></ol></div></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a name="eng-standards-style"></a>Style</h3></div></div></div><p>
Some general style guidelines to follow for the purpose of
consistency across applications.
</p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>
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.
</p></li><li class="listitem"><p>
Lowercase everything, with the exception of %TYPE and %ROWTYPE.
</p></li></ol></div><div class="cvstag">($Id: eng-standards-plsql.html,v 1.55 2024/09/11 06:15:47 gustafn Exp $)</div></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="eng-standards-filenaming.html">Prev</a> </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right"> <a accesskey="n" href="variables.html">Next</a></td></tr><tr><td width="40%" align="left">ACS File Naming and Formatting Standards </td><td width="20%" align="center"><a accesskey="u" href="eng-standards.html">Up</a></td><td width="40%" align="right"> Variables</td></tr></table><hr><address><a href="mailto:docs@openacs.org">docs@openacs.org</a></address></div><a name="comments"></a></body></html>