169-175 | High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL

Table of Contents

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;

Table of Contents