- CREATE PROCEDURE and CREATE FUNCTION require the CREATE ROUTINE privilege.
Procedure
--------------
- 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.
- Cannot invoke a procedure in an expression.
- 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.
- To invoke a stored procedure, use the CALL statement.
- Specifying a parameter as IN, OUT, or INOUT is valid only for a PROCEDURE.
- A procedure is invoked using a CALL statement, and can only pass back values using output variables.
Function
-----------
- A function is invoked within an expression and returns a single value directly to the caller to be used in the expression.
- Cannot invoke a function with a CALL statement.
- 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 function, refer to it in an expression. The function returns a value during expression evaluation.
- For a FUNCTION, parameters are always regarded as IN parameters.
- 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.
Note: log_bin_trust_function_creators affects how MySQL enforces restrictions on stored function and trigger creation.
References:
http://stackoverflow.com/questions/3744209/mysql-stored-procedure-vs-function-which-would-i-use-when
http://dev.mysql.com/doc/refman/5.7/en/create-procedure.html
Procedure
--------------
- 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.
- Cannot invoke a procedure in an expression.
- 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.
- To invoke a stored procedure, use the CALL statement.
- Specifying a parameter as IN, OUT, or INOUT is valid only for a PROCEDURE.
- A procedure is invoked using a CALL statement, and can only pass back values using output variables.
Function
-----------
- A function is invoked within an expression and returns a single value directly to the caller to be used in the expression.
- Cannot invoke a function with a CALL statement.
- 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 function, refer to it in an expression. The function returns a value during expression evaluation.
- For a FUNCTION, parameters are always regarded as IN parameters.
- 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.
Note: log_bin_trust_function_creators affects how MySQL enforces restrictions on stored function and trigger creation.
References:
http://stackoverflow.com/questions/3744209/mysql-stored-procedure-vs-function-which-would-i-use-when
http://dev.mysql.com/doc/refman/5.7/en/create-procedure.html
No comments:
Post a Comment