In Oracle FUNCTION is a set of Oracle statements you can call by name. The Oracle Procedure is very similar to Oracle 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.
Procedures
Functions
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 Oracle block.
There are three ways to pass parameters in function:
To create a function use CREATE OR REPLACE FUNCTION statement, use the following syntax:
Where:
function_name: The name of the function.
[OR REPLACE]: This option used to modify of an existing function.
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.
To delete a function use DROP FUNCTION statement, use the following syntax:
Where:
function_name: The name of the function.