Encapsulation of related functionality is key to making our software maintainable and upgradeable. Try to bundle your code into packages whenever possible. This will make upgrading, bug fixing, customizing, and many 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. The end statement for a package should be end <package_name>;, not just end;. The same is true 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. That is, use the column name if the parameter corresponds to a table column. The syntax v_* and *_in is being deprecated in favor of the named parameter notation. Therefore, of these two examples, the first one is preferred:
acs_user.create(first_names => 'Jane', last_name => 'Doe', etc.) |
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 by telling the parser when you want the value of the column and when you want the value from the local variable.
For 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 cannot be used in SQL queries.
All new functions (for example, 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; |
The function above 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.