What's the differences between stored procedures, functions and routines?
In MySQL database context, what is the difference among these 3 terms:
- stored procedure
- stored function
- stored routine
Also the build-in functions like those date time functions (e.g. WEEKDAY()
etc) are considered as what?
Solution 1:
Google is your friend. The first match for "mysql routine function procedure" is this: http://dev.mysql.com/doc/refman/5.0/en/stored-routines-syntax.html
A quick summary:
A stored routine is either a procedure or a function.
A procedure is invoked using a CALL statement and can only pass back values using output variables.
A function can be called from inside a statement just like any other function and can return a scalar value.
Solution 2:
Here I have tried to summarize the differences between functions and procedures:
- A FUNCTION always returns a value using the return statement. PROCEDURE may return one or more values through parameters or may not return any at all.
- Functions are normally used for computations where as procedures are normally used for executing business logic.
- A Function returns 1 value only. Procedure can return multiple values (max 1024).
- Stored procedure always returns an integer value of zero by default. Whereas function return types could be scalar or table or table values.
- Stored procedures have a precompiled execution plan, where as functions are not.
- A function can be called directly by SQL statement like
SELECT func_name FROM DUAL
while procedures cannot. - Stored procedure has the security and reduces the network traffic and also we can call stored procedure in any number of applications at a time.
- A Function can be used in the SQL queries while a procedure cannot be used in SQL queries. That causes a major difference between function and procedures.
Solution 3:
Difference between MySQL function and mysql procedure
MYSQL Function
It must return value.
IN
,OUT
andINOUT
cannot be used in function.But return datatype must be declare when create a function. function can be called from a SQL statement. Function return one values.
MYSQL Procedure
Return Values is not mandatory but may be uses the OUT parameter to procedure returns. Can use the
IN
|OUT
|INOUT
parameters. Procedure cannot be called from the SQL Statement. procedure return multiple values by usingOUT
orINOUT
parameters.
Solution 4:
PROCEDURES VS FUNCTIONS
1. PROCEDURES may or may not return a value but FUNCTION must return a value 2. PROCEDURES can have input/output parameter but FUNCTION only has input parameter. 3. We can call FUNCTION from PROCEDURES but cannot call PROCEDURES from a function. 4. We cannot use PROCEDURES in SQL statement like SELECT, INSERT, UPDATE, DELETE, MERGE etc. but we can use them with FUNCTION. 5. We can use try-catch exception handling in PROCEDURES but we cannot do that in FUNCTION. 6. We can use transaction in PROCEDURES but it is not possible in FUNCTION.
Solution 5:
Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values). Functions can have only input parameters for it whereas Procedures can have input/output parameters . Functions can be called from Procedure whereas Procedures cannot be called from Function.