MySQL stored procedures

1. What is a stored procedure?

The SQL statements we operate need to be compiled and executed at each execution, while the stored procedure (stroed Procedure) is a set of SQL statements to complete a particular function, compiled and stored in a database (somewhat analogous to Java's static variables).

When we need to use it, we call it by specifying the name of the stored procedure and specifying the parameter (if the stored procedure has parameters) to execute it.

A stored procedure is a programmable function that is created in the database and is saved, consisting of SQL statements and some special control structure statements.

2. What are the advantages of stored procedures?

(1) Enhance the functionality and flexibility of the SQL language

Stored procedures can use flow control statements (IF ... ELSE ... ), there is a strong flexibility to complete complex judgments and calculations.

(2) Improve execution speed

If an operation contains a large number of Transaction-sql code or is executed more than once, the stored procedure is much faster than the batch execution. Because stored procedures are precompiled, when a stored procedure is first run, the optimizer optimizes it for analysis and ultimately stores it without having to compile it every time.

(3) Reduce network traffic

For operations on the same database object (such as queries, modifications), the TRANSACTION-SQL statement may be more complex than calling a stored procedure statement, so the use of stored procedures reduces Network load and network traffic.
(4) Ensure safety

The system administrator restricts the access to the corresponding data by executing the permission of a stored procedure, avoids the unauthorized user's access to the data, and ensures the security of the data.

3. mysql Stored procedure

Stored procedures are an important feature of database storage, but MySQL does not support stored procedures until 5.0, which makes MySQL a great compromise for 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.

4. Prepare the data

DROP TABLE IF EXISTS' T_user ';CREATE TABLE' T_user ' (' ID ')int( One) not NULL, ' USERNAME 'varchar( -) not NULL, ' City 'varchar(Ten)CHARACTER SETUtf8DEFAULT NULL, ' PHONE 'varchar(Ten)DEFAULT NULL,  PRIMARY KEY(' ID '),UNIQUE KEY' name_city_phone ' (' USERNAME ', ' City ', ' phone '),KEY' U_index ' (' USERNAME ')) ENGINE=InnoDBDEFAULTCHARSET=latin1;-- ------------------------------Records of T_user-- ----------------------------INSERT  into' T_user 'VALUES('4','Jack','Shenzhen','185');INSERT  into' T_user 'VALUES('3','Jerry','Beijing','181');INSERT  into' T_user 'VALUES('1','Parry','Guangzhou',' the');INSERT  into' T_user 'VALUES('2','Tom','Shanghai','189');

5. mysql Stored procedure

(1) Creating a stored procedure

CREATE PROCEDURE P_name () BEGIN     ...... END

(2) calling a stored procedure

You must add parentheses after the call P_name ()///stored procedure name (p_name), even if there are no parameters

(3) Delete a stored procedure

DROP PROCEDURE P_name; Cannot delete another store in one stored procedure Old town, can only call another stored procedure

(4) View all stored procedures in the database

PROCEDURE STATUS

(5) View details of a single stored procedure

CREATE PROCEDURE P_name

6. Create a detailed description of the stored procedure

(1) Simple, non-parametric stored procedure (query all result sets)

DROP PROCEDURE IF EXISTS P_user; CREATE PROCEDURE P_user () BEGIN    SELECT *  from T_user; END;

Invocation: Call P_user ();

　　

(2) with parameter stored procedure

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 ...])

A.in parameters

/*create a stored procedure*/DROP PROCEDURE IF EXISTSP_user;CREATE PROCEDUREP_user (incht_idVARCHAR(Ten))BEGIN        SELECT *  fromT_userWHEREId=t_id;END;/*call a stored procedure*/Call P_user (1);

View Code

　　　　　　

B.out parameters

/*create a stored procedure*/DROP PROCEDURE IF EXISTSP_user;CREATE PROCEDUREP_user (incht_idVARCHAR(Ten), Out UcountINT)BEGIN        SELECT COUNT(*) fromT_userWHEREId=t_id;END;/*call a stored procedure*/Call P_user (4,@p_num); SLEEP (@p_num)

View Code

　　　　

The C.inout parameter (parameter is the entry parameter is also an argument)

/*create a stored procedure*/DROP PROCEDURE IF EXISTSP_user;CREATE PROCEDUREP_user (INOUT t_idVARCHAR(Ten))BEGIN        SELECT COUNT(*) fromT_userWHEREId=t_id;END;/*call a stored procedure*/SET @p_num = 4; Call P_user (@p_num);SELECT @p_num;

View Code

　　　　

(3) Process Control statement (if ID is divisible by 2, query 1, 3; otherwise query 2,4)

IF ... Then

ELSEIF ... Then

ELSE ...

END IF

/*create a stored procedure*/DROP PROCEDURE IF EXISTSP_user;CREATE PROCEDUREP_user (incht_idVARCHAR(Ten))BEGIN    IFt_id%2 = 0  Then        SELECT *  fromT_userWHEREIdinch(2,4); ELSE        SELECT *  fromT_userWHEREIdinch(1,3); END IF;END;/*call a stored procedure*/SET @p_num=1; Call P_user (@p_num);

View Code

　　　　　　

(4) While statement

While ... do

......

End while;

/*create a stored procedure*/DROP PROCEDURE IF EXISTSP_user;CREATE PROCEDUREP_user ()BEGIN    DECLARE Temp INT; SET Temp = 1;  while Temp<5 DoSELECT *  fromT_userWHEREId= Temp; SET Temp = Temp + 1; END  while;END;/*call a stored procedure*/Call P_user ();

View Code

　　　　

(5) Repeat statement

Repeat ...

Until ...

End repeat;

DROP PROCEDURE IF EXISTSP_user;CREATE PROCEDUREP_user ()BEGIN    DECLARE Temp INT; SET Temp = 1; REPEATSELECT *  fromT_userWHEREId= Temp; SET Temp = Temp + 1; UNTILTemp>=5    ENDREPEAT;END;/*call a stored procedure*/Call P_user ();

View Code

　　　　

MySQL stored procedures