Difference between MySQL procedure and Function

1. It can only be said to look at their habits. procedure can be used for implementation. Most functions can be used. Therefore, one function is implemented in some packages and one process is implemented.

 

2. Consider whether to use a function or stored procedure as follows:
From the returned results of the parameter, it is best to use the stored procedure if multiple parameter values are returned. If there is only one returned value, you can use the function;
From the call situation:

(1) if the SQL statement(DML or select) the stored function or stored encapsulated function cannot be a stored procedure, but there are many restrictions and the purity level of the function when calling the stored function, you can refer to Oracle 9i PL \ SQLProgramDesign (Mechanical Industry Press );
(2) If it is called in a procedural statementIt depends on what features you want to implement. A function is generally used to calculate and return a computing result, while a stored procedure is generally used to perform specific data operations (such as modifying, inserting a database table, or executing some DDL statements ), so although their syntax is very similar, the functions that users need to complete when using them are mostly different.

3. let me simply say that procedure can return values or do one thing. For example, if data is transferred between several tables, function must return values and only one .....

4. Three different points from the stored procedure:
A. You must have a returns statement to define the return value type.
B. The In, out, or inout modifier cannot be specified. All parameters are implicitly in
C. The function body must contain a return statement to end function execution and return the specified result to the caller.
 

 

5.

Http://dev.mysql.com/doc/refman/5.0/en/stored-program-restrictions.html

Some of the restrictions noted here apply to all stored routines; that is, both to stored procedures and stored functions. Some of these restrictions apply to stored functions but not to stored procedures.

The restrictions for stored functions also apply to triggers.

Stored routines cannot contain arbitrary SQL statements. The following statements are disallowed:

• The table-maintenance statementsCheck tableAndOptimize table. This restriction is lifted beginning with MySQL 5.0.17.

• The locking statementsLock tablesAndUnlock tables.

• Alter View. (Before MySQL 5.0.46, this restriction is enforced only for stored functions .)

• Load dataAndLoad table.

• SQL prepared statements ( prepare , execute , deallocate prepare ). implication: you cannot use dynamic SQL within stored routines (where you construct dynamically statements as strings and then execute them ). this restriction is lifted as of MySQL 5.0.13 for stored procedures; it still applies to stored functions and triggers.

  In addition, SQL statements that are not permitted within prepared statements are also not permitted in stored routines. see section 12.7, "SQL syntax for prepared statements", for a list of statements supported as prepared statements. statements not listed there are not supported for SQL prepared statements and thus are also not supported for stored routines Unless noted otherwise in section 17.2, "using stored routines (procedures and functions )".

• Inserts cannot be delayed.Insert delayedSyntax is accepted but the statement is handled as a normalInsert.

For stored functions (but not stored procedures), the following additional statements or operations are disallowed:

• Statements that perform explicit or implicit commit or rollback. Support for these statements is not required by the SQL standard, which states that each DBMS vendor may decide whether to allow them.

• Statements that return a result set. This includes desSelectStatements that do not haveIntoVar_listClause and other statements suchShow,Explain, AndCheck table. A function can process a result set eitherSelect...Var_listOr by using a cursor andFetchStatements. See section 12.8.3.3,"Select...Statement ".

• FlushStatements.

• Before MySQL 5.0.10, stored functions createdCreate FunctionMust not contain references to tables, with limited exceptions. They may include someSetStatements that contain table references, for exampleSet a: = (select max (ID) from T), AndSelectStatements that fetch values directly into variables, for exampleSelect I into var1 from t.

• Stored functions cannot be used recursively.

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

• If you refer to a temporary table multiple times in a stored function under different aliases,Can't reopen table :'Tbl_name'Error occurs, even if the references occur in different statements within the function.

• A stored function acquires table locks before executing, to avoid inconsistency in the binary log due to mismatch of the order in which statements execute and when they appear in the log. statements that invoke a function are recorded rather than the statements executed within the function. consequently, stored functions that update the same underlying tables do not execute in parallel. in contrast, stored procedures do not acquire table-level locks. all statements executed within stored procedures are written to the binary log. see section 17.5, "binary logging of stored programs ".