How to write database stored procedures

Introduction to stored procedures

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.

Recommended courses: MySql Tutorial.

Advantages of stored procedures:

(1). Enhance the functions of SQL language And flexibility: 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 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 no delimiter is declared, the compiler will treat the stored procedure as a SQL statement, so the compilation process will report an error, so you must use "DELIMITER //" to declare the current segment delimiter in advance. , allowing the compiler to regard the content between the two "//" as the code of the stored procedure, and will not execute these codes; "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: It is specified when calling and can be changed and returned.

Procedure body

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

Summary

This time we mainly mastered the use of stored procedures. In general, stored procedures are actually similar to functions in C. In C, we need to call this function in the file that creates this process, but for stored procedures, it is equivalent to storing this operation process in the database. You can use call and call it, and Input or output some parameters and results.

The most impressive thing is the definitions of input and output like "in n int" and "out sum int". By looking at related blogs, we can summarize the usage rules of input and output definitions as follows:

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

IN, OUT, INOUT
The format is: Create procedure|function([[IN |OUT |INOUT] parameters Name data type...])

IN Input parameter
indicates that the value of this parameter must be specified when calling the stored procedure. If the value of this parameter is modified during the stored procedure, it cannot be returned and is the default value.

OUT output parameters
This value can be changed inside the stored procedure and can be returned

INOUT input and output parameters
Specified when calling, and can be changed and returned

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

Usage example of parameter inout (can both input a value and pass out a value)
Statement function: Pass an age and automatically increase the age by 10 years
create procedure p3(inout age int)
begin
set age:=age 10;
end
where: when called , the parameter value of the inout type is both an input type and an output type. Give it a value. The value is not a variable, so we need to set a variable first and initialize the value. Then just pass the variable directly when calling.
set @currentAge=8$
call p3(@currentAge)$
select @currentAge$
After creating and executing the stored procedure, the running results are as follows:

 ​ 

The above is the detailed content of How to write database stored procedures. For more information, please follow other related articles on the PHP Chinese website!