What is difference between procedure and function with Examples?

In my previous article I have given the details about the procedures and how to execute the parameterized procedures in detail. In this article I would like to throw light on what are user defined functions in PL/SQL and the key interview question asked in PL/SQL interviews i.e. the difference between stored procedures and functions with examples. The procedures and functions are basic building blocks of PL SQL. There is always a question in interview about difference between procedure and function with examples.

This article contains :

What are functions in PL/SQL with example?
What are procedures in PL/SQL with example?
Difference between procedure and function with example.

What are PLSQL functions with example?

The PL SQL functions are nothing but the named PL/SQL blocks which will used to return the specified value. The functions always returns the value.

There are two types of PL SQL functions :

In Built functions : These are built in functions by Oracle SQL or PL SQL engine.
User Defined Functions : These are basic building blocks which will return the value created by developers.

Functions in PL SQL

The functions are stored in the Schema as database objects.

You can call the function as expression or it is used to provide the parameter value.

Syntax :

Create function function_name
is as
Variable declaration
Begin
Business logic
Return statement;
Exception
End;

Real life example :

You can create a function to return the salary of employee.

create or replace function F_get_sal
(p_emp_id number)
return number
is
v_sal number;
begin
select salary into v_sal
from employees
where employee_id=p_emp_id;
return v_sal;
end;

What are procedures with real life example?

I have already given the details about the procedure and execution of the procedure. In this section I just want to throw light on what is mean by procedure.

The PL/SQL procedure is basic building block which is used to perform any action.

Just like a function it also can be stored as database object named procedure.

The key features of procedure is reusability and maintainability.

There are three types of parameters which you can use to call the procedures in PLSQL i.e. IN,OUT,INOUT

You can execute procedure in anonymous block or using exec keyword.

What is difference between procedure and function :

Procedure	Function
Procedures are basic PL SQL blocks to perform a specific action.	Functions are blocks used mainly to perform the computations.
Procedures will not return the value	Functions must return the value. When you are writing functions make sure that you can write the return statement.
Procedures always executes as PL SQL statement	Functions executes as part of expression
It does not contain return clause in header section	It must contain return clause in header
We can pass the values using IN OUT IN OUT parameters	Function must return a single value
Procedures can not be executed in Select statement	Functions can execute or call using select statement but it must not contain Out or IN OUT parameters.
Example : CREATE OR REPLACE PROCEDURE P_UPDATE_SAL (P_EMP_ID IN NUMBER, P_AMOUNT IN NUMBER) IS –here you define variables — n number; BEGIN UPDATE employees set salary=salary+P_AMOUNT where employee_id=P_EMP_ID; commit; exception WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (SQLCODE); DBMS_OUTPUT.PUT_LINE (SQLERRM); END;	Example : create or replace function P_get_sal (p_emp_id number) return number is v_sal number; begin select salary into v_sal from employees where employee_id=p_emp_id; return v_sal; end;

Difference between procedure and function

These are few difference between procedures and function in PL SQL. I hope you get clear idea about difference between Procedure and function in PL SQL with real examples. If you like this article or if you have any concerns with the same kindly comment in comments section.

Amit S

Oracle Consultant with vast experience in Oracle BI and PL/SQL Development. Amiet is the admin head of this website who contributes by preparing tutorials and articles related to database technologies. He is responsible to manage the content and front-end of the website.