Previous Table of Contents Next


It’s often useful when designing a module to generate pseudocode that outlines the logical steps that the module must take. Listing 5.21 shows a very simple bit of pseudocode for the Parse_String() function.

Listing 5.21 Pseudocode for the Parse_String() function.

store the string to be parsed in a local variable;

enter a loop

   get the location of the caret inside the string;
   if there is no delimiter in the string then
      return the array to the calling procedure;

   store the first section of the string in the array;

   chop off the first section of the string;
   increment the index variable for the array;

if the VALUE_ERROR exception occurs then
   log an error using the system log function;

This approach is especially useful with procedures, but functions often handle some very tough problems, too. Breaking the functionality of the module down into steps is the real work when writing code. Once the logic for the function has been thoroughly defined, the code can be written without too many problems.


Used thoughtfully, comments are an excellent tool for documenting code. The best comments describe why code works the way it does instead of describing how it works. Consider the following two sample comments:

Comment A:

-- If the employee has been late for work less than 1% of the time,
-- grant the employee a 0.5% raise.
IF (nOntimePercent > 99.0) THEN
   nRaiseAmount := nRaiseAmount + 0.005;

Comment B:

-- If nOntimePercent > 99 add .005 to nRaiseAmount.
IF (nOntimePercent > 99.0) THEN
   nRaiseAmount := nRaiseAmount + 0.005;

Comment A explains why the code is written in a particular way. Comment B paraphrases the code but doesn’t explain the business rules behind the code. There is nothing in comment B that will help you understand what business rules the code satisfies. The sample PL/SQL coding standard in Appendix D includes some guidelines about the content and location of comments.


Consider this example:

IF (x > 99.0) THEN
   y := y + 0.05;

It might take you a moment to recognize this block of code as the code from the previous example, only with different variable names. While it’s relatively easy to keep track of x and y in this example, when repeated several times in 200 lines of code, x and y will become painfully and hair-wrenchingly obscure.

The only time variable names like x and y are potentially meaningful is when referencing a loop control variable or the index of a PL/SQL table. Even then, it’s better to give variables names related to their functions. Using meaningful identifiers is one of the easiest ways to document code.

Step-By-Step: Creating A Function

Let’s get our feet wet by creating a function from scratch. We’ll start with a problem and discuss the relevant data, then we’ll design, write, and test our new function.

Function Requirements

Your assignment is to create a function that will find a professor who can teach a class. The ID number of the class will be provided as a parameter:. The business rules that the function must enforce are:

  A full professor is not allowed to teach more than four classes.
  A graduate student may not teach more than two classes.
  An instructor may not teach a class with a number above his or her approved level.
  An instructor with a minimum course level may not be assigned to teach a course below that level.
  Obviously, no one can teach two courses at the same time.

The next step is to establish which tables and views hold data that is relevant to the problem.

A record of information about professors is kept in the INSTRUCTORS table, which has this structure:

instructor_number    NOT NULL number   (5)
last_name                     varchar2 (15)
first_name                    varchar2 (15)
faculty_member_flag           char     (1)
approved_class_level          number   (3)
min_class_level               number   (3)
approved_field                varchar2 (3)
maximum_classes               char     (1)

Information about classes is kept in the SCHEDULED_CLASSES table, which has this structure:

course_number        NOT NULL number   (5)
course_field         NOT NULL char     (3)
instructor_number             number   (5)
semester_id          NOT NULL number   (3)
class_time                    number   (2)
field_level          NOT NULL number   (3)

The class_time column is a lookup code that points to a more detailed description of the class’s day and time in the CLASS_TIMES table, which has this structure:

class_time           NOT NULL varchar2 (2)
description          NOT NULL varchar2 (9)

Now that you understand the relationships between the various pieces of data that the function must consider, it’s time to start designing the function.

Previous Table of Contents Next