using pl/sql in oralce applications(1) General PL/SQL Coding Standards

If you put most of your code in packaged PL/SQL procedures, and then call those
procedures from your triggers, you will have modular form code that is easy to develop and maintain.

Be sure to group these smaller procedures into logical packages so their purpose is clear.
--尽量使用小的而且逻辑清楚的procedure.

Server–side
Server–side is a term used to describe PL/SQL procedures that are
stored in an Oracle database (on the database server).

Client–side
Client–side is a term used to describe PL/SQL procedures that run in
programs that are clients of the Oracle database, such as Oracle Forms,
Oracle Reports, and libraries.

[@more@]

General PL/SQL Coding Standards

1.Always Use Packages

PL/SQL procedures should always be defined within packages. Create
a package for each block of a form, or other logical grouping of code.

2.Package Sizes

A client–side (Oracle Forms) PL/SQL program unit’s source code and
compiled code together must be less than 64K.procedure.) This implies
that the source code for a program unit cannot exceed 10K.

If a package exceeds the 10K limit, you can reduce the size of the
package by putting private variables and procedures in one or more
”private packages.”

Keep the number of procedures in a package less than 25 to avoid exceeding the 10K
limit

3.Adding New Procedures to Existing Packages

When you add new procedures or functions to existing packages
(either stored in the database or in Oracle Forms libraries), you should
usually add them to the end of the package (and package specification).ORA–4062 errors.

4.Using Field Names in Client–Side PL/SQL Packages

Always specify field names completely by including the block name
(that is, BLOCK.FIELD_NAME instead of just FIELD_NAME).

5.Field Names in Procedure Parameters

Pass field names to procedures and use COPY to update field values
instead of using IN OUT or OUT parameters.

For example, declare a procedure as test(my_var VARCHAR2 IN)
and call it as test(’block.field’) instead of declaring the
procedure as test(my_var VARCHAR2 IN OUT) and calling it as
test(:block.field).

Explicitly associate the parameter name and value with => when the
parameter list is long to improve readability and ensure that you are
not ”off” by a parameter.

for example:
v_request_completed :=
fnd_concurrent.wait_for_request
(request_id => v_import_items_id,
INTERVAL => 5,
max_wait => 0,
phase => v_phase,
status => v_status,
dev_phase => v_dphase,
dev_status => v_dstatus,
MESSAGE => v_request_message
);

6.Using DEFAULT

Use DEFAULT instead of ”:=” when declaring default values for your parameters.
the calling procedure can override the values.

Using ”:=” is more precise because you are
assigning the values, not defaulting them; the values cannot be
overridden.

-- 常量用:=, 变量用default.

7.Use Object IDs

You should also consider storing the ID in a package global so that you
retrieve it only once while the form is running.

8.Handling NULL Value Equivalence

Use caution when handling NULL values in PL/SQL. For example, if
a := NULL and b := NULL, the expression (a = b) evaluates to
FALSE.
For this reason, to check if a value is equal to NULL, you must use the
operator ”is” instead.

9.Global Variables
Oracle Forms Developer and PL/SQL support different types of global
variables:
• Oracle Forms Global: a variable in the ”global” pseudo–block of
a form
• PL/SQL Package Global: a global defined in the specification of
a package
• Oracle Forms Parameter: a variable created within the Oracle
Forms Designer as a Parameter

-- define in package spec
v_errbuf VARCHAR2 (1000);
v_retcode NUMBER;
v_stage VARCHAR2 (1000);

--use in package body
PROCEDURE update_result (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
i_set_id IN NUMBER
);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/226700/viewspace-878024/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/226700/viewspace-878024/