10.4 How do I…Create a stored function?Problem
In my PL/SQL program, there is repetitive code that could be better designed using functions. I want to create a stored function that returns a result after performing some operations. I also want to create some local procedures and functions within a PL/SQL block. How do I create a stored function?
Technique
Stored functions are similar to stored procedures, except that a stored function returns a value and must be called as part of an expression. A function is a named PL/SQL block, which contains a declarative, an execution, and an exception section. The declarative and exception sections are optional. For more information about the sections of a PL/SQL block, see How-To 10.1. The structure of a stored function is shown here:
CREATE [OR REPLACE] FUNCTION function_name (parameter list) IS
[Optional declarative section contains all local types, variables, constants,
cursors and subprogram declarations. Note: There is no DECLARE statement.]
BEGIN
Executable section which is run when the function is executed.
A RETURN statement must be specified to return a value.
[EXCEPTION
Optional exception section to handle errors that may occur.]
END;
A stored function can accept parameters like a stored procedure (more information on passing parameters is covered in How-To 10.2). It must contain a RETURN statement to return a value back to the calling expression. When created, the stored function can be used in expressions in other PL/SQL blocks executed by the function owner or any user account granted EXECUTE privileges to the stored function.
Ever since the release of PL/SQL version 2.1, stored functions can be called from SQL statements with certain limitations. Stored functions can be used like system functions presented in Chapter 2, but they must meet certain conditions, depending on how they are used. Parameters used in these functions must use positional notation. They cannot be OUT or IN OUT parameters and are limited to only those datatypes supported by SQL. No stored function used in a SQL statement can modify data, and no procedures or functions called by the function can modify data. These rules are automatically enforced by checking the function body of a standalone stored function, but for functions defined in a package, the RESTRICT_REFERENCES pragma should be used in the package specification to enforce these rules, as explained in How-To 10.5.
Local subprograms (procedures and functions) can be specified in the declarative section of a PL/SQL block and remain local to the block; they can be called only from the executable section of the same block. Except that the CREATE OR REPLACE keywords cannot be used, a local procedure or function specified in the declarative section of a PL/SQL block has the same structure as stored procedures and functions. They all contain a header, a declarative section, an executable section, and an exception section. Local subprograms can reference other local subprograms as long as the referenced subprogram is declared before the referencing subprogram, or at least a forward declaration is provided for the referenced subprogram. All type, variable, and cursor declarations for the PL/SQL block must precede local subprogram declaration.
Steps
1. Run SQL*Plus, connect as the WAITE user account, and execute the CHP10_13.SQL file. It has a CREATE OR REPLACE FUNCTION statement to create a stored function, or you can replace it if it already exists in the database. It receives a single string parameter and returns a string. The results of the operation are displayed in Figure 10.13.
Lines 1 and 2 contain the function specification. Line 2 specifies the name of the function, its parameter, and the datatype of the value the function returns. All functions must include a returned datatype, which must be contained in the function specification. Lines 3 through 5 specify the body of the function. Line 4 uses the RETURN statement to return the value of the input parameter concatenated with a text string. A RETURN statement must be executed when the function is run, and a RETURN statement in a function must contain an expression. It is possible for a function to contain more than one RETURN statement, but only one can be executed each time the function is called.
2. Run the CHP10_14.SQL file in SQL*Plus. The PL/SQL block contained in the file executes the stored function and displays the value returned. The results of the operation are displayed in Figure 10.14.
Lines 1 and 2 contain the declarative section of the anonymous block. Line 2 declares a variable assigned the value returned by the function created in the previous steps. Line 4 calls the stored function, passing a string as a parameter, and it assigns the return value to the local variable created in line 2. Line 5 displays the string returned by the function.
When the PL/SQL block is executed, the stored function modifies the parameter passed and returns a string, which is displayed in SQL*Plus using the DBMS_OUPUT.PUT_LINE procedure.
3. Run the CHP10_15.SQL file in SQL*Plus. A function is declared within the declarative section of the anonymous PL/SQL block. Figure 10.15 shows the output of the block in SQL*Plus.
Lines 1 through 5 contain the declarative section of the block. Lines 2 through 5 declare a function local to the block. Line 2 contains the function specification for the local function. Lines 3 through 5 contain the executable section of the local function. Line 4 returns the string ‘WORLD’ when the function is called. Lines 6 through 8 contain the executable section for the main PL/SQL block. Line 7 uses the local function as a parameter to the DBMS_OUTPUT.PUT_LINE procedure to display the string returned by the function.
The string returned by the local function is displayed by the DBMS_OUTPUT.PUT_LINE procedure in SQL*Plus.
4. Run the CHP10_16.SQL file in SQL*Plus. The PL/SQL block in the file contains both a procedure and a function defined in the declarative section of the code. Figure 10.16 shows the output of the nested calls in the PL/SQL block.
The module demonstrates a local procedure calling a local function, all within the declarative section of a PL/SQL block. Lines 2 through 5 create a local function returning a string when it is called. Lines 6 through 9 define a local procedure that calls the local function. Lines 10 through 12 contain the execution section of the block, which calls the CALL_HELLO procedure, which in turn calls the HELLO_WORLD function.
Because the function is defined before the procedure in the declarative section, the function is visible to the procedure. An error would occur if the order of the procedure and function were reversed—if the procedure were defined before the function it calls. This can be resolved by a forward declaration, which consists of a subprogram specification terminated by a semicolon. In this case, you would have a forward declaration for the function as
FUNCTION HELLO RETURN VARCHAR2;
before the procedure definition, followed by the function definition. The forward declaration of the function enables it to be referenced in the procedure before it is defined.
The output displayed in SQL*Plus is generated by a local procedure calling a local function. The visibility of local procedures and functions depends on the order in which they are declared in the declarative section. Only after a declaration or forward declaration is provided for a local subprogram is it visible to other local subprograms.
5. Run the CHP10_17.SQL file in SQL*Plus, as shown in Figure 10.17. The file contains a local function declared in the declarative section of a stored procedure. This local function is complicated and generates words from numbers.
In the first block, lines 2 through 16 define a function local to the PL/SQL block. Lines 2 and 3 declare the local function. The function receives a numeric parameter and returns a string. The purpose of the function is to convert a number between 1 and 99 to a text representation. Line 4 declares the TMP variable in the local function. The variable holds the result of a DUAL table query using the TO_CHAR and TO_DATE functions to convert a numeric value to its text representation, shown in line 10. The conditional statement in line 6 returns a NULL value if the parameter is outside the acceptable range. Lines 17 through 19 contain the executable section for the main block. Line 18 uses the local function within the DBMS_OUTPUT.PUT_LINE procedure to display the output generated by the function.
An easier way to execute a stored procedure instead of using the anonymous block is to use the EXECUTE SQL*Plus command, which effectively wraps the BEGIN and END statements around the statement that calls the stored procedure. The stored procedure is invoked in SQL*Plus using the EXECUTE command (or EXEC for short):
SQL> EXECUTE SPELL_IT10(22);
TWENTY-TWO
PL/SQL procedure successfully completed.
SQL> EXECUTE SPELL_IT10(17);
SEVENTEEN
PL/SQL procedure successfully completed.
SQL> EXEC SPELL_IT10(75);
SEVENTY-FIVE
PL/SQL procedure successfully completed.
Each of the three values converted with the local function of the stored procedure are displayed in SQL*Plus. This step illustrates how you can use local subprograms in stored functions and procedures to simplify coding.
How It Works
Stored functions are similar to stored procedures because they execute a PL/SQL block on the server. Unlike stored procedures, stored functions return a value and must be used in an expression. They can also return simple datatypes or record variables. Step 1 presents a stored function that receives one parameter and returns a VARCHAR2 result. Step 2 executes the function created in Step 1. Step 3 creates a stored function in the declarative section of the anonymous PL/SQL block and calls it in the execution section. Step 4 illustrates the method of calling a local subprogram from another local subprogram, both of which are defined in the declarative section of a PL/SQL block. Step 5 shows the use of nested subprograms by defining local subprograms within the declarative section of a stored subprogram.
Comments
Stored functions return a single value and, unlike stored procedures, must be used in expressions. Although it is possible to return more than one value from a stored function by using OUT parameters, this is not recommended because it creates code that is difficult to read and maintain.
Functions and procedures should always be used to make your code more modular. If there is a piece of code that is repeated in a PL/SQL block, consider putting it in a local function or procedure. If it should be accessible to other applications, consider creating a stored function or procedure in the database. Stored functions can be used just like built-in functions in PL/SQL expressions and even in SQL statements. Columns returned by a query can be used as parameters for stored functions, to generate complex output from queries. Stored functions cannot be used in SQL statements if they modify data in the database.