Home / PL/SQL Tutorial / PL/SQL Function

PL/SQL Function

Summary: in this tutorial, we will introduce you to PL/SQL function. We will show you how to develop a PL/SQL function and call it from an anonymous block and SELECT statement.

Introducing to PL/SQL function

PL/SQL function is a named block that returns a value. A PL/SQL function is also known as a subroutine or a subprogram. To create a PL/SQL function, you use the following syntax:

CREATE [OR REPLACE] FUNCTION function_name [(
   parameter_1 [IN] [OUT] data_type,
   parameter_2 [IN] [OUT] data_type,
   parameter_N [IN] [OUT] data_type]
    RETURN return_data_type IS
--the declaration statements
BEGIN
   -- the executable statements
   return return_data_type;
   EXCEPTION
    -- the exception-handling statements
END;
/

Let’s examine the syntax of creating a function in greater detail:

You specify the function name function_name after the FUNCTION keyword. By convention, the function name should start with a verb, for example convert_to_number .

A function may have zero or more than one parameter. You specify the parameter names in the parameter_1, parameter_2, etc. You must specify the data type of each parameter explicitly in the data_type. Each parameter has one of three modes: IN, OUT and IN OUT.

  • An IN parameter is a read-only parameter. If the function tries to change the value of the IN parameters, the compiler will issue an error message. You can pass a constant, literal, initialized variable, or expression to the function as the IN parameter.
  • An OUT parameter is a write-only parameter. The OUT parameters are used to return values back to the calling program. An OUT parameter is initialized to default value of its type when the function begins regardless of its original value before being passed to the function.
  • An IN OUT parameter is read and write parameter. It means the function reads value from an IN OUT parameter, change its value and return it back to the calling program.

The function must have at least one RETURN statement in the execution section. The RETURN clause in the function header specifies the data type of returned value.

The block structure of a function is similar to an anonymous block with an additional function header section.

Examples of PL/SQL Function

We are going to create a function named try_parse that parses a string and returns a number if the input string is a number or NULL if it cannot be converted to a number.

CREATE OR REPLACE FUNCTION try_parse(
    iv_number IN VARCHAR2)
  RETURN NUMBER IS
BEGIN
   RETURN to_number(iv_number);
   EXCEPTION
     WHEN others THEN
        RETURN NULL;
END;

The iv_number is an IN parameter whose data type is VARCHAR2 so that you can pass any string to the   try_parse() function.

Inside the function, we used the built-in PL/SQL function named  to_number() to convert a string into a number. If any exception occurs, the function returns NULL in the exception section, otherwise it returns a number.

Calling PL/SQL Function

The PL/SQL function returns a value so you can use it on the right hand side of an assignment or in a SELECT statement.

Let’s create an anonymous block to use the function try_parse() function.

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
  n_x number;
  n_y number;
   n_z number;
BEGIN
   n_x := try_parse('574');
   n_y := try_parse('12.21');
   n_z := try_parse('abcd');

   DBMS_OUTPUT.PUT_LINE(n_x);
   DBMS_OUTPUT.PUT_LINE(n_y);
   DBMS_OUTPUT.PUT_LINE(n_z);
END;
/

We can also use the try_parse() function in a SELECT statement as follows:

SELECT try_parse('1234') FROM dual;

SELECT try_parse('Abc') FROM dual;

Notice that dual table a special one-row table that is used for selecting pseudo-column like our examples above.

In this tutorial, you’ve learned how to develop a custom PL/SQL function and call it in an anonymous blocks and in a SQL SELECT statement.