MySQL Stored Procedures

The SQL statements that we commonly use to manipulate database languages need to be compiled and executed at the time of execution, while stored procedures (Stored Procedure) are sets of SQL statements that are compiled for specific functions and stored in the database after compilation. The user invokes execution by specifying the name of the stored procedure and the given parameter (if the stored procedure has parameters).

A stored procedure is a programmable function that is created and saved in the database. It can consist of SQL statements and some special control structures. Stored procedures are useful when you want to perform the same functions on different applications or platforms, or encapsulate specific functionality. Stored procedures in a database can be seen as simulations of object-oriented methods in programming. It allows control over how data is accessed.

Stored procedures often have the following advantages:

(1). Stored procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written with flow control statements, with a strong flexibility to complete complex judgments and more complex operations.

(2). Stored procedures allow standard components to be programmed. After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement for the stored procedure. and database professionals can modify stored procedures at any time, without affecting the source code of the application.

(3). The stored procedure can achieve a faster execution speed. If an operation contains a large number of Transaction-sql code or is executed more than once, the stored procedure is much faster than the batch execution. Because the stored procedure is precompiled. When you run a stored procedure for the first time, the optimizer optimizes it for analysis and gives the execution plan that is ultimately stored in the system table. The batch TRANSACTION-SQL statements are compiled and optimized each time they are run, relatively slowly.

(4). Stored procedures can reduce network traffic. For operations on the same database object, such as queries, modifications, if the TRANSACTION-SQL statement involved in this operation is an organized stored procedure, when the stored procedure is called on the client computer, only the calling statement is transmitted on the network, which greatly increases network traffic and reduces network load.

(5). Stored procedures can be used as a security mechanism to make full use of them. The system administrator restricts the access to the corresponding data by executing the permission of a stored procedure, avoids the unauthorized user's access to the data, and ensures the security of the data.

To create a stored procedure: (Refer to MySQL 5.5 manual)

>>Delimiter//#声明分隔符: If no delimiter is declared, the compiler treats the stored procedure as a SQL statementCREATE PROCEDUREProcedure_name (inch |Out|inout variable_name datatype) #in|Out|INOUT are three types of stored procedures, default to in type, call stored procedure designation, and modify its value in stored procedure cannot be returned;
#OUT型, the inside of the store can be modified, and can be returned; InOut type, called when specified, can be modified and returnedBEGIN#过程体用BEGIN和END进行标识SELECT .........END//>>delimiter;

When calling a stored procedure, use the call

Call Procedure_name (...);

Local variable definition:

DECLARE [, Var_name] [DEFAULT value]

Type is the data type of SQL, such as Int,float,char, can be specified as the initial value, or omitted, if omitted, the initial value is null.

The scope of the local variable is the begin at which it resides ... End.

MySQL Stored Procedures