Home » MySQL » MySQL5.6 stored procedures and functions.

MySQL5.6 stored procedures and functions.

1. stored procedure can be divided into stored procedures and functions, create stored procedures and functions used in MySQL statements are create procedure and create function, use the call statement to call a stored procedure, can only use the output variable return value. You can call a function from the sentence (by reference function name), can also return a scalar value is stored. The process can also call other
stored procedure.
2. the concept of stored procedures

Stored procedures are collections of one or more SQL statements, which can be considered as batch files, but their role is not limited to batch processing.

3. stored procedures to create

(1) basic grammar:

create procedure sp_name ([proc_parameter]) 
[characteristics...] routine_body

create procedure: is the keyword that defines the stored procedure.

The name of the sp_name: stored procedure

Proc_parameter: specifies the parameter list of stored procedures

[in|out|inout] param_name type

in represents input parameters; out represents output parameters; inout indicates input or output;

Param_name: represents the parameter name; type: represents the parameter type and can be any type of MySQL in the

Characteristics specifies the characteristics of stored procedures:

A) language sql: illustrates that the routine body section is made up of SQL statements, and that the current system supported language is SQL, and that SQL is the unique value of the LANGUAGE property.

B) [not]deterministic: indicates that the stored procedure execution results correctly according to.DETERMINSTIC results is determined, at a time when the stored procedure is executed, the same input will get the same output.NOT DETERMINSTIC said the results are uncertain, the same input output may be different, if you do not specify an arbitrary value, the default is NOT DETERMINISTIC.

C {CONTAINS SQL|READS SQL DATA|MODIFIES) SQL|NO SQL DATA}: the specified subroutine uses the SQL statement to limit.CONTAINS SQL show that the subroutine contains the SQL statement, but does not include the data read and write statements; NO SQL marked subroutine contains no SQL statement; READS SQL DATA subroutine contains the read data MODIFIES SQL DATA statement; that subroutine contains the write data the statement. By default, the system will be designated as CONTAINS SQL.

D) SQL SECUEITY{DEFINER|INVOKER}: specifies who has the authority to execute, and the DEFINER indicates that only the caller can execute the.INVOKER, and the caller that owns the permissions can execute. By default, the system is specified as DEFINER.

E) COMMENT 'string' annotation information, which can be used to describe stored procedures or functions.

Routine_body: is the content of the SQL code, and you can use BEGIN... AND to represent the beginning and end of the SQL code.

(2) the DELIMITER// statement is the role of the MySQL is set to end / /, because the MySQL default statement ends with a semicolon, in order to avoid storage in the process of SQL statement at the end of the conflict, need to use DELIMITER to change the storage process at the end of storage, process and ending with the END// stored procedure definition after use DELIMITER; restore the default end of.DELIMITER can also specify other symbols as the end of.