In my previous article I have given the detailed idea about the difference between procedure and functions in oracle. I have briefly explained about functions in that article. In this article I would like to focus on functions in PL/SQL with real life examples. There are few different ways of calling the function. I would like to cover different ways to call the function in PL/SQL with real industry examples.
What we cover in this article?
What are functions in PL/SQL ?
Different ways to call PL/SQL functions with examples.
What are functions in PL/SQL with its advantages?
In this section we will focus on functions in PL/SQL with its advantages. The PL/SQL function is nothing but the named PL./SQL block that must return the value. You can store the function as database object and we can reuse that object in multiple PL SQL blocks. The function always called as a part of expression or it is used to provide the parameter value.
In simple words Function is basic PL/SQL building block which must return the value.
Example :
Function to return the salary of employee from Employee table.
Syntax to create a function in PL/SQL :
Create or replace function Function_name RETURN data_type
Is/AS
BEGIN
–Business Logic
Return Expression
End;
Advantages of Function :
In this section we can see multiple advantages of functions in PL/SQL :
- Reduce Complexity : When there are complex or awkward activities we can write a function and easily can capture those activities.
- Increase Efficiency : The second advantage of using function is to increase efficiency.
- Reusability : We can easily reuse the functions in PL/SQL so reusability is another advantage of using function.
- Data Manipulation : We can use functions to easily manipulate the data.
Real Life Industry example :
We need to create a function to get salary of employee.
create or replace function F_get_sal —Function name should start with F for easy reading purpose
(p_emp_id number) —Input parameter as Employee ID
return number —We need to return salary so returning number
is
v_sal number; —Variable to store salary
begin
select salary into v_sal
from employees
where employee_id=p_emp_id;return v_sal; —Returning salary
end;
When you compile this function you can see the details in Functions objects in PL/SQL.
How to call PL/SQL Functions?
Now we have created the function in PL/SQL. We can call the function in PL/SQL with multiple ways. Following are places where you can call the PL/SQL Functions :
1.Select list or clause or select statement
2.You can call function on where and having clause of SQL statement
3.You can call function in Connect by,Start with,order by or group by clauses.
4.You can call function in values clause of Insert statement
5.You can call function in set clause of update statement.
Here are few examples ,
Type 1 : Using Anonymous block :
syntax :
Begin
dbms_output.put_line(Function_name(parameters));
End;
Example :
begin
dbms_output.put_line (F__get_sal(110));
end;
The above statement will return the salary of employee who’s employee id is 110.
Type 2 : Using Select Statement
Select Function_name(parameter) from Dual;
Example :
Select F_get_sal(110) from Dual;
The above statement will return the salary for employee who’s employee id is 110.
Type 3 : Where clause or order by clause
We can also call functions in where clause.
Example : If you want to fetch the data for employee who’s employee_id is 100 but salary as filter is 10000.
Select * from Employee where F_get_sal(110)=’10000′;
These are few examples of calling PL/SQL functions.I hope you like this article of Functions in PL/SQL with examples. If you like this article or if you have any questions related to same kindly comment in comments section.