function plsql

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;

 

Please follow and like us:
Content Protection by DMCA.com