Oracle PL/SQL Tutorial - PL/SQL Functions

• « Previous
• Next »

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); 
exception 
    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.

Note

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 
BEGIN 
    -- the executable section 
    return <return_data_type>; 
EXCEPTION
    -- the exception-handling section 
END; 
/ 

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'); 
exception 
    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.

begin 
    sys.dbms_output.put_line(to_mmsddsyyyy_or_null('01/01/2000')); 
    sys.dbms_output.put_line(to_mmsddsyyyy_or_null('02/29/2000')); 
    sys.dbms_output.put_line(to_mmsddsyyyy_or_null('02/29/2001')); 
    sys.dbms_output.put_line(to_mmsddsyyyy_or_null('9/9/2016')); 
    sys.dbms_output.put_line(to_mmsddsyyyy_or_null('9/9/9999')); 
    sys.dbms_output.put_line(to_mmsddsyyyy_or_null('1/1/9999 BC')); 
end; 
/ 

The code above generates the following result.

• Next »
• « Previous