MySQL database Stored Procedure learning implementation page

1. Create a stored procedure

1. Basic Syntax:

Create procedure process name ([process parameter [,...]) procedure [feature...] process body

Example:

1.  mysql> DELIMITER //  

2.  mysql> CREATE PROCEDURE proc1(OUT s int)  

3.      -> BEGIN 

4.      -> SELECT COUNT(*) INTO s FROM user;  

5.      -> END 

6.      -> //  

7.  mysql> DELIMITER ; 

2. Parameters

MySQL stored procedure parameters are used in the definition of stored procedures. There are three parameter types: In, out, And inout. The format is as follows:

Create procedure ([[In | Out | inout] Parameter Name Data class...])

In input parameters:Indicates that the value of this parameter must be specified when the stored procedure is called. Modifying the value of this parameter in the stored procedure cannot be returned, which is the default value.

Out output parameters:This value can be changed within the stored procedure and can be returned

Inout input and output parameters:Can be changed and returned.

3. Variables

Variable definition

Declare variable_name [, variable_name...] datatype [default value];

Datatype is the data type of MySQL, such as int, float, date, varchar (length)

Variable assignment

Set variable name = expression value [, variable_name = expression...]

User variable

Note:

① User variable names generally start @

② Misuse of user variables will make the program hard to understand and manage

Note

MySQL stored procedures can be annotated using two styles

Dual-mode bar :--

This style is generally used for single-line comments.

C style:/* Comment content */generally used for multi-line comment

Ii. Call the Stored Procedure

1. Basic Syntax:

Call sp_name ()

Note: The stored procedure must be enclosed in parentheses, even if the stored procedure is not stored.

Iii. MySQL prepare statement

Prepare statement_name from SQL _text/* definition */

Execute statement_name [using variable [, variable...]/* execute preprocessing statement */
Deallocate prepare statement_name/* delete definition */

Iv. delete stored procedures

1. Basic Syntax:

Drop procedure ap_name //

Note: You cannot delete another stored procedure in one stored procedure. You can only call another stored procedure.

V. MySQL stored procedure control statements

1. Block definition: Common

Begin

.......

End;

You can also give the block an alias.

.........

End label;

You can use the leave label to jump out of the block.

2. conditional statements

If condition then

Statment

Else

Statement

Else if;

3. Loop statements

(1). While Loop

[Label:] While expression do

Statements

End while [label];

(2) loop

[Label:] Loop

Statements

End loop [label];

(3). Repeat until Loop

[Label:] Repeat

Statements

Until expression

End repeat [label];

VI,MySQL paging Stored Procedure

The code is not written here, please download the documentation with paging code: http://download.csdn.net/detail/rhljiayou/3801910