MYSQL stored procedure is a summary of common logical knowledge points

Mysql stored procedure

1. Create stored procedure syntax (format)

DELIMITER $
CREATE PROCEDURE  Stored procedure name A ( IN  Pass in parameter name a INT , IN  Pass in parameter name b VARCHAR(20) , OUT  Returns the parameter name c INT ) 
BEGIN
 Content ..........
END $

Analysis:

IN represents the passed-in parameter, defines the passed-in parameter name, followed by the passed-in parameter type (INT, VARCHAR, DOUBLE,......) OUT represents the data returned from the execution of the stored procedure, defines the parameter name, followed by the parameter type (INT, VARCHAR, DOUBLE,......) INOUT stands for either passing in or returning, defining parameter names followed by parameter types (INT, VARCHAR, DOUBLE,......)

2. Specific syntax and logic within stored procedures

A. Defining 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 values to variables directly):

SET  Defined variables a = NEW();

Mode 2 (sql query results are directly assigned to variables):

SELECT `student`.age INTO  Defined variables a FROM `student` WHERE...........

Mode 3 (sql query results are directly assigned to multiple variables):

SELECT `student`.name AS  Defined variables a,`student`.age AS  Defined variables b INTO  Defined variables a, Defined variables b FROM `student` ...............

c. Logical judgment:

#IF Judgment: 

   　　  　　   　　  IF  Conditional statement (3>5) THEN
      　　  　　    The condition is TRUE Execute when .........;
   　　  　　   　　  END IF ; 
 　　 #IF ELSE Judgment: 
   　　  　　   　　  IF  Condition (a>0) THEN
    　　     The condition is (a>0) Execute when ........;
   　　  　　   　　  ELSE IF  Condition (a<0) THEN
 The condition is (a<0) Execute when .......;
   　　  　　   　　  ELSE
 Other executions .......;
   　　  　　   　　  END IF;

D. Cursor, (LOOP) Loop:

# Example . Single cursor loop: create procedure my_procedure() --  Create a 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 variables , Default false

DECLARE cur CURSOR FOR ( SELECT id, name FROM t_people ); --  Define cursors and enter result sets 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; --  Bind control variables to cursors , Cursor Loop End Auto Turn true
 
OPEN cur; --  Open cursor 
 posLoop: LOOP --  Beginning circulatory body ,myLoop Custom loop name , Used when ending a loop 
  FETCH cur into my_id, my_name; --  Assign the data order of the current row read by the cursor to a custom variable 12
   　　  IF done THEN --  Determine whether to continue the loop 
     　　  　　   LEAVE posLoop; --  End loop 
  　　  END IF;
  --  What you have to do , In  sql  You can use custom variables directly in 
  UPDATE t_user SET c_name = my_name WHERE id = my_id and rtrim(ltrim(c_name)) = ''; --  Left and right to go to the space 
 
  COMMIT; --  Commit transaction 
 END LOOP posLoop; --  End custom loop body 
 CLOSE cur; --  Close the cursor 
END; --  End stored procedure 

# Example . Multiple cursor loops: create procedure my_procedure() --  Create a 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 variables , Default false

DECLARE cur_1 CURSOR FOR ( SELECT id, name FROM t_people ); --  Define cursors and enter result sets 
DECLARE cur_2 CURSOR FOR  (  SELECT id_2,name_2 FROM t_people_2 ) ; -- Define cursors and enter result sets 

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; --  Bind control variables to cursors , Cursor Loop End Auto Turn true
 
OPEN cur_1; --  Open cursor 
 posLoop: LOOP --  Beginning circulatory body ,myLoop Custom loop name , Used when ending a loop 
  FETCH cur_1 into my_id, my_name; --  Assign the data order of the current row read by the cursor to a custom variable 12
   　　  IF done THEN --  Determine whether to continue the loop 
      　　 　 　  LEAVE posLoop; --  End loop 
  　　  END IF;
  --  What you have to do , In  sql  You can use custom variables directly in 
  UPDATE ..........; 　　 -- Specific operations to be done by yourself 
 END LOOP posLoop; --  End custom loop body 
 CLOSE cur_1; --  Close the cursor 

 　　 SET done = FALSE; 　　 --  Because when the first 1 After the cursor is traversed, its value is handler Set to TRUE If you don't use it, set Set it to  FALSE  , then the first 2 A cursor will not traverse. (It is best to use this statement before each operation that opens the cursor to ensure that the cursor actually traverses.) 
 　　 
OPEN cur_2; --  Open cursor 
 posLoop_2: LOOP --  Beginning circulatory body ,myLoop Custom loop name , Used when ending a loop 
  FETCH cur_2 into my_id, my_name; --  Assign the data order of the current row read by the cursor to a custom variable 12
   　　  IF done THEN --  Determine whether to continue the loop 
      　　 　 　  LEAVE posLoop_2; --  End loop 
  　　  END IF;
  --  What you have to do , In  sql  You can use custom variables directly in 
  INSERT ..........;  　　 　 -- Specific operations to be done by yourself 
 END LOOP posLoop_2; --  End custom loop body 
 CLOSE cur_2; --  Close the cursor 


END; --  End stored procedure 

3. Invocation of stored procedures

# Calling a stored procedure with no return value CALL  Stored procedure name ( Parameter .....);

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

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

4. Delete stored procedures

DROP PROCEDURE  Stored procedure name ; 

5. Considerations

Semicolons in stored procedures (;) It's very important, try not to omit it.