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:

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

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 –

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.

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 –

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 –

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 –

Conclusion – PL/SQL Function

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

This is a guide to PL/SQL Function. Here we discuss the Introduction, syntax, Functions, Examples, and code implementation. You may also have a look at the following articles to learn more –

1. PL/SQL TRIM
2. PL/SQL to DATE
3. PL/SQL stored procedure
4. SQL Multiple Join