Function in SQL

Syntax
CREATE [OR REPLACE] FUNCTION FunctionName
[(Parameter_Name [IN | OUT | IN OUT] type [, ...])]
RETURN ReturnType
{IS | AS}
BEGIN 
   Function_Body_Area
END [function_name];

Example
CREATE OR REPLACE FUNCTION totalStudents
RETURN number
IS 
   totalStud number(2) := 0;
BEGIN 
   SELECT count(*) into totalStud
   FROM Tblm_Students;   
   RETURN totalStud;
END;

POINTS

• Function always returns a value
• In function only SELECT statement can be used, DML statement cannot be used.
• Function takes input (IN) parameter only, not output (OUT) parameter
• try-catch block cannot be used inside function
• Function be used in the SELECT statement
• Function can be called from procedures
• Function can return only one value while procedure can return more.
• Function purpose is to perform some basic calculations and return the result.

Example2

DECLARE 

   num1 number; 

   num2 number; 

   maxVal number; 

FUNCTION findMaximuxValue(n2 IN number, n2 IN number)  

RETURN number 

IS 

    n3 number; 

BEGIN 

   IF n1 > n2 THEN 

      n3:= n1; 

   ELSE 

      n3:= n2; 

   END IF;  

   RETURN n3; 

END; 

BEGIN 

   num1:= 23; 

   num2:= 45;  

   maxVal := findMaximuxValue(num1, num2); 

   dbms_output.put_line(' Maximum Value  of (11,99): ' || maxVal); 

END;