Our commonly used operation database language SQL statement needs to be compiled and then executed at the time of execution, and the stored procedure (Stored Procedure) is a set of SQL statements for completing a specific function, which is compiled and stored in the database, and the user specifies Store the name of the procedure and give it a parameter (if the stored procedure takes a parameter) to call it.
A stored procedure is a programmable function that is created and saved in the database. It can consist of SQL statements and some special control structures. Stored procedures are very useful when you want to execute the same function on different applications or platforms, or to encapsulate specific functions. Stored procedures in the database can be thought of as simulations of object-oriented methods in programming, which allow control over how data is accessed.
Stored procedures are an important feature of database storage, but MySQL did not support stored procedures before 5.0, which made MySQL compromised in applications. Fortunately, MySQL 5.0 has finally started to support stored procedures, which can greatly improve the processing speed of the database, while also improving the flexibility of database programming.
(1) The stored procedure enhances the functionality and flexibility of the SQL language. Stored procedures can be written in flow control statements, with great flexibility to perform complex judgments and more complex operations.
(2) The stored procedure allows the standard component to be programmed. After the 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 the stored procedures at any time, without any impact on the application source code.
(3) The stored procedure can achieve faster execution speed. If an operation contains a large amount of TransacTIon-SQL code or is executed multiple times, the stored procedure is much faster than the batch execution. Because the stored procedure is precompiled. The query is queried the first time a stored procedure is run, and the optimizer analyzes it and gives an execution plan that is ultimately stored in the system tables. Batched TransacTIon-SQL statements are compiled and optimized each time they are run, which is relatively slow.
(4) The stored procedure 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 call is transmitted in the network. Statements, which greatly increase network traffic and reduce network load.
(5) The stored procedure can be fully utilized as a security mechanism. The system administrator limits the access rights of a certain stored procedure, and can limit the access rights of the corresponding data, avoiding the access of the unauthorized users to the data, and ensuring the security of the data.
MySQL stored procedure creation (1) format The format created by the MySQL stored procedure: CREATE PROCEDURE procedure name ([process parameter [,...]])
[characteristic. ..] process body
Here is an example:
1. mysql> DELIMITER //
2. mysql> CREATE PROCEDURE proc1(OUT s int)
3. -> BEGIN
4. -> SELECT COUNT(*) INTO s FROM user;
5. -> END
6. -> //
7. mysql> DELIMITER ;
Note:(1) Here we need to pay attention to DELIMITER // and DELIMITER; two sentences, DELIMITER is the meaning of the separator, because MySQL defaults to ";" as the separator, if we do not declare the separator, then the compiler will treat the stored procedure as The SQL statement is processed, the compilation process of the stored procedure will report an error, so the DELIMITER keyword should be used to declare the current segment separator in advance, so that MySQL will treat ";" as the code in the stored procedure, and will not execute the code. Then you need to restore the separator.
(2) The stored procedure may have input, output, input and output parameters as needed. There 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 identified by BEGIN and END.
In this way, one of our MySQL stored procedures is complete, is it easy? It doesn't matter if you don't understand. Next, let's explain in detail.
In fact, regarding the declaration of the separator, the above comments have been written very clearly, do not need to say more, just to note that if you use the MySQL Administrator management tool, you can create directly, no longer need to declare.
(3) ParametersThe parameters of the MySQL stored procedure are used in the definition of the stored procedure. There are three types of parameters, IN, OUT, INOUT, and the form is as follows:
CREATE PROCEDURE([[IN |OUT |INOUT ] parameter name data type..])
IN input parameter: indicates that the value of this parameter must be specified when the stored procedure is called. The value of the parameter modified in the stored procedure cannot be returned, which 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 at the time of the call, and can be changed and returned
I, IN parameter example
create:
1. mysql > DELIMITER //
2. mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int)
3. -> BEGIN
4. -> SELECT p_in;
5. -> SET p_in=2;
6. -> SELECT p_in;
7. -> END;
8. -> //
9. mysql > DELIMITER ;
Results of the:
1. mysql > SET @p_in=1;
2. mysql > CALL demo_in_parameter(@p_in);
3. +------+
4. | p_in |
5. +------+
6. | 1 |
7. +------+
8.
9. +------+
10. | p_in |
11. +------+
12. | 2 |
13. +------+
14.
15. mysql> SELECT @p_in;
16. +-------+
17. | @p_in |
18. +-------+
19. | 1 |
20. +-------+
As can be seen from the above, although p_in is modified in the stored procedure, it does not affect the value of @p_id.
II, OUT parameter example
create:
1. mysql > DELIMITER //
2. mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int)
3. -> BEGIN
4. -> SELECT p_out;
5. -> SET p_out=2;
6. -> SELECT p_out;
7. -> END;
8. -> //
9. mysql > DELIMITER ;
Results of the:
1. mysql > SET @p_out=1;
2. mysql > CALL sp_demo_out_parameter(@p_out);
3. +-------+
4. | p_out |
5. +-------+
6. | NULL |
7. +-------+
8.
9. +-------+
10. | p_out |
11. +-------+
12. | 2 |
13. +-------+
14.
15. mysql> SELECT @p_out;
16. +-------+
17. | p_out |
18. +-------+
19. | 2 |
20. +-------+
III, INOUT parameter example
create:
1. mysql > DELIMITER //
2. mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)
3. -> BEGIN
4. -> SELECT p_inout;
5. -> SET p_inout=2;
6. -> SELECT p_inout;
7. -> END;
8. -> //
9. mysql > DELIMITER ;
Results of the:
1. mysql > SET @p_inout=1;
2. mysql > CALL demo_inout_parameter(@p_inout) ;
3. +---------+
4. | p_inout |
5. +---------+
6. | 1 |
7. +---------+
8.
9. +---------+
10. | p_inout |
11. +---------+
12. | 2 |
13. +---------+
14.
15. mysql > SELECT @p_inout;
16. +----------+
17. | @p_inout |
18. +----------+
19. | 2 |
20. +----------+
Delay relay is a kind of Signal Relay classification, is commonly used in automatic control system devices, generally used to connect and disconnect the circuit, is an important component of automatic control and remote control circuit.
Delay Relay,Delay Relay Module,Industrial Time Delay Relays,Signal Delay Relays
Ningbo Xingchuangzhi Electric Appliance Co.,Ltd. , https://www.xingchuangzhi.com