MySQL stored procedures and stored functions

Section I: Introduction to Stored procedures and functions

Stored procedures and functions are a collection of SQL statements that are defined in the database, and then call these stored procedures and functions directly to perform the defined
of SQL statements. Stored procedures and functions can prevent developers from writing the same SQL statements repeatedly. Also, stored procedures and functions are in MySQL
The server is stored and executed, which can reduce the data transfer between client and server;

Section II: Creating Stored Procedures and functions

2.1 Creating a Stored procedure

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[Characteristic ...] Routine_body
The Sp_name parameter is the name of the stored procedure;
Proc_parameter represents a list of parameters for a stored procedure;
The characteristic parameter specifies the characteristics of the stored procedure;
The Routine_body parameter is the contents of the SQL code and can be used with begin ... End to flag the start and end of the SQL code.
Each parameter in the Proc_parameter is made up of 3 parts. These 3 sections are input and output types, parameter names, and parameter types, respectively.
[In | Out | INOUT] Param_name Type
Where the in represents the input parameter; out represents the output parameter; The INOUT represents either an input or an output; the Param_name parameter is
The parameter name of the stored procedure; The type parameter specifies the parameter type of the stored procedure, which can be any data type of the MySQL database;
The characteristic parameter has multiple values. The values are described as follows:
LANGUAGE sql: Description The routine_body part is made up of SQL language statements, which are the default languages of the database system.
[NOT] Deterministic: Indicates whether the execution result of the stored procedure is deterministic. Deterministic indicates that the result is deterministic. Every
The same input will get the same output when executing the stored procedure. Not deterministic indicates that the result is indeterminate, the same input
may get different output. By default, the result is non-deterministic.
{CONTAINS SQL | NO SQL | READS SQL DATA | Modifies SQL DATA}: Indicates the limitations of the use of SQL statements by sub-programs;
CONTAINS SQL indicates that a subroutine contains SQL statements but does not contain statements that read or write data; No SQL indicates that the subroutine does not contain SQL
READS SQL data represents a statement in a subroutine that contains read data; Modifies SQL data indicates that a subroutine contains write data
Statement. By default, the system is specified as contains SQL;
SQL SECURITY {definer | INVOKER}; Indicates who has permission to execute. Definer that only the definition can be executed by himself;
INVOKER indicates that the caller can execute. By default, the system-specified permissions are definer.
COMMENT ' string ': Comment information;

DELIMITER &&
CREATE PROCEDURE Pro_book (in BT int,out count_num INT)
READS SQL DATA
BEGIN
SELECT COUNT (*) from T_book WHERE BOOKTYPEID=BT;
END
&&
DELIMITER;

Call Pro_book (1, @total);

2.2 Creating a storage function

CREATE FUNCTION sp_name ([func_parameter[,...])
RETURNS type
[Characteristic ...] Routine_body
The Sp_name parameter is the name of the stored function; Func_parameter represents the parameter list of the stored function; RETURNS type Specifies the value of the
The characteristic parameter specifies the property of the stored procedure, and the value of the parameter is the same as the value in the stored procedure; routine_body parameter
is the contents of the SQL code, you can use the BEGIN ... End to mark the beginning and end of the SQL code;
Func_parameter can consist of multiple parameters, each of which consists of a parameter name and a parameter type, in the form of the following:
Param_name type where the Param_name parameter is the parameter name of the stored function, and the type parameter specifies the parameter types of the stored function,
The type can be any data type of the MySQL database;

DELIMITER &&
CREATE FUNCTION Func_book (bookId INT)
RETURNS VARCHAR (20)
BEGIN
RETURN (SELECT bookname from T_book WHERE id=bookid);
END
&&
DELIMITER;
SELECT Func_book (2);

MySQL stored procedures and stored functions