165-169

Previous

Table of Contents

Next

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; 

Previous

Table of Contents

Next