Guideline of writing cross functional PL/SQL project team codes.

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;

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,