PLSQL Code Naming Conventions and Formatting Guidelines

PL/SQL Code Naming Conventions and Formatting Guidelines

Case

PL/SQL, like SQL, is case insensitive. The general guidelines here are as follows :

• Use uppercase for keywords (BEGIN, EXCEPTION, END, IF THEN ELSE, LOOP, END LOOP, etc.), datatypes (VARCHAR2, NUMBER), built-in functions (LEAST, SUBSTR, etc.), and user -defined subroutines (procedures, functions, packages).

• Use lowercase for variable names as well as column and table names in SQL.

White Space

White space (extra lines and spaces) is as important in PL/SQL as it is in SQL. It is a main factor in providing readability. In other words, you can reveal the logical structure of the program by using indentation in your code. Here are some suggestions:

• Put spaces on both sides of an equality sign or comparison operator.

• Line up structure words on the left (DECLARE, BEGIN, EXCEPTION, and END, IF and END IF, LOOP and END LOOP, etc.). In addition, indent three spaces (use the spacebar, not the tab key) for structures within structures.

• Put blank lines between major sections to separate them from each other.

• Put different logical parts of the same structure on a separate lines even if the structure is short. For example, IF and THEN are placed on one line, while ELSE and END IF are placed on separate lines.

Naming Conventions

To ensure against conflicts with keywords and column/table names, it is helpful to use the following prefixes:

• v_variable_name

• con_constant_name

• i_in_parameter_name, o_out_parameter_name, io_in_out_parameter_name

• c_cursor_name or name_cur

• rc_reference_cursor_name

• r_record_name or name_rec

  FOR r_stud IN c_stud LOOP...

  FOR stud_rec IN stud_cur LOOP

• type_name, name_type (for user-defined types)

• t_table, name_tab (for PL/SQL tables)

• rec_record_name, name_rec (for record variables )

• e_exception_name (for user-defined exceptions)

The name of a package should be the name of the larger context of the actions performed by the procedures and functions contained within the package.

The name of a procedure should be the action description that is performed by the procedure. The name of a function should be the description of the return variable.

FOR EXAMPLE

 PACKAGE student_admin    -- admin suffix may be used for administration.    PROCEDURE remove_student       (i_student_id IN student.studid%TYPE);    FUNCTION student_enroll_count       (i_student_id student.studid%TYPE)    RETURN INTEGER; 

Comments

Comments in PL/SQL are as important as in SQL. They should explain the main sections of the program and any major nontrivial logic steps.

Use single-line comments "--" instead of the multiline "/*" comments. While PL/SQL treats these comments in the same way, it will be easier for you to debug the code once it is completed because you cannot embed multiline comments within multiline comments. In other words, you are able to comment out portions of code that contain single-line comments, and you are unable to comment out portions of code that contain multiline comments.