Oracle Tutorial

What is Oracle
SQL Keywords

Oracle Wildcards

Oracle Wildcards

Oracle Aliases

Oracle Aliases

MySQL Tutorial

MySQL Tutorial

PL/SQL Tutorial

PL/SQL Tutorial

Oracle Interview Questions

Oracle Interview Questions and Answers


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;