PL/SQL functions are reusable blocks of code that can be used to perform specific tasks. They are similar to procedures but must always return a value.
A function in PL/SQL contains:
- Function Header: The function header includes the function name and an optional parameter list. It is the first part of the function and specifies the name and parameters.
- Function Body: The function body contains the executable statements that implement the specific logic. It can include declarative statements, executable statements, and exception-handling statements.
Create Function in PL/SQL
To create a procedure in PL/SQL, use the CREATE FUNCTION statement.
Syntax
The syntax to create a function in PL/SQL is given below:
CREATE [OR REPLACE] FUNCTION function_name
(parameter_name type [, ...])
-- This statement is must for functions
RETURN return_datatype
{IS | AS}
BEGIN
-- program code
[EXCEPTION
exception_section;
END [function_name];
Example
In this example, we create a PL/SQL function to calculate factorial of a number
PL/SQL CREATE OR REPLACE FUNCTION factorial(x NUMBER) RETURN NUMBER IS f NUMBER; BEGIN IF x = 0 THEN f := 1; ELSE f := x * factorial(x - 1); END IF; RETURN f; END;
How to Call Function in PL/SQL
To call a function, specify the function name and any required parameters. The function will execute and return a value.
Example
Here, we call the factorial function which we created earlier.
PL/SQL DECLARE num NUMBER; result NUMBER; BEGIN num := 5; result := factorial(num); DBMS_OUTPUT.PUT_LINE('Factorial of ' || num || ' is ' || result); END; Output:
the reverse of number is 987654321
PL/SQL Recursive Function
A PL/SQL recursive function is a function that calls itself to perform a specific task. The function continues to call itself until a certain condition is met, at which point it returns a value.
Recursive Function Example
Lets implement a recursive function to calculate the factorial of a number Recursive functions example:
PL/SQL DECLARE num INT; answer INT; -- Defining the function FUNCTION factorial(x NUMBER) RETURN INT IS f INT; BEGIN IF x = 0 THEN f := 1; ELSE f := x * factorial(x - 1); END IF; RETURN f; END; BEGIN num := 5; answer := factorial(num); DBMS_OUTPUT.PUT_LINE('Factorial of ' || num || ' is ' || answer); END; Output:
Factorial of 5 is 120
Exception handling in PL/SQL Functions
Exception handling can be done using an exception block in functions but exception handling using a try-catch block cannot be done. Example:
PL/SQL SET SERVEROUTPUT ON; DECLARE a INT; b FLOAT; myexp EXCEPTION; FUNCTION sqroot(x INT) RETURN FLOAT AS answer FLOAT; BEGIN IF x < 0 THEN RAISE myexp; ELSE answer := SQRT(x); END IF; RETURN answer; EXCEPTION WHEN myexp THEN DBMS_OUTPUT.PUT_LINE('Square root of a negative number is not allowed, so returning the same number'); RETURN x; END; BEGIN b := sqroot(-2); DBMS_OUTPUT.PUT_LINE('The value is ' || b); END; Output:
square of negative number is not allowed so returning the same number
the value is -2
Advantages of PL/SQL Functions
- We can make a single call to the database to run a block of statements thus it improves the performance against running SQL multiple times. This will reduce the number of calls between the database and the application.
- We can divide the overall work into small modules which becomes quite manageable also enhancing the readability of the code.
- It promotes reusability.
- It is secure since the code stays inside the database thus hiding internal database details from the application(user). The user only makes a call to the PL/SQL functions. Hence security and data hiding is ensured.
DROP Function in PL/SQL
To drop a function in PL/SQL, DROP function statement is used.
Syntax
DROP Function <function_name>;
Example
DROP Function func1;
Important Points About Function in PL/SQL
- Functions in PL/SQL must return a value, which can be a scalar, table, or collection.
- They can improve performance by executing logic on the database server.
- They can enhance security by enforcing strict access controls.
- They can simplify complex logic by breaking it down into smaller, manageable parts.
- They can handle exceptions and errors by including exception handling blocks.
- They can simplify data retrieval by encapsulating complex join operations and filtering conditions.
Similar Reads
SQL LAG() Function The LAG() function in SQL is one of the most powerful and flexible tools available for performing advanced data analysis. It is often used to compare rows, calculate differences, and tracks trends in a dataset, especially for time-series data. If we are working with sales, stock prices, or even empl
5 min read
PL/SQL SUM() Function The SUM() function in PL/SQL is used to calculate the sum of the numeric column. It is an aggregate function that performs calculations on a set of values and returns a single value. In this article, we will explore the syntax, usage, and examples of the PL/SQL SUM() function to help us understand i
4 min read
PL/SQL MAX() Function The PL/SQL MAX() function is an essential aggregate function in Oracle databases, enabling users to efficiently determine the largest value in a dataset. Whether working with numerical data, dates, or strings, the MAX() function is flexible and widely applicable.In this article, we will provide a de
4 min read
PLSQL | LN Function The LN function is an inbuilt function in PLSQL which is used to return the natural logarithm of a given input number. The natural logarithm of a number is the logarithm of that number to the base e, where e is the mathematical constant approximately equal to 2.718. This is written using the notatio
2 min read
PLSQL | LEAST Function The LEAST is an inbuilt function in PLSQL which is used to return the least value from a given list of some expressions. These expressions may be numbers, alphabets etc. Syntax: LEAST(exp1, exp2, ... exp_n) Parameters Used: This function accept some parameters like exp1, exp2, ... exp_n. These each
2 min read