• March 3, 2016
  • pl sql tutorial
  • Comments Off on Oracle pl sql function tutorial

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;


Comments are closed.