Oracle PL/SQL by Example, Fifth Edition (2015)

A. PL/SQL Formatting Guide

This appendix summarizes some of the PL/SQL formatting guidelines used throughout this book. While formatting guidelines are not a required part of PL/SQL, they act as best practices that facilitate development of better-quality code, greater readability, and easier maintenance.

Case

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

Image Use uppercase for keywords (e.g., BEGIN, EXCEPTION, END, IF-THEN-ELSE, LOOP, END LOOP), data types (e.g., VARCHAR2, NUMBER), built-in functions (e.g., LEAST, SUBSTR), and user-defined subroutines (e.g., procedures, functions, packages).

Image 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 major factor in improving readability. In other words, you can reveal the logical structure of the program by using appropriate indentation in your code. Here are some suggestions:

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

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

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

Image Put different logical parts of the same structure on 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:

Image v_variable_name

Image con_constant_name

Image i_in_parameter_name, o_out_parameter_name, io_in_out_parameter_name

Image c_cursor_name or name_cur

Image rc_reference_cursor_name

Image r_record_name or name_rec

Image FOR r_stud IN c_stud LOOP...

Image FOR stud_rec IN stud_cur LOOP

Image type_name or name_type (for user-defined types)

Image t_table or name_tab (for PL/SQL tables)

Image rec_record_name or name_rec (for record variables)

Image 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 they are 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 complete because you cannot embed multiline comments within multiline comments. In other words, you can comment out portions of code that contain single-line comments, but you cannot comment out portions of code that contain multiline comments.

Other Suggestions

Here are a few additional small recommendations to assist you in making sure your PL/SQL code is neat and easy to follow.

Image For SQL statements embedded in PL/SQL, use the same formatting guidelines to determine how the statements should appear in a block.

Image Provide a comment header that explains the intent of the block and lists the creation date and the author’s name. Also include a line for each revision with the author’s name, date, and the description of the revision.

The following example shows the aforementioned suggestions. Notice that it also uses a monospaced font (Courier New) that makes the formatting easier. Proportionally spaced fonts can hide spaces and make lining up clauses difficult. Most text and programming editors by default use a monospaced font.

For Example

REM ********************************************************
REM * filename: coursediscount01.sql        version: 1
REM * purpose:  To give discounts to courses that have at
REM *           least one section with an enrollment of more
REM *           than 10 students.
REM * args:     none
REM *
REM * created by:  s.tashi       date: January 1, 2000
REM * modified by: y.sonam       date: February 1, 2000
REM * description: Fixed cursor, added indentation and
REM *              comments.
REM ********************************************************
DECLARE
  -- C_DISCOUNT_COURSE finds a list of courses that have
  -- at least one section with an enrollment of at least 10
  -- students.
  CURSOR c_discount_course IS
    SELECT DISTINCT course_no
      FROM section sect
     WHERE 10 <= (SELECT COUNT(*)
                    FROM enrollment enr
                   WHERE enr.section_id = sect.section_id
             );

  -- discount rate for courses that cost more than $2000.00
  con_discount_2000 CONSTANT NUMBER := .90;

  -- discount rate for courses that cost between $1001.00
  -- and $2000.00
  con_discount_other CONSTANT NUMBER := .95;

  v_current_course_cost course.cost%TYPE;
  v_discount_all NUMBER;
  e_update_is_problematic EXCEPTION;
BEGIN
  -- For courses to be discounted, determine the current
  -- and new cost values
  FOR r_discount_course in c_discount_course LOOP
    SELECT cost
      INTO v_current_course_cost
      FROM course
     WHERE course_no = r_discount_course.course_no;

    IF v_current_course_cost > 2000 THEN
       v_discount_all := con_discount_2000;
    ELSE
       IF v_current_course_cost > 1000 THEN
          v_discount_all :=  con_discount_other;
       ELSE
          v_discount_all := 1;
       END IF;
    END IF;

    BEGIN
      UPDATE course
         SET cost = cost * v_discount_all
       WHERE course_no = r_discount_course.course_no;
    EXCEPTION
      WHEN OTHERS THEN
        RAISE e_update_is_problematic;
    END;    -- end of sub-block to update record
  END LOOP; -- end of main LOOP

  COMMIT;
EXCEPTION
  WHEN e_update_is_problematic THEN
    -- Undo all transactions in this run of the program
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE
       ('There was a problem updating a course cost.');
  WHEN OTHERS THEN
    NULL;
END;
/