Difference between Function and Procedure
Last Updated :
01 Aug, 2022
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. |
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...