In this blog, we’ll discuss the functions in PL/SQL. A function is same as a procedure except that it returns a worth. Therefore, all the discussions of the previous chapter are true for functions too.
Creating a Function
A standalone function is made using the CREATE FUNCTION statement. The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows −

CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];

Where,
• function-name specifies the name of the function.
• [OR REPLACE] option allows the modification of an existing function.
• The optional parameter list contains name, mode and kinds of the parameters. IN represents the worth which will be passed from outside and OUT represents the parameter which will be wont to return a worth outside of the procedure.
• The function must contain a return statement.
• The RETURN clause specifies the info type you’re getting to return from the function.
• function-body contains the executable part.
• The AS keyword is employed rather than the IS keyword for creating a standalone function.
Example
The following example illustrates the way to create and call a standalone function. This function returns the entire number of consumers within the customers table.
We will use the purchasers table, which we had created within the PL/SQL Variables chapter −
Select * from customers;

+—-+———-+—–+———–+———-+
| ID | NAME | AGE | ADDRESS | SALARY |
+—-+———-+—–+———–+———-+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+—-+———-+—–+———–+———-+

CREATE OR REPLACE FUNCTION totalCustomers
RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM customers;

RETURN total;
END;
/

When the above code is executed using the SQL prompt, it’ll produce the subsequent result −
Function created.
Calling a Function
While creating a function, you provides a definition of what the function has got to do. To use a function, you’ll need to call that function to perform the defined task. When a program calls a function, the program control is transferred to the called function.
A called function performs the defined task and when its return statement is executed or when the last end statement is reached, it returns the program control back to the most program.
To call a function, you merely got to pass the specified parameters alongside the function name and if the function returns a worth , then you’ll store the returned value. Following program calls the function totalCustomers from an anonymous block −

DECLARE
c number(2);
BEGIN
c := totalCustomers();
dbms_output.put_line('Total no. of Customers: ' || c);
END;
/

When the above code is executed at the SQL prompt, it produces the subsequent result −
Total no. of Customers: 6

PL/SQL procedure successfully completed.
Example
The following example demonstrates Declaring, Defining, and Invoking an easy PL/SQL Function that computes and returns the utmost of two values.

DECLARE
a number;
b number;
c number;
FUNCTION findMax(x IN number, y IN number)
RETURN number
IS
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE
Z:= y;
END IF;
RETURN z;
END;
BEGIN
a:= 23;
b:= 45;
c := findMax(a, b);
dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
/

When the above code is executed at the SQL prompt, it produces the subsequent result −
Maximum of (23,45): 45

PL/SQL procedure successfully completed.
PL/SQL Recursive Functions
We have seen that a program or subprogram may call another subprogram. When a subprogram calls itself, it’s mentioned as a recursive call and therefore the process is understood as recursion.
To illustrate the concept, allow us to calculate the factorial of variety . Factorial of variety n is defined as −
n! = n*(n-1)!
= n*(n-1)*(n-2)!
…
= n*(n-1)*(n-2)*(n-3)… 1
The following program calculates the factorial of a given number by calling itself recursively −

DECLARE
num number;
factorial number;

FUNCTION fact(x number)
RETURN number
IS
f number;
BEGIN
IF x=0 THEN
f := 1;
ELSE
f := x * fact(x-1);
END IF;
RETURN f;
END;

BEGIN
num:= 6;
factorial := fact(num);
dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);
END;
/

When the above code is executed at the SQL prompt, it produces the subsequent result −
Factorial 6 is 720

PL/SQL procedure successfully completed.
So, this brings us to the end of blog. This Tecklearn ‘Functions in PL-SQL’ blog helps you with commonly asked questions if you are looking out for a job in Oracle Pl-SQL. If you wish to learn Oracle PL-SQL and build a career in Database domain, then check out our interactive, Oracle PL-SQL Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Oracle PL SQL Training

Oracle PL-SQL Training

About the Course

Oracle PL/SQL online training course provides you the complete skills needed to create, implement and manage robust database applications using the Oracle Database tools. Our expert instructors will help you to master PL SQL advanced features, from performance to maintainability to the application code architecture. Our best online classes will help you to gain a precise knowledge of PL SQL language, architecture, interactions with the SQL engine, data types, and much more. The entire training is in line with the Oracle PL/SQL certification.

Why Should you take Oracle PL-SQL Training?

• The Average salary of a Senior Oracle PL-SQL Developer is $131,878 per annum – ZipRecuiter.com
• PL-SQL has a market share of 23% globally.
• IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle SQL
• Database Models
• RDBMS
• Components of SQL
• DataTypes
• DDL-Create, Alter, Rename, Drop, Truncate
Manipulating Data using SQL
• Constraints –Unique, Not Null, Primary Key, Check Constraint, Foreign Key
• DML Commands-Insert, Update, Delete
• Order by Clause
• Group Functions
• SET Operators- Union All, Union, Intersect, Minus
• TCL Commands-Commit, RollBack, Savepoint
Oracle Views and Synonyms
• Types of Views
• Synonyms
• Types of Synonyms
• Indexes
• Types of Indexes
Using Subqueries to Solve Queries
• Subqueries
• Co-Related Subquery
OLAP Functions
• OLAP Features
• Roll Up
• Model Clause
• Dimension Modelling
Conditional Statement
• Block
• Variable Attributes
• Nested Blocks
• Conditional Control Statements
• Iterative Controls (Loop)
Cursor Management
• Types of Cursor
• Writing Explicit cursors
• Explicit cursor functions
• Advance Explicit cursor
• Cursor with parameters
Exception Handling
• Handling Exception
• Handling Exception with PL/SQL Predefined Exceptions,
• User Defined Exceptions
• Non-Predefined Error
• Function for trapping Exception
• Trapping user-defined Exception
Subprogram, Procedure and passing parameters and Advance Package Concepts and functions
• Important Features of Sub-Programs
• Procedure
• Functions
Trigger Management
• Introduction to Triggers
• Types of Triggers
• Compound Triggers
Oracle Job Scheduling
Large Object Functions
• Large object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB etc
Important Features of Oracle
Advance level- Scripting

480