Lesson 4	Functions returning a number, text, or date value
Objective	Create and execute function that returns number, text, or date value.

Create and execute a 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

a number,
text, or
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.
The following series of images below describe how to create a function from start to finish.

Creating a PL/SQL Function

1) Use CREATE or REPLACE to begin the definition of the function. In this example, the function name is FORMAT_ZIP — 1)
CREATE OR REPLACE FUNCTION FORMAT_ZIP
1) Use CREATE or (optionally) CREATE REPLACE to begin the definition of the function. In this example, the function name is FORMAT_ZIP.

2)A function can only use incoming parameters. In this example, the parameter is name ZIPCODE and is of the VARCHAR2 datatype. Notice that the parameter has no length. — 2)
CREATE OR REPLACE FUNCTION FORMAT_ZIP (ZIPCODE IN VARCHAR2)
2) A function can only use incoming parameters. In this example, the parameter is name ZIPCODE and is of the VARCHAR2 datatype. Notice that the parameter has no length.

3) The RETURN clause is required. The clause defines the datatype of the field returned by the function. In this exampel, the function returns a field that is a VARCHAR2 datatype. — 3)
CREATE OR REPLACE FUNCTION FORMAT_ZIP (ZIPCODE IN VARCHAR2) RETURN VARCHAR2 AS
3) The RETURN clause is required. The clause defines the datatype of the field returned by the function. In this example, the function returns a field that is a VARCHAR2 datatype. Notice that there is no name or length identifying the returning value. The AS keyword marks the beginning of the body of the function's definition.

4) The BEGIN clause marks the beginning of the function's declaration section, if there is one.

5) The DECLARE section is optional. In this example, a single variable named FORMATTED_ZIP is declared.

6) The second BEGIN keyword marks the end of the DECLARE section and the beginning of the body of the function. If there had been no DECLARE section, this line would have been left out.

7) Here is the first portion of the logic. In this example, the length of the incoming parameter (ZIPCODE) is tested. If the length is five, the declared variable (FORMATTED_ZIP) assigned a value equal to ZIPCODE concatenated with a dash and four zeros.

8) Continuing with the logic, this next section tests ZIPCODE for a length of nine. If true, the FORMATTED_ZIP variable is assigned to a value which takes the first five characters of ZIPCODE, adds a dash, and then tacks on the last four characters of ZIPCODE.

9) The final portion of this IF statement simply assigns FORMATTED_ZIP the value of ZIPCODE without any changes in its format.

10) There must be at least one RETURN clause like this one inside the body of the function. In this example, the value stored in the FORMATTED_ZIP variable is returned.

11) The first END closes the body section. It is needed in this case because there is a DECLARE section and a second BEGIN. The second END completes the entire function syntax.

12) Type a slash on a separate line and press ENTER to execute the CREATE FUNCTION command. The line "Function created" is the response from SQL*Plus after executing the CREATE command listed here. It tells you that the function was successfully created.

13) To call the function within a query the function name follows the SELECT clause, and the one parameter is placed between parentheses. — 13)
FUNCTION FORMAT_ZIP (ZIPCODE IN VARCHAR2) RETURN VARCHAR2 IS FORMATTED_ZIP VARCHAR2(10); BEGIN IF LENGTH(ZIPCODE) = 5 THEN FORMATTED_ZIP := ZIPCODE || '-0000'; ELSIF LENGTH(ZIPCODE) = 9 THEN FORMATTED_ZIP := SUBSTR(ZIPCODE,1,5) || '-' || SUBSTR(ZIPCODE,6,4); ELSE FORMATTED_ZIP := ZIPCODE; END IF; RETURN FORMATTED_ZIP; END; / SQL> SELECT FORMAT_ZIP('96753') FROM DUAL;

To call the function within a query the function name follows the SELECT clause (in this case), and the one parameter is placed between parentheses. For simplicity, the example uses a literal value as the parameter. The parameter can also be a

14) The results of the query show how the function has formatted the incoming parameter, 96753 into a ten-character string: 96753-0000 — 14)
ELSE FORMATTED_ZIP := ZIPCODE; END IF; RETURN FORMATTED_ZIP; END; / Function created. SQL> SELECT FORMAT_ZIP('96753') FROM DUAL; FORMAT_ZIP('96753') ------------------- 96753-0000 SQL>
The results of the query show how the function has formatted the incoming parameter, '96753' into a ten-character string: '96753-0000'. This function can be reused because its definition is now stored in the database.

PL/SQL Function Declaration

A function is a subprogram that can take parameters and return a single value. A function has two parts:

the specification and
the body.

The specification (spec for short) begins with the keyword FUNCTION and ends with the RETURN clause, which specifies the datatype of the return value. Parameter declarations are optional and functions that take no parameters are written without parentheses. The function body begins with the keyword IS (or AS) and ends with the keyword END followed by an optional function name.

PL/SQL Function Body: The function body has three parts:
1. an optional declarative part,
2. an executable part, and
3. an optional exception-handling part.
The declarative part contains declarations of
1. types,
2. cursors,
3. constants,
4. variables,
5. exceptions, and
6. subprograms.
These items are local and cease to exist when you exit the function. The executable part contains statements that assign values, control execution, and manipulate data. The exception-handling part contains handlers that deal with exceptions raised during execution. Note that the function declaration in a PL/SQL block or package is not the same as creating a function in SQL.
Oracle PL/SQL function that returns both a number and text:
Here is an example of an Oracle PL/SQL function that returns both a number and text. This function will take an input parameter, perform a calculation, and return a number along with a descriptive text.
```
CREATE OR REPLACE FUNCTION calculate_area(radius IN NUMBER) 
RETURN VARCHAR2 IS
  pi CONSTANT NUMBER := 3.14159;
  area NUMBER;
  result_text VARCHAR2(100);
BEGIN
  area := pi * radius * radius;
  result_text := 'The area of the circle with radius ' || TO_CHAR(radius) || ' is ' || TO_CHAR(area);
  RETURN result_text;
END calculate_area;
/
```
In this function, calculate_area, we declare a constant pi and two variables area and result_text. The function takes one input parameter, radius, which is of type NUMBER. The function is declared to return a VARCHAR2 type, which is a string of variable length. The BEGIN and END keywords enclose the body of the function. In the body, we calculate the area of a circle using the formula pi * radius * radius and assign it to the area variable. We then construct a string result_text that includes both the input radius and the calculated area. This string is returned by the function.
To call this function, you would use a SQL statement like this:
```
SELECT calculate_area(5) FROM dual;
```
This would return: "The area of the circle with radius 5 is 78.53975".

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.

Syntax for creating Function in PL/SQL

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] 
function
[( 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.