MYSQL: Basics-Stored procedures

Quick Start Understanding:

so far, most of the SQL statements we've learned are single statements for one or more tables . However, not all operations can be done with a single statement, and there are often some operations that require multiple statement mates to complete . the stored procedure that we introduced (Stored Procedure) is a set of SQL statements that are compiled to accomplish a particular function , stored in a database, and invoked by the user by specifying the name of the stored procedure and given the parameter (if the stored procedure has parameters).
In simple terms, a stored procedure is one or more SQL statements that are saved for later use. It can be treated as a batch file with enhanced versions .

benefits of using stored procedures :
? By encapsulating the processing in an easy-to-use unit, complex operations can be simplified.
? There is no need to repeatedly establish a series of processing steps, thus ensuring the consistency of the data.
? Simplifies the management of changes, which is an extension of security.
? Stored procedures are typically stored in a compiled form, so the DBMS requires less effort to process the commands and improves performance.

Create:

To create a statement:

　　

General form:

CREATE PROCEDURE ([[In | Out | INOUT] Parameter name Data class ...])

Description
　　DELIMITER $$ DELIMITER; used to define delimiters , because MySQL defaults to ";" As a delimiter, if we do not declare the separator, then the compiler will treat the stored procedure as an SQL statement, the process of compiling the stored procedure will be error -free, so you have to use the delimiter keyword in advance to declare the current segment delimiter, so that MySQL will ";" As the code in the stored procedure, the code is not executed and the delimiter is restored after it is exhausted.

Call:

Example:
　　

Description

Call Avg_price (); Executes the stored procedure you just created and displays the returned results. because a stored procedure is actually a function, the stored procedure name needs to have a () symbol (even if you do not pass parameters).

Delete:

　　

Description

Note that only the stored procedure name is given and not written ().

Using parameter Descriptions

In the above we simply display the result of the SELECT statement, and the general stored procedure does not display the result, but instead returns the result to the variable you specified .
Now we need the following :

Calculates the lowest, highest, and average price of a commodity and saves it to three variables.

Create a stored procedure

　　

Call this stored procedure

　　

Passing in a condition using the in parameter

　　To create a stored procedure:

　　

To call a stored procedure:

　　

A more intelligent stored Procedure description:

　　all of the stored procedures that have been used so far are basically a simple SELECT statement that encapsulates MySQL . While they are all examples of valid stored procedures, the work that they can accomplish is done directly with these encapsulated statements (and if they bring more, it complicates things). only when a stored procedure contains business rules and intelligent processing does their power really manifest to make our statement execution more reliable and intelligent, such as we can declare local variables, add internal annotations, use loops or judgment statements, and so on .

Example:

　　

Common control Statements and examples

emphasis: This part of the content is referred to from the king of the best Twilight Sina Blog.

(1). Conditional Statements

Ⅰ . If-then-else Statement

    MySQL > DELIMITER//      mysql > CREATE PROCEDURE proc2 (in Parameter int),           begin,          declare var int;           set var=parameter+1;           If var=0 then,          insert into T values (+);           -End If;           If parameter=0 then-          update t set s1=s1+1;          update t set s1=s1+2;           -End If;           -End;           //      mysql > DELIMITER;  

Ⅱ . case statement:

     MySQL > DELIMITER//      mysql > CREATE PROCEDURE proc3 (in Parameter int),           begin,          declare var int ;           Set var=parameter+1;           Case Var           -when 0 then-and insert into            t values (+);           -When 1 then,            insert into T values (+);           , else,            insert into T values (+);           -End case;           -End;           //      

(2). Loop Statements

Ⅰ . While end while :

     MySQL > DELIMITER//      mysql > CREATE PROCEDURE proc4 ()-          declare var int;           Set var=0;           And while var<6 do,           insert into T values (VAR);           Set var=var+1;           and end while;           -End;           //      

Ⅱ . Repeat end Repeat :

It checks the results after performing the operation, while the while is checked before execution.

     MySQL > DELIMITER//      mysql > CREATE PROCEDURE proc5 ()-            declare v int;           Set v=0;           -Repeat           , insert into T values (v);           Set v=v+1;           -Until v>=5           , end repeat;           -End;           //      mysql > DELIMITER;  

Ⅲ . Loop End Loop:

Loop loops do not require initial conditions, which are similar to while loops and do not require an end condition like repeat loops, and the meaning of the leave statement is to leave the loop.

     MySQL > DELIMITER//      mysql > CREATE PROCEDURE proc6 ()-          declare v int;           Set v=0;           -Loop_lable:loop           , insert into T values (v);           Set v=v+1;           If v >=5 then-          leave loop_lable;           -End If;           -End loop;           -End;           //      mysql > DELIMITER;  

Ⅳ . LABLES Marking:

The label can be used before the begin repeat while or loop statement, and the statement designator can only be used before a valid statement. You can jump out of the loop so that the run instruction reaches the final step of the compound statement.

(3). Iterate Iteration

Ⅰ . Iterate:

To start a compound statement from scratch by referencing the label of a compound statement

         MySQL > DELIMITER//          mysql > CREATE PROCEDURE proc10 ()-              declare v int;               Set v=0;               , Loop_lable:loop               , if V=3 then                , set v=v+1;               Iterate loop_lable;               -End If;               INSERT into T values (v);               Set v=v+1;               If V>=5 then and              leave loop_lable;               -End If;               -End loop;               -End;               //          mysql > DELIMITER;

MYSQL: Basics-Stored procedures