---

PL/SQL Create Function

A stored FUNCTION is a set of PL/SQL statements you can call by name. The PL/SQL Procedure is very similar to PL/SQL Function, except that a function returns a value to the environment in which it is called. Use the CREATE FUNCTION statement to create a standalone stored function or a call specification.

Difference between Procedure and Function

Procedures

• Procedures can have input/output parameters.
• Procedure can return zero or n values.
• Procedures cannot be called from Function.

Functions

• Functions can have only input parameters.
• Function must return a value.
• Functions can be called from Procedure.

A function contains a header and a body.

Header: The header part consist of name of the function and the parameters passed to the function.

Body: The body part consist of a declaration section, execution section and exception section similar to a general PL/SQL block.

Pass parameters in function

There are three ways to pass parameters in function:

• IN parameters: Indicate that you must supply a value for the argument when calling the function.
• OUT parameters: Indicate that the function passes a value for this argument back to its calling environment after execution.
• INOUT parameters: Indicate that you must supply a value for the argument when calling the function and that the function passes a value back to its calling environment after execution.

PL/SQL Create Function Syntax

To create a function use CREATE OR REPLACE FUNCTION statement, use the following syntax:

Where:

procedure-name: The name of the procedure_name.

[OR REPLACE]: This option used to modify of an existing procedure.

parameter_name: The optional parameter list contains name, mode and types of the parameters.

return_datatype : The RETURN clause specifies the data type you are going to return from the function.

procedure-body: This part contains executable block.

IS | AS: The AS keyword is used instead of the IS keyword for creating a standalone procedure.

PL/SQL Create Function example

The following PL/SQL show how to create a standalone stored function or a call specification:

PL/SQL Call Function example

The following PL/SQL show how to call above created function by passing required parameters along with the function name and if the function returns a value, then you can store the returned value:

---

---