Oracle PL/SQL Tutorial - PL/SQL Functions

A FUNCTION is a PL/SQL block or method that returns a value, so it can be used on the right-hand side of an assignment. Here is an example:

n_value := to_number('123.45'); 

Since a FUNCTION returns a value, you can also use it in a SQL statement, as in this example:

select to_number('1') from dual; 

Create a Function

Instead of dealing with errors when we try to convert a varchar2 to a number on the fly as in a SELECT statement, let's create an errorless to_number() function.

CREATE OR REPLACE FUNCTION to_number_or_null ( aiv_number IN varchar2 ) 
return number is 
begin -- w  w w  .  ja  v  a 2s.  c om
    return to_number(aiv_number); 
    when OTHERS then return NULL; 
end to_number_or_null; 

select to_number_or_null ( '123') from dual;

The code above generates the following result.


The DDL syntax to create the function is as follows:

CREATE [OR REPLACE] FUNCTION <function_name> [( 
<parameter_name_1> [IN] [OUT] <parameter_data_type_1>, 
<parameter_name_2> [IN] [OUT] <parameter_data_type_2>,... 
<parameter_name_N> [IN] [OUT] <parameter_data_type_N> )] 
RETURN <return_data_type> IS-- from www. j a v a  2s  . com
     --the declaration section 
    -- the executable section 
    return <return_data_type>; 
    -- the exception-handling section 

where <function_name> is the name of the FUNCTION; <parameter_name> is the name of a parameter being passed IN, OUT, or INand OUT; <parameter_data_type> is the PL/SQL data type of the corresponding parameter; <return_data_type> is the PL/SQL data type of the value that will be returned by the FUNCTION when it completes its execution.

Note 2

The brackets [] around the keywords OR REPLACE denote that they are optional.

The brackets around the parameters denote that they are optional, too.

The block structure of a FUNCTION is the same as an anonymous procedure, except for the addition of the Data Definition Language (DDL) CREATE FUNCTION keywords, the optional parameters, and the RETURN clause.

The following code is trying to use this errorless FUNCTION in a SELECT statement:

select to_number_or_null('A') from DUAL; 

It returned a NULL value since the letter A is not a number.

select to_number_or_null('234.56') from DUAL; 

Example 2

The following code creates a to_date() Function.

create or replace FUNCTION to_mmsddsyyyy_or_null ( aiv_date in varchar2 ) 
return date is 
begin --   w w w  . j a va  2 s . co m
    return to_date(aiv_date, 'MM/DD/YYYY'); 
    when OTHERS then 
    return NULL; 
end to_mmsddsyyyy_or_null; 

select to_mmsddsyyyy_or_null('A') from DUAL; 

The code above generates the following result.

The following PL/SQL block creates a test unit in order to test the function.

    sys.dbms_output.put_line(to_mmsddsyyyy_or_null('1/1/9999 BC')); 

The code above generates the following result.