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 SELECTstatement.

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;
/Code language: SQL (Structured Query Language) (sql)

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

You specify the function name function_nameafter the FUNCTIONkeyword. 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 INparameters, the compiler will issue an error message. You can pass a constant, literal, initialized variable, or expression to the function as the INparameter.
• An OUTparameter is a write-only parameter. The OUTparameters are used to return values back to the calling program. An OUTparameter is initialized to a 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 the value from an IN OUT parameter, change its value and return it back to the calling program.

The function must have at least one RETURNstatement 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 NULLif 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;Code language: SQL (Structured Query Language) (sql)

The iv_number is an INparameter 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 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;
/Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Notice that dualtable 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 anonymous blocks and in an SELECT statement.