Understanding of stored procedures in MySQL

What exactly is a stored procedure and why do you need to use a stored procedure?

A stored procedure is simply a collection of one or more MySQL statements saved for later use, which can be treated as a batch file, although they are not limited to batch processing.

There are 3 main benefits of using stored procedures, namely, simple, secure, high-performance, in fact, and the call function is almost, only provide input and output, the internal implementation is completely encapsulated up.

Create a stored procedure that has no input parameters and no output parameters, and the process itself is just a simple SQL statement,

The reason why this is not created in the command-line interaction program is because the interaction program defaults to the Terminator of an SQL statement, so that the creation of the stored procedure will be faulted.

Therefore, you need to use delimiter//To temporarily modify the program's statement delimiter, and after the creation of the stored procedure, you need to use delimiter, and then change back, where//can also be converted to other characters

Stored procedure with output parameters

Here we need to add that the keyword out indicates that the corresponding parameter is used to send a value from the stored procedure, MySQL supports in (passed to the stored procedure), out (outgoing from stored procedure) and inout (incoming and outgoing) parameters of the stored procedure type

The code for the stored procedure is between begin and end, and the SELECT statement is used to retrieve the value and then save it to the appropriate variable (by specifying the INTO keyword)

All MySQL variables must start with @k

Note that when MySQL is processing a statement, it ignores whitespace, which is written for easy-to-read SQL statements, and it is also recommended to write

Stored procedures with both input parameters and output parameters

In general, stored procedures are more complex to write than basic SQL statements, and writing stored procedures requires a higher level of skill and richer experience.

Please refer to the "MySQL must Know" book for examples of relevant smart storage.

The above examples refer to the book "MySQL Must Know".

Understanding of stored procedures in MySQL