Shulou(Shulou.com)05/31 Report--

This article shows you how to understand SQL stored procedures, the content is concise and easy to understand, can definitely brighten your eyes, through the detailed introduction of this article, I hope you can get something.

First, MySQL stored procedure: a stored procedure is a reachable function that is created and saved in. Stored procedures are useful when you want to execute the same function on different applications or platforms, or to encapsulate specific functions.

Advantages:

1. Stored procedures enhance the function and flexibility of SQL statements. Stored procedures can be written with flow control, have strong flexibility, and can complete complex judgments and more complex operations.

2. Stored procedures allow standard components to be programming. After the stored procedure is created, it can be called many times in the program without having to rewrite the SQL statement of the stored procedure. On the other hand, database professionals can modify the stored procedure at any time, which has no effect on the application source code.

3. Stored procedures can reduce network traffic. For the operation of the same database object (such as query, modification), if the Transaction-SQL statement involved in this operation is organized into a stored procedure, then when the stored procedure is called on the client computer, only the calling statement is transmitted in the network, thus greatly increasing the network traffic and reducing the network load.

4. Stored procedures can be fully utilized as a security mechanism. By restricting the permissions of a stored procedure, the system administrator can restrict the access to the corresponding data, avoid the access of unauthorized users to the data, and ensure the security of the data.

Second, the creation of MySQL stored procedures:

(1) format

The format created by the MySQL stored procedure:

Create procedure procedure name ([procedure parameters [,...]]) Procedure [feature.] Process body

Example:

Note:

(1) what we need to pay attention to here are DELIMITER / / and DELIMITER; in two sentences, DELIMITER means a delimiter, because MySQL defaults to ";". If we do not declare a delimiter, the compiler will treat the stored procedure as a SQL statement, and the compilation process of the stored procedure will report an error, so the current segment delimiter must be declared with the DELIMITER keyword in advance, so that MySQL will set the " "as code in a stored procedure, the code will not be executed and the delimiter will be restored when it is used up.

(2) the stored procedure may have input, output, input and output parameters as needed, here is an output parameter s, the type is int, if there are multiple parameters separated by ",".

(3) the beginning and end of the process body are marked by BEGIN and END.

In this way, one of our MySQL stored procedures is completed, isn't it easy? It doesn't matter if you don't understand it. Next, we'll explain it in detail.

(2), parameters

The parameters of MySQL stored procedures are used in the definition of stored procedures. There are three parameter types, IN,OUT,INOUT, in the form of:

CREATE PROCEDURE stored procedure name ([[IN | OUT | INOUT] parameter name data class.])

IN input parameter: indicates that the value of this parameter must be specified when calling the stored procedure. The value that modifies this parameter in the stored procedure cannot be returned and is the default value.

OUT output parameter: this value can be changed inside the stored procedure and can be returned

INOUT input and output parameters: specified when called, and can be changed and returned

The above is how to understand the stored procedures of SQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.