Function in PL/SQL – Oracle PL/SQL Tutorial

Hi! Friends, We are back with the new content of Function in PL/SQL in Oracle PL/SQL Tutorial series. Please also read the previous post about Procedures in PL/SQL.

Let’s get started now…

The topics which will be covered in this tutorial:

  • Introduction of Function
  • Creating Function in PL/SQL
  • Calling a function in PL/SQL
  • Summary
  • FAQS (Frequently Asked Questions)
  • Basic introduction of Function in PL/SQL

    PL/SQL functions are not very different from PL/SQL Procedures. Only one difference is there that function is returning the values. So, what we learn in the previous article of PL/SQL Procedures, will also correct for function.

    How to Create Functions in PL/SQL

    We can create a PL/SQL function by using the CREATE FUNCTION statement. The syntax of the Creation of function is as follows −

    CREATE [OR REPLACE] FUNCTION function_name 
    (parameter_name [IN | OUT | IN OUT] datatype [, ...])
    RETURN return_datatype 
    IS
    BEGIN 
       < body > 
    END [function_name];
    Syntax Explanation:
    1. Here, the function-name refers to the function’s name.
    1. Here, the [OR REPLACE] option allows us to modify the existing function.
    2. The parameter_name, as we already know which passes in the function. It can be input or output type or both IN OUT.
    3. The PL/SQL functions are must contain a return statement which returns the datatype.
    4. The body contains the executable part where we are doing the queries and other kinds of stuff.
    5. A Function can also return the value through OUT parameters other than using RETURN.
    Example:
    CREATE OR REPLACE FUNCTION functionExample
    RETURN number
     IS 
       vtotalno number() := 0; 
    BEGIN 
       SELECT count(*) into vtotalno 
       FROM virtualtable; 
        
       RETURN vtotalno; 
    END; 
    / 
    function in pl/sql

    How to Call a Function in PL/SQL

    When we are creating a function at that time, we know what the purpose of the function is. Where we are going to use that whether in standalone or somewhere in a procedure.

    To call a function in the to declare block, we need to pass the necessary parameters among function_name, and if the function returns a value, we can store the value. The below- mentioned program calls the function example from an unnamed block −

    DECLARE 
       vnumber number(10); 
       vtotalcost number(5);
    BEGIN 
    SELECT t.cost into vtotalcost 
       FROM virtualtable t;  
     vnumber := totalempollyee(); 
    if(vnumber > vtotalcost 
    ) then
    dbms_output.put_line('Total no. of empollyee: ' || vnumber);
    end if;
       dbms_output.put_line('Total cost. of empollyee: ' || vtotalcost ); 
    END; 
    /

    To call a function in the to a procedure, we need to pass the necessary parameters through the function name, and if the function returns a value, then we can store the value. The below- mentioned example calls the function example −

    CREATE OR REPLACE PROCEDURE printContact(
        vcustomerid IN NUMBER default NULL;
         
    )
    IS
      r_contact varchar2:=null;
      vnumber   number:=0;
    BEGIN
      
      SELECT t.contactno
      INTO r_contact
      FROM contacts t
      WHERE t.customer_id = vcustomerid;
    
     if( r_contact
     = null) then
      vnumber := totalCustomers(); 
      end if;
    
    EXCEPTION
       WHEN OTHERS THEN
          dbms_output.put_line( SQLERRM );
    END;

    Summary

    • Basic Introduction of Function in PL/SQL
    • How to Create a Function in PL/SQL
    • How to Call a Function in PL/SQL
    • Calling a function in declare block
    • Calling a function in a procedure.

    FAQs:

    1. What are the characteristics of Function?

      A function can allow passing one or more than one parameter or no parameter.

    2. What is a translate function in Oracle?

      A translate function in PL/SQL executes a character-wise replacement of a string.

    3. Can we use UPDATE FOR instead of rowid in SQL?

      Yes, we can use UPDATE FOR for updating or inserting a row or value manually in the SQL query.

    4. What is the Key difference between Function and Procedure?

      A FUNCTION must always have a return statement which returns a value while a PROCEDURE can return one or more than values through parameters or can not return any.


    In the above, we have discussed the Function in PL/SQL with suitable examples; also, we have mentioned some Faqs related to Oracle PL/SQL.

    I hope you guys are like this one. For any query, please feel free to contact us.

    Thank you 🙂

10 thoughts on “Function in PL/SQL – Oracle PL/SQL Tutorial

Leave a Reply

Your email address will not be published.