Previous | Table of Contents | Next |
The structure of a stored function consists of the following five sections:
Each section of a function has specific components , which are discussed in the following sections.
The function declaration consists of three distinct portions: function name, parameter definitions, and return datatypes.
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;
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;
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 |