MYSQL stored procedures, that is, a summary of common logical knowledge points

DELIMITER $
CREATE PROCEDURE stored procedure name A (IN incoming parameter name a INT, IN incoming parameter name b VARCHAR(20), OUT return parameter name c INT)
BEGIN
  content..........
END $

DECLARE variable name a parameter type (INT, VARCHAR (20), BOOLEAN, .....) [DEFAULT NULL];

SET defined variable a = NEW();

SELECT `student`.age INTO defined variable a FROM `student` WHERE...........

SELECT `student`.name AS defined variable a,`student`.age AS defined variable b INTO defined variable a, defined variable b FROM `student` ...............

#IF judgment:　

         IF conditional statement (3>5) THEN
        Execute if the condition is TRUE.........;
      END IF;
  #IF ELSE judgment:
      IF condition (a>0) THEN
     Execute when the condition is (a>0)........;
      ELSE IF condition (a<0) THEN
         Execute when the condition is (a<0).......;
      ELSE
         Other executions.......;
      END IF;

#Example. Single cursor loop: create procedure my_procedure() -- Create a stored procedure begin -- Start a 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 the cursor loop will automatically turn to true
 
OPEN cur; -- Open the cursor posLoop: LOOP -- Start the loop body, myLoop is the custom loop name, and use FETCH cur into my_id, my_name to end the loop; -- Assign the data sequence of the current row read by the cursor to the custom variable 12
    IF done THEN -- Determine whether to continue the loop LEAVE posLoop; -- End the loop END IF;
  -- To do what you want, you can directly use custom variables in SQL UPDATE t_user SET c_name = my_name WHERE id = my_id and rtrim(ltrim(c_name)) = ''; -- Remove spaces on the left and right COMMIT; -- Commit the transaction END LOOP posLoop; -- End the custom loop CLOSE cur; -- Close the cursor END; -- End the stored procedure

#Example. Multi-cursor loop: create procedure my_procedure() -- Create a stored procedure begin -- Start a 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 the cursor loop will automatically turn to true when it ends
 
OPEN cur_1; -- Open cursor posLoop: LOOP -- Start the loop body, myLoop is the custom loop name, and use FETCH cur_1 into my_id, my_name to end the loop; -- Assign the data sequence of the current row read by the cursor to the custom variable 12
    IF done THEN -- Determine whether to continue the loop LEAVE posLoop; -- End the loop END IF;
  -- To do what you want to do, just use the custom variable in sql UPDATE ..........; --Specific operations you want to do END LOOP posLoop; --End the custom loop body CLOSE cur_1; --Close the cursor SET done = FALSE; --Because when the first cursor is traversed, its value is set to TRUE by the handler. If you don't use set to set it to FALSE, the second cursor will not traverse. (It is best to use this statement before each operation to open a cursor to ensure that the cursor can actually be traversed)
　
 OPEN cur_2; -- Open cursor posLoop_2: LOOP -- Start the loop body, myLoop is the custom loop name, and use FETCH cur_2 into my_id, my_name to end the loop; -- Assign the data sequence of the current row read by the cursor to the custom variable 12
    IF done THEN -- Determine whether to continue the loop LEAVE posLoop_2; -- End the loop END IF;
  -- To do what you want, you can directly use the custom variable in SQL INSERT ..........; --The specific operation you want to do END LOOP posLoop_2; --End the custom loop body CLOSE cur_2; --Close the cursor END; --End the stored procedure

#Call a stored procedure that has no return value CALL stored procedure name (parameters.....);

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

CALL stored procedure name (@aaa);
SELECT @aaa;

DROP PROCEDURE stored procedure name;