CodesJava

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;  
IS|AS
	//Declaration block
BEGIN  
	//Execution_block 
	Return return_variable;  
EXCEPTION  
	//Exception block  
	Return return_variable;  
END; 
 /

How to create a function?

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

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 hr@codesjava.com.

Development

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

Copyright © 2019 CodesJava DMCA.com Protection Status SiteMap Reference: Java Wiki