RelationalDBDesign RelationalDBDesign

User-Defined Functions «Prev  Next»
Lesson 4 Functions returning a number, text, or date value
ObjectiveCreate and execute function that returns number, text, or date value.

Create and execute function in PL/SQL

Create and execute a function that returns a number, text, or date value. As mentioned in the previous lesson, you can use a function just about anywhere that you use a column or expression in an SQL command, as long as the function returns a standard Oracle datatype
  1. a number,
  2. text, or
  3. date value.
This lesson demonstrates how to create a function using SQL*Plus, and includes an exercise to create a function yourself.
This function is going to be used to format a zip code. It will add a dash between the first five characters and the last four characters when the value is a 9-digit zip code. If it is any other length, the format of the value remains unchanged.

Creating a function

Look at the Slide Show below and follow along as we create a function from start to finish.

The following link contains the same slides with additional detail.
Function Returning Number

Now that you have seen how a function is created and executed within an SQL query, it is time to try to create one yourself.

create function Syntax

The syntax for the create function command is more complicated than the syntax for the create procedure command. At a high level, the syntax is

create [or replace] function [schema] 
[( argument [ in | out | in out ] [nocopy] datatype
[, argument [ in | out | in out ] [nocopy] datatype]...
return datatype
[{ invoker_rights_clause | deterministic | parallel_enable_clause }
[ invoker_rights_clause | deterministic | parallel_enable_clause ]...
{ { aggregate | pipelined } using [schema .] implementation_type
| [pipelined] { is | as } { pl/sql_function_body | call_spec }};

Both the header and the body of the function are created by this command. The return keyword specifies the datatype of the function's return value. This can be any valid PL/SQL datatype. Every function must have a return clause, since the function must, by definition, return a value to the calling environment.

Function Returning Value - Exercise

Click the Exercise link below to create your own function.
Function Returning Value - Exercise
The next lesson examines functions that return a Boolean value.