The creation of mysql stored procedure

Note:

(1) what we need to pay attention to here are DELIMITER / / and DELIMITER; two sentences, DELIMITER means delimiter, because MySQL defaults to ";". If we do not declare a delimiter, the compiler will treat the stored procedure as a SQL statement, and the compilation process of the stored procedure will report an error, so declare the current segment delimiter with the DELIMITER keyword in advance.

In this way, MySQL will treat ";" as the code in the stored procedure, will not execute the code, and will restore the delimiter when it is finished.

(2) the stored procedure may have input, output, input and output parameters as needed, here is an output parameter s, the type is int, if there are multiple parameters separated by ",".

(3) the beginning and end of the process body are marked by BEGIN and END.

In this way, one of our MySQL stored procedures is completed, isn't it easy? It doesn't matter if you don't understand it. Next, we'll explain it in detail.

(2)。 Declare delimiter

In fact, with regard to the declaration separator, the above comments have been written very clearly, there is no need to say more, just a little note is: if you are using MySQL's Administrator management tool, you can directly create, no longer need to declare.

(3)。 Parameters.

The parameters of MySQL stored procedures are used in the definition of stored procedures. There are three parameter types, IN,OUT,INOUT, in the form of:

CREATE PROCEDURE ([[IN | OUT | INOUT] parameter name data class.])

IN input parameter: indicates that the value of this parameter must be specified when calling the stored procedure. The value that modifies this parameter in the stored procedure cannot be returned and is the default value.

OUT output parameter: this value can be changed inside the stored procedure and can be returned

INOUT input and output parameters: specified when called, and can be changed and returned

Insert data in a loop

Delimiter $$

Drop procedure if exists test

Create procedure test (num int)

Begin

Declare i int default 1

While i