Summary of common logic knowledge points of MySQL stored procedure

Time:2021-1-14

MySQL stored procedure

1. Create stored procedure syntax (format)

DELIMITER $
Create procedure stored procedure name a (in passes in parameter name a int, in passes in parameter name B varchar (20), out returns parameter name C int)
BEGIN
  Content
END $

Analysis:

  • In represents the incoming parameter, defines the name of the incoming parameter, followed by the type of the uploaded parameter (int, varchar, double,…)
  • Out represents the data returned by the stored procedure after execution, defines the parameter name, followed by the parameter type (int, varchar, double,…)
  • Inout means that it can be passed in or returned, defining the parameter name, followed by the parameter type (int, varchar, double,…)

2. Specific syntax and logic in stored procedure

A. Define variable syntax:

Declare variable name a parameter type (int, varchar (20), Boolean,...) [default null];

Note: you can add default null after the parameter type to set the initial value.

B. Variable assignment:

Method 1 (assign value to variable directly)

The variable defined by set a = new();

Method 2 (the result of SQL query is directly assigned to the variable)

The variable a from 'student' where defined by select 'student'. Age into

Method 3 (the results of SQL query are assigned to multiple variables directly): the method is simple and easy to implement

Select 'student'. Name as, B 'student'. Age as, B from 'student'

c. Logical judgment:

#If judgment:    

      If conditional statement (3 > 5) then
         If the condition is true, execute;
      END IF;
  #If else judgment:
      If condition (a > 0) then
        When the condition is (a > 0);
      Else if condition (a < 0) then
         When the condition is (a < 0);
      ELSE
         Other execution;
      END IF;

D. Cursor, (loop) loop:

#Single cursor loop: create procedure my_ Procedure () -- create stored procedure
Begin -- start stored procedure
declare my_ ID varchar (32); -- custom variable 1
declare my_ Name varchar (50); -- custom variable 2
Declare done int default false; -- custom control cursor loop variable, default false

DECLARE cur CURSOR FOR ( SELECT id, name FROM t_ People); -- define the cursor and enter the result set
Declare continue handler for not found set done = true; -- bind the control variable to the cursor and automatically turn to true at the end of the cursor loop
 
Open cur; -- Open cursor
 Posloop: loop -- start loop body, and myloop is the user-defined loop name, which is used to end the loop
  FETCH cur into my_ id, my_ Name; -- assign the data order of the current row read by the cursor to the custom variable 12
     If done then -- judge whether to continue the cycle
        Lead posloop; -- end loop
    END IF;
  --What you want to do is to use custom variables directly in SQL
  UPDATE t_ user SET c_ name = my_ name WHERE id = my_ id and rtrim(ltrim(c_ Name)) = '; -- space left and right
 
  Commit; -- commit transaction
 End loop posloop; -- end custom loop body
 Close cur; -- close cursor
End; -- ends the stored procedure
#Example. Multi cursor loop: create procedure my_ Procedure () -- create stored procedure begin -- start stored procedure
declare my_ ID varchar (32); -- custom variable 1
declare my_ Name varchar (50); -- custom variable 2
Declare done int default false; -- custom control cursor loop variable, default false

DECLARE cur_ 1 CURSOR FOR ( SELECT id, name FROM t_ People); -- define the cursor and enter the result set
DECLARE cur_ 2 CURSOR FOR ( SELECT id_ 2,name_ 2 FROM t_ people_ 2) ; -- define the cursor and enter the result set

Declare continue handler for not found set done = true; -- bind the control variable to the cursor and automatically turn to true at the end of the cursor loop
 
OPEN cur_ 1. -- Open cursor
 Posloop: loop -- start loop body, and myloop is the user-defined loop name, which is used to end the loop
  FETCH cur_ 1 into my_ id, my_ Name; -- assign the data order of the current row read by the cursor to the custom variable 12
     If done then -- judge whether to continue the cycle
        Lead posloop; -- end loop
    END IF;
  --What you want to do is to use custom variables directly in SQL
  Update......; -- specific operation to do
 End loop posloop; -- end custom loop body
 CLOSE cur_ 1. -- close cursor

  Set done = false; -- because after the first cursor traverses, its value is set to true by handler. If it is set to false without set, then the second cursor will not traverse. (it is better to use this statement before each operation to open the cursor to ensure that the cursor can actually traverse)
  
OPEN cur_ 2. -- Open cursor
 posLoop_ 2: Loop -- start loop body, and myloop is the custom loop name, which is used to end the loop
  FETCH cur_ 2 into my_ id, my_ Name; -- assign the data order of the current row read by the cursor to the custom variable 12
     If done then -- judge whether to continue the cycle
        LEAVE posLoop_ 2. -- end cycle
    END IF;
  --What you want to do is to use custom variables directly in SQL
  Insert......; -- specific operation to do
 END LOOP posLoop_ 2. -- end custom loop body
 CLOSE cur_ 2. -- close cursor


End; -- ends the stored procedure

3. Call of stored procedure

#Call the stored procedure with no return value, call the stored procedure name (parameter......);

#Call stored procedure with return value (get return value)

Call stored procedure name (@ AAA);
SELECT @aaa;

4. Delete stored procedure

Drop procedure stored procedure name;

5. Precautions

Semicolons (;) in stored procedures are important and should not be omitted.