MySQL Stored procedure Learning notes

1. Introduction to Stored Procedures

The SQL statements that we commonly use to manipulate database languages need to be compiled and executed at the time of execution, while stored procedures (Stored Procedure) are sets of SQL statements to complete a particular function. Compiled and stored in a database, the user invokes execution by specifying the name of the stored procedure and the given parameter, if the stored procedure has parameters.

A stored procedure is a programmable function that is created and saved in the database. It can consist of SQL statements and some special control structures. Stored procedures are useful when you want to perform the same functions on different applications or platforms, or encapsulate specific functionality. Stored procedures in a database can be seen as simulations of object-oriented methods in programming. It allows control over how data is accessed.

Stored procedures often have the following advantages:

(1). Stored procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written with flow control statements, with a strong flexibility to complete complex judgments and more complex operations.

(2). Stored procedures allow standard components to be programmed. After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement for the stored procedure. and database professionals can modify stored procedures at any time, without affecting the source code of the application.

(3). Stored procedures can achieve a faster execution speed.

2. about MySQL stored Procedures

stored procedures are an important feature of database storage, but MySQL does not support stored procedures before 5.0, which makes MySQL a big compromise on applications. Fortunately , MySQL 5.0 has finally started to support stored procedures, which can greatly improve the processing speed of the database, but also improve the flexibility of database programming.

3. MySQL stored procedure creation

format created by the MySQL stored procedure: CreatePROCEDURE procedure name ([ process parameters [,...]])
[ features ...] Process Body

Here is a first example:

DELIMITER      CREATE PROCEDURE int )          BEGIN             SELECTCOUNT(* to from User ;           END

Note:

1 ) Here's what you need to be aware of is the delimiter// and delimiter; Two sentences, delimiter is the meaning of the separator, because mysql default to As a delimiter, if we do not declare the separator, then the compiler will treat the stored procedure as sql statement for processing, the process of compiling the stored procedure error, So to use delimiter keyword declares the current segment delimiter, so mysql will As the code in the stored procedure, the code is not executed and the delimiter is restored after it is exhausted.

(2) The stored procedure may have input, output, input and output parameters as required, here is an output parameter s, type int , if there are multiple parameters with "," split open.

(3) The beginning and end of the process body is identified by using begin with end.

Parameters

The parameters of the MySQL stored procedure are used in the definition of stored procedure, there are three kinds of parameter types , in,out,inout, form such as:

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

inch input parameter : The value that represents the parameter must be specified when the stored procedure is called, and the value of the parameter modified in the stored procedure cannot be returned as the default value

out output Parameters : This value can be changed inside the stored procedure and can be returned

INOUT input and OUTPUT parameters : specified at call, and can be changed and returned

Variable

Ⅰ. variable definition

DECLARE variable_name [, variable_name ...] datatype [DEFAULT value];

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

For example :

1. DECLARE l_int int unsigned default 4000000;
2. DECLARE l_numeric Number (8,2) DEFAULT 9.95;
3. DECLARE l_date date DEFAULT ' 1999-12-31 ';
4. DECLARE l_datetime datetime DEFAULT ' 1999-12-31 23:59:59 ';
5. DECLARE L_varchar varchar (255) DEFAULT ' This is not being padded ';

Ⅱ. assigning values to variables

SET variable name = expression Value [, variable_name = ' expression ...]

User variables:

① user variable names usually begin with @

1. SELECT ' Hello World '  into @x ;   SELECT @x;  

MySQL calls to stored procedures

with Call and your procedure name and a parenthesis, enclose the parameters in parentheses as necessary, parameters include input parameters, output parameters, input and output parameters. Refer to the example above for specific invocation methods.

MySQL changes to Stored procedures

Change with Alter PROCEDURE, set up with create PROCEDURE .

MySQL deletion of stored procedures

DROP PROCEDURE

Conditional statements

Ⅰ . If-then-else Statement

DELIMITERCREATE PROCEDUREPROC2 (inchParameterint)           begin          Declare var int; Set var=Parameter+1; if var=0  Then              Insert  intoTValues( -); End if; ifParameter=0  Then              UpdateTSetS1=S1+1; Else              UpdateTSetS1=S1+2; End if; End;         DELIMITER; 

Ⅱ . case statement:

DELIMITERCREATE PROCEDUREPROC3 (inchParameterint)       begin          Declare var int; Set var=Parameter+1;  Case var            when 0  Then                Insert  intoTValues( -);  when 1  Then                 Insert  intoTValues( -); Else                 Insert  intoTValues( +); End  Case; End;     DELIMITER; 

Looping statements

Ⅰ . While end while :

MySQL Stored procedure Learning notes