SQL stored procedure

The stored procedure is a procedural SQL, which can directly manipulate the underlying data table. Compared with the collection-oriented operation, it can achieve some more complex data processing. A stored procedure can be said to be a statement collection composed of SQL statements and flow control statements. It can receive input parameters, and can also return output parameters to the caller and return calculation results, just like the functions we have learned before.

What is a stored procedure and how to create a stored procedure

The stored procedure is the encapsulation of the SQL statement. The stored procedure is actually composed of SQL statements and flow control statements.

The definition of the stored procedure:

CREATE PROCEDURE stored procedure name ([parameter list])

BEGIN

Control flow statement

END

Like views, we can delete stored procedures that have been created, using DROP PROCEDURE. If you want to update the stored procedure, we need to use ALTER PROCEDURE.

The role of DELIMITER

DELIMITER comes to define a new terminator. Because SQL uses (;) as the terminator by default, so when it is stored

After each SQL sentence in the process ends, using (;) as the terminator is equivalent to telling SQL that this sentence can be executed

But the stored procedure is a whole, we do not want SQL to be executed one by one, but the whole way of executing the stored procedure, because

We need to temporarily define a new DELIMITER, the new terminator can use (//) or ($$).

eg:


DELIMITER //
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
       DECLARE i INT;
       DECLARE sum INT;
       
       SET i = 1;
       SET sum = 0;
       WHILE i <= n DO
              SET sum = sum + i;
              SET i = i + 1;
       END WHILE;
       SELECT sum;
END //
DELIMITER;

If you use Navicat tool, Navicat will automatically set DELIMITER to other symbols when writing stored procedures, and we don't need to perform DELIMITER operation.

3 types of stored procedures

Control flow statement

1. BEGIN...END: BEGIN...END contains multiple statements, and each statement ends with a (;) sign.

2. DECLARE: DECLARE is used to declare variables. The position of use is in the middle of the BEGIN...END statement, and the variable declaration needs to be made before other statements are used.

3. SET: Assignment statement, used to assign values ​​to variables.

4. SELECT...INTO: Store the query result from the data table in a variable, that is, assign a value to the variable.

5. IF...THEN...ENDIF: conditional judgment statement, we can also use ELSE and ELSEIF in IF...THEN...ENDIF to make conditional judgment.

6. CASE: The CASE statement is used for multi-condition branch judgment. The syntax used is as follows.

7. LOOP, LEAVE and ITERATE: LOOP is a loop statement. Use LEAVE to jump out of the loop, and use ITERATE to enter the next loop. If you have experience in process-oriented programming languages, you can understand LEAVE as BREAK and ITERATE as CONTINUE.

8. REPEAT...UNTIL...END REPEAT: This is a loop statement. First, the loop will be executed, and then the expression will be judged in UNTIL. If the condition is met, it will exit, that is, END REPEAT; if the condition is not met, it will continue. The loop is executed until the exit condition is met.

9. WHILE...DO...END WHILE: This is also a loop statement. Unlike the REPEAT loop, this statement needs to be conditionally judged. If the condition is met, the loop will be executed, and if the condition is not met, the loop will be exited.

Advantages and disadvantages of stored procedures

The stored procedure can be compiled once and used many times. The stored procedure is compiled only when it is created, and no recompilation is required for subsequent use, which improves the execution efficiency of SQL. Secondly, it can reduce development workload.

Encapsulating the code into modules is actually one of the core ideas of programming. In this way, complex problems can be disassembled into different modules, and then the modules can be reused. While reducing the amount of development work, it can also ensure the code's integrity. The structure is clear.

The security of the stored procedure is strong. We can set the user permissions when setting the stored procedure, so that it has strong security like the view.

The amount of network transmission can be reduced, because the code is encapsulated in a stored procedure, and each use only needs to call the stored procedure, which reduces the amount of network transmission. At the same time, when performing relatively complex database operations, the operations that originally required the use of one SQL statement, which may have to be connected to the database multiple times, have now become a stored procedure and only need to be connected once.

Its portability is poor, and stored procedures cannot be transplanted across databases. For example, stored procedures written in MySQL, Oracle, and SQL Server need to be rewritten when they are replaced with other databases.

Debugging is difficult, and only a few DBMS support the debugging of stored procedures. For complex stored procedures, development and maintenance are not easy.

The version management of the stored procedure is also very difficult. For example, the index of the data table changes, which may cause the stored procedure to become invalid. We often need to perform version management when developing software, but the stored procedure itself does not have version control, and it is very troublesome to iteratively update the version.

Not suitable for high-concurrency scenarios, high-concurrency scenarios need to reduce the pressure on the database, sometimes the database will use the method of sub-database sub-table, and high scalability requirements, in this case, the stored procedure will become difficult to maintain , Increasing the pressure on the database is obviously not applicable.