SQL | Difference between functions and stored procedures in PL/SQL

Prerequisite:

• Procedures in PL/SQL
• Functions in PL/SQL.

Difference between functions and stored procedures in PL/SQL

Differences between Stored procedures(SP) and Functions(User-defined functions (UDF)): 

1. SP may or may not return a value but UDF must return a value. The return statement of the function returns control to the calling program and returns the result of the function. 
Example: 

SP ->
create or replace procedure GEEKS(x int) 
is
y int;
begin
.....

UDF->
FUNCTION GEEKS(x int)  
/*return statement so we must return value in function */  
RETURN int             
IS  
  y int;  
BEGIN 
.....

2. SP can have input/output parameters but UDF only has input parameters. 
Example: 

 SP ->
 CREATE OR REPLACE PROCEDURE Factorial(x IN NUMBER, result OUT NUMBER)
 is
 begin
 ....

UDF ->
FUNCTION Factorial(x IN NUMBER) /* only input parameter */ 
return  NUMBER
is
result NUMBER;
begin
.....

3. We can call UDF from SP but cannot call SP from a function. 
Example: 

Calling UDF cal() inside SP square() but reverse is not possible.

set serveroutput on;
declare
a int;
c int;

function cal(temp  int)
return int
as 
ans int;
begin
ans:=temp* temp;
return ans;
end;

procedure square(x in int, ans out int) 
is
begin
dbms_output.put_line('calling function in procedure');
ans:= cal(x);
end;

begin
a:=6;
square(a, c);
dbms_output.put_line('the answer is '|| c);
end;

OUTPUT: 
calling function in the procedure
the answer is 36

4. We cannot use SP in SQL statements like SELECT, INSERT, UPDATE, DELETE, MERGE, etc. but we can use them with UDF.

5. SP is not allowed to be used in SELECT statements, but UDF can be used.

6. SP cannot be used anywhere in the WHERE/HAVING/SELECT part of SQL queries, but UDF can be used.

7. The execution plan can be re-used in SP but in UDF it will be compiled every time.

8. We can use try-catch exception handling in SP but we cannot do that in UDF.

9. We can use transactions in SP but it is not possible in UDF.

10. We can consider UDF as an expression but it is not possible in SP.

11. SP cannot be used in the join clause but it is possible in UDF as a resultset.

12. SP can have both table variables and temporary tables but UDF can have only table variables as it does not permit the use of temporary tables.

13. Advantages of using UDF– 

• Enables faster execution
• Modular Programming
• Can reduce network traffic (data)

      Advantages of using SP–

• Improved security measures
• Client/server traffic is reduced.
• Programming abstraction and efficient code reuse
• Pre-compiled Execution 

My Personal Notes arrow_drop_up

Save