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. |
Contact Us