What variable types do mysql stored procedures have?

Variable types of mysql stored procedures: 1. Local variables, the definition syntax is "DECLARE variable name data type"; 2. User variables, the definition syntax is "set @variable name:=..."; 3 , system variables, which can be divided into global variables "@@global" and session variables "@@session".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

What are the variable types of mysql stored procedures?

Common variables in MySQL stored procedures: local variables, user variables, system variables

1. Local variables

In the process body, you can declare local variables to temporarily save some values.

1. Syntax for defining local variables:

DECLARE  var_name[, var_name] ...  type [DEFAULT value];

Among them, type is the data type of MySQL, such as: int, float, date, varchar(length)

Note:

①DECLARE is used to declare local variables, and DECLARE is only used in BEGIN ... END compound statements, and must be at the beginning of the compound statement, before any other statements; it can be used in nested blocks , except those blocks that declare variables with the same name.

② If you want to provide a default value for a variable, use the DEFAULT clause (the value can be a constant or specified as an expression); if there is no DEFAULT clause, the initial value is NULL.

2. Basic format:

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
BEGIN
DECLARE var_name1 type [DEFAULT value];
DECLARE var_name2 type [DEFAULT value];
DECLARE……;
[characteristic ...];

Example 1: Create a process, specify default values ​​for local variables, and call the process

mysql>delimiter $$
mysql> create procedure test1(out num1 int)->begin-> declare num2 int default 100;-> set num1=num2;->end->$$
mysql>delimiter ;
mysql>call test1(@num);
mysql> select@num;+------+
| @num |
+------+
| 100 |
+------+

Analysis:

Create the test1 stored procedure: the output is the value of the num1 variable. Declare the local variable num2 to be of int type and the default value is 100. Use set to assign the value of num2 (if not processed, the default value) to num1;

Call test1: Use the user variable num to receive the output value num1 of the stored procedure...

In addition to literal values, the default value can also be a complex expression, including a scalar subquery

Example 2: Creation process, using the number of all players to initialize a variable

mysql>delimiter $$
mysql> create procedure test2(out num1 int)->begin-> declare num2 int default(select count(*) fromPLAYERS);-> set num1=num2;->end$$mysql>delimiter ;
mysql>call test2(@num);mysql> select@num;+------+
| @num |
+------+
| 14 |
+------+

Note: MySQL does not support arrays as local variables.

3. The scope of local variables:

That is, the scope of the program block where the variable can be used normally without errors.

In the case of nested blocks,

Variables declared in the outer block can be used directly in the inner block;

Variables declared in the inner block can only Used inside blocks.

Analysis: Variable v2 can only be used in internal block b2. The set statement and the last set statement in block b3 are wrong.

2. User variables

>User variables are related to the database connection: variables declared in the current connection will disappear when the connection is disconnected; Variables declared in this connection cannot be used in another connection.

>An @ symbol represents a user variable.

1. Definition of user variables (set, select):

1) The set statement assigns values ​​to user variables:

You can use "=" or ":=" as Assignment character;

The expr assigned to each variable can be an integer, real number, string or NULL value;

mysql> set @zjc:=999;
mysql> select@zjc;+------+
| @zjc |
+------+
| 999 |
+------+

2) The select statement assigns a value to the user variable:

The allocation operator must be ":=" instead of "=", because = is regarded as a comparison operator in non-SET statements;

mysql> select @abc:=123;+-----------+
| @abc:=123 |
+-----------+
| 123 |
+-----------+mysql> select@abc;+------+
| @abc |
+------+
| 123 |
+------+

Note:

① User variables can be used anywhere If defined, it can be used anywhere; if not defined, it can be used directly (the value defaults to null).

②The format of the variable name of user variables: @var_name, with the @ symbol.

③Abuse of user variables will make the program difficult to understand and manage.

Extended variable assignment: set syntax

The variables in MySQL do not strictly limit the data type. The data type of the variable changes at any time according to the value assigned to the variable.

SET variable_assignment [, variable_assignment] ...
variable_assignment:
user_var_name=expr|[GLOBAL | SESSION] system_var_name =expr|[@@global. | @@session. | @@] system_var_name = expr

## is used to assign values ​​to user variables, system variables, and can also assign values ​​to local variables in the process.

Note:

When setting system variables, there is no scope modification, and the default refers to the session scope;

(Special note, some system variables do not have scope modification , cannot be set, so it is best to set system variables with scope).

2. The scope of user variables

As long as the user variables defined in the current connection are used in the current connection, it is OK, but nothing else.

3. Difference from local variables:

Local variables only have variable names and no @ symbol; user variable names have @ symbol before them.

They are defined first and then used; for undefined variables, the select value is empty.

Local variables are only used inside the stored procedure and have no meaning outside the process. When the begin-end block is processed, the local variables disappear; while user variables can be used inside and outside the stored procedure.

Tips: Inside the stored procedure, use local variables instead of user variables.

3. User variables and system variables of MySQL variables

1. User variables are as mentioned above@var_name (an @ symbol)

①User Variables are related to the database connection. They are declared after the connection and disappear automatically after the connection is disconnected;

②Select a user variable without assignment and return NULL, that is, no value;

Mysql variables are similar to dynamic languages, and the value of the variable changes with the type of value to be assigned.

2. System variables: According to the scope of system variables, they are divided into: global variables and session variables (two @ symbols)

①Global variables (@@global.)

When MySQL starts, the server automatically initializes global variables to their default values;

The default values ​​of global variables can be changed by changing the MySQL configuration file (my.ini, my.cnf).

②Session variables (@@session.)

Every time a new connection is established, it is initialized by MySQL;

MYSQL will store all current global variables Copy the value as a session variable (that is, if the values ​​of the session variable and global variable are not manually changed after the session is established, the values ​​of all these variables will be the same).

#The difference between global variables and session variables: Modifications to global variables will affect the entire server, but modifications to session variables will only affect the current session.

Recommended learning: mysql video tutorial

The above is the detailed content of What variable types do mysql stored procedures have?. For more information, please follow other related articles on the PHP Chinese website!