MySQL stored procedure and MySQL common process control syntax

/* This Code creates a stored procedure named "P4" and sets two int-type varchar parameters, S1, S2, and S3, or other data types, the internal variables X1 and X2 are created. Delimiter is the delimiter $ that modifies the delimiter, which means to change the default delimiter ";" to "$ ", in this way, the storage process of multipart write can be executed as a whole, instead of being used as multiple SQL statements to separately execute the creation process and then change the separator back "; "Two ways to create and assign values to variables: Set @ variable name = value; select value into @ variable name; before using a variable, you must first run the variable value assignment statement. You can also use "declare variable name type (string type requires a range) default value;" to create a variable, however, variables created in this process can only be accessed within the stored procedure. There are only three types of parameters in the In, out, And inout calls. Several parameters are passed during the procedure, only in-type parameters can pass variables and constants, while out and inout parameters must pass variables to out, the variable value of the inout parameter changes externally as it changes during the process, the external value of the variable given to the in parameter is not affected by the change of the variable value in the process */delimiter $ drop procedure if exists 'p4 '$ create procedure 'p4' (in S1 int, out S2 int, inout S3 varchar (10) begindeclare X1 varchar (10) default 'this is x1'; declare X2 varchar (10) default 'this is x2 '; set S1 = 11; Set S2 = 22; Set S3 = 'iss3';/* If syntax */If S1 = 11 and S2 = 12 thenselect S1, S2; end if; if S3 = 's3' or S1 = S2 thenselect S3; else select S1, S2, S3; end if; /* case syntax */case s3when 's1' thenselect 'this is s1'; When 's2 'thenselect' this is s2'; elseselect 'this is s3'; end case; /* While loop */while S1> 1 doset S1 = s1-1; end while; select S1;/* The repeat loop statement is executed when the while clause meets the condition, repeat is always executed until conditions are met to terminate */repeat set S1 = s1-1; until S1 = 1 end repeat; select S1;/* loop without loop conditions, it will keep repeating until "Leave zidingyi;" "zidingyi" is the custom loop mark */zidingyi: loopset S1 = S1 + 1; if S1 = 5 thenleave zidingyi; end if; end loop; select S1; end $ delimiter; Set @ p_in = 1; Set @ p_out = 2; Set @ p_inout = 's3 '; select 'Hello world1' into @ P_4;/* call the Stored Procedure */call P4 (@ p_in, @ p_out, @ p_inout); select @ p_in, @ p_out, @ p_inout, @ P_4;/* If the stored procedure has only one statement, the statement does not need to be written... in the end stored procedure, you can directly use the externally defined variables. In the stored procedure, the variables defined by set and select are global, after this process is executed, the variables defined by set and select can be accessed externally. Other processes can also directly use */create procedure p1 () set @ Var = 'p1 '; create procedure P2 () Select Concat ('Last procedure was', @ var); Call p1 (); Call P2 (); select @ var; Create procedure P3 () select Concat (@ p3_var, 'World'); Set @ p3_var = 'hello'; call P3 ();/* Delete the Stored Procedure */drop procedure P4; /* or */drop procedure if exists 'p4 '/* check which stored procedures test in the database is the database name */select name from MySQL. proc where DB = 'test';/* or */show procedure status where DB = 'test';/* view the stored procedure details, including statement */show create procedure P4;