What is the mysql stored procedure syntax?

In mysql, you can use the "CREATE PROCEDURE" statement to create a stored procedure. The syntax is "CREATE PROCEDURE process name process parameter process body process parameter format [IN|OUT|INOUT] parameter name type".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

What is mysql stored procedure syntax

SQL statements need to be compiled first and then executed, and stored procedures (Stored Procedure) are a set of SQL statements to complete specific functions. They are compiled and stored in In the database, the user calls and executes the stored procedure by specifying its name 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 perform the same function on different applications or platforms, or when you want to encapsulate specific functionality. Stored procedures in a database can be seen as a simulation of the object-oriented approach in programming, which allows control of how data is accessed.

Advantages of stored procedures:

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

(2). Standard component programming: After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement of the stored procedure. And database professionals can modify stored procedures at any time without affecting the application source code.

(3). Faster execution speed: If an operation contains a large amount of Transaction-SQL code or is executed multiple times, the stored procedure will be executed much faster than batch processing. Because stored procedures are precompiled. When a stored procedure is run for the first time, the query is analyzed and optimized by the optimizer and an execution plan is finally stored in the system table. The batch Transaction-SQL statement must be compiled and optimized every time it is run, and the speed is relatively slower.

(4). Reduce network traffic: For operations on the same database object (such as query, modification), if the Transaction-SQL statements involved in this operation are organized into stored procedures, then when the transaction is performed on the client computer When the stored procedure is called, only the call statement is transmitted over the network, thereby greatly reducing network traffic and reducing network load.

(5). Make full use of it as a security mechanism: By restricting the permissions to execute a certain stored procedure, it is possible to limit the access permissions of the corresponding data and avoid unauthorized users from accessing the data. access, ensuring data security.

MySQL's stored procedures

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

Creation of MySQL stored procedures

Syntax

CREATE PROCEDURE  过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体

DELIMITER //
  CREATE PROCEDURE myproc(OUT s int)
    BEGIN
      SELECT COUNT(*) INTO s FROM students;
    END
    //
DELIMITER ;

Delimiter

MySQL uses ";" as the delimiter by default. If If the delimiter is not declared, the compiler will treat the stored procedure as an SQL statement, so the compilation process will report an error. Therefore, you must use "DELIMITER //" to declare the current segment delimiter in advance, so that the compiler can separate the two "//" The content between them is treated as the code of the stored procedure, and these codes will not be executed; "DELIMITER;" means to restore the delimiter.

Parameters

The stored procedure may have input, output, input and output parameters as needed. If there are multiple parameters, use "," to separate them. 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. Modify the parameter in the stored procedure. The value cannot be returned and is the default value

OUT: The value can be changed inside the stored procedure and can be returned

INOUT: Specified when calling, and can be changed and returned

Process body

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

Recommended learning: mysql video tutorial

The above is the detailed content of What is the mysql stored procedure syntax?. For more information, please follow other related articles on the PHP Chinese website!