Shulou(Shulou.com)06/01 Report--

Today, I will talk to you about how to write the stored procedure of the database, many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

SQL statements need to be compiled and then executed, and a stored procedure (Stored Procedure) is a set of SQL statements that are compiled and stored in the database. The user invokes and executes the stored procedure by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters). Stored procedures are programmable functions that are created and saved in the database and can be composed of SQL statements and control structures. Stored procedures are useful when you want to execute the same function on different applications or platforms, or to encapsulate specific functions. The stored procedure in the database can be seen as a simulation of the object-oriented method in programming, which allows you to control how the data is accessed.

Advantages of stored procedures:

(1)。 Enhance the function and flexibility of SQL language: stored procedures can be written with control statements, have strong flexibility, and can complete complex judgments and more complex operations.

(2)。 Standard component programming: after a stored procedure is created, it can be called many times in the program without having to rewrite the SQL statement of the stored procedure. And database professionals can modify the stored procedure at any time, which has no effect on the application source code.

(3)。 Faster execution: if an operation contains a large amount of Transaction-SQL code or is executed multiple times separately, the stored procedure executes much faster than batch processing. Because the stored procedure is precompiled. When a stored procedure is run for the first time, the optimizer analyzes and optimizes it, and gives the execution plan that is eventually stored in the system table. Batch Transaction-SQL statements are compiled and optimized each time they are run, which is relatively slow.

(4)。 Reduce network traffic: an operation (such as query, modification) against the same database object, 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 reducing the network traffic and reducing the network load.

(5)。 As a security mechanism to make full use of: by restricting the authority to execute a stored procedure, we can achieve the restrictions on the access to the corresponding data, avoid unauthorized users' access to the data, and ensure the security of the data.

Stored procedures for MySQL

Stored procedure is an important function of database. MySQL 5.0did not support stored procedure before, which greatly reduced the application of MySQL. Fortunately, MySQL 5.0 begins to support stored procedures, which can not only greatly improve the processing speed of the database, but also improve the flexibility of database programming.

The creation of MySQL stored procedure

Grammar

CREATE PROCEDURE procedure name ([[IN | OUT | INOUT] parameter name data type [, [IN | OUT | INOUT] parameter name data type …]]) [features.] Process body DELIMITER / / CREATE PROCEDURE myproc (OUT s int) BEGIN SELECT COUNT (*) INTO s FROM students; END / / DELIMITER

Separator

MySQL defaults to ";". If the delimiter is not declared, the compiler will treat the stored procedure as a SQL statement, so the compilation process will report an error, so declare the current segment delimiter with "DELIMITER / /" in advance, so that the compiler will not execute the code between the two "/ /" as the code of the stored procedure; "DELIMITER;" means to restore the delimiter.

Parameters.

Stored procedures may have input, output, input and output parameters as needed, and if there are multiple parameters, they may be separated by ",". The parameters of MySQL stored procedures are used in the definition of stored procedures. There are three parameter types, IN,OUT,INOUT:

The value of the IN parameter must be specified when calling the stored procedure. The value that modifies the parameter in the stored procedure cannot be returned, it is the default value OUT: the value can be changed inside the stored procedure and can be returned INOUT: specified when called, and can be changed and returned

Process body

The beginning and end of the process body are identified by BEGIN and END.

Summary

This time is mainly to master the use of stored procedures, generally speaking, stored procedures are actually similar to functions in C++, and in C++ we need to create this process in the file to call this function, but for stored procedures, equivalent to storing this operation in the database, you can use call and call it, and enter or output some parameters and results.

What impresses me most is the definitions of input and output such as "in n int" and "out sum int". By viewing the relevant blogs, you can summarize the following rules for the use of input and output definitions:

The parameters of MySQL stored procedures are used in the definition of stored procedures. There are three parameter types.

IN,OUT,INOUT

Format: Create procedure | function ([[IN | OUT | INOUT] parameter name data class.])

IN input parameters

The value that represents the parameter must be specified when the stored procedure is called. The value that modifies the parameter in the stored procedure cannot be returned and is the default value.

OUT output parameters

This value can be changed within the stored procedure and can be returned

INOUT input and output parameters

Specified when called and can be changed and returned

IN and OUT have experienced in the experiment, mainly the understanding of INOUT. Here is an example:

An example of the use of the parameter inout (you can enter a value and pass a value out)

Sentence function: pass an age, automatically increase the age by 10 years

Create procedure p3 (inout age int)

Begin

Set age:=age+10

End

Among them: when calling, the parameter value of inout type is both input type and output type, give it a value, the value is not a variable, so we need to set a variable and initialize this value first, and pass this variable directly when calling.

Set @ currentAge=8 $

Call p3 (@ currentAge) $

Select @ currentAge$

After the stored procedure is created and executed, the running result is as follows:

After reading the above, do you have any further understanding of how to write the stored procedure of the database? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.