A *function* is a subprogram that can take parameters and return a single value. A function has two parts:
*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.

- the specification and
- the body.

The function body has three parts:

The*declarative part* contains declarations of

- an optional declarative part,
- an executable part, and
- an optional exception-handling part.

The

- types,
- cursors,
- constants,
- variables,
- exceptions, and
- subprograms.

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.

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:

This would return: "The area of the circle with radius 5 is 78.53975".

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