The Function Body

The PL/SQL statements that follow the BEGIN statement and precede the EXCEPTION and/or END statement make up the body of a function. The highlighted portion of Listing 5.18 is the function’s body.

Listing 5.18 The body of a function.

FUNCTION Parse_String (vStringToParse IN    varchar2)

  RETURN Global_Types.VARCHAR2_TABTYPE

IS

  iStringPos              integer;
  biIndex                 binary_integer := 0;
  DELIMITER      CONSTANT char (1) := '^';
  vString                 varchar2 (2000);
  Return_tab              VARCHAR2_TABTYPE;

  FUNCTION DelimiterPosition (vString IN     varchar2)
    RETURN integer;

  FUNCTION NextWord (vCheckString IN     varchar2)

    RETURN varchar2

  IS

  BEGIN
    iStringPos := DelimiterPosition (vString => vCheckString);
    IF (iStringPos > 0) THEN
       RETURN (substr (vCheckString, 1, iStringPos));
    END IF;

    RETURN NULL;
  END NextWord;

  FUNCTION DelimiterPosition (vString IN     varchar2)

    RETURN integer

  IS

  BEGIN
    RETURN (instr (vString, DELIMITER));
  END DelimiterPosition;

BEGIN
  vString := vStringToParse;

  LOOP
    Return_tab (biIndex) := NextWord;
     —
     — If there are no more elements in the string, return
     — the table.
     —
    IF (iStringPos = 0) THEN
       RETURN Return_tab;
    END IF;

    Return_tab (biIndex) := substr (vString, 1, (iStringPos - 1));

    biIndex := biIndex + 1;

    —
    — Chop off the first portion of the string.
    —
    vString := substr (vString, (iStringPos + 1));
  END LOOP;

EXCEPTION
  WHEN VALUE_ERROR THEN
      Log_System_Error (vErrorLocation => 'Parse_String',
                        vErrorText     => SQLERRM);
END;

Exception Handling

Exception handlers are defined within the function to handle error conditions that could reasonably be expected to occur while the function is executing. In Listing 5.19, the developer feels that the VALUE_ERROR exception might be encountered while processing the string if a delimited portion of the string exceeds the defined length of the PL/SQL table row.

Listing 5.19 The exception handling portion of a function.

FUNCTION Parse_String (vStringToParse IN    varchar2)

  RETURN Global_Types.VARCHAR2_TABTYPE

IS

  iStringPos              integer;
  biIndex                 binary_integer := 0;
  DELIMITER      CONSTANT char (1) := '^';
  vString                 varchar2 (2000);
  Return_tab              VARCHAR2_TABTYPE;

  FUNCTION DelimiterPosition (vString IN     varchar2)
    RETURN integer;

  FUNCTION NextWord (vCheckString IN     varchar2)

    RETURN varchar2

  IS

  BEGIN
    iStringPos := DelimiterPosition (vString => vCheckString);
    IF (iStringPos > 0) THEN
       RETURN (substr (vCheckString, 1, iStringPos));
    END IF;

    RETURN NULL;
  END NextWord;

  FUNCTION DelimiterPosition (vString IN     varchar2)

    RETURN integer

  IS

   BEGIN
     RETURN (instr (vString, DELIMITER));
   END DelimiterPosition;

BEGIN
  vString := vStringToParse;

  LOOP
    Return_tab (biIndex) := NextWord;
    --
    -- If there are no more elements in the string, return
    -- the table.
    --
    IF (iStringPos = 0) THEN
       RETURN Return_tab;
    END IF;

    Return_tab (biIndex) := substr (vString, 1, (iStringPos - 1));

    biIndex := biIndex + 1;

    --
    -- Chop off the first portion of the string.
    --
    vString := substr (vString, (iStringPos + 1));
  END LOOP;

EXCEPTION
  WHEN VALUE_ERROR THEN
      Log_System_Error (vErrorLocation => 'Parse_String',
                        vErrorText     => SQLERRM);
END;

Documenting Functions

The essential elements of documentation are the same for both functions and procedures. Your documentation must cover the following four basic aspects of the function:

•  Purpose

•  Parameters

•  Return value

•  Error conditions

Headers, pseudocode, comments, and identifier names all contribute to creating a well-documented piece of code.

The Header

Listing 5.20 contains a header for the Parse_String() function that we’ve been discussing.

Listing 5.20 The Parse_String() function with a header.

FUNCTION Parse_String (vStringToParse IN    varchar2)

  RETURN Global_Types.VARCHAR2_TABTYPE

-- *****************************************************************
-- Description: The Parse_String function accepts a single parameter
-- of type varchar2. This parameter is expected to be a series of
-- varchar2 strings delimited by a caret (^) symbol. If any section
-- of the parameter is longer than 10 characters, a VALUE_ERROR
-- exception will be raised when the section is stored in the
-- PL/SQL table.
--
-- The function returns a PL/SQL table, with each element of the
-- table containing a single section of the string.
--
-- REVISON HISTORY
-- Date           Author       Reason For Change
-- ----------------------------------------------------------------
-- 19 FEB 1997    J. Schmoe    Function created.
-- *****************************************************************

IS

  iStringPos     integer;
  biIndex        binary_integer := 0;
  DELIMITER      CONSTANT  char (1) := '^';
  vString        varchar2 (2000);
  Return_tab     VARCHAR2_TABTYPE;

BEGIN
  vString := vStringToParse;

  LOOP
     --
     -- Get the position of the next delimiter.
     --
     iStringPos := instr (vString, DELIMITER);
     --
     -- If there are no more elements in the string, return
     -- the table.
     --
     IF (iStringPos = 0) THEN
        RETURN Return_tab;
     END IF;

     Return_tab (biIndex) := substr (vString, 1, (iStringPos - 1));

     biIndex := biIndex + 1;

     --
     -- Chop off the first portion of the string so that the
     -- next iteration of the loop will get the next section.
     --
     vString := substr (vString, (iStringPos + 1));
  END LOOP;

EXCEPTION
  WHEN VALUE_ERROR THEN
      Log_System_Error (vErrorLocation => 'Parse_String',
                        vErrorText     => SQLERRM);
END;