The Structure Of A Function

The structure of a stored function consists of the following five sections:

•  Function declaration—Function name, parameters, and return datatype.

•  Variable declarations—Variables, constants, and user-defined exceptions.

•  Executable declarations —Local procedures and functions.

•  Body—The internal logic of the function.

•  Exception handling—Handlers for exceptions likely to occur while the function is executing.

Each section of a function has specific components, which are discussed in the following sections.

The Function Declaration

The function declaration consists of three distinct portions: function name, parameter definitions, and return datatypes.

•  Function name—The name of the function, which identifies the function to other stored objects.

•  Parameter definitions—The names, parameter types, datatypes, and default values of the function’s parameters, which specify how the function must be called.

•  Return datatype—The datatype that the function returns, which specifies what type of input the calling code should expect as a result.

The function declaration is highlighted in Listing 5.15.

Listing 5.15 The function declaration.

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;

Variable Declarations

The declarations section of a function allows you to define local variables, constants, and user-defined exceptions used by the function. These definitions are highlighted in Listing 5.16.

Listing 5.16 The variable declaration section 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;

Executable Declarations

The declarations of local procedures and functions are made in the executable declarations section of a function. These definitions are highlighted in Listing 5.17.

Listing 5.17 The executable declarations 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;