Oracle PL/SQL by Example, Fifth Edition (2015)

Chapter 20. Functions

In this chapter, you will learn about

Creating Functions

Using Functions in SQL Statements

Optimizing Function Execution in SQL

A function that is stored in the database is much like a procedure, in that it is a named PL/SQL block that can take parameters and be invoked. There are key differences both in the way it is created and how it is used, however. In this short chapter, you will learn the basics of how to create, use, and drop a function. In Chapter 21, you will learn how to extend functions when they are placed into packages.

Lab 20.1: Creating Functions

After this lab, you will be able to

Create Stored Functions

Make Use of Functions

Functions are another type of stored code and are very similar to procedures. The significant difference between the two is that a function is a PL/SQL block that returns a single value. Functions can accept one, many, or no parameters, but they must have a return clause in their execution section. The data type of the return value must be declared in the header of the function. A function is not a stand-alone executable in the same way that a procedure is; that is, a function must always be used in some context. You can think of it as equivalent to a sentence fragment. A function produces output that needs to be assigned to a variable, or it can be used in a SELECT statement.

Creating Stored Functions

This section covers the basic function syntax and demonstrates how to create a function. The syntax for creating a function is as follows:

CREATE [OR REPLACE] FUNCTION function_name
(parameter list)
RETURN datatype
IS
BEGIN
<body>
RETURN (return_value);
END;

The function does not necessarily have any parameters, but it must have a RETURN value declared in the header, and it must return values for all of the possible execution streams. The RETURN statement does not have to appear as the last line of the main execution section, and there may be more than one RETURN statement (there should be a RETURN statement for each exception). A function may have IN, OUT, or IN OUT parameters—although you will rarely see anything except IN parameters, because it is bad programming practice to use the other parameters.

The following example shows the script for creating a function named show_description.

For Example ch20_1.sql

CREATE OR REPLACE FUNCTION show_description
(i_course_no course.course_no%TYPE)
RETURN varchar2
AS
v_description varchar2(50);
BEGIN
SELECT description
INTO v_description
FROM course
WHERE course_no = i_course_no;
RETURN v_description;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN('The Course is not in the database');
WHEN OTHERS
THEN
RETURN('Error in running show_description');
END;

When the function has been created in SQL Developer, the following message will be displayed in the script editor:

FUNCTION SHOW_DESCRIPTION compiled

This message indicates that the function was successfully compiled. It can also been seen in the Function node of the Database Objects in SQL Developer. See Figure 20.1.

Figure 20.1 Show_Description Function as seen in SQL Developer

The example’s function heading indicates that the function takes in a parameter of the number data type and returns a VARCHAR2. The function makes use of a VARCHAR2(5) variable called v_description. It gives this variable the value of the description of the course, whose number is passed into the function. The return value is then the variable.

There are two exceptions in the function. The first is the WHEN NO_DATA_FOUND exception, which is the one most likely to occur. The second exception, WHEN OTHERS, which is used as a catchall for any other error that may occur.

The RETURN clause is one of the last statements in the function. The reason for this positioning is that the program focus will return to the calling environment once the RETURN clause is issued.

The following example demonstrates the syntax for creating a function named id_is_good. The output returned in this example is a Boolean value.

For Example ch20_2.sql

CREATE OR REPLACE FUNCTION id_is_good
(i_student_id IN NUMBER)
RETURN BOOLEAN
AS
v_id_cnt NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_id_cnt
FROM student
WHERE student_id = i_student_id;
RETURN 1 = v_id_cnt;
EXCEPTION
WHEN OTHERS
THEN
RETURN FALSE;
END id_is_good;

The function id_is_good checks whether the ID passed in exists in the database. It takes in a number (which is assumed to be a student ID) and returns a BOOLEAN value. The function uses the variable v_id_cnt as a means to process the data. The SELECT statement obtains a count of the number of students with the numeric value that was passed in. If a student is found in the database, using the student_id as the primary key, the value of v_id_cnt will be 1. If the student is not in the database, the SELECT statement passes the focus to the exception-handling section, where the function returns a value of FALSE. The function makes use of a very interesting method to return TRUE. If the student is in the database, then v_id_cnt will equal 1, so the code RETURN 1 = v_id_cnt will actually return a value of TRUE.

Making Use of Functions

This section demonstrates how to make use of the stored function show_description that was created in the last section. The following example demonstrates how to call the stored function show_description in a PL/SQL block.

For Example ch20_3.sql

SET SERVEROUTPUT ON
DECLARE
v_description VARCHAR2(50);
BEGIN
v_description := show_description(&sv_cnumber);
DBMS_OUTPUT.PUT_LINE(v_description);
END;

A lexical parameter in the PL/SQL block of &cnumber causes the user to be prompted as follows:

Enter value for cnumber:

In SQL Developer, a pop-up window will be displayed, which requests that the user enter a value for the substitution variable, as shown in Figure 20.2.

Figure 20.2 Enter Substitution Dialog Box in SQL Developer

If you enter “350,” you will see “Java Developer II” in the DBMS Output window of SQL Developer. In the Script Output window, you will see the old version of the script with the substitution variable &sv_cnumber and then you will see the new version of the script where the substitution variable &sv_cnumber has been replaced by 350. See Figure 20.3.

Figure 20.3 Execution of Function Show_Description

When this script is run in SQL*Plus, you will see the following output:

old 4: v_descript := show_description();
new 4: v_descript := show_description(350);
Java Developer II
PL/SQL procedure successfully completed.

This message means that the value for &sv_cnumber has been replaced with 350. The function show_description returns a VARCHAR2 value, which is the course description for the course number that is passed in. The PL/SQL block initializes the value of v_description with the value returned by the show_description function. This value is then displayed with the DBMS_OUTPUT package.

The following example is an anonymous block that makes use of the function id_is_good.

For Example ch20_4.sql

DECLARE
V_id number;
BEGIN
V_id := &id;
IF id_is_good(v_id)
THEN
DBMS_OUTPUT.PUT_LINE
('Student ID: '||v_id||' is a valid.');
ELSE
DBMS_OUTPUT.PUT_LINE
('Student ID: '||v_id||' is not valid.');
END IF;
END;

This PL/SQL block evaluates the return from the function and then determines which output to project. Because the function id_is_good returns a Boolean value, the easiest way to make use of this function is to run it and use the result (which will be either TRUE or FALSE) in an IFstatement. When testing the Boolean function id_is_good, the line IF id_is_good(v_id) means that if the function id_is_good for the variable results in a return of TRUE, another set of statements will be executed. The ELSE clause covers what will happen if the function returns FALSE.

Lab 20.2: Using Functions in SQL Statements

After this lab, you will be able to

Invoke Functions in SQL Statements

Write Complex Functions

Invoking Functions in SQL Statements

Functions return a single value and can be very useful in a SELECT statement. In particular, they can help you avoid repeated complex SQL statements within a SELECT statement. The following statement demonstrates the use of the show_description function in a SELECT statement for every course in the COURSE table:

SELECT course_no, show_description(course_no)
FROM course;

It is identical to the following SELECT statement:

SELECT course_no, description
FROM course;

Functions can also be used in a SQL statement. In fact, you have been using them all along; you just might not have realized it. As a simple example, imagine using the function UPPER in a SELECT statement:

SELECT UPPER('bill') FROM DUAL;

The Oracle-supplied function UPPER returns the uppercase value of the parameter that was passed in.

For a user-defined function to be called in a SQL expression, it must be a ROW function, not a GROUP function, and the data types must be SQL data types. The data types cannot be PL/SQL data types like a Boolean value, table, or record. Additionally, the function is not allowed to include any DML statements (INSERT, UPDATE, DELETE).

To use a function in a SQL SELECT statement, the function must have a certain level of purity, which is accomplished with the PRAGMA RESTRICT_REFERENCES clause. This topic is discussed in detail in Chapter 21 in the context of functions within packages.

Writing Complex Functions

This section introduces a more complex function that will be used in Chapter 21 on packages. As the following example demonstrates, functions can become very elaborate and complex.

For Example ch20_5.sql

CREATE OR REPLACE FUNCTION new_instructor_id
RETURN instructor.instructor_id%TYPE
AS
v_new_instid instructor.instructor_id%TYPE;
BEGIN
SELECT INSTRUCTOR_ID_SEQ.NEXTVAL
INTO v_new_instid
FROM dual;
RETURN v_new_instid;
EXCEPTION
WHEN OTHERS
THEN
DECLARE
v_sqlerrm VARCHAR2(250)
:= SUBSTR(SQLERRM,1,250);
BEGIN
RAISE_APPLICATION_ERROR(-20003,
'Error in instructor_id: '||v_sqlerrm);
END;
END new_instructor_id;

This is a function that generates a new instructor ID. If the sequence fails to generate a new instructor ID, then a SQL error is returned.

Lab 20.3: Optimizing Function Execution in SQL

After this lab, you will be able to

Defining a Function Using the WITH Clause

Create a Function with the UDF Pragma

In Oracle 12.1, a few new features were introduced that allow for improved optimization of functions that are used in SQL statements. In particular, function definition can take place in the same statement as the SELECT operation.

Defining a Function Using the WITH Clause

Starting with Oracle Database 12.1, you can define functions as well as procedures within the same SQL statement in which the SELECT statement appears. This alleviates the context switch between the PL/SQL and SQL engines by allowing both steps to take place in the SQL engine and, in turn, provides for a performance gain. The function or procedure needs to be defined using the WITH clause. In previous versions of the Oracle platform, only subqueries could be defined in the WITH clause.

The following example demonstrates how the show_description function, which was developed earlier in this chapter, can be used in the WITH clause. The function has been renamed show_descript to ensure that it is not confused with the previous version,show_description.

For Example ch20_6.sql

WITH
FUNCTION show_descript
(i_course_no course.course_no%TYPE)
RETURN varchar2
AS
v_description varchar2(50);
BEGIN
SELECT description
INTO v_description
FROM course
WHERE course_no = i_course_no;
RETURN v_description;
END;
SELECT course_no, show_descript(course_no), cost
FROM COURSE

The WITH FUNCTION feature is useful in many different situations. The main downside to this feature is that you lose the benefits of a reusable function in favor of obtaining improved performance through reduced context shifts between the SQL and PL/SQL engines. Before deciding which approach to use, it is advisable to do a cost analysis and weigh the benefits against the possible need to reuse the function in other contexts.

Creating a Function with the UDF Pragma

Functions can be created by adding the UDF pragma syntax, which notifies the compiler that a user-defined function will be used in SQL statements. A pragma is basically a hint to the compiler that allows it to optimize the function appropriately. When the UDF pragma syntax is used, the function will have higher performance when used in SQL. Very little needs to be done to apply the pragma, except to add the phrase pragma UDF prior to the variable declaration, as shown in bold in the following example:

For Example ch20_7.sql

CREATE OR REPLACE FUNCTION show_description
(i_course_no course.course_no%TYPE)
RETURN varchar2
AS
pragma UDF;
v_description varchar2(50);
BEGIN
SELECT description
INTO v_description
FROM course
WHERE course_no = i_course_no;
RETURN v_description;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN('The Course is not in the database');
WHEN OTHERS
THEN
RETURN('Error in running show_description');
END;

Summary

In this chapter, you learned how to create and execute functions. You also learned how to include functions in SQL statements. Finally, you learned two methods to optimize function execution in SQL: use of the WITH function and use of the pragma UDF syntax.

By the Way

The companion website provides additional exercises and suggested answers for this chapter, with discussion related to how those answers resulted. The main purpose of these exercises is to help you test the depth of your understanding by utilizing all of the skills that you have acquired throughout this chapter.