MySQL procedure vs function, which would I use when?

Question

I'm looking at MySQL procedures and functions. What is the real difference?

They seem to be similar, but a function has more limitations.

I'm likely wrong, but it seems a procedure can do everything and more than a function can. Why/when would I use a procedure vs a function?

Lalitta

Answer 1

You can't mix in stored procedures with ordinary SQL, whilst with stored function you can.

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.

Hinayana

What kind of limitations do functions have? – Vanegas

Ah, I found some good info here: dev.mysql.com/doc/refman/5.0/en/… – Vanegas

Answer 2

The most general difference between procedures and functions is that they are invoked differently and for different purposes:

1. 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.
2. A function is invoked within an expression and returns a single value directly to the caller to be used in the expression.
3. You cannot invoke a function with a CALL statement, nor can you invoke a procedure in an expression.

Syntax for routine creation differs somewhat for procedures and functions:

1. 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.

2. 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.

   If no keyword is given before a parameter name, it is an IN parameter by default. Parameters for stored functions are not preceded by IN, OUT, or INOUT. All function parameters are treated as IN parameters.

To define a stored procedure or function, use CREATE PROCEDURE or CREATE FUNCTION respectively:

CREATE PROCEDURE proc_name ([parameters])
 [characteristics]
 routine_body


CREATE FUNCTION func_name ([parameters])
 RETURNS data_type       // diffrent
 [characteristics]
 routine_body

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.

Stored routines (referring to both stored procedures and stored functions) 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:

1. (This point is copied from a blogpost.) 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.

2. (I'm not sure for this point.)
   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

3. Functions are normally used for computations where as procedures are normally used for executing business logic.

4. 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

5. Functions can't use FLUSH statements whereas Stored procedures can do.

6. 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.

7. 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?

Note: that although some restrictions normally apply to stored functions and triggers but not to stored procedures, those restrictions do apply to stored procedures if they are invoked from within a stored function or trigger. For example, although you can use FLUSH in a stored procedure, such a stored procedure cannot be called from a stored function or trigger.

Sulcus

@GrijeshChauhan, What do you mean when you say that "Function Parsed and compiled at runtime"? – Murry

@Murry means functions in MySQL are something like scripts those compiles and executes on the fly. I copied it from some blog post, but didn't perform any practical to inspect this behaviors. – Sulcus

In procedures you can pass an out variable as parameter, then call it with a select statement – Juicy

bullet point #4 in the bottom section of this answer is, i think, the core of the difference between procedures and functions. procedures can change the database, functions cannot. all the other differences are just to serve that purpose more effectively. – Toluol

Answer 3

One significant difference is that you can include a function in your SQL queries, but stored procedures can only be invoked with the CALL statement:

UDF Example:

CREATE FUNCTION hello (s CHAR(20))
   RETURNS CHAR(50) DETERMINISTIC
   RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

CREATE TABLE names (id int, name varchar(20));
INSERT INTO names VALUES (1, 'Bob');
INSERT INTO names VALUES (2, 'John');
INSERT INTO names VALUES (3, 'Paul');

SELECT hello(name) FROM names;
+--------------+
| hello(name)  |
+--------------+
| Hello, Bob!  |
| Hello, John! |
| Hello, Paul! |
+--------------+
3 rows in set (0.00 sec)

Sproc Example:

delimiter //

CREATE PROCEDURE simpleproc (IN s CHAR(100))
BEGIN
   SELECT CONCAT('Hello, ', s, '!');
END//
Query OK, 0 rows affected (0.00 sec)

delimiter ;

CALL simpleproc('World');
+---------------------------+
| CONCAT('Hello, ', s, '!') |
+---------------------------+
| Hello, World!             |
+---------------------------+
1 row in set (0.00 sec)

Trifle

Your function has two returns? I mean what's this line? RETURNS CHAR(50) DETERMINISTIC ? – Helve

The RETURNS CHAR(50) states what type of data will be returned. The RETURN CONCAT(... is the data that is being returned. Both are needed. The DETERMINISTIC is needed to state that the underlying data will not be modified. – Buntline

Answer 4

A stored function can be used within a query. You could then apply it to every row, or within a WHERE clause.

A procedure is executed using the CALL query.

Frostbite

Answer 5

Stored procedure can be called recursively but stored function can not

Alina

Answer 6

Beside the answer given above, I would like to add that

Function(s) can be used in combination with other function and expressions and also in a nested fashion (in short they can be used in very complex form form to get the job done what we wants).

Same thing can be implemented in procedure but in procedure we had to done all the work done inside that procedure, meaning in a monolithic fashion code. (Whereas in function(s) can be for every task; a new function can be implemented). So at the end we can get the task done by using a combination of different function.

Bandur

Answer 7

You should use a function when not using transaction and/or not returning multiple values.

You should use a procedure when using transaction and/or returning multiple values.

A function:

• can have zero or more SQL statements within BEGIN ... END statement. *No statement without BEGIN ... END statement gets error.

• can have only single RETURN statement not changing the delimiter to $$ without error.

• can get zero or more values with zero or more parameters from the caller then, return a value to the caller.

• must return a value with RETURN statement.

• must be DETERMINISTIC or NOT DETERMINISTIC. *If any one of them is not set, NOT DETERMINISTIC is set implicitly.

• can have SELECT INTO statement but cannot have SELECT without INTO otherwise there is the error.

• can have local variables and user-defined variables. *My answer explains local variables and user-defined variables.

• cannot have transaction otherwise there is the error.

• is atomic by default so if there is error, it is rollbacked automatically. *My answer explains it in detail.

*My answer and the doc explain a function in detail.

A procedure:

• can have zero or more SQL statements within BEGIN ... END statement. *No statement without BEGIN ... END statement gets error.

• can have only single statement without BEGIN ... END statement not changing the delimiter to $$ without error.

• can have multiple parameters with IN, OUT and INOUT and without them.

• can get zero or more values with zero or more IN and INOUT parameters from the caller.

• can return zero or more values to the caller with OUT and INOUT parameters.

• can have local variables and user-defined variables.

• can have transaction.

• cannot have RETURN statement otherwise there is the error.

• is not atomic by default so if there is error, it is not rollbacked automatically. *My answer explains it in detail.

*My answer and the doc explain a procedure in detail.

Rothermere