EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Software Development Software Development Tutorials PL/SQL Tutorial PL/SQL Function

PL/SQL Function

PL_SQL Function

Introduction to PL/SQL Function

PL/SQL is a program unit block that is saved inside the schema as an object and can be used again and again, which means that it is a reusable programming unit. This function works similarly to that of a stored procedure inside the Pl/ SQL DBMS. We can call the function anywhere within its scope inside the program, such as in Boolean expressions, assignment statements to assign the return value of the function to some variable or even inside the SQL statement, and queries such as inside where clause, etc. In this article, we will learn about the general usage of the function, how to declare and define the functions and how to give a call to them whenever required inside the PL/SQL programs with the help of certain examples and study its syntax.

Syntax:

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

A PL/ SQL function is made up of two main parts, which are function header and function body, respectively. The syntax of the PL/ SQL function is as shown below –

CREATE [OR REPLACE] FUNCTION name of function (list of parameter values)
RETURN type of value to be returned
IS
Section used for declaring the variables and constants to be used inside the program
BEGIN
Section to be executed
[EXCEPTION]
[section for handling various kinds of exceptions that arise while running the program]
END;

The PL/ SQL function has the header, which has the name of the function declared, and the RETURN clause, which helps in specifying the data type of the value to be returned.

List of parameters – The parameters that are to be passed or forwarded from the function are declared here. It is necessary to declare the type of parameter along with the name of a parameter in the list of parameters in the comma-separated format for multiple parameters. The type of parameter can be either IN, OUT, or INOUT mode.
The further parts of the syntax starting from IS are considered to be a Pl/ SQL function body, and it consists of broadly three different sections that are declaration section, executable section, and a section for exception handling. The various terminologies used in the syntax of the function body are as described one by one –
Section for declaration – This section starts after the IS clause and ends before the BEGIN clause. In this section, we can declare all types of things required in the function, such as user-defined types, cursors, constants, and the variables used for storing values in the memory.

Section for execution –

This is the most important section where you will write all the logical code or business code to be executed by the function. This is the crux of function, talking about the role that the function is executing. This section starts with BEGIN clause and ends with the END keyword. The main difference between a procedure in PL/ SQL and the function can be observed here. It is compulsory to have at least a single return statement inside the executable section of the PL/ SQL function.

Section for exception handling –

While executing the statements inside the PL/SQL function body, there might be the case that sometimes an exception might arise inside the executable section. We can write the code to handle the possible exceptions that might arise inside the exception handling section.

Examples of PL/SQL Function

Let us consider some examples which can demonstrate the use and implementation of PL/SQL functions inside the program. Let us talk about the sample data firstly, which we will consider for demonstrating the use of functions in the program. We have one existing table in our PL/SQL database whose name is customer_details. To check the existing content of the table, you can fire the following query statement –

SELECT * FROM customer_details;

The execution of the above query statement gives out the following output –

pl sql 1

Example #1

Let us write a PL/ SQL function that can retrieve the total bill amount that is generated for a particular store id which is passed as a parameter to it. Our stored procedure will then be written as shown below –

CREATE OR REPLACE FUNCTION retrieve_total_billAmt(
store_id_value PLS_VARCHAR
)
RETURN NUMBER
IS
total_bill_amt NUMBER := 0;
BEGIN
-- retrieve total bill amount
SELECT SUM(bill_amount)
INTO total_bill_amt
FROM customers_details
GROUP BY store_id
HAVING store_id = store_id_value;
-- return the total bill amount
RETURN total_bill_amt;
END;

After writing this function, it is required to compile inside the PL/ SQL DBMS. In order to compile the same, you can just click on the Run button provided in the editor which you are using for executing and interacting with Pl/SQL DBMS. If the compilation of your function becomes successful, you can see the name of the function inside the database contents. The below shows the run button on click of which the function compiles.

function

pl sql 2

Calling the Pl/ SQL function

In order to call the Pl/ SQL function anywhere within your PL/ SQL program, you can make the use of the following syntax described with the help of an example for calling any function inside the program in PL/ SQL –
Inside an assignment statement –
We can call our function retrieve_total_billAmt inside an assignment statement to assign the value of the total bill amount to a variable. Let consider the following example for the same –

DECLARE
billAmount_for_Electronics NUMBER := 0;
BEGIN
billAmount_for_Electronics := retrieve_total_billAmt('ELECTRONICS');
DBMS_OUTPUT.PUT_LINE('Total Bill AMount for Electronic store is : ' || billAmount_for_Electronics);
END;

The output of the above program after its execution is as shown below –

pl sql 3

Inside the Boolean expression, a function can be called in the following way –

BEGIN
IF retrieve_total_billAmt('ELECTRONICS') > 30000 THEN
DBMS_OUTPUT.PUT_LINE('Total Bill Amount for Electronic store is above target');
END IF;
END;

The output of the above program after its execution is as shown below –

pl sql 4

Inside the SQL statement, the function can be called in the following way –

SELECT
retrieve_total_billAmt('ELECTRONICS')
FROM
dual;

The output of the above program after its execution is as shown below –

output 4

Conclusion

The PL/SQL function is the reusable program unit that can be called inside the assignment statement, SQL query statement, or even inside the Boolean expression condition specification.

Recommended Articles

We hope that this EDUCBA information on “PL/SQL Function” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PL/SQL TRIM
  2. PL/SQL to DATE
  3. PL/SQL stored procedure
  4. SQL Multiple Join 
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Software Development Bundle3000+ Hours of HD Videos | 149 Learning Paths | 600+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program1000+ Hours of HD Videos | 43 Learning Paths | 250+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more