Mysql-create procedure and create function syntax

MySQL 5.1 Reference Manual

In MySQL 5.1, a storage subroutine or function is associated with a specific database. Here are a few meanings:

· When a sub-program is called, an implicit useDb_nameExecuted (stops executing when the subroutine ends ). It is not allowed to store use statements in subprograms.

· You can use the database name to limit the subprogram name. This can be used to reference a subroutine that is not in the current database. For example, to reference a stored program P or function f associated with the test database, you can call test. P () or test. F ().

· When the database is removed, all stored subroutines associated with it are also removed.

In MySQL 5.1, a storage subroutine or function is associated with a specific database. Here are a few meanings:

· When a sub-program is called, an implicit useDb_nameExecuted (stops executing when the subroutine ends ). It is not allowed to store use statements in subprograms.

· You can use the database name to limit the subprogram name. This can be used to reference a subroutine that is not in the current database. For example, to reference a stored program P or function f associated with the test database, you can call test. P () or test. F ().

· When the database is removed, all stored subroutines associated with it are also removed.

-- When defining a subroutine, You need to insert a space between the name and the brackets that follow. Otherwise, a syntax error occurs. When you call a subroutine, insert it.
Create procedure sp_name ([proc_parameter [,...]) [characteristic...] routine_body create function sp_name ([func_parameter [,...]) returns type [characteristic...] routine_body --------------------------------------------------------------------------------
Proc_parameter: [In | Out | inout] param_name type -- the parameter column surrounded by parentheses must always exist. If no parameter exists, use an empty parameter column (). Each parameter is an in parameter by default. Func_parameter: param_name type: Any valid MySQL Data Type Characteristic: Language SQL | [not] deterministic -- if the program or thread always produces the same results for the same input parameter, it is regarded as "definite", otherwise it is "uncertain.
-- Contains SQL: a subprogram that does not contain read or write data
-- No SQL: subprograms do not contain SQL statements
-- Reads SQL data: a subprogram containing read data
-- Modifies SQL data: the subprogram contains the statement for writing data.
-- If these features are not explicitly specified, the default value is contains SQL.
| {Contains SQL | no SQL | reads SQL data | modifies SQL data}
-- The subroutine should be executed with the permission of the creator or the caller. The default value is definer. | SQL security {definer | invoker}
The -- Comment clause is an extension of MySQL, which can be used to describe the storage program. This information is displayed by the show create procedure and show create function statements. | Comment 'string' routine_body: valid SQL procedure statement or statements


 
 

  
  
To create a subroutine in MySQL 5.1, you must have the create routine permission and the alter routine and execute permissions are automatically granted to its creator. If the binary log function is permitted, you may also need the super permission,
 
 
 
 

  
  
Specifying the parameter in, out, or inout is valid only for procedure. (Function parameters are always considered as in parameters)

 
 
 
 

  
  
The returns clause can only be specified for the function, which is mandatory for the function. It is used to specify the return type of the function, and the function body must contain a return value statement.

 
 
 
 

  
  
The creator or caller must be authorized to access the database associated with the subroutine. In MySQL 5.1, you must have the Execute Permission to execute a subroutine.
 
 
 
 

  
  
MySQL allows subprograms to contain DDL statements, such as create and drop. MySQL also allows the storage Program (but not the storage function) to contain SQL interactive statements, such as commit. Stored functions cannot contain explicit and absolute commit or rollback statements.
  
  
The statements in the returned result package cannot be used in the storage function. This includes select statements, show statements, and other statements such as explain that do not use into to read Column values from the variable.