User's Manual

182 Appendix B. PL/SQL Standards
4. 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.
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. That is, use the column name if the parameter corre-
sponds 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
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 cannot be used in SQL
queries.
11. All new functions (for example, acs_object.new, party.new, etc.) should optionally ac-
cept 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;