Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

Functions in PL/SQL

  • Last Updated : 05 Apr, 2018

A function can be used as a part of SQL expression i.e. we can use them with select/update/merge commands. One most important characteristic of a function is that unlike procedures, it must return a value.

Syntax to create a function:

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

CREATE [OR REPLACE] FUNCTION function_name 
[(parameter_name  type [, ...])] 

// this statement  is must for functions 
RETURN return_datatype  
{IS | AS} 

BEGIN 
    // program code

[EXCEPTION
   exception_section;

END [function_name];

Example:

  1. We have to find the total strength of students using functions present
    in different sections in a school






    // first lets create a table
    create table section(s_id int, s_name varchar(20), strength int );
      
    // Inserting values into table
    insert into section values(1, 'computer science', 20);
    insert into section values(2, 'portal', 45);
    insert into section values(3, 'geeksforgeeks', 60);
      
    // Defining function
    create or replace function totalStrength
      
    // Defining return type
    return integer
    as
    total integer:=0;
      
    begin                        
      
    // calculating the sum and storing it in total 
    select sum(strength) into total from section;
    return total;
      
    // closing function
    end totalStrength;
      
    set serveroutput on;
      
    declare
    answer integer;
      
    begin
    answer:=totalstrength();
       dbms_output.put_line('Total strength of students is  ' || answer);  
    end;

    Output:

    Total strength of students is  125
    
  2. Now, let’s take an example to demonstrate Declaring, Defining and Invoking a simple PL/SQL
    function which will compute and return the reverse of a number.




    set serveroutput on;
    declare
       
        a int;
        c int;
        n int;
       rev int:=0;
        r int;
      
    // Defining function 
    function reverse_it( x  IN int
    return  int
    as
    z int;
      
    // function code
    begin
        n := x;
              
        while (n > 0)
        loop
            r := mod(n, 10);
            rev := (rev * 10) + r;
            n := trunc(n / 10);
        end loop;
          
        z := rev;
        return z;
          
    end  ;  
      
    BEGIN  
       a := 123456789;    
         
       c := reverse_it(a);  
       dbms_output.put_line('the reverse of number is  ' || c);  
      
    END;  

    Output:

    the reverse of number is  987654321
    
  3. Lets implement a recursive function to calculate the factorial of a number
    Recursive functions example:




    DECLARE  
       num int;  
       answer int;  
      
    // Defining function  
    FUNCTION factorial(x number)  
    RETURN int   
    IS  
       f int;  
      
    BEGIN  
       IF x = 0 THEN  
          f := 1;  
       ELSE  
          f := x * factorial(x - 1);  
       END IF;  
    RETURN f;  
    END;  
        
    BEGIN  
       num := 5;  
       answer := factorial(num);  
       dbms_output.put_line(' Factorial of  '|| num || ' is ' || answer);  
    END;  

    Output:

     Factorial of  5 is 120
    
  4. Exception handling can be done using exception block in functions but exception handling using try-catch block cannot be done.

    Example:




    set serveroutput on;
      
    declare
    a int;
    b float;
    myexp exception;
      
    function  sqroot(x int)
    return float
      
    as
    answer float;
      
    begin
      
    if x < 0 then
    raise myexp;
      
    // pre-defined sqrt()  to 
    // calculate square root 
    else
    answer := SQRT(x); 
      
    end if;
    return answer;
      
    exception
    when myexp then
    dbms_output.put_line('square of negative number is  
                         not allowed so returning the same 
                         number');
    return x;
    end;
      
    begin
    b := sqroot(-2);
    dbms_output.put_line('the value is '|| b);
    end;

    Output:

    square of negative number is  not allowed so returning the same number
    the value is -2
    
    

Advantages:

  1. We can make a single call to the database to run a block of statements thus it improves the performance against running SQL multiple times. This will reduce the number of calls between the database and the application.
  2. We can divide the overall work into small modules which becomes quite manageable also enhancing the readability of the code.
  3. It promotes reusability.
  4. It is secure since the code stays inside the database thus hiding internal database details from the application(user). The user only makes a call to the PL/SQL functions. Hence security and data hiding is ensured.



My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!