So now that we have already learnt in the previous PL/SQL tutorial about what are PL/SQL Functions in Oracle Database let’s take a leap ahead and learn how to create a PL/SQL function using a very simple example.

This tutorial will require a proper knowledge of the syntax of PL/SQL functions thus I will suggest you to check out the previous tutorial first. That being said let’s start this tutorial.

In order to keep this tutorial simple and easy to understand, we will create a very easy function which will calculate the area of a circle. I guess that will serve the purpose and help you in learning how to create PL/SQL functions in Oracle Database.

As discussed in the previous tutorial that the function body is divided into two parts

  • First is the header of the PL/SQL function and
  • Second is the execution part of the PL/SQL function

So let’s start with header of our function.

Step 1. Create the Header of a PL/SQL Function.

The header consists of the signature of the function or the declaration of the PL/SQL function. 

--Function Header
CREATE OR REPLACE FUNCTION circle_area (radius NUMBER) 
RETURN NUMBER IS

Step 2. Declare Variables or the Constant.

If your program requires you to declare any variable or constant or anything then you can do it right after creating the header, that too without using the DECLARE keyword.

--Declare a constant and a variable
pi  	CONSTANT NUMBER(7,2) :=	3.141;
area 	NUMBER(7,2);

Step 3. Create the Execution Part of the PL/SQL function.

Once you have created the header of your function and declared all your necessary variables as well as constants then you are all set to create the execution part of your PL/SQL function. Here in the execution section of a PL/SQL function, you write all your execution statements. This part also defines the working of your function.

BEGIN
  --Area of Circle pi*r*r;
  area := pi * (radius * radius);
  RETURN area; 
END;
/

Quick Info:
To calculate the square of the circle’s radius in the area of circle, you can also use the inbuilt function of POWER (p, q). This function takes two numeric input and returns one numeric value which will be the answer to the arithmetic expression of p raise to q.

Now let’s join all the above chunks of codes together into a single named unit.

PL/SQL function for calculating “Area of the Circle”.

--Function Header
CREATE OR REPLACE FUNCTION circle_area (radius NUMBER) 
RETURN NUMBER IS
--Declare a constant and a variable
pi  	CONSTANT NUMBER(7,2) :=	3.141;
area 	NUMBER(7,2);
BEGIN
  --Area of Circle pi*r*r;
  area := pi * (radius * radius);
  RETURN area; 
END;

A successful compilation will create a named PL/SQL block which is your PL/SQL function with the name circle_area.

As PL/SQL functions are named PL/SQL block thus they are permanently saved in your database which you can use anytime.

In order to see your PL/SQL Function in action you have to call it through your program. Your program can be an anonymous PL/SQL block, or a named PL/SQL Block or even using a SELECT statement. Few of these various ways of calling a function have been demonstrated in my video tutorial on the same topic on my YouTube channel. I highly encourage you to watch that video.

But in case you want me to do a separate blog on calling a PL/SQL function then write to me on my Facebook or leave a Tweet on my Twitter. 

That’s it for this tutorial on How to Create a PL/SQL function in Oracle Database. Do make sure to share this blog on your social media and help others in learning.