web analytics

Creating Custom Functions in Oracle

Here it is a simple procedure to create and call a custom function in Oracle. A custom function is a simple PL/SQL subprogram that is used to calculate a value. It creation syntax is almost same as of procedure except it has a RETURN clause that is used to return the computed value.

Syntax of creating a custom function:

CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [function_name];

Custom functions may have zero or more parameters in place. There are three types of parameters that can be defined in a custom function.

  1. IN: This parameter can be called from a procedure or a function. The value of this parameter cannot be overwritten by procedure or function.
  2. OUT: This parameter cannot be called from a procedure or function, but its value can be overwritten from them.
  3. IN OUT: It has both the properties of above type. As it can be called from procedure and functions and also can be overwritten by them.

Some examples of creating and calling custom functions:

  • Function without parameters.

FUNC1

 

 

 

 

 

 

 

 

 

Function with parameter.

FUNC2

Leave a Reply

%d bloggers like this: