Example

Here is a block of code that reflects no standardization of naming conventions:

CREATE OR REPLACE PROCEDURE showborrowedbooks (
   date_borrowed IN DATE)
IS
   date_returned DATE := SYSDATE;
   mindaysborrowed INTEGER := 10;

   TYPE book_borrowed IS RECORD (
      dateborrowed DATE,
      daysborrowed INTEGER,
      isbn         book.isbn%TYPE,
      datedue      DATE);

   borrowedbook book_borrowed;

   CURSOR allborrowed IS
      SELECT * FROM borrowed_book
       WHERE returned = 'N';
BEGIN
   IF dateborrowed < datereturned
   THEN
      FOR rec IN allborrowed
      LOOP
         borrowedbook:= rec;

         IF borrowedbook.daysborrowed > mindaysborrowed
         THEN
            pl (borrowedbook.isbn);
         END IF;
      END LOOP;
   END IF;
END showborrowedbooks;

Here’s that same block of code based on standards. I use underscores in names; suffixes on parameters, records, and cursors; prefixes to show scope (l_ for local) and type (c_ for constant). Compare carefully the following item names with those in the previous example:

CREATE OR REPLACE PROCEDURE show_borrowed_books (
   date_borrowed_in IN DATE)
IS
   c_date_returned CONSTANT DATE := SYSDATE;
   l_min_days_borrowed INTEGER := 10;

   TYPE book_borrowed_rt IS RECORD (
      date_borrowed DATE,
      days_borrowed INTEGER,
      isbn          book.isbn%TYPE,
      date_due      DATE);

   borrowed_book_rec book_borrowed_rt;

   CURSOR all_borrowed_cur IS
      SELECT * FROM borrowed_book
       WHERE returned = 'N';
BEGIN
   IF date_borrowed_in < c_date_returned
   THEN
      FOR book_rec IN all_borrowed_cur
      LOOP
         borrowed_book_rec := book_rec;

         IF borrowed_book_rec.days_borrowed > 
            l_min_days_borrowed
         THEN
            pl (borrowed_book_rec.isbn);
         END IF;
      END LOOP;
   END IF;
END show_borrowed_books;

Now it’s possible to look at any individual part of show_borrowed_books and make sense of the different kinds of structures manipulated by the program.

Benefits

By setting standards, you don’t have to constantly worry about how to write your code. Concentrate on the important stuff: the business logic.

Developers come up to speed more quickly on the code base as they transfer into new groups; they also transfer their knowledge and productivity from one project to another.

Challenges

Define naming standards that are appropriate to your project (not overly rigid for the size of the team and complexity of the application).

Get developer buy-in for the conventions. One way to achieve this is to get the developers themselves to set those conventions.

Check for compliance with conventions (although this is difficult to do). You can build scripts in PL/SQL and SQL to analyze source code for conformance with some rules (e.g., “Don’t use fixed-length CHAR declarations.”). Currently, a comprehensive review must be performed manually; I hope that tools will become available in the next several years.

Resources