The most general difference between procedures and functions is that they are invoked differently and for different purposes:
- A procedure does not return a value. Instead, it is invoked with a CALL statement to perform an operation such as modifying a table or processing retrieved records.
- A function is invoked within an expression and returns a single value directly to the caller to be used in the expression.
- You cannot invoke a function with a CALL statement, nor can you invoke a procedure in an expression.
You can't mix in stored procedures with ordinary SQL, whilst with stored function you can.
e.g.
e.g.
SELECT get_foo(myColumn) FROM mytable is not valid if get_foo()
is a procedure, but you can do that if get_foo() is a function. The
price is that functions have more limitations than a procedure.
Syntax for routine creation differs somewhat for procedures and functions:
- Procedure parameters can be defined as input-only, output-only, or both. This means that a procedure can pass values back to the caller by using output parameters. These values can be accessed in statements that follow the CALL statement. Functions have only input parameters. As a result, although both procedures and functions can have parameters, procedure parameter declaration differs from that for functions.
- Functions return value, so there must be a RETURNS clause in a
function definition to indicate the data type of the return value. Also,
there must be at least one RETURN statement within the function body to
return a value to the caller. RETURNS and RETURN do not appear in
procedure definitions.
- To invoke a stored procedure, use the
CALL statement. To invoke a stored function, refer to it in an expression. The function returns a value during expression evaluation. - 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 (that is, by invoking the function's name), and can return a scalar value.
- Specifying a parameter as IN, OUT, or INOUT is valid only for a PROCEDURE. For a FUNCTION, parameters are always regarded as IN parameters.
To define a stored procedure or function, use CREATE PROCEDURE or CREATE FUNCTION respectively:
A MySQL extension for stored procedure (not functions) is that a procedure can generate a result set, or even multiple result sets, which the caller processes the same way as the result of a SELECT statement. However, the contents of such result sets cannot be used directly in expression.CREATE PROCEDURE proc_name ([parameters]) [characteristics] routine_body CREATE FUNCTION func_name ([parameters]) RETURNS data_type // diffrent [characteristics] routine_body
Stored routines are associated with a particular database, just like tables or views. When you drop a database, any stored routines in the database are also dropped.
Stored procedures and functions do not share the same namespace. It is possible to have a procedure and a function with the same name in a database.
In Stored procedures dynamic SQL can be used but not in functions or triggers.
SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers. Thus, stored functions and triggers cannot use Dynamic SQL (where you construct statements as strings and then execute them). (Dynamic SQL in MySQL stored routines)
Some more interesting differences between FUNCTION and STORED PROCEDURE:
- To invoke a stored procedure, use the
- Stored procedure is precompiled execution plan where as functions are not. Function Parsed and compiled at runtime. Stored procedures, Stored as a pseudo-code in database i.e. compiled form.
- Stored procedure has the security and reduces the network traffic and also we can call stored procedure in any no. of applications at a time. reference
- Functions are normally used for computations where as procedures are normally used for executing business logic.
- Functions Cannot affect the state of database
(Statements that do explicit or implicit commit or rollback are disallowed in function)
Whereas
Stored procedures Can affect the state of database using commit etc.
refrence: J.1. Restrictions on Stored Routines and Triggers - Functions can't use FLUSH statements whereas Stored procedures can do.
- Stored functions cannot be recursive Whereas Stored procedures can be. Note: Recursive stored procedures are disabled by default, but can be enabled on the server by setting the max_sp_recursion_depth server system variable to a nonzero value. See Section 5.2.3, “System Variables”, for more information.
- Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger. Good Example: How to Update same table on deletion in MYSQL?
