How to assign values ​​to variables in MySQL stored procedures

A stored procedure is a collection of pre-written SQL statements and is named. It can be used to complete a specific task or perform a specific action. Stored procedures in MySQL support multiple programming languages, including SQL, C, C, Java, etc. Among them, SQL is the most commonly used programming language. In a stored procedure, variables need to be used for data storage and processing. This article mainly introduces how to assign variables in MySQL stored procedures.

1. Define variables

In the MySQL stored procedure, you need to define a variable to store data. Variables play a very important role in stored procedures. The following is the syntax for defining a variable:

DECLARE 变量名 数据类型 [DEFAULT 默认值];

Among them, DECLARE is used to declare a variable. Variable name represents the name of the variable. The data type indicates the type of data that needs to be stored. DEFAULT is an optional parameter that means setting the default value of the variable.

For example, we can define a variable of type int:

DECLARE num INT DEFAULT 0;

We can define a variable of type string:

DECLARE str VARCHAR(255) DEFAULT '';

2. Assignment statement

The assignment statement of variables in MySQL is completed using the SET statement. It is used to assign a new value to a variable. The following is the syntax of a variable assignment statement:

SET 变量名 = 值;

Among them, the variable name indicates the name of the variable that needs to be assigned. value represents the value to be assigned.

For example:

SET num = 1;
SET str = 'hello, world';

3. Use variables

In the stored procedure, you can use variables to store and process data. The following is a simple example showing how to define a variable and assign a value to it:

DELIMITER $$

CREATE PROCEDURE test()
BEGIN
    DECLARE num INT DEFAULT 0;
    DECLARE str VARCHAR(255) DEFAULT '';

    SET num = 1;
    SET str = 'hello, world';

    SELECT num, str;
END $$
DELIMITER ;

CALL test();

Executing the above stored procedure will output the following results:

+------+--------------+
| num  |     str      |
+------+--------------+
|   1  | hello, world |
+------+--------------+

Summary

In MySQL stored procedures, variable assignment is a very common operation. Defining variables, assigning values ​​to variables, and using variables to process and store data are essential functions in stored procedures. The syntax introduced above is a variable assignment statement commonly used in stored procedures. I hope this article will help you learn variable assignment in MySQL stored procedures.

The above is the detailed content of How to assign values ​​to variables in MySQL stored procedures. For more information, please follow other related articles on the PHP Chinese website!