Guideline of writing beautiful,
plsql code.
Many people, who are
working with Oracle SQL/PLSQL or just started, will find this post useful.
Writing the PLSQL
Procedure, Package, Function, Trigger. etc. especially when you're working
with cross project teams, can follow the following intending and
alignment guidelines to stream up and to generate the excellent result
of better code management and ease of reuse ability of code
which help to ease and enhance the code readability across the cross
functional project team.
First i will start
writing cursor.
CURSOR
cr_get_emp_salarry
IS
SELECT n_sal_amt
FROM
emp
WHERE n_empno =
64544;
As you
have already noticed that above Cursor All Keywords
are aligned and braked at each line.
As this is my prefer
writing style where all the Oracle Keywords are in Capital Letters and all our
column, table name, and the cursor name is in the small letters. This will make
the code looks more cleaner and more readable.,
Similarly, if you opt for the reverse style of choice for your PL/SQL Development project it is totally up to you but not recommended Like
below.
In the Following scheme
all the keywords are in small letters and all the columns, table and cursor
name are in capital letters.
cursor
CR_GET_EMP_SALARRY
is
select N_SAL_AMT
from EMP
where N_EMPNO =
64544;
but Notice
this type of style looks odd than the above style and hard to read. I prefer
writing the code in above in first example.
Likewise for all
Keywords used in PL/SQL such as IF, THEN, ELSE, RETURN, BEGIN, END should be in
Capital Letters and rest of your codes of name of the table, cursor, variables,
type name etc. should be in small letter.
One more thing to notice
here that whenever you are using variables names should tell by
itself what kind of variable is this. i.e DATE, NUMBER, VARCHAR2, CHAR, Parameter, Ref
Cursor etc.
"L(l)" tells
that the variable scope is local variable.
1. Variables which
are of Date type should start with small letter ld
like ld_dob,
ld_start_date, ld_end_date etc..
2. variables
which are of VARCHAR2 Type should start with lv
like lv_name,
lv_desc, lv_address
3.
variables which are of NUMBER data type should start with ln
i.e ln_sal,
ln_sum,
ln_amount etc.
4. same as with
collection type variable, if it is collection type variable can be start with
lty
CREATE TYPE ty_address
IS VARRAY(200) OF VARCHAR2(100);
lty_customer_adress ty_address;
lty_customer_adress ty_address;
5. Same as with
parameter of the procedure will be start with "p" along with type of
variable
i.e pv_name, pn_amount, pty_address etc.
6. To use for reference
cursor it will start with ref
i.e ref_name, ref_cur_date etc,
