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; 
/ 



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 🙂