Stored procedures Detailed

Original

Log key syntax in the MySQL stored procedure:

DELIMITER//Declaration statement Terminator, for distinction;
Ceate PROCEDURE demo_in_parameter (in p_in int) declares a stored procedure
BEGIN .... End stored procedure opening and closing symbols
SET @p_in = 1 variable Assignment
DECLARE l_int int unsigned default 4000000; Variable definition

What is a MySQL storage routine?
A storage routine is a set of SQL statements stored in a database server that executes these SQL statement commands by invoking a specified name in the query.

Why you should use a MySQL stored procedure.
We all know that there are two kinds of applications, one is web-based, the other is based on the desktop, they are interacting with the database to complete the data access work. Assuming that there is one application that now contains both, and now to modify one of the query SQL statements, then we may have to modify their corresponding query SQL statements at the same time, when our application is very large and complex, the problem arises, it is difficult to maintain. In addition, placing SQL query statements on our web programs or desktops can be easily compromised by SQL injection. And the storage routines can help us solve these problems.
Stored procedures (stored procedure), storage routines (store routine), storage function differences
The MySQL storage routines actually contain stored procedures and stored functions, which are collectively called storage routines.
The stored procedure mainly completes the work of fetching or inserting records or updating records or deleting records, that is, select Insert Delete Update, and so on. The stored function only completes the work of the query, accepting the input parameters and returning a result.

Creating MySQL stored procedures, stored functions
Create procedure stored procedure name (parameters)
Stored Procedure Body
Create function store functions name (parameters)

The following is an example of a stored procedure:

Mysql> DELIMITER// 
mysql> CREATE PROCEDURE proc1 (out s int) 
   -> BEGIN
   -> SELECT COUNT (*) into S FR OM user; 
   -> End
   ->// 
mysql> DELIMITER;
     
     
      
      1
      
      2
      
      3
      
      4
      
      5
      
      6 7
     
     

Note:
(1) Here need to pay attention to is delimiter//and delimiter; two sentences, delimiter is the meaning of the separator, because MySQL defaults to ";" As a separator, if we do not declare a delimiter, then the compiler will treat the stored procedure as an SQL statement, the stored procedure will be compiled to the error, so the delimiter keyword to declare the current segment separator, so that MySQL will be ";" 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 according to the need, here is an output parameter s, the type is int, if there are more than one parameter with "," split open.
(3) The beginning and end of the process body is identified using begin and ends.
In this way, one of our MySQL stored procedures completed, is not very easy? It doesn't matter if you don't understand, then we'll explain it in detail. 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:
Createprocedure stored procedure name ([[In | Out | INOUT] Parameter name data class ...]
In input parameter: The value that represents the parameter must be specified when the stored procedure is invoked, and the value that modifies the parameter in the stored procedure cannot be returned, the default value
Out output parameter: This value can be changed inside the stored procedure and can be returned
INOUT input and OUTPUT parameters: Specified at call time, and can be changed and returned . In parameter example

Create:

1.  mysql > DELIMITER//  
2.  MySQL > CREATE PROCEDURE demo_in_parameter (in p_in int)  
3.       -> BEGIN   
4.       -> SELECT p_in;   
5.       -> SET p_in=2;   
6.       -> SELECT p_in;   
7.       -> end;   
8.       ->//  
9.  mysql > DELIMITER; 
     
     
      
      1
      
      2
      
      3
      
      4
      
      5
      
      6 7 8 9
     
     

Execution results:

1.  mysql > SET @p_in =1;  
2.  mysql > Call demo_in_parameter (@p_in);  
3.  +------+  
4.  | p_in |  
5.  +------+  
6.  |   1  |   
7.  +------+  
8.   
9.  +------+  
10.| p_in |  
11.+------+  
12.|   2  |   
13.+------+  
. 
15.mysql> SELECT @p_in;  
16.+-------+  
17.| @p_in |  
18.+-------+  
19.|  1    |  
20.+-------+  
     
     
      
      1
      
      2
      
      3
      
      4
      
      5
      
      6 7 8 9 of 13
      
      20 (
      
      MB
      
      )
      
      
     
     

as you can see, p_in is modified in the stored procedure, but does not affect the value of @p_id . Out parameter Example

Create:

  1. mysql > DELIMITER//2.       MySQL > CREATE PROCEDURE demo_out_parameter (out p_out int) 3.       -> BEGIN 4.  
-> SELECT p_out;  
5.-> SET p_out=2;  
6.-> SELECT p_out;  
7.-> end;  8.->//9. 
     
     
      
      MySQL > DELIMITER;
     
     1 2 3 4 5 6 7 8 9 

Execution results:

1.  mysql > SET @p_out =1;  
2.  mysql > Call sp_demo_out_parameter (@p_out);  
3.  +-------+  
4.  | p_out |   
5.  +-------+  
6.  | NULL  |   
7.  +-------+  
8.   
9.  +-------+  
10.| p_out |  
11.+-------+  
12.|   2   |   
13.+-------+  
. 
15.mysql> SELECT @p_out;  
16.+-------+  
17.| p_out |  
18.+-------+  
19.|   2   |  
20.+-------+  
     
     
      
      1
      
      2
      
      3
      
      4
      
      5
      
      6 7 8 9 of 13
      
      20 (
      
      MB
      
      )
      
      
     
     

InOut Parameter Example

Create:

1.  mysql > DELIMITER//   
2.  MySQL > CREATE PROCEDURE demo_inout_parameter (inout p_inout int)   
3.       -> BEGIN 
4.       -> SELECT p_inout;  
5.       -> SET p_inout=2;  
6.       -> SELECT p_inout;   
7.       -> end;  
8.       ->//   
9.  mysql > DELIMITER; 
     
     
      
      1
      
      2
      
      3
      
      4
      
      5
      
      6 7 8 9
     
     

Execution results:

1.  mysql > SET @p_inout =1;  
2.  mysql > Call demo_inout_parameter (@p_inout);  
3.  +---------+  
4.  | p_inout |  
5.  +---------+  
6.  |    1    |  
7.  +---------+  
8.   
9.  +---------+  
10.| p_inout |   
11.+---------+  
12.|    2    |  
13.+---------+  
. 
15.mysql > SELECT @p_inout;  
16.+----------+  
17.| @p_inout |   
18.+----------+  
19.|    2     |  
20.+----------+ 
     
     
      
      1
      
      2
      
      3
      
      4
      
      5
      
      6 7 8 9 of 13
      
      20 (
      
      MB
      
      )
      
      
     
     

Local Variables variable definition

Local variable declarations must be placed at the beginning of the stored procedure body
Declarevariable_name [, variable_name ...] datatype [DEFAULT value];
Among them, datatype 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 won't be padded ';   
     
     
      
      1
      
      2
      
      3
      
      4
      
      5
     
     

variable Assignment

SET Variable name = expression Value [, variable_name = expression ...] User variables use user variables on MySQL client

1.  mysql > SELECT ' Hello world ' into @x;  
2.  mysql > SELECT @x;  
3.  +-------------+  
4.  |   @x        |  
5.  +-------------+  
6.  | Hello World |  
7.  +-------------+  
8.  MySQL > SET @y= ' Goodbye cruel world ';  
9.  mysql > SELECT @y;  
10.+---------------------+  
11.|     @y              |  
12.+---------------------+  
13.| Goodbye Cruel World |  
14.+---------------------+  
. 
16.mysql > SET @z=1+2+3;  
17.mysql > SELECT @z;  
18.+------+  
19.| @z   |  
20.+------+  
21.|  6   |  
22.+------+  
     
     
      
      1
      
      2
      
      3
      
      4
      
      5
      
      6 7 8 9 of 13
      
      22 in all
     
     

using User variables in stored procedures

1.  mysql > CREATE PROCEDURE greetworld () SELECT CONCAT (@greeting, ' World ');  
2.  mysql > SET @greeting = ' Hello ';  
3.  mysql > Call Greetworld ();  
4.  +----------------------------+  
5.  | CONCAT (@greeting, ' World ') |  
6.  +----------------------------+  
7.  |  Hello World               |  
8.  +----------------------------+  
     
     
      
      1
      
      2
      
      3
      
      4
      
      5 6 7 8
     
     

passing Global-scoped user variables between stored procedures

1.  mysql> CREATE PROCEDURE p1 ()   SET @last_procedure = ' P1 ';  
2.  mysql> CREATE PROCEDURE p2 () SELECT CONCAT (' Last PROCEDURE is ', @last_procedure);  
3.  mysql> call P1 ();  
4.  mysql> call P2 ();  
5.  +-----------------------------------------------+  
6.  | CONCAT (' Last procedure is ', @last_proc       |  
7.  +-----------------------------------------------+  
8.  | Last procedure is P1                         |  
9.  +-----------------------------------------------+  
     
     
      
      1
      
      2
      
      3
      
      4
      
      5 6 7
      
      8
      
      9
     
     

Attention:
① user variable name usually begins with @
② misuse of user variables can cause programs to be difficult to understand and manage annotations

MySQL stored procedures can use two styles of annotations
Double-mode bar: –
This style is typically used for single-line annotations
C Style: General use for multiline annotations
For example:

  1. mysql > DELIMITER//