Difference between Function and Procedure

Pre-requisites: What is SQL?

Structured Query Language is a computer language that we use to interact with a relational database.SQL is a tool for organizing, managing, and retrieving archived data from a computer database.

In this article, we will see the difference between Function and Procedure.

Function:

The function is one of the fundamental thoughts in computer programming. It is used to calculate something from a given input. Hence it got its name from Mathematics. The function can be either user-defined or predefined. The function program has a block of code that performs some specific tasks or functions. 

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:

create function MultiplyNumbers(@int1 as int,@int2 as int)
As
BEGIN
Return (@int1 * @int2)
end

Procedure:

In programming a particular set of instructions or commands along known as a procedure. Counting on the programming language it is known as a procedure, subroutine, function, or subprogram. 

CREATE or REPLACE PROCEDURE name(parameters)

IS

variables;

BEGIN

//statements;

END;

Example:

CREATE or REPLACE PROCEDURE INC_SAL(eno IN NUMBER, up_sal OUT NUMBER)
IS
BEGIN
UPDATE emp_table SET salary = salary+1000 WHERE emp_no = eno;
COMMIT;
SELECT sal INTO up_sal FROM emp_table WHERE emp_no = eno;
END; 

Difference between Function and Procedure:

S.NO	Function	Procedure
1.	Functions always return a value after the execution of queries.	The procedure can return a value using “IN OUT” and “OUT” arguments.
2.	In SQL, those functions having a DML statement can not be called from SQL statements. But autonomous transaction functions can be called from SQL queries.	A procedure can not be called using SQL queries.
3.	Each and every time functions are compiled they provide output according to the given input.	Procedures are compiled only once but they can be called many times as needed without being compiled each time.
4.	A Function can not return multiple result sets.	A procedure is able to return multiple result sets.
5.	The function can be called using Stored Procedure.	While procedures cannot be called from function.
6.	A function used only to read data.	A procedure can be used to read and modify data.
7.	The return statement of a function returns the control and function’s result value to the calling program.	While the return statement of the procedure returns control to the calling program, it can not return the result value.
8.	The function does not support try-catch blocks.	Procedure supports try-catch blocks for error handling.
9.	A function can be operated in the SELECT statement.	While it can’t be operated in the SELECT statement.
10.	Functions do not permit transaction management.	It allows transaction management.
11.	In functions, we can use only a table variable. Temporary tables can not be created in function.	In procedures, we can use temporary tables or table variables to store temporary data.

My Personal Notes arrow_drop_up

Save