About MySQL stored procedures

A stored procedure resembles a database script that is stored in a database. It is similar to a method that can perform some database operations in bulk.

This article writes a simple stored procedure to quickly understand the stored procedure.

1. Because the stored procedure is similar to the programming language method, it may be used in the method; operator to flag the end of a statement. This and the MySQL command line statement end flag; is in conflict. To write a stored procedure on the command line, we first change the end flag of the MySQL statement:

Mysql> delimiter $

After executing this statement, the end identifier of the MySQL statement is defined as $. This avoids the problem of defining identifier collisions in stored procedures.

For example, the query statement select * FROM student; After definition, you need to enter SELECT * FROM student $ to execute.

2. Defining stored Procedure statements

Create procedureAddstudent (inchMaxSizeint) begin Declare var int; DeclareStu_namevarchar( -); Declaresch_idint; DeclareCls_namevarchar( -); Set var = 0;  while var <maxsize DoSetStu_name= 'Rockderia'; Setsch_id= RAND()* Ten; SetCls_name= 'One Class A year'; Insert  intoStudent (CName, SchoolID, ClassName)Values(Stu_name, sch_id, cls_name);Set var = var + 1; End  while; End;$

The end of the $ mark defines the completion. As we can see, the general pattern of creating a stored procedure is similar to creating a table, and the ontology is similar to a scripting method. There are a few differences to pay special attention to:

[1] MySQL is required to use its own basic variable type.

[2] The variable type is placed behind the variable name.

[3] Defines a variable statement (for example: declare var int;) to precede all statements.

The above statement is not difficult to understand, and does not use too many features, mainly to understand the following stored procedures use process.

3. Once the definition is successful, we can call this stored procedure

MySQL> call addstudent (ten) $

The above method will insert 10 data.

4. The storage method does not support overwriting, so it is necessary to delete the old storage mode when modifying it.

Drop procedure addstudent$

5. Of course, we can change the end identifier back when there is no conflict operation.

MySQL> delimiter;

About MySQL stored procedures