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.