Oracle Function
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.
Difference between Oracle Procedure and Oracle 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 Oracle 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.
Oracle Create Function Syntax
To create a function use CREATE OR REPLACE FUNCTION statement, use the following syntax:
Syntax
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
executable_section
END [function_name];
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.
Oracle CREATE FUNCTION example
The following Oracle show how to create a function or a call specification:
Example
CREATE OR REPLACE FUNCTION TotalUsers
RETURN number IS
total_users number(2) := 0;
BEGIN
SELECT count(*) into total_users
FROM tblUsers;
RETURN total_users;
END;
Oracle Call Function example
The following Oracle 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:
Example
DECLARE
total number(2);
BEGIN
total := TotalUsers();
DBMS_OUTPUT.PUT_LINE('Total no. of Users: ' || total);
END;
Oracle Drop Function Syntax
To delete a function use DROP FUNCTION statement, use the following syntax:
Syntax
DROP FUNCTION function_name;
Where:
function_name: The name of the function.
Oracle Drop Function example
The following Oracle statement show how to delete or drop existing Oracle function called "TotalUsers":
Example
DROP FUNCTION TotalUsers;