Easy learning with example program codes

Oracle pl sql function tutorial

Pl sql function:

The pl sql function is a named PL/SQL block which performs one or more specific tasks and must returns a value.

How to pass parameter in a function?

We can use the below modes to pass the parameters in a function:
IN-parameters: These parameters are the read-only parameters. Function cannot change the value of IN parameters.
OUT-parameters: These parameters are the write-only parameters and used to return values back to the calling program. Function can change the value of OUT parameters.
IN OUT-parameters: These parameters are read and write parameters i.e. a function can reads and change the IN OUT parameter value and return it back to the calling program.

Syntax of pl sql function:

CREATE [OR REPLACE] FUNCTION function_name [parameters] 
RETURN return_datatype;  
	//Declaration block
	Return return_variable;  
	//Exception block  
	Return return_variable;  

How to create a function?

create or replace function getMultiple(num1 in number, num2 in number)    
return number    
  num3 number(8);    
  num3 :=num1*num2;    
  return num3;    

How to execute a function?

A functions return value can be assign to a variable.

result :=  getMultiple(4, 5);

As a part of a SELECT statement:

SELECT getMultiple(4, 5) FROM dual;

In a PL/SQL Statement:

dbms_output.put_line(getMultiple(4, 5));

How to drop a function?

DROP FUNCTION function_name;


Industrial Training

We offers Placement Oriented Training on Java, Spring, JSF, Hibernate, PHP, AngularJS, Angular 4, PLSQL, Oracle BI Publisher etc. We also provides Online training, please mail us at


We also provides the Development services for Website Development , Java Development, PHP Development, Android App Development etc. You can contact us on

Copyright © 2019 CodesJava Protection Status SiteMap Reference: Java Wiki