 |
|
The Structure of a
Stored PL/SQL Function
Oracle Tips by
Burleson
|
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;
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
