Stored program

Sometimes, in order to complete a common function, many statements need to be executed. It is very annoying to enter so many statements one by one in the client each time. The uncle who designed MySQL kindly provided us with something called a stored program. This so-called stored program can encapsulate some statements, and then provide users with a simple way to call the stored program, so as to execute these statements indirectly. According to different calling methods, we can divide stored programs into stored routines, triggers and events. Among them, storage routines can be subdivided into storage functions and stored procedures. Let's draw a picture to show:

Although there are many strange concepts, don't be afraid, we will break them one by one. However, before formally introducing the stored program, we need to understand the concepts of custom variables and statement end delimiters in MySQL.

Introduction to custom variables

In life, we often encounter some fixed values, such as the number 100 and the string "hello". We call these fixed values constants. However, sometimes for convenience, we will use a symbol to represent a value, and the value it represents can be changed. For example, we specify that the symbol a represents the number 1, and then we can let the symbol a represent the number 2. We call everything where this value can change as a variable, and the symbol a is called the variable name of this variable. In MySQL, we can define our own variables through the SET statement, for example:

mysql> SET @a = 1;
Query OK, 0 rows affected (0.00 sec)

mysql>

The above statement shows that we have defined a variable called a and assigned the integer 1 to this variable. However, you should note that the uncle who designed MySQL stipulates that we must add an @ symbol in front of our user-defined variables (although it's a little strange, this is what others stipulate. Just abide by it).

If we want to check the value of this variable later, we can use the SELECT statement, but we still need to add an @ symbol before the variable name:

mysql> SELECT @a;
+------+
| @a   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql>

The same variable can also store different types of values. For example, we assign a string value to variable a:

mysql> SET @a = 'Ha ha ha';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @a;
+-----------+
| @a        |
+-----------+
| Ha ha ha    |
+-----------+
1 row in set (0.00 sec)

mysql>

In addition to assigning a constant to a variable, we can also assign a variable to another variable:

mysql> SET @b = @a;
Query OK, 0 rows affected (0.00 sec)

mysql> select @b;
+-----------+
| @b        |
+-----------+
| Ha ha ha    |
+-----------+
1 row in set (0.00 sec)

mysql>

In this way, variables a and b have the same value 'Wahaha'!

We can also assign the result of a query to a variable, provided that the result of this query has only one value:

mysql> SET @a = (SELECT m1 FROM t1 LIMIT 1);
Query OK, 0 rows affected (0.00 sec)

mysql>

You can also use another form of statement to assign the result of the query to a variable:

mysql> SELECT n1 FROM t1 LIMIT 1 INTO @b;
Query OK, 1 row affected (0.00 sec)

mysql>

Because the query results of statements SELECT m1 FROM t1 LIMIT 1 and SELECT n1 FROM t1 LIMIT 1 have only one value, they can be directly assigned to variables a or b. Let's look at the values of these two variables:

mysql> SELECT @a, @b;
+------+------+
| @a   | @b   |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

mysql>

If our query result is a record with multiple column values, we can only use the INTO statement to assign these values to different variables:

mysql> SELECT m1, n1 FROM t1 LIMIT 1 INTO @a, @b;
Query OK, 1 row affected (0.00 sec)

mysql>

The result set of this query statement contains only one record. We assign the value of m1 column of this record to variable a and the value of n1 column to variable b.

Statement end delimiter

At the interactive interface of MySQL client, when we complete keyboard input and press enter, MySQL client will detect whether the content we input contains\ G or \ g, if any, will send our input to the server. In this way, if we want to send multiple statements to the server at one time, we need to write these statements in one line, such as:

mysql> SELECT * FROM t1 LIMIT 1;SELECT * FROM t2 LIMIT 1;SELECT * FROM t3 LIMIT 1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
+------+------+
1 row in set (0.00 sec)

+------+------+
| m3   | n3   |
+------+------+
|    3 | c    |
+------+------+
1 row in set (0.00 sec)

mysql>

The reason for this inconvenience is that the symbol used by MySQL client to detect the end of input is the same as the symbol separating each statement! In fact, we can also use the delimiter command to customize the input end symbol of MySQL detection statement, that is, the so-called statement end separator, for example:

mysql> delimiter $
mysql> SELECT * FROM t1 LIMIT 1;
    -> SELECT * FROM t2 LIMIT 1;
    -> SELECT * FROM t3 LIMIT 1;
    -> $
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
+------+------+
1 row in set (0.00 sec)

+------+------+
| m3   | n3   |
+------+------+
|    3 | c    |
+------+------+
1 row in set (0.00 sec)

mysql>

The delimiter $command means that the delimiter at the end of the modified statement is $, that is, the MySQL client detects that the symbol at the end of the user statement input is $. In the above example, we input three semicolons in succession; At the end of the query statement and press enter, but the input content is not submitted. The MySQL client will not submit the content we entered to the server until the $symbol is knocked down and enter. At this time, the content we entered already contains three independent query statements, so three result sets are returned.

We can also redefine the statement end separator as a string containing one or more characters other than $, for example:

mysql> delimiter EOF
mysql> SELECT * FROM t1 LIMIT 1;
    -> SELECT * FROM t2 LIMIT 1;
    -> SELECT * FROM t3 LIMIT 1;
    -> EOF
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
+------+------+
1 row in set (0.00 sec)

+------+------+
| m3   | n3   |
+------+------+
|    3 | c    |
+------+------+
1 row in set (0.00 sec)

mysql>

We use EOF as the end symbol of MySQL client detection input. Isn't it easy! Of course, this is just to facilitate us to input multiple statements at one time. It's best to change back to our commonly used semicolon after input; Right:

mysql> delimiter ;

Tip: we should avoid using the backslash (\) character as the statement end separator, because this is the escape character of MySQL.