MySQL Learning Note (iv)-Stored procedures

I. Overview

A stored procedure is a collection of some SQL statements defined by the database , and then calls these stored procedures and functions directly to execute an already defined SQL statement. Stored procedures prevent developers from writing the same SQL statements repeatedly, and the stored procedures are stored and executed in the MySQL server, which reduces the data transfer between the client and server side.

1. Advantages

(1) Provide execution performance

Usually when the client executes the SQL command, the database has to parse to compile this pre-preparation process. However, the stored procedure is completed after parsing, compiled processing saved in the database, the execution can reduce the burden of the database, improve execution performance.

(2) To reduce network burden

With stored procedures, complex database operations can also be done on the database server, as long as the necessary parameters are passed from the application to the database, which greatly reduces the burden of the network compared to the multiple passes of SQL commands.

II. basic operations 1. Define Stored procedures

#创建存储过程create procedure P1 () begin select * FROM T_user;end

2. Calling a stored procedure

#调用存储过程call P1 ();

3. View stored Procedures

#查看存储过程show procedure  Status;

4. Delete a stored procedure

#删除存储过程drop procedure P1;

5. Creating a stored procedure with parameters

#1. Creating a stored procedure with parameters CREATE PROCEDURE P2 (i int) begin SELECT * FROM T_user where ID > i;end#2. Calling stored procedure with parameters call P2 (3);

Results:

6. Create a stored procedure with a judgment statement

#1. Creating a stored procedure with a judgment statement create PROCEDURE P3 (I int,j char (TEN)) begin   If j = ' High ' then     select * from T_user where ID > i;  ElseIf  j = ' low ' thenselect * from T_user where ID < i;  End If;   End#2. Calling a stored procedure with a selection structure call P3 (3, "low");

Results:

7. Using the case command to use multiple conditional statements

#1. Using the case command multiple conditional branch CREATE PROCEDURE P4 (i int) begin case      I when     1 then  select * from T_user where address= ' Beijing '; w Hen 2 Then  select * from T_user where address= ' Shanghai '; else  select * from T_user where address= ' Jilin '; end case;end#2. See Results call P4 (2);

Results:

8. Create a local variable

#1. Creating a local variable CREATE PROCEDURE P5 (I int) BEGIN DECLARE temp varchar (ten); case I when  1 then Set temp = ' Beijing '; when 2 then set T EMP = ' Shanghai '; when 3 then Set temp = ' Jilin '; end Case;select * from T_user where address= temp;end#2. View Results Call P5 (3);

Results:

MySQL Learning Note (iv)-Stored procedures