18.2. Differences Between Stored Procedures and Functions


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. That is, a function is used in expressions the same way as a constant, a built-in function, or a reference to a table column.

  • 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:

  • Procedure parameters can be defined as input-only, output-only, or for both input and output. 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 syntax differs from that for functions.

  • Functions return a 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.

A MySQL extension for stored procedures (but not functions) is that a procedure can generate a result set, or even multiple result sets, which the caller processes the same way as result sets produced by a SELECT statement. However, the contents of such result sets cannot be used directly in expressions.



MySQL 5 Certification Study Guide
MySQL 5.0 Certification Study Guide
ISBN: 0672328127
EAN: 2147483647
Year: 2006
Pages: 312

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net